Between Dates on Combo Form

C

cityscaper

I have a form for user to pick multiple and/or parameters for a report and
can't quite get the programming for the between dates working correctly. I
keep getting the error: Run Time error 3075
Syntax error (missing operator) in query expression ‘strDateField = &
tblSession.StartDate ([tblSession.StartDate] BETWEEN 1/1/2008) AND
([tblSession.StartDate] 12/31/2008) AND tblEvent.EventName Like ‘*’ AND
tblPersonnel.BusinessUnit Like ‘*’

It seems I've placed the strDateField incorrectly, but can't figure out
where. Below is the code behind the OK button on the form.
Private Sub cmdOK_Click()
Dim varItem As Variant
Dim strClass As String
Dim strDept As String
Dim strDateField As String
Dim strClassCondition As String
Dim strDeptCondition As String
Dim strSQL As String

Const conJetDate = "\#mm\/dd\/yyy\#"

If Not IsNull(Me.txtStartDate) Then
strDateField = "([tblSession.StartDate] BETWEEN " &
Format(Me.txtStartDate, conDateFormat) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then
strDateField = strDateField & "([tblSession.StartDate] " &
Format(Me.txtEndDate, conDateFormat) & ") "
End If

For Each varItem In Me.lstClass.ItemsSelected
strClass = strClass & ",'" & Me.lstClass.ItemData(varItem) _
& "'"
Next varItem
If Len(strClass) = 0 Then
strClass = "Like '*'"
Else
strClass = Right(strClass, Len(strClass) - 1)
strClass = "IN (" & strClass & ")"
End If

For Each varItem In Me.lstDept.ItemsSelected
strDept = strDept & ",'" & Me.lstDept.ItemData(varItem) _
& "'"
Next varItem
If Len(strDept) = 0 Then
strDept = "Like '*'"
Else
strDept = Right(strDept, Len(strDept) - 1)
strDept = "IN (" & strDept & ")"
End If

If Me.optAndClass.Value = True Then
strClassCondition = " AND "
Else
strClassCondition = " OR "
End If

If Me.optAndDept.Value = True Then
strDeptCondition = " AND "
Else
strDeptCondition = " OR "
End If

strSQL = "SELECT tblPersonnel.BusinessUnit, tblSession.StartDate,
tblEvent.EventName, tblPersonnelAndSession.EmpID, tblPersonnel.LastName,
tblPersonnel.FirstName, tblPersonnel.EmpID, tblPersonnel.BusinessUnit,
tblPersonnel.ActiveBSCEmployee, tblPersonnel.ActivePM, " & _
"tblPersonnel.PMLevelCode, tblPersonnelAndSession.SessionID,
tblPersonnelAndSession.AttendanceStatus, tblSession.StartDate,
tblSession.SessionLocation, tblSession.InstructorID,
tblSession.SessionCancelled, tblEvent.EventID " & _
"FROM tblPersonnel INNER JOIN (tblPersonnelAndSession INNER JOIN (tblEvent
INNER JOIN tblSession ON tblEvent.EventID = tblSession.EventID) ON
tblPersonnelAndSession.SessionID = tblSession.SessionID) ON
tblPersonnel.EmpID = tblPersonnelAndSession.EmpID " & _
"WHERE strDateField = & tblSession.StartDate " & strDateField & _
strClassCondition & "tblEvent.EventName " & strClass & _
strDeptCondition & "tblPersonnel.BusinessUnit " & strDept & ";"


Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryOption")
qdf.sql = strSQL
Set qdf = Nothing
Set db = Nothing

DoCmd.OpenQuery "qryOption"
DoCmd.OpenReport "rptCourseBU", acViewPreview
DoCmd.Close acForm, Me.Name
DoCmd.Close acQuery, "qryOption"

End Sub

Private Sub optAndClass_Click()
If Me.optAndClass.Value = True Then
Me.optOrClass.Value = False
Else
Me.optAndClass.Value = True
End If
End Sub

Private Sub optAndDept_Click()
If Me.optAndDept.Value = True Then
Me.optOrDept.Value = False
Else
Me.optAndDept.Value = True
End If
End Sub


-- Any help in correcting my code would be appreciated.
Many Thanks for any assistance
 
J

John Spencer

You might be better off using expressions like the following.

StrDateField = "[tblSession].[StartDate] >=" &
Format(Me.txtStartDate,conDateFormat)

strDateField = strDateField & " And [tblSession].[StartDate] <=" &
Format(Me.txtEndDate,conDateFormat)

Or if you want the option of just a single date and having StartDate
equal to the input of txtStartDate, you might use:

strDateField = strDateField & " And [tblSession].[StartDate] <=" &
Format(NZ(Me.txtEndDate,me.TxtStartDate),conDateFormat)


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a form for user to pick multiple and/or parameters for a report and
can't quite get the programming for the between dates working correctly. I
keep getting the error: Run Time error 3075
Syntax error (missing operator) in query expression ‘strDateField = &
tblSession.StartDate ([tblSession.StartDate] BETWEEN 1/1/2008) AND
([tblSession.StartDate] 12/31/2008) AND tblEvent.EventName Like ‘*’ AND
tblPersonnel.BusinessUnit Like ‘*’

