A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Use combo to print report between two sets of dates



 
 
Thread Tools Display Modes
  #1  
Old May 27th, 2010, 10:18 PM posted to microsoft.public.access.reports
MoonBlosm
external usenet poster
 
Posts: 15
Default Use combo to print report between two sets of dates

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

  #2  
Old May 28th, 2010, 08:52 AM posted to microsoft.public.access.reports
Wolfgang Kais[_4_]
external usenet poster
 
Posts: 18
Default Use combo to print report between two sets of dates

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:45 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.