Use combo to print report between two sets of dates


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
 
Ad

Advertisements

W

Wolfgang Kais

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 :)

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top