Have a query which shows all fields from two tables. One table is much
shorter than the other, so several field values are Null values.
I would like to have these set to zero, rather than Null.
Could someone please tell me how to do this?
Thanks in advance >
--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

My thought was something like this, but I don't know the exact syntax, and
also, help says to insert this statement in the control source field. Is
that available for a query? Isn't in field properties

IIF([field]=Null,fieldvalue,0)

???

--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

"What-a-Tool" <Die!FrigginSpammersDieDie!@NoShitSherlock.Net> wrote in
message news:lellb.92595$0Z5.3455 (AT) lakeread03 (DOT) ..

Try something like this:

Update tblSomething
Set field1 = 0
Where IsNull(field1)

"What-a-Tool" <Die!FrigginSpammersDieDie!@NoShitSherlock.Net> wrote

Worked out a way to do it, but it seems like a really round-about way :

Took Quantity field from 2 tbls, one with many records, one with few.

Created query to show all from long tbl, but only matching from short, which
leaves many Null value fields in tblShort Cloumn.

Created "Make Table" query to transfer this to a table.

Created Update Query to change value of tblShort Quantity list to zero if
"Is Null" in newly created table.

Created new "Select Query" to read this data and calculate a difference
between these 2 quantity columns.

Run these all from a macro, which leaves my newly updated query with up to
date difference values open.

Is there an easier way? Don't like clicking "yes" to all those warnings that
appear during the run of this Macro.

--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)


"What-a-Tool" <Die!FrigginSpammersDieDie!@NoShitSherlock.Net> wrote in
message news:lellb.92595$0Z5.3455 (AT) lakeread03 (DOT) ..

First, you can set the SetWarnings = False in code or a macro to stop the
messages.

Second, why not just put the field ..

ReplaceNull: iif( IsNull([myField]), 0 [myField] )

in your query to return a zero if the field is null?


--
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/response

"What-a-Tool" <Die!FrigginSpammersDieDie!@NoShitSherlock.Net> wrote in
message news:aZ%lb.98976$0Z5.472 (AT) lakeread03 (DOT) ..

Aaaaahhhh! I see said the blind man!
After reading your tips, it came to me in a flash!
Got rid of my macro and all my extra queries and tables and replaced them
with one IIF statement.
I knew there had to be an easier way!

Thanks! Thanks! Thanks!

Maybe you could help with another problem.
I bring a calculated field from another query into a query.
Every time I run query2, I get an input box asking for Parameters for this
field. What is this, and how can I get rid of it?
Thanks again.

--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

"Bradley" <bradley (AT) REMOVETHIScomcen (DOT) com.au> wrote

Figured out the "Parameter" problem. Just had one level too many of "Group
By". Eliminated this, and the request for parameter input went away.

--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

"What-a-Tool" <Die!FrigginSpammersDieDie!@NoShitSherlock.Net> wrote in
message news:6Lhmb.99032$0Z5.10519 (AT) lakeread03 (DOT) ..