Use combo to print report between two sets of dates

  • Thread starter Thread starter MoonBlosm
  • Start date Start date
M

MoonBlosm

I am trying to print a report that shows timesheet entries for two sets of
dates. The report also prints out based on the data entry person selected.
The user selects their name and then uses another combo box to select pre
entered dates.

My code is as follows:

Private Sub cboDates_Click()

Dim stDocName As String
Dim FromDate As Date 'Name of criteria start timesheets date field.
Dim ToDate As Date 'Name of criteria end timesheets date field.
Dim FromOTDate As Date 'Name of criteria start OT date field.
Dim ToOTDate As Date 'Name of criteria end timesheets date field.
Const conDateFormat = "\#mm\/dd\/yyyy\#"
Dim strWhere As String


'DataEntry Combo
If Not IsNull(Me.cboDataEntry) Then
strWhere = strWhere & "([DataEntry] = """ & Me.cboDataEntry.Column(0) &
""") AND "
End If

'Date Combo
FromDate = Me.cboDates.Column(0)
ToDate = Me.cboDates.Column(1)
FromOTDate = Me.cboDates.Column(2)
ToOTDate = Me.cboDates.Column(3)

If Not IsNull(me.cboDates)Then
strWhere = strWhere & "([Date] Is Between " & Format(FromDate,
conDateFormat) _
& " And " & Format(ToDate, conDateFormat) & ") AND "
strWhere = strWhere & "([Date] Is Between " & Format(FromOTDate,
conDateFormat) _
& " And " & Format(ToOTDate, conDateFormat) & ")"
End If

stDocName = "RptTimeSheet"
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

End Sub

I keep getting the '3075' error, Syntax error (missing operator) in query
expression

'[DataEntry] = "Heather") AND ([Date] is Between #03/02/2010# And
#03/09/2010#) AND ([Date]) is Between #02/18/2010# And #02/24/2010))'

I am sure I am missing something quite simple, but once you have looked at
it for a few hours everything begins to blur :)

Thanks for any help provided! I can't put a price on how valuable this site
is to me! Thank you to all the people providing questions and support.

~Nicole
 
Hello Nicole

Nicole "MoonBlosm" wrote:
[snip]
If Not IsNull(me.cboDates)Then
strWhere = strWhere & "([Date] Is Between " & Format(FromDate,
conDateFormat) _
& " And " & Format(ToDate, conDateFormat) & ") AND "
strWhere = strWhere & "([Date] Is Between " & Format(FromOTDate,
conDateFormat) _
& " And " & Format(ToOTDate, conDateFormat) & ")"
End If [snip]
I keep getting the '3075' error, Syntax error (missing operator)
in query expression

'[DataEntry] = "Heather") AND ([Date] is Between #03/02/2010# And
#03/09/2010#) AND ([Date]) is Between #02/18/2010# And #02/24/2010))'

I am sure I am missing something quite simple, but once you have
looked at it for a few hours everything begins to blur :)
Click to expand...

The "between" operator has no "is", so try this:
If Not IsNull(me.cboDates) Then
strWhere = strWhere & "([Date] Between " & Format(FromDate, conDateFormat)
_
& " And " & Format(ToDate, conDateFormat) & ") AND "
strWhere = strWhere & "([Date] Between " & Format(FromOTDate,
conDateFormat) _
& " And " & Format(ToOTDate, conDateFormat) & ")"
End If
 
Back
Top