By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,699 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,699 IT Pros & Developers. It's quick & easy.

Invalid Use Of Null In Query

P: 32
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
May 23 '07 #1
Share this Question
Share on Google+
7 Replies

jamjar
P: 50
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
May 23 '07 #2

P: 32
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.
May 23 '07 #3

jamjar
P: 50
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
May 24 '07 #4

P: 32
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
May 24 '07 #5

jamjar
P: 50
Can you post the SQL of your query?

James
May 24 '07 #6

NeoPa
Expert Mod 15k+
P: 31,770
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 ;)
May 24 '07 #7

NeoPa
Expert Mod 15k+
P: 31,770
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.
May 25 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.