Query from dates/comboboxes

I have a form that has 6 combo boxes. Each combo box represents a part of a date. eg combo1 = months, combo2=days, combo3= year and it repeats for 4,5,6. The user chooses if they want to search before a date, after, and in between. When in between is selected boxes 4, 5 ,6 are enabled. My problem is how to get it to work and accept null values. They way I have done it is replace null values with a number. On some numbers it works and others it doesn't. Same goes for some of the combo boxes. I also keep getting this error:
Syntax error in date in query expression 'Xdate < #2/31/1992#'

When I look at the entire SQL statement it looks correct. I think it might be from the format of the table it gets data. Thanks for your help in advance. Also if need code let me know it is slightly big.

theguz

To query using dates a valid date format must always be used and the "#" sign before and after the variable. To set a default value for a null, you can use the Nz function;

Example if Combo1 is a month and is null, the current month can be inserted in place of the null like this:
Nz(Combo1, Format(Date,"m")) which will return the current month as the valid month format for the date string.

Standard criteria for the date formats in queries will be
strStart = "#" & startdate & "#"
strEnd = "#" & enddate & "#"

So your criteria WHERE clause would look something like this
"WHERE DateField BETWEEN " & strStart & " And " & strEnd

Hope this clears things up
lwells

Thanks again for the response!! That is pretty much what I did. For each combo box value I assigned a variable like this:

date1 = combo1.Value 'I did this for all 3 dates for criteria
'and I gave a value to those that are null eg

date2 = 31 'Assuming this is one is null (day)
to develop my SQL I did this:

"SELECT * FROM [Gendata] WHERE"& _
" XDATE > #" & date1 & "/" & date2 & "/" & date3 & "# ;"

However it is giving me errors only on certain numbers (like 2) and certain combo (like day). I check in the immediate window what my SQL is and it looks correct. But the compiler doesn't think so. Oh well always a learning experience. Also do you know of a good website to help with VBA? Thanks again
theguz

Try changing the syntax slightly

"SELECT * FROM [Gendata] WHERE "& _
" XDATE > " & "#" & date1 & "/" & date2 & "/" & date3 & "#"

Remember all dates(1,2,3) need to have a valid date format
Date1 Months numbers 1 To 12
Date2 Days numbers 1 To 31
Date3 Years numbers 1899 to 2099 - maybe higher not sure of the max year

Thanks again. How does VBA compiler move through the code. Does it go sequentially through? My compiler is acting weird. Consider this bit if code:
If IsNull(Combo2) Then
If Frame14 = 1 Or Frame14 = 3 Then 'These allow user to choose either before or after
date2 = 31
End If

If Fram14 = 2 Then 'This allows for after the date choosen
date2 = 1
End If
Else
date2 = Combo2.Value 'If it isn't null it skips here
End If 'and date2 = 0

I have no idea what it is doing. Sometimes it says this SQL statement is bad:
SELECT * FROM [GenData] WHERE XDATE < #2/31/1991#;
and sometimes it doesn't. I am considering erasing all of my code and starting over.

I think I understand what you are wanting to do here, but lets look at the code you posted:

If IsNull(Combo2) Then
If Frame14 = 1 Or Frame14 = 3 Then 'These allow user to choose either before or after
date2 = 31
End If

If Fram14 = 2 Then 'This allows for after the date choosen
date2 = 1
End If
Else
date2 = Combo2.Value 'If it isn't null it skips here
End If 'and date2 = 0

In the above you are wanting to assign a value to Date2 depending on what selection was made in what I believe is Frame14 however you show it as Fram14 and Frame14 in your code...which one is it? Also you are stating that if the combo2 is Null (run the procedure if True) else skip the entire procedure which is why your last else statement can never be executed...Date2=Combo2.Value because the entire statement is false if the Combo2 is not Null.

A better way to evaluate the condition would be to use a Select statement rather than trying to evaluate several If statements

In the AfterUpdate event for Frame14 or Fram14 however it is named

Select Case Frame14
Case 2
If IsNull(Combo2) Then
Date2 = 1
End If
Case Else
If IsNull(Combo2) Then
Date2 = 31
End If
End Select

See if this clears things up.

lwells

Thanks again for the help. The only way I got the stupid thing to work was by the select statement . However, Now I am confused, since I added the else after the second end if in the code shouldn't that not ignore the rest of the code? Souldn't it if the box isn't null then take its value. I am not questioning you just trying to understand how this crazy language works. And sorry that should be frame14, I am horrible at proof reading.

Hi theguz

Why don't you take a look at the attached sample database for some ideas. It's based along the ideas of what you are trying to do.

lwells