Invalid Use Of Null In Query

I am new to this forum and wanted to say Hello to every one, and i hope i can help every one as much as they can help me.
I have made a query for weekly reports and i have used this to give me the weeks to look like what i wanted instead of just the number of week it is:
WeekPeriod: CStr([mydate]-Weekday([mydate],6)+1) & " - " & CStr([mydate]-Weekday([mydate],6)+7)

but becouse this comes back as text i group on this:
WeekStart:[mydate]-Weekday([mydate],6)+1

My Problem is that in my dates of Arrivals field that i am basing this off of, I have values that are empty(Null) but they need to stay that way, I don't want to count thoughts in the weekly report. I have tried a number of steps and keep getting either an #error or the big Invalid Use Of Null error every time I try to run this query. If any one could help on this problem it would be greatly appreciated

Thank You

re: Invalid Use Of Null In Query

Do you need the null value records? If not, can you filter them out ('Is Not Null')?

Have you tried using Nz (null to zero) - that should eliminate the null value error but may not give you a sensible result for WeeklyPeriod!

cheers-
James

re: Invalid Use Of Null In Query

I tried putting Is Not Null in critera but it gave me the same error, and i am not sure were to use the NZ.

re: Invalid Use Of Null In Query

You can put the Nz around your date expression:

Nz([mydate])

everywhere you use it.

I'm not sure why the Is Not Null is not working. If you run the query with the [mydate] field displayed, are you getting any results where it is null? Or does the query not run at all?

James

re: Invalid Use Of Null In Query

The IS Not Null worked if i put the date of arrival (My date) in the query, but it shows double ex: if 2 ppl came in on 24-may-07 then it has to strings, its not grouping them together. and the NZ([Date of arrival]) on each one of them keeps giving me errors, missing operators mostly, even if i enclose it again with parentheses

re: Invalid Use Of Null In Query

Can you post the SQL of your query?

James

re: Invalid Use Of Null In Query

The second parameter of Nz() is the default you want to use if it finds a Null value. You may want to set this to a standard date such as Date() (=Today).
Nz() will typically work by inference with strings or numbers. It may have more difficulty with a Date/Time value so you will need to provide the default parameter.

PS. It wouldn't hurt to post the SQL anyway ;)

re: Invalid Use Of Null In Query

Before I try to answer this one, I will split this into another thread (Report Shows #Error When Recordset Empty). Things get messy and difficult to search when multiple questions are mixed in together.

MODERATOR.