Syntax Error

[forms_are_nightmares]

Hope someone can help as my eyes are going cross...

Scenario:

Have multiple combo boxes (filters) that are triggered when a user runs a report. All work correctly. the ability to add a date range was requested. I searched this forum and found a link to allen browne's site. I added the code and I get a syntax error on the following code (I attached a screen shot of the error):

If IsDate(Me.txtStartDate) Then
sWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ") "
End If

If IsDate(Me.txtEndDate) Then
If sWhere <> vbNullString Then
sWhere = sWhere & " AND "
End If
sWhere = sWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ") "
End If


Thanks...

 

[JANR]

if you look at the error you'll see that the enddate is trunctuated and missing a # at the end.
....[adate] < #06/02/20)

so focus on that.

perhaps:

sWhere = sWhere & "(" & strDateField & " < " & Format([COLOR=red]([/COLOR]Me.txtEndDate[COLOR=red])[/COLOR] + 1, strcJetDate) & ") "

JR

 

[forms_are_nightmares]

I did notice that. I also tried your suggestion with no luck. I can't figure out why it's truncating the end date.

 

[JANR]

I tried to duplicate your problem but coulden't do it. in my example it prodused a good sWhere-string.

my test code with some guessing of your variables:

Private Sub Kommando7_Click()
Dim sWhere As String                  'The criteria string.
Const strcJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
Dim strdatefield As String
strdatefield = "[Adate]"
 
If IsDate(Me.txtStartDate) Then
    sWhere = "(" & strdatefield & " >= " & Format(Me.txtStartDate, strcJetDate) & ") "
End If
 
If IsDate(Me.txtEndDate) Then
    If sWhere <> vbNullString Then
        sWhere = sWhere & " AND "
        sWhere = sWhere & "(" & strdatefield & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ") "
    End If
End If
 
Debug.Print sWhere
End Sub

the Debug.Print gave me this string:

([Adate] >= #08/18/2010#)  AND ([Adate] < #08/21/2010#)

So, either your variable txtEndDate is NOT formatted as ShortDate or your variable strdatefield is the problem.

perhaps you could put a breakpoint in your code and when it breaks hover over your variables and see what their value are.

JR

 

[forms_are_nightmares]

The Adate wasn't formatted properly. Seems to be working now. Thanks for all your help

 

[JANR]

No problem.