It seems I've placed the strDateField incorrectly, but can't figure out
where. Below is the code behind the OK button on the form.
Private Sub cmdOK_Click()
Dim varItem As Variant
Dim strClass As String
Dim strDept As String
Dim strDateField As String
Dim strClassCondition As String
Dim strDeptCondition As String
Dim strSQL As String

Const conJetDate = "\#mm\/dd\/yyy\#"

If Not IsNull(Me.txtStartDate) Then
strDateField = "([tblSession.StartDate] BETWEEN " &
Format(Me.txtStartDate, conDateFormat) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then
strDateField = strDateField & "([tblSession.StartDate] " &
Format(Me.txtEndDate, conDateFormat) & ") "
End If

For Each varItem In Me.lstClass.ItemsSelected
strClass = strClass & ",'" & Me.lstClass.ItemData(varItem) _
& "'"
Next varItem
If Len(strClass) = 0 Then
strClass = "Like '*'"
Else
strClass = Right(strClass, Len(strClass) - 1)
strClass = "IN (" & strClass & ")"
End If

For Each varItem In Me.lstDept.ItemsSelected
strDept = strDept & ",'" & Me.lstDept.ItemData(varItem) _
& "'"
Next varItem
If Len(strDept) = 0 Then
strDept = "Like '*'"
Else
strDept = Right(strDept, Len(strDept) - 1)
strDept = "IN (" & strDept & ")"
End If

If Me.optAndClass.Value = True Then
strClassCondition = " AND "
Else
strClassCondition = " OR "
End If

If Me.optAndDept.Value = True Then
strDeptCondition = " AND "
Else
strDeptCondition = " OR "
End If

strSQL = "SELECT tblPersonnel.BusinessUnit, tblSession.StartDate,
tblEvent.EventName, tblPersonnelAndSession.EmpID, tblPersonnel.LastName,
tblPersonnel.FirstName, tblPersonnel.EmpID, tblPersonnel.BusinessUnit,
tblPersonnel.ActiveBSCEmployee, tblPersonnel.ActivePM, " & _
"tblPersonnel.PMLevelCode, tblPersonnelAndSession.SessionID,
tblPersonnelAndSession.AttendanceStatus, tblSession.StartDate,
tblSession.SessionLocation, tblSession.InstructorID,
tblSession.SessionCancelled, tblEvent.EventID " & _
"FROM tblPersonnel INNER JOIN (tblPersonnelAndSession INNER JOIN (tblEvent
INNER JOIN tblSession ON tblEvent.EventID = tblSession.EventID) ON
tblPersonnelAndSession.SessionID = tblSession.SessionID) ON
tblPersonnel.EmpID = tblPersonnelAndSession.EmpID " & _
"WHERE strDateField = & tblSession.StartDate " & strDateField & _
strClassCondition & "tblEvent.EventName " & strClass & _
strDeptCondition & "tblPersonnel.BusinessUnit " & strDept & ";"


Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryOption")
qdf.sql = strSQL
Set qdf = Nothing
Set db = Nothing

DoCmd.OpenQuery "qryOption"
DoCmd.OpenReport "rptCourseBU", acViewPreview
DoCmd.Close acForm, Me.Name
DoCmd.Close acQuery, "qryOption"

End Sub

Private Sub optAndClass_Click()
If Me.optAndClass.Value = True Then
Me.optOrClass.Value = False
Else
Me.optAndClass.Value = True
End If
End Sub

Private Sub optAndDept_Click()
If Me.optAndDept.Value = True Then
Me.optOrDept.Value = False
Else
Me.optAndDept.Value = True
End If
End Sub


-- Any help in correcting my code would be appreciated.
Many Thanks for any assistance
Click to expand...
 
C

cityscaper

Thank you, John, for the suggestion. I assumed you meant the expressions in
my IF statements so I replaced the codes with your lines, but I still get the
error message about my query expression when I enter any dates.
I think it's my where statement that is the issue. If I change that to:
"WHERE strDateField = strDateField & " And [tblSession].[StartDate] <= " & "
Format(NZ(Me.txtEndDate,me.TxtStartDate),conDateFormat) & _

I get a compile (syntax) error.
--
Many Thanks for any assistance


John Spencer said:
You might be better off using expressions like the following.

StrDateField = "[tblSession].[StartDate] >=" &
Format(Me.txtStartDate,conDateFormat)

strDateField = strDateField & " And [tblSession].[StartDate] <=" &
Format(Me.txtEndDate,conDateFormat)

Or if you want the option of just a single date and having StartDate
equal to the input of txtStartDate, you might use:

