convert null data

[pazgb]

I came upon an error when running a couple queries. I get cant use "qryname" as a row source? I believe it has something to do with null values in my tables. Is there a way to check to see if null value. I also get the set 1 field to null data type converison error. This might help too, In my critiera it pulls all the data from our fiscal year calander. The only thing is it takes a few days for the person to enter the data in, so we just started a new period on june 29th and the last data was entered in on june 26th. so some how i have to check to make sure data was entered after the start of the period.

query sql example:

SELECT Count(*) AS NumRetailTisPackCon
FROM qry_PTD, tbl_Tissue INNER JOIN tbl_TisPack ON tbl_Tissue.ID = tbl_TisPack.ID
WHERE (((tbl_Tissue.Date1)>=[qry_PTD]![start_date] And (tbl_Tissue.Date1)<=[qry_PTD]![end_date]));

 

[scriverb]

You should use the NZ function to check the field value for null and then convert it to something useable.

NZ([fieldname],value to substitute)

The red code can be whatever you want to be substituted for the Null value of the fieldname.




Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[pazgb]

Thank you for helping me Bob,

can you give me an example on how you can use this function in sql query?

 

[scriverb]

Analyzing a field and substituting an default value for a null text field value:
Select A.ID, A.Name, NZ(A.[CountryOfOrigin],&quot;CountryOfOrigin Not Known&quot as CityOfBirth
FROM tblCustomers as A;

I hope this helps you to understand this function.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???