Syntex Error (missing operator) in query expression

[monkeysee]

I have a form with 3 parameters. These parameters dictate what to display on a report. The user will choose their name from the Employee combo drop down box and then choose a date range for the report. I have the following code in a command button to open the report.

DoCmd.OpenReport "EmpBalSheet1", acViewPreview, , "[VisitDate] Between #" & Me.Text2 & "# And #" & Me.Text4 & "#"

This works perfectly for the dates, but I am at a loss as to how to incorporate the Employee parameter in the code as this code below does not work
DoCmd.OpenReport "EmpBalSheet1", acViewPreview, , "[EmployeeName]" & "[VisitDate] Between #" & Me.Text2 & "# And #" & Me.Text4 & "#"

I know I am missing something, but don't know what. Any help will be greatly appreciated!
Thank you to all the great programmers that help out there!

 

[dhookom]

What is the name of the control for the employee combo box. I would expect it to be cboEmployeeName. Also, it makes sense to have the bound column of the combo box be the primary key from the employee table. I assume this is the value stored in the primary and foreign keys.

Do you always enter a value into all three filter controls?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[monkeysee]

Good Evening Duane, you have helped me many times before, I really do appreciate it!
Control name for the combo box is combo6
yes bound column is the primary key and value is stored in primary and foreign keys
yes values are entered in all three filters, but the 3rd (text4) doesn't really need to be unless user requires more than 1 day in the report.

 

[dhookom]

Is the primary key numeric or text?
What is the primary key field name(s) since I doubt it is [EmployeeName]?
Would you consider changing combo6 to something that makes sense?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[monkeysee]

Hi Duane,
Sorry I haven't answered sooner, was at work today.....
Also, I must apologize as when I answered last night, I must have been half asleep.
The boxes on the parameter form are unbound. The form is based on a query with only 2 fields.
The unbound combobox pulls from the EmployeeName field. The other two text boxes pull from visitdate.
As far as changing the combo6 name to something else - I think it is okay as this is not a real complicated data base, I'm just trying to give the users some 'bells & whistles"
The command button opens a report based on the chosen parameters - the event procedure that I have works for the dates, I'd like to include the EmployeeName filter so each employee can print their individual activity.
Hope I am making some sense.
Thanks!
Deb

 

[dhookom]

You should always give you significant controls significant names. No excuses for anything else ;-)

Dim strWhere as String
strWhere = "1=1 "
If not IsNull(Me.text2) Then
    strWhere = strWhere & " AND [VisitDate] >=#" & Me.Text2 & "# "
End If 
If not IsNull(Me.text4) Then
    strWhere = strWhere & " AND [VisitDate] <=#" & Me.Text4 & "# "
End If 
If not IsNull(Me.combo6) Then
    strWhere = strWhere & " AND [EmployeeName]=""" & Me.combo6 & """ "
End If 
DoCmd.OpenReport "EmpBalSheet1", acViewPreview, , strWhere

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016