strDateField = strDateField & " And [tblSession].[StartDate] <=" &
Format(NZ(Me.txtEndDate,me.TxtStartDate),conDateFormat)


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a form for user to pick multiple and/or parameters for a report and
can't quite get the programming for the between dates working correctly. I
keep getting the error: Run Time error 3075
Syntax error (missing operator) in query expression ‘strDateField = &
tblSession.StartDate ([tblSession.StartDate] BETWEEN 1/1/2008) AND
([tblSession.StartDate] 12/31/2008) AND tblEvent.EventName Like ‘*’ AND
tblPersonnel.BusinessUnit Like ‘*’

It seems I've placed the strDateField incorrectly, but can't figure out
where. Below is the code behind the OK button on the form.
Private Sub cmdOK_Click()
Dim varItem As Variant
Dim strClass As String
Dim strDept As String
Dim strDateField As String
Dim strClassCondition As String
Dim strDeptCondition As String
Dim strSQL As String

Const conJetDate = "\#mm\/dd\/yyy\#"

If Not IsNull(Me.txtStartDate) Then
strDateField = "([tblSession.StartDate] BETWEEN " &
Format(Me.txtStartDate, conDateFormat) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then
strDateField = strDateField & "([tblSession.StartDate] " &
Format(Me.txtEndDate, conDateFormat) & ") "
End If

For Each varItem In Me.lstClass.ItemsSelected
strClass = strClass & ",'" & Me.lstClass.ItemData(varItem) _
& "'"
Next varItem
If Len(strClass) = 0 Then
strClass = "Like '*'"
Else
strClass = Right(strClass, Len(strClass) - 1)
strClass = "IN (" & strClass & ")"
End If

For Each varItem In Me.lstDept.ItemsSelected
strDept = strDept & ",'" & Me.lstDept.ItemData(varItem) _
& "'"
Next varItem
If Len(strDept) = 0 Then
strDept = "Like '*'"
Else
strDept = Right(strDept, Len(strDept) - 1)
strDept = "IN (" & strDept & ")"
End If

If Me.optAndClass.Value = True Then
strClassCondition = " AND "
Else
strClassCondition = " OR "
End If

If Me.optAndDept.Value = True Then
strDeptCondition = " AND "
Else
strDeptCondition = " OR "
End If

strSQL = "SELECT tblPersonnel.BusinessUnit, tblSession.StartDate,
tblEvent.EventName, tblPersonnelAndSession.EmpID, tblPersonnel.LastName,
tblPersonnel.FirstName, tblPersonnel.EmpID, tblPersonnel.BusinessUnit,
tblPersonnel.ActiveBSCEmployee, tblPersonnel.ActivePM, " & _
"tblPersonnel.PMLevelCode, tblPersonnelAndSession.SessionID,
tblPersonnelAndSession.AttendanceStatus, tblSession.StartDate,
tblSession.SessionLocation, tblSession.InstructorID,
tblSession.SessionCancelled, tblEvent.EventID " & _
"FROM tblPersonnel INNER JOIN (tblPersonnelAndSession INNER JOIN (tblEvent
INNER JOIN tblSession ON tblEvent.EventID = tblSession.EventID) ON
tblPersonnelAndSession.SessionID = tblSession.SessionID) ON
tblPersonnel.EmpID = tblPersonnelAndSession.EmpID " & _
"WHERE strDateField = & tblSession.StartDate " & strDateField & _
strClassCondition & "tblEvent.EventName " & strClass & _
strDeptCondition & "tblPersonnel.BusinessUnit " & strDept & ";"


Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryOption")
qdf.sql = strSQL
Set qdf = Nothing
Set db = Nothing

DoCmd.OpenQuery "qryOption"
DoCmd.OpenReport "rptCourseBU", acViewPreview
DoCmd.Close acForm, Me.Name
DoCmd.Close acQuery, "qryOption"

End Sub

Private Sub optAndClass_Click()
If Me.optAndClass.Value = True Then
Me.optOrClass.Value = False
Else
Me.optAndClass.Value = True
End If
End Sub

Private Sub optAndDept_Click()
If Me.optAndDept.Value = True Then
Me.optOrDept.Value = False
Else
Me.optAndDept.Value = True
End If
End Sub


-- Any help in correcting my code would be appreciated.
Many Thanks for any assistance
Click to expand...
Click to expand...
 
J

John W. Vinson

If I change that to:
"WHERE strDateField = strDateField & " And [tblSession].[StartDate] <= " & "
Format(NZ(Me.txtEndDate,me.TxtStartDate),conDateFormat) & _
Click to expand...

Your ampersands and quotemarks are mixed up. The & operator takes two String
arguments - either string constants such as

" And [tblSession].[StartDate] <= "

or string variables or functions such as

strDateField

or

Format(NZ(Me.txtEndDate,me.TxtStartDate),conDateFormat)

and concatenates them. You're including an ampersand in the middle of a quoted
string.

