Query by Form

[Lyncroft]

I've only just realised that I need to change the format of the dates when using SQL. I picked up the info as below from a previous thread but keep getting an error message 3075.

It works fine when I do a search including the date field but not when I don't want to include the date field.

EG, when I leave all the fields empty the msgbox says "Select * ..............where [date] =# #. That date bit shouldn't appear. This is driving me nuts.


where = Null
where = where & " AND [Name]= '" + Me![Name] + "'"
where = where & " AND [Subject]= '" + Me![Subject] + "'"
where = where & " AND [Date]= #" + Format(Me![Date], "mm/dd/yyyy") & " #"

MsgBox "Select * from table1 " & (" where " + Mid(where, 6) & ";")
Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select * from table1 " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query"

 

[DES]

Need an If Statement

try:

where = Null
where = where & " AND [Name]= '" + Me![Name] + "'"
where = where & " AND [Subject]= '" + Me![Subject] + "'"
if Not IsNull([Date]) then
where = where & " AND [Date]= #" + Format(Me![Date], "mm/dd/yyyy") & " #"
end if

And let me know how it works.

 

[Lyncroft]

Des - Magic!!! Thanks ever so much for that. Spent hours trying to figure that one out!!! And it's so easy when you know if you know what I mean.

Can go off and watch QPR play football happily now!

Have a good one!