VBA Where with two conditions

[Access_Help]

I require the report to open using two conditions (one condition using a listbox and the other from a drop down). I am getting a data mismatch error:

DoCmd.OpenReport vRpt, acPreview, , "[Name]= '" & lstEAddrs.Column(1) & "'" And "[WeekEnding] = '" & Combo12 & "'"

Combo12 holds a date.

 

[plog]

Your 'And' exists in a state of limbo. As it resides, it is neither part of a string (inside quotes) nor is it a variable (with ampersands before and after it). So when the computer comes to it, it has no idea what you are talking about.

 

[Mark_]

In general, I would suggest declaring a string to hold your "Where" clause. You would populate the string before doing the DoCmd.OpenReport and pass the variable where you have your criteria.

The big advantage of doing this as a habit is that you can verify exactly what your Where clause will be prior to having the report run. Often this will expose issues such as this long before you turn to the forum for help.

 

[June7]

The And belongs inside quotes as part of the literal string. Also, date fields need # delimiter around parameters.

& "' And [WeekEnding] = #" & Combo12 & "#"

 

[Access_Help]

The And belongs inside quotes as part of the literal string. Also, data fields need # delimiter around parameters.

& "' And [WeekEnding] = #" & Combo12 & "#"

When adding the date criteria, it returns no results

DoCmd.OpenReport vRpt, acPreview, , "[Name]= '" & lstEAddrs.Column(1) & "' And [WeekEnding] = #" & Combo12 & "#"

 

[June7]

I assumed WeekEnding is a native Date/Time field in table. Provide sample data.

 

[MajP]

Take Mark's advice

In general, I would suggest declaring a string to hold your "Where" clause. You would populate the string before doing the DoCmd.OpenReport and pass the variable where you have your criteria.
The big advantage of doing this as a habit is that you can verify exactly what your Where clause will be prior to having the report run. Often this will expose issues such as this long before you turn to the forum for help.

Dim strWhere as string
strWhere = "[Name]= '" & lstEAddrs.Column(1) & "' And [WeekEnding] = #" & Combo12 & "#"
debug.print strWhere
'What you get?
DoCmd.OpenReport vRpt, acPreview, ,strWhere

It would be a whole lot easier for anyone to help, instead of guessing at what you are doing wrong.

 

[Access_Help]

I assumed WeekEnding is a native Date/Time field in table. Provide sample data.

It is set to date/time and short date; sample data:
06/09/2018

 

[arnelgp]

& "' And [WeekEnding] = #" & Format(Combo12,"mm/dd/yyyy") & "#"

 

[Access_Help]

works well, thank you all!

 

[arnelgp]

Shiw us the value of the combo and its recordsource

 

[Access_Help]

Shiw us the value of the combo and its recordsource

realised my silly mistake, was running the query with no value in the combo. works fine now!

 

[MajP]

Only to completely support my and Mark's point. Learn to debug your own code.

in general, I would suggest declaring a string to hold your "Where" clause. You would populate the string before doing the DoCmd.OpenReport and pass the variable where you have your criteria.

The big advantage of doing this as a habit is that you can verify exactly what your Where clause will be prior to having the report run. Often this will expose issues such as this long before you turn to the forum for help.

 

[Mark_]

It is set to date/time and short date; sample data:
06/09/2018

Something important to remember; Date/Time is stored as a number NOT a formatted date. While YOU see 06/09/2018 ACCESS sees either 43260 (if MM/DD/YYYY as Arnel posted) OR 43349 (if your short date is DD/MM/YYYY). For date/time the format is NOT saved and is done when displayed.

Probably not relevant to your issue with the filter, but something you as a developer need to be aware of as "and short date" will not affect what is actually stored, just how it is displayed. This does become very relevant when you use NOW() to fill a date/time but display only the 'short date'. You'll be wondering why it isn't sorting the way you think if you don't remember what is really in the table.