Try changing this to

"WHERE strDateField = " & strDateField & " And [tblSession].[StartDate] <= " &
Format(NZ(Me.txtEndDate,me.TxtStartDate),conDateFormat) & _


I don't understand what strDatefield is or how you're planning to use it in
the query, though.
 
C

cityscaper

John,
This form/query is adapted from one on Martin Green's web site, only I use a
start and end date as a parameter. I entered your string for my where
statement and now get a blank 'enter parameter' window for the strDateField.
I use the form for a user to select parameters for a report:
txtstartdate and txtenddate, and/or class name, and/or department name.
strdatefield should relate to the tblsession.startdate field in my table.
Perhaps I made this all too complicated and I should just enter Between
forms!frmcoursebu!txtstartdate AND forms!frmcoursebu!txtenddate in the
underlying query for the tblsession.startdate field? Since I adapted the
coding, I am very unsure of where/how to get the start date and end date
defined. The other definitions work fine, it's just getting the between
dates to work. I welcome any suggestions as this seems over my head.


--
Many Thanks for any assistance


John W. Vinson said:
If I change that to:
"WHERE strDateField = strDateField & " And [tblSession].[StartDate] <= " & "
Format(NZ(Me.txtEndDate,me.TxtStartDate),conDateFormat) & _
Click to expand...

Your ampersands and quotemarks are mixed up. The & operator takes two String
arguments - either string constants such as

" And [tblSession].[StartDate] <= "

or string variables or functions such as

strDateField

or

Format(NZ(Me.txtEndDate,me.TxtStartDate),conDateFormat)

and concatenates them. You're including an ampersand in the middle of a quoted
string.

Try changing this to

"WHERE strDateField = " & strDateField & " And [tblSession].[StartDate] <= " &
Format(NZ(Me.txtEndDate,me.TxtStartDate),conDateFormat) & _


I don't understand what strDatefield is or how you're planning to use it in
the query, though.
Click to expand...
 
J

John W. Vinson

John,
This form/query is adapted from one on Martin Green's web site, only I use a
start and end date as a parameter. I entered your string for my where
statement and now get a blank 'enter parameter' window for the strDateField.
I use the form for a user to select parameters for a report:
txtstartdate and txtenddate, and/or class name, and/or department name.
strdatefield should relate to the tblsession.startdate field in my table.
Perhaps I made this all too complicated and I should just enter Between
forms!frmcoursebu!txtstartdate AND forms!frmcoursebu!txtenddate in the
underlying query for the tblsession.startdate field? Since I adapted the
coding, I am very unsure of where/how to get the start date and end date
defined. The other definitions work fine, it's just getting the between
dates to work. I welcome any suggestions as this seems over my head.
Click to expand...

Sorry, I think I confused the issue rather than helping! I didn't go back in
the thread and review John Spencer's previous suggestions.

Step back a bit. Just what do you want to accomplish? Do you want the date
range to be optional or obligatory? What records do you want to retrieve if
the user leaves the form controls blank?
 
J

Jim Burke in Novi

If your users should always enter a start and end date on the form, then if
you change your originally posted code to this I think it should work:

If Not IsDate(Me.txtStartDate) Then
msgbox "Please enter a start date."
Me.txtStartDate.SetFocus
exit sub
End IF

If Not IsDate(Me.txtEndDate) Then
msgbox "Please enter an end date."
Me.txtEndDate.SetFocus
exit sub
End IF

strDateField = "([tblSession.StartDate] BETWEEN " &
Format(Me.txtStartDate, conDateFormat) & " AND "
& Format(Me.txtEndDate, conDateFormat) & ") "

Before you start forming your SQL string, you should check to make sure that
the user entered all of the fields they're supposed to enter. First check to
make sure that the enddate and startdate are valid dates. If not, let the
user know they did not enter a date, and exit the sub. Do any other error
checking as well. Once you know all data has been entered, then form your SQL
string.

If start and end date aren't both always required then you would need some
additional code, but I have no idea whether you need that or not.

cityscaper said:
I have a form for user to pick multiple and/or parameters for a report and
can't quite get the programming for the between dates working correctly. I
keep getting the error: Run Time error 3075
Syntax error (missing operator) in query expression ‘strDateField = &
tblSession.StartDate ([tblSession.StartDate] BETWEEN 1/1/2008) AND
([tblSession.StartDate] 12/31/2008) AND tblEvent.EventName Like ‘*’ AND
tblPersonnel.BusinessUnit Like ‘*’

It seems I've placed the strDateField incorrectly, but can't figure out
where. Below is the code behind the OK button on the form.
Private Sub cmdOK_Click()
Dim varItem As Variant
Dim strClass As String
Dim strDept As String
Dim strDateField As String
Dim strClassCondition As String
Dim strDeptCondition As String
Dim strSQL As String

Const conJetDate = "\#mm\/dd\/yyy\#"

