Query that also includes records with NULL values

[Chuck]

Table1:

Col1 Col2 Col3 Col4
1 2 3 4
3 1 5 null
4 3 2 2


SQL Statement:

Select Table1.Col1, Table2.Col2
From Table1
Where Col3 > 3 OR Col4 > 3


Results wanted:

1 2 (row 1)
3 1 (row 2)


Results Obtained:

1 2 (Row 1 only)


How can I get the row that contains a null value for one of the records to
be included?

Thanks

 

[Ted Allen]

Hi Chuck,

In general you can avoid problems with null values by
using the Nz() function, which will convert Nulls to a
specified value. In your case you could replace the Col4
entry with Nz(Col4,0), which will cause all Null entries
to be replaced with 0's.

Post back if that doesn't work.

-Ted Allen

 

[Chuck]

Thanks. I knew about the Nz function and had tried it but it still was not
working. After trying different scenarios, I saw why it was not working.
My criteria was actually based on a function calculated from three columns.
I was entering the values for the three columns and then running the query.
The query did not return the record for which I had just added data. Only
when I moved to another record did the previous record show up in the query
results. I assume that the values I had entered were not actually saved and
the function did not work. When are changes to a field actually commited?
I thought it was as soon as you exited the field in question!

 

[Ted Allen]

Hi Chuck,

Changes are usually posted when you move to another
record. The control values in a form are updated as you
move off of them, but the actual field values in the
underlying table are not updated until the entire record
is posted when moving to another record, or saving
manually (by clicking on the little pencil in the corner
of the form or saving from the menu - which I can't
remember the exact command name for because I always
click the pencil if I have a need to force a save).

-Ted Allen