Prev: Force the group footer to print on the first page
Next: OpenReport/OutputTo
From: MoonBlosm on 27 May 2010 17:18
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

From: Wolfgang Kais on 28 May 2010 03:52
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

--
Regards,
Wolfgang


 | 
Pages: 1
Prev: Force the group footer to print on the first page
Next: OpenReport/OutputTo