If Not IsNull(Me.txtStartDate) Then
strDateField = "([tblSession.StartDate] BETWEEN " &
Format(Me.txtStartDate, conDateFormat) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then
strDateField = strDateField & "([tblSession.StartDate] " &
Format(Me.txtEndDate, conDateFormat) & ") "
End If

For Each varItem In Me.lstClass.ItemsSelected
strClass = strClass & ",'" & Me.lstClass.ItemData(varItem) _
& "'"
Next varItem
If Len(strClass) = 0 Then
strClass = "Like '*'"
Else
strClass = Right(strClass, Len(strClass) - 1)
strClass = "IN (" & strClass & ")"
End If

For Each varItem In Me.lstDept.ItemsSelected
strDept = strDept & ",'" & Me.lstDept.ItemData(varItem) _
& "'"
Next varItem
If Len(strDept) = 0 Then
strDept = "Like '*'"
Else
strDept = Right(strDept, Len(strDept) - 1)
strDept = "IN (" & strDept & ")"
End If

If Me.optAndClass.Value = True Then
strClassCondition = " AND "
Else
strClassCondition = " OR "
End If

If Me.optAndDept.Value = True Then
strDeptCondition = " AND "
Else
strDeptCondition = " OR "
End If

strSQL = "SELECT tblPersonnel.BusinessUnit, tblSession.StartDate,
tblEvent.EventName, tblPersonnelAndSession.EmpID, tblPersonnel.LastName,
tblPersonnel.FirstName, tblPersonnel.EmpID, tblPersonnel.BusinessUnit,
tblPersonnel.ActiveBSCEmployee, tblPersonnel.ActivePM, " & _
"tblPersonnel.PMLevelCode, tblPersonnelAndSession.SessionID,
tblPersonnelAndSession.AttendanceStatus, tblSession.StartDate,
tblSession.SessionLocation, tblSession.InstructorID,
tblSession.SessionCancelled, tblEvent.EventID " & _
"FROM tblPersonnel INNER JOIN (tblPersonnelAndSession INNER JOIN (tblEvent
INNER JOIN tblSession ON tblEvent.EventID = tblSession.EventID) ON
tblPersonnelAndSession.SessionID = tblSession.SessionID) ON
tblPersonnel.EmpID = tblPersonnelAndSession.EmpID " & _
"WHERE strDateField = & tblSession.StartDate " & strDateField & _
strClassCondition & "tblEvent.EventName " & strClass & _
strDeptCondition & "tblPersonnel.BusinessUnit " & strDept & ";"


Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryOption")
qdf.sql = strSQL
Set qdf = Nothing
Set db = Nothing

DoCmd.OpenQuery "qryOption"
DoCmd.OpenReport "rptCourseBU", acViewPreview
DoCmd.Close acForm, Me.Name
DoCmd.Close acQuery, "qryOption"

End Sub

Private Sub optAndClass_Click()
If Me.optAndClass.Value = True Then
Me.optOrClass.Value = False
Else
Me.optAndClass.Value = True
End If
End Sub

Private Sub optAndDept_Click()
If Me.optAndDept.Value = True Then
Me.optOrDept.Value = False
Else
Me.optAndDept.Value = True
End If
End Sub


-- Any help in correcting my code would be appreciated.
Many Thanks for any assistance
Click to expand...
 
C

cityscaper

The user should be able to select from the form any or all of the options in
combinations:
txtstartdate and txtenddate are text boxes for user input for the table
field tblsession.startdate of the class
then there is an AND/OR for 1stclass: a multi select list box for class name
then an AND/OR for 1stdept: a multi select list box for dept. name

All of the above are unbound, the user enters the parameters and clicks the
OK button to run the report or hits Cancel to leave the form and cancel the
report

User has to enter at least one parameter, either start and end date, class
name or dept name. User can leave any of the other options null.
 
C

cityscaper

Hi Jim,
I've entered your suggested strDateField expression and now get a syntax
compile error.
The user doesn't have to enter a start and end date, that can be left blank
and they choose from the class name list box and/or dept list box to return
all records regardless of date based on just class name(s) and/or dept
name(s).
--
Many Thanks for any assistance


Jim Burke in Novi said:
If your users should always enter a start and end date on the form, then if
you change your originally posted code to this I think it should work:

If Not IsDate(Me.txtStartDate) Then
msgbox "Please enter a start date."
Me.txtStartDate.SetFocus
exit sub
End IF

If Not IsDate(Me.txtEndDate) Then
msgbox "Please enter an end date."
Me.txtEndDate.SetFocus
exit sub
End IF

strDateField = "([tblSession.StartDate] BETWEEN " &
Format(Me.txtStartDate, conDateFormat) & " AND "
& Format(Me.txtEndDate, conDateFormat) & ") "

Before you start forming your SQL string, you should check to make sure that
the user entered all of the fields they're supposed to enter. First check to
make sure that the enddate and startdate are valid dates. If not, let the
user know they did not enter a date, and exit the sub. Do any other error
checking as well. Once you know all data has been entered, then form your SQL
string.

