Date search syntax

[smalty]

I have a form with several text boxes into which I enter search criteria which will be applied to a query that in turn will update a form based on the result of the query.
All of the boxes are working fine except the one where I enter a date value in 'short date format ie 06/01/2011', and I want the result to be greater than or equal to this date.
The text box is qdte which updates field PriceFrom. This forms pat of a filter. I am using code:

Private Function BuildFilter() As Variant
Dim varWhere As Variant

varWhere = Null


If Me.qdte > "" Then
varWhere = varWhere & "[PriceFrom] >= " & Format(Me.qdte, "dd-mm-yyyy") & " AND "
End If

If Me.qprtr > "" Then
varWhere = varWhere & "[Printer] LIKE """ & Me.qprtr & "*"" AND "
End If

If Me.qpsize > "" Then
varWhere = varWhere & "[PaperSize] LIKE """ & Me.qpsize & "*"" And "
End If

If Me.qlam > "" Then
varWhere = varWhere & "[Laminate] LIKE """ & Me.qlam & "*"" And "
End If

If Me.qnofpages > "" Then
varWhere = varWhere & "[NoOfPages] LIKE """ & Me.qnofpages & "*"" And "
End If

If Me.qptype > "" Then
varWhere = varWhere & "[PaperType] LIKE """ & Me.qptype & "*"" And "
End If

If Me.qpweight > "" Then
varWhere = varWhere & "[PaperWeight] LIKE """ & Me.qpweight & "*"" And "
End If

If Me.qqty > "" Then
varWhere = varWhere & "[Quantity] LIKE """ & Me.qqty & "*"" And "
End If


' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function

It's the first part of the filter referring to Me.qdte that doesn't work. I have tried many other variations of the syntax without success. I either get a syntax error or a runtime error.

Any suggestions would be much appreciated

Smalty

 

[dhookom]

Try:

If Not IsNull(Me.qdte) Then
varWhere = varWhere & "[PriceFrom] >= #" & Format(Me.qdte, "dd-mm-yyyy") & "# AND "
End If

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

I'd try this instead:
varWhere = varWhere & "PriceFrom>=#" & Format(Me!qdte, "yyyy-mm-dd") & "# AND "

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

PH,
Good catch. I guess I didn't notice the format string

Duane
Hook'D on Access
MS Access MVP

 

[smalty]

Many thanks to both you guys. It worked or course although I fail to see why. I mean why would your FORMAT of yyyy-mm-dd be interpreted by the application as dd-mm-yyyy. I am extremely happy that this is now working but I confess to be a little confused. You may have guessed that I do not code normally and rely heavily on guys like yourselves and forums like this to assist, but I do try to learn rather than just DO so if you have the time I would appreciate an explanation or point me towards a site that might explain in details the whys and wherefores on date formatting in code.

Once again many thanks for your help

Smalty

 

[dhookom]

The date format for queries is typically m/d/y (US format) rather than d/m/y. If you use yyyy/mm/dd, there should not be any confusion. Allen Browne has some additional information.

Duane
Hook'D on Access
MS Access MVP