Hello

I am running reports, and enabling the user to set the criteria via a form.

The criteria works well, except when I tried adding a date range (See
Below). Not quite sure what the syntax is.

Any help would be much appreciated.

Thanks
AC
' THIS DOES NOT WORK '
If Not IsNull(Me.StartDt) And Not IsNull(Me.EndDt) Then
strWhere = strWhere & "([ProposalDate] Between """ & Me.StartDt And
Me.EndDt & """) AND "
End If

' THIS WORKS '
If Not IsNull(Me.cmb_RequestType) Then
strWhere = strWhere & "([RequestType] = """ & Me.cmb_RequestType & """)
AND "
End If

Database is what? If you debug.print the SQL what does it look like, and
can you run it in another query tool?
What error do you get?

Details elicit suggestions...

Tim

--
Tim Williams
Palo Alto, CA


"ACase" wrote in message
...
Hello

I am running reports, and enabling the user to set the criteria via a
form.

The criteria works well, except when I tried adding a date range (See
Below). Not quite sure what the syntax is.

Any help would be much appreciated.

Thanks
AC
' THIS DOES NOT WORK '
If Not IsNull(Me.StartDt) And Not IsNull(Me.EndDt) Then
strWhere = strWhere & "([ProposalDate] Between """ & Me.StartDt And
Me.EndDt & """) AND "
End If

' THIS WORKS '
If Not IsNull(Me.cmb_RequestType) Then
strWhere = strWhere & "([RequestType] = """ & Me.cmb_RequestType &
""")
AND "
End If

ACase,

Although i don't understand the specifics of your problem, I may be able to
shed some light on the formating with some examples:

strWhere = "1" 'defines old/existing string
strJunk = "3" 'defines another string variable

strWhere = strWhere & "2" & strJunk & str(2+2) & "5"
'combines the old string and the number 2 and another string
'and the result of str(2+2) and the number 5

MsgBox strWhere 'shows the output of 12345

Christmas May


"ACase" wrote:

Hello

I am running reports, and enabling the user to set the criteria via a form.

The criteria works well, except when I tried adding a date range (See
Below). Not quite sure what the syntax is.

Any help would be much appreciated.

Thanks
AC
' THIS DOES NOT WORK '
If Not IsNull(Me.StartDt) And Not IsNull(Me.EndDt) Then
strWhere = strWhere & "([ProposalDate] Between """ & Me.StartDt And
Me.EndDt & """) AND "
End If

' THIS WORKS '
If Not IsNull(Me.cmb_RequestType) Then
strWhere = strWhere & "([RequestType] = """ & Me.cmb_RequestType & """)
AND "
End If

Sorry for the confusing post.

I have a report form, which lets a user select a report and criteria for
that report (Country, Region, Department......)

Once a user has runs the report the 'Where' clause is created dynamically in
the code.

Example - In the code I have a series of statements which will check each
and every combo box. If the combo box is null it gets ignored, if it is
populated (COuntry = USA) then the criteria is added to the 'Where' clause.

If Not IsNull(Me.cmb_Country) Then
strWhere = strWhere & "([Country] = """ & Me.cmb_Country
End If

I now wanted to add the ability to select a date range as a filter. I don't
know how to write the syntax.

My 2 combo boxes on the user form are cmb_StartDate & cmb_EndDate.

What is wrong with this syntax?
If Not IsNull(Me.cmb_StartDate) and IsNull (Me.cmb_EndDate) Then
strWhere = strWhere & "([Date] = between """ & Me.cmb_StartDate and
Me.cmb_EndDate.
End If

Thanks
AC
"Christmas May" wrote:

ACase,

Although i don't understand the specifics of your problem, I may be able to
shed some light on the formating with some examples:

strWhere = "1" 'defines old/existing string
strJunk = "3" 'defines another string variable

strWhere = strWhere & "2" & strJunk & str(2+2) & "5"
'combines the old string and the number 2 and another string
'and the result of str(2+2) and the number 5

MsgBox strWhere 'shows the output of 12345

Christmas May


"ACase" wrote:

Hello

I am running reports, and enabling the user to set the criteria via a form.

The criteria works well, except when I tried adding a date range (See
Below). Not quite sure what the syntax is.

Any help would be much appreciated.

Thanks
AC
' THIS DOES NOT WORK '
If Not IsNull(Me.StartDt) And Not IsNull(Me.EndDt) Then
strWhere = strWhere & "([ProposalDate] Between """ & Me.StartDt And
Me.EndDt & """) AND "
End If

' THIS WORKS '
If Not IsNull(Me.cmb_RequestType) Then
strWhere = strWhere & "([RequestType] = """ & Me.cmb_RequestType & """)
AND "
End If