If start and end date aren't both always required then you would need some
additional code, but I have no idea whether you need that or not.

cityscaper said:
I have a form for user to pick multiple and/or parameters for a report and
can't quite get the programming for the between dates working correctly. I
keep getting the error: Run Time error 3075
Syntax error (missing operator) in query expression ‘strDateField = &
tblSession.StartDate ([tblSession.StartDate] BETWEEN 1/1/2008) AND
([tblSession.StartDate] 12/31/2008) AND tblEvent.EventName Like ‘*’ AND
tblPersonnel.BusinessUnit Like ‘*’

It seems I've placed the strDateField incorrectly, but can't figure out
where. Below is the code behind the OK button on the form.
Private Sub cmdOK_Click()
Dim varItem As Variant
Dim strClass As String
Dim strDept As String
Dim strDateField As String
Dim strClassCondition As String
Dim strDeptCondition As String
Dim strSQL As String

Const conJetDate = "\#mm\/dd\/yyy\#"

If Not IsNull(Me.txtStartDate) Then
strDateField = "([tblSession.StartDate] BETWEEN " &
Format(Me.txtStartDate, conDateFormat) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then
strDateField = strDateField & "([tblSession.StartDate] " &
Format(Me.txtEndDate, conDateFormat) & ") "
End If

For Each varItem In Me.lstClass.ItemsSelected
strClass = strClass & ",'" & Me.lstClass.ItemData(varItem) _
& "'"
Next varItem
If Len(strClass) = 0 Then
strClass = "Like '*'"
Else
strClass = Right(strClass, Len(strClass) - 1)
strClass = "IN (" & strClass & ")"
End If

For Each varItem In Me.lstDept.ItemsSelected
strDept = strDept & ",'" & Me.lstDept.ItemData(varItem) _
& "'"
Next varItem
If Len(strDept) = 0 Then
strDept = "Like '*'"
Else
strDept = Right(strDept, Len(strDept) - 1)
strDept = "IN (" & strDept & ")"
End If

If Me.optAndClass.Value = True Then
strClassCondition = " AND "
Else
strClassCondition = " OR "
End If

If Me.optAndDept.Value = True Then
strDeptCondition = " AND "
Else
strDeptCondition = " OR "
End If

strSQL = "SELECT tblPersonnel.BusinessUnit, tblSession.StartDate,
tblEvent.EventName, tblPersonnelAndSession.EmpID, tblPersonnel.LastName,
tblPersonnel.FirstName, tblPersonnel.EmpID, tblPersonnel.BusinessUnit,
tblPersonnel.ActiveBSCEmployee, tblPersonnel.ActivePM, " & _
"tblPersonnel.PMLevelCode, tblPersonnelAndSession.SessionID,
tblPersonnelAndSession.AttendanceStatus, tblSession.StartDate,
tblSession.SessionLocation, tblSession.InstructorID,
tblSession.SessionCancelled, tblEvent.EventID " & _
"FROM tblPersonnel INNER JOIN (tblPersonnelAndSession INNER JOIN (tblEvent
INNER JOIN tblSession ON tblEvent.EventID = tblSession.EventID) ON
tblPersonnelAndSession.SessionID = tblSession.SessionID) ON
tblPersonnel.EmpID = tblPersonnelAndSession.EmpID " & _
"WHERE strDateField = & tblSession.StartDate " & strDateField & _
strClassCondition & "tblEvent.EventName " & strClass & _
strDeptCondition & "tblPersonnel.BusinessUnit " & strDept & ";"


Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryOption")
qdf.sql = strSQL
Set qdf = Nothing
Set db = Nothing

DoCmd.OpenQuery "qryOption"
DoCmd.OpenReport "rptCourseBU", acViewPreview
DoCmd.Close acForm, Me.Name
DoCmd.Close acQuery, "qryOption"

End Sub

Private Sub optAndClass_Click()
If Me.optAndClass.Value = True Then
Me.optOrClass.Value = False
Else
Me.optAndClass.Value = True
End If
End Sub

Private Sub optAndDept_Click()
If Me.optAndDept.Value = True Then
Me.optOrDept.Value = False
Else
Me.optAndDept.Value = True
End If
End Sub


-- Any help in correcting my code would be appreciated.
Many Thanks for any assistance
Click to expand...
Click to expand...
 
J

John W. Vinson

The user should be able to select from the form any or all of the options in
combinations:
txtstartdate and txtenddate are text boxes for user input for the table
field tblsession.startdate of the class
then there is an AND/OR for 1stclass: a multi select list box for class name
then an AND/OR for 1stdept: a multi select list box for dept. name

All of the above are unbound, the user enters the parameters and clicks the
OK button to run the report or hits Cancel to leave the form and cancel the
report

