VBA Where with two conditions (1 Viewer)

Access_Help · Aug 7, 2018

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:

Code:

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

Combo12 holds a date.

plog · Aug 7, 2018

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_ · Aug 7, 2018

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 · Aug 7, 2018

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

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

Access_Help · Aug 11, 2018

June7 said:
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

Code:

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

June7 · Aug 11, 2018

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

MajP · Aug 11, 2018

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 · Aug 11, 2018

June7 said:
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 · Aug 11, 2018

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

Access_Help · Aug 12, 2018

works well, thank you all!

arnelgp · Aug 12, 2018

Shiw us the value of the combo and its recordsource

Access_Help · Aug 12, 2018

arnelgp said:
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 · Aug 12, 2018

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_ · Aug 13, 2018

Access_Help said:
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.

VBA Where with two conditions (1 Viewer)

Access_Help

Registered User.

plog

Banishment Pending

Mark_

Longboard on the internet

June7

AWF VIP

Access_Help

Registered User.

June7

AWF VIP

MajP

You've got your good things, and you've got mine.

Access_Help

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Access_Help

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Access_Help

Registered User.

MajP

You've got your good things, and you've got mine.

Mark_

Longboard on the internet

Similar threads

Users who are viewing this thread