Problem in Access 2003 with Queries written in VBA

[JoyEakins via AccessMonster.com]

Hi -

I wrote code in Access 2007 and it works great there. Now I have a client in
Access 2003 and I'm trying to get the same code to work. Here's the problem -
I made a new form that allows my users to select filter criteria for their
main form. There are several different areas they can filter on. One is a
start or end date.

Here's the code:
If Not IsNull(Me.txtStartDate) And IsNull(Me.txtEndDate) Then
sWhere = sWhere & "tblEngagement.PeriodEnd >= " & Me.txtStartDate
ElseIf IsNull(Me.txtStartDate) And Not IsNull(Me.txtEndDate) Then
sWhere = sWhere & "tblEngagement.PeriodEnd <= " & Me.txtEndDate
ElseIf Not IsNull(Me.txtStartDate) And Not IsNull(Me.txtEndDate) Then
sWhere = sWhere & "tblEngagement.PeriodEnd Between #" & Me.
txtStartDate & "# AND #" & Me.txtEndDate & "# "
End If

If they enter BOTH date values, the resultant query pulls back the correct
data.

If however they only enter one value, the resultant query returns all the
values and almost "ignores" the criteria.

Here are the queries built on the fly in my VBA code:
SELECT DISTINCT tblEngagement.EngID , tblEngagement.PeriodEnd WHERE
(tblEngagement.PeriodEnd >= 9/30/2008)

I don't think it is the code because if I actually create the same query in
SQL view in Access 2003, I get the same behavior. Anyone see this before and
have an idea for solving it?

Any help would be appreciated!!!

Thanks,
Joy

 

[Stefan Hoffmann]

hi Joy,

Here's the code:
If Not IsNull(Me.txtStartDate) And IsNull(Me.txtEndDate) Then
sWhere = sWhere& "tblEngagement.PeriodEnd>= "& Me.txtStartDate
ElseIf IsNull(Me.txtStartDate) And Not IsNull(Me.txtEndDate) Then
sWhere = sWhere& "tblEngagement.PeriodEnd<= "& Me.txtEndDate
ElseIf Not IsNull(Me.txtStartDate) And Not IsNull(Me.txtEndDate) Then
sWhere = sWhere& "tblEngagement.PeriodEnd Between #"& Me.
txtStartDate& "# AND #"& Me.txtEndDate& "#"
End If

If they enter BOTH date values, the resultant query pulls back the correct
data.

If however they only enter one value, the resultant query returns all the
values and almost "ignores" the criteria.

Take a closer look at your code: In your failing cases you have
forgotten the Date/Time literal delimiters (#).

Using a helper function makes life easier:

Public Function SqlDateTime(ADateTime As Date) As String

SqlDateTime = Format(ADateTime, "\#m\/d\/yyyy hh\:nn\:ss\#")

End Function


Use it as

If Not IsNull(Me.txtStartDate) And IsNull(Me.txtEndDate) Then
sWhere = sWhere & _
"tblEngagement.PeriodEnd >= " & _
SqlDateTime(txtStartDate.Value)

ElseIf IsNull(Me.txtStartDate) And Not IsNull(Me.txtEndDate) Then
sWhere = sWhere & _
"tblEngagement.PeriodEnd <= " & _
SqlDateTime(txtEndDate.Value)

ElseIf Not IsNull(Me.txtStartDate) And Not IsNull(Me.txtEndDate) Then
sWhere = sWhere & _
"tblEngagement.PeriodEnd Between " & _
SqlDateTime(txtStartDate.Value) & " " & _
"AND " & _
SqlDateTime(txtEndDate.Value)

End If


mfG
--> stefan <--

 

[JoyEakins via AccessMonster.com]

Stefan -

Thanks! DUH! I got sloppy today - can I blame it on the time change?.
Thanks for the quick reply and adding the delimiters (#) fixed the problem. :
-)

Joy