User has to enter at least one parameter, either start and end date, class
name or dept name. User can leave any of the other options null.
Click to expand...

Well then, I'll apologise to John Spencer who responded earlier and reiterate
what he said in my own words.

You need to construct a valid SQL Query in your VBA code, based on the values
on the form. To do so you do need to know what constitutes a valid SQL Query
and how to concatenate things in code, but this should give you a start.

Put the following code in the OK button's Click event:

Private Sub cmdOK_Click()
Dim strSQL As String ' define a variable to hold the query SQL
Dim vItem as Variant ' a variable to hold selections from listboxes
strSQL = "" ' start with an empty string
' Did user enter a start date?
If Me!txtStartDate & vbNullString <> vbNullString Then
' build a criterion using the # date delimiter
strSQL = strSQL & " AND [StartDate] >= #" & Me!txtStartDate & "#"
End If
' If there's a date this will set strSQL to something like
' AND [StartDate] >= #11/15/2008#
' How about end date?
If Me!txtEndDate & vbNullString <> vbNullString Then
strSQL = strSQL & " AND [StartDate] >= #" & Me!txttxtEndDate & "#"
End If
' If there are dates in both controls, strSQL will become
' AND [StartDate] >= #11/15/2008# AND [StartDate] <= #12/1/2008#
' Now the listboxes...
If Me!lstClassName.ItemsSelected.Count > 0 Then
' start a multiple item criterion using the IN clause
strSQL = " AND [ClassName] IN ("
For Each vItem in Me!lstClassName.ItemsSelected
strSQL = Chr(34) & Me!lstClassName.ItemData(vItem) & Chr(34) & ", "
Next vItem
' trim off the trailing comma and blank and add a parenthesis
strSQL = Left(strSQL, Len(strSQL) - 2) & ")"
' strSQL will now include a clause like
' AND [ClassName] IN ("Divination", "Arithromancy", "Potions")
End If
If Me!lstClassName.ItemsSelected.Count > 0 Then
' start a multiple item criterion using the IN clause
strSQL = " AND [DeptName] IN ("
For Each vItem in Me!lstDeptName.ItemsSelected
strSQL = Chr(34) & Me!lstDeptName.ItemData(vItem) & Chr(34) & ", "
Next vItem
' trim off the trailing comma and blank and add a parenthesis
strSQL = Left(strSQL, Len(strSQL) - 2) & ")"
' strSQL will now include a clause like
' AND [Department] IN ("Slytherin", "Hufflepuff")
End If
If strSQL = "" Then ' did user not enter ANYTHING?
MsgBox "Please enter at least one criterion", vbOKOnly
Else
' Trim the leading AND
strSQL = Mid(strSQL, 4)
DoCmd.OpenReport "MyReport", WhereCondition := strSQL
End If
End Sub
 
J

Jim Burke in Novi

Are they ever allowed to enter just one of the dates? You have to put in
logic for every possible thing the user might do. If they will only enter
either no dates or both, then do this:

If (Not IsDate(Me.txtStartDate) and isdate(Me.txtEndDate)) or _
(IsDate(Me.txtStartDate) and not isdate(Me.txtEndDate))
Then
msgbox "You must either leave start and end date blank or else enter
values for both."
Me.txtStartDate.SetFocus
exit sub
End IF

If Not IsDate(Me.txtStartDate) then
strDateField = vbNullString
Else
strDateField = "([tblSession.StartDate] BETWEEN " &
Format(Me.txtStartDate, conDateFormat) & " AND "
& Format(Me.txtEndDate, conDateFormat) & ") " & strClassCondition
ENd If

Then start your WHERE clause with:

"WHERE " & strDateField & "tblEvent.EventName " & strClass ....

I can't tell whether all your logic is right or not. There's a lot there,
and I'm not sure exactly what you need to do. But the part about the date
range should be OK I think.

I'm not so sure you want to have the user controlling whether they use AND
or OR - you need to make sure you're using those properly. Intermixing those
in a WHERE clause may require parentheses in some places or not, depending on
exactly what your requirements are.

cityscaper said:
Hi Jim,
I've entered your suggested strDateField expression and now get a syntax
compile error.
The user doesn't have to enter a start and end date, that can be left blank
and they choose from the class name list box and/or dept list box to return
all records regardless of date based on just class name(s) and/or dept
name(s).
--
Many Thanks for any assistance


Jim Burke in Novi said:
If your users should always enter a start and end date on the form, then if
you change your originally posted code to this I think it should work:

If Not IsDate(Me.txtStartDate) Then
msgbox "Please enter a start date."
Me.txtStartDate.SetFocus
exit sub
End IF

If Not IsDate(Me.txtEndDate) Then
msgbox "Please enter an end date."
Me.txtEndDate.SetFocus
exit sub
End IF

strDateField = "([tblSession.StartDate] BETWEEN " &
Format(Me.txtStartDate, conDateFormat) & " AND "
& Format(Me.txtEndDate, conDateFormat) & ") "

