Invalid Use Of Null In Query

[Darknight850]

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

[jamjar]

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

[Darknight850]

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.

[jamjar]

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.

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

[Darknight850]

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

[jamjar]

Can you post the SQL of your query?

James

[NeoPa]

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 ;)

[NeoPa]

well i got it to work, with the Is Not Null fuction, I put it in the Weekstart string and it worked. So thank you very much for all the help. Saddly i have another small problem that doesn't make sence.


The new problem I have is that this was my last big thing, so I updated all of the data in my table and so all the reports do not have any records in them yet. So all of my total counts are 0 in my reports but instead of 0 they are Errors for some wierd reason, and i am not sure why at all, but my macros and some delete querys stoped working becouse it is returning an Error insted of 0. I have got caught way off gaurd with that.

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.