SQL null dates

[myrt]

Hello. I tried but couldn't make a sql line work. If you give me a hand, I'd be very very grateful.

I'd like to put a WHERE clause where I confont the date in an unbound textbox with the date in the table. In vba I control the null case of the unbound textbox, but I'm not sure how to control the null value in the sql code.

here is the code "strDataRiferiment" that I later insert in a concatenated SQL code. The rest of the code works, while this line doesn't. The problem are the null date records present in the table COMMESSE.

What I'd like is a WHERE clause that:
- returns records with all null date values and
- returns records with not null values where these dates are < date chosen in the unbound textbox

Select Case Nz(Me.InizioDataRif.Value, "Null") 'the unbound textbox
       Case "Null" 
            strDataRiferimento = "AND (([if is not null] COMMESSE.DataChiusura =< #" &  Format(Me.FineDataRif, "mm\/dd\/yyyy") & "#)  OR (COMMESSE.DataChiusura IS NULL)) "
       Case 'etc
       'etc..
end select

I tried for example:

"AND ( IFF ((COMMESSE.DataChiusura,0) IS NOT NULL,  
(COMMESSE.DataChiusura,0) =< #" & Format(Me.FineDataRif, "mm\/dd\/yyyy") & "#) , (COMMESSE.DataChiusura) IS NULL) )) "

Help?

 

[pbaldy]

More like

(DataChiusura <= #4/30/2015# Or DataChiusura Is Null)

 

[myrt]

More like

(DataChiusura <= #4/30/2015# Or DataChiusura Is Null)

I believe I tried it already.
I imagine that if the date analysed happens to be null then access can't understand if the where clause is to be applied or not: to be applied the clause has to be true in the first half (<) or the second half (null). If I have a null value, I can't determine if it's < #date# ot not. This means I can't tell if the first half is true or not. Thus the entire where clause becomes unsure. This is my understanding. Correct me if I'm wrong.

 

[pbaldy]

Please try again; it should work.

 

[myrt]

Please try again; it should work.

Hi! Thanks for your suggestions. At your insitence I tried again, however the code doesn't return me any record, not even the blanc ones.

I tried:

 strDataRiferimento = "AND (((COMMESSE.DataChiusura) =< #" & Format(Me.FineDataRif, "mm\/dd\/yyyy") & "#) " & _
                                   "OR  ((COMMESSE.DataChiusura) IS NULL)) "

strDataRiferimento = " AND (1) OR (2) "

I tried and the (2) null part alone works.
Also, I know that the (1) date part works ( I'm using it in this same SQL code some line prior referenced to a data entry without null values), so I'm sure that the date disugualiance itself works. I had to put the format part because the date textbox is unbound, hence Access becomes confused with 09/04/2015 date since it could be either dd/mm/yyyy or mm/dd/yyyy.

Thus I assumed that the problem stems from the null values, as I told in my previous post. (Of course if I wrote everything without vba/sql grammar mistakes )

 

[pbaldy]

If you're integrating this into other criteria, the parentheses I had in my post become important (which is why I included them). Using your example, this would have to be:

strDataRiferimento = " AND (1 OR 2) "

Looks like you've copied SQL from the design builder, which is fine but it adds numerous unnecessary parentheses, which confuses the issue. You might try getting rid of all the extra parentheses, and use this method to see how the finished SQL is coming out:

http://www.baldyweb.com/ImmediateWindow.htm

 

[myrt]

I regularly add spaces at the end of the string (in case of variables, at the beginning) and the parenthesis are counted (since that's the first mistake I made. Now I pay attention ).
it's WHERE ( ..etc..."AND ( (1) OR (2) ) " ...etc...)

 

[pbaldy]

I'm not saying the parentheses aren't counted correctly, I'm saying the logic they result in may be incorrect. I would delete them so the logic is clearer. In other words there is a difference between

WHERE 1 And 2 Or 3

and

WHERE 1 And (2 Or 3)

When you have all the extra parentheses in there, it's hard to figure out what the actual logic is.

Besides, it looks like Italian, which would explain why it's messed up.

Just kidding, my heritage is Italian.

 

[myrt]

I'm not saying the parentheses aren't counted correctly, I'm saying the logic they result in may be incorrect. I would delete them so the logic is clearer. In other words there is a difference between

WHERE 1 And 2 Or 3

and

WHERE 1 And (2 Or 3)

When you have all the extra parentheses in there, it's hard to figure out what the actual logic is.

Besides, it looks like Italian, which would explain why it's messed up.

Just kidding, my heritage is Italian.

ps. the messiness of the code is due to my inexperience wih vba and partly to the feedback system of choice - a simple msgbox(StrSQL).

I tried to delete the paranthesis around the field name. (Unfortunately) the result doesn't change. In my post #5 I wrote the problematic line. I wrote down my suspitions. I can guarantee that the rest of the messy code runs smoothly.
Could I ask how come you're so sure the problem lies in the logic of the clause? Have you tested it in a test form somewhere? Because if you can assure me 100% that null values run fine in an inequality comparison, tomorrow -with a clear hear - I'll clear all the redundant parenthesis. It's just that it's not really possible to mess up logic in a line such as "AND (...OR...) " , but maybe it's just the late hour

 

[pbaldy]

Yes, I tested in a simple situation. Can you post your db here?

 

[JHB]

You've two other criteria in the Where clause, "SeachText" and "SeachText2", have you filled something in them too?

 

[myrt]

I discovered the problem!
I had to write <= instead of =< . Now it works.
Pbaldy, you were right. Null values wasn't the issue at all. Thanks!

 

[pbaldy]

Happy to help!