Before you start forming your SQL string, you should check to make sure that
the user entered all of the fields they're supposed to enter. First check to
make sure that the enddate and startdate are valid dates. If not, let the
user know they did not enter a date, and exit the sub. Do any other error
checking as well. Once you know all data has been entered, then form your SQL
string.

If start and end date aren't both always required then you would need some
additional code, but I have no idea whether you need that or not.

cityscaper said:
I have a form for user to pick multiple and/or parameters for a report and
can't quite get the programming for the between dates working correctly. I
keep getting the error: Run Time error 3075
Syntax error (missing operator) in query expression ‘strDateField = &
tblSession.StartDate ([tblSession.StartDate] BETWEEN 1/1/2008) AND
([tblSession.StartDate] 12/31/2008) AND tblEvent.EventName Like ‘*’ AND
tblPersonnel.BusinessUnit Like ‘*’

It seems I've placed the strDateField incorrectly, but can't figure out
where. Below is the code behind the OK button on the form.
Private Sub cmdOK_Click()
Dim varItem As Variant
Dim strClass As String
Dim strDept As String
Dim strDateField As String
Dim strClassCondition As String
Dim strDeptCondition As String
Dim strSQL As String

Const conJetDate = "\#mm\/dd\/yyy\#"

If Not IsNull(Me.txtStartDate) Then
strDateField = "([tblSession.StartDate] BETWEEN " &
Format(Me.txtStartDate, conDateFormat) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then
strDateField = strDateField & "([tblSession.StartDate] " &
Format(Me.txtEndDate, conDateFormat) & ") "
End If

For Each varItem In Me.lstClass.ItemsSelected
strClass = strClass & ",'" & Me.lstClass.ItemData(varItem) _
& "'"
Next varItem
If Len(strClass) = 0 Then
strClass = "Like '*'"
Else
strClass = Right(strClass, Len(strClass) - 1)
strClass = "IN (" & strClass & ")"
End If

For Each varItem In Me.lstDept.ItemsSelected
strDept = strDept & ",'" & Me.lstDept.ItemData(varItem) _
& "'"
Next varItem
If Len(strDept) = 0 Then
strDept = "Like '*'"
Else
strDept = Right(strDept, Len(strDept) - 1)
strDept = "IN (" & strDept & ")"
End If

If Me.optAndClass.Value = True Then
strClassCondition = " AND "
Else
strClassCondition = " OR "
End If

If Me.optAndDept.Value = True Then
strDeptCondition = " AND "
Else
strDeptCondition = " OR "
End If

strSQL = "SELECT tblPersonnel.BusinessUnit, tblSession.StartDate,
tblEvent.EventName, tblPersonnelAndSession.EmpID, tblPersonnel.LastName,
tblPersonnel.FirstName, tblPersonnel.EmpID, tblPersonnel.BusinessUnit,
tblPersonnel.ActiveBSCEmployee, tblPersonnel.ActivePM, " & _
"tblPersonnel.PMLevelCode, tblPersonnelAndSession.SessionID,
tblPersonnelAndSession.AttendanceStatus, tblSession.StartDate,
tblSession.SessionLocation, tblSession.InstructorID,
tblSession.SessionCancelled, tblEvent.EventID " & _
"FROM tblPersonnel INNER JOIN (tblPersonnelAndSession INNER JOIN (tblEvent
INNER JOIN tblSession ON tblEvent.EventID = tblSession.EventID) ON
tblPersonnelAndSession.SessionID = tblSession.SessionID) ON
tblPersonnel.EmpID = tblPersonnelAndSession.EmpID " & _
"WHERE strDateField = & tblSession.StartDate " & strDateField & _
strClassCondition & "tblEvent.EventName " & strClass & _
strDeptCondition & "tblPersonnel.BusinessUnit " & strDept & ";"


Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryOption")
qdf.sql = strSQL
Set qdf = Nothing
Set db = Nothing

DoCmd.OpenQuery "qryOption"
DoCmd.OpenReport "rptCourseBU", acViewPreview
DoCmd.Close acForm, Me.Name
DoCmd.Close acQuery, "qryOption"

End Sub

Private Sub optAndClass_Click()
If Me.optAndClass.Value = True Then
Me.optOrClass.Value = False
Else
Me.optAndClass.Value = True
End If
End Sub

Private Sub optAndDept_Click()
If Me.optAndDept.Value = True Then
Me.optOrDept.Value = False
Else
Me.optAndDept.Value = True
End If
End Sub


-- Any help in correcting my code would be appreciated.
Many Thanks for any assistance
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Making a macro only work on certain files 2
Cleanup Code on Form ?? 4
How can I filter by Dates on a Form? 6
data type mismatch 4
Error 3085: Undefined function 'N' in expression. - Access 2010 1
Closing programs on remote PC - help please 1
Combine combo boxes with date range 16
Filter a Report with a Form 2

Top