Where Condition not returning any items

[ChrisCione]

I have an OpenReport macro which currently uses a query as a filter, which works 100%. I attempted to add a where condition to further "filter" results. The statement reads:

[Specialist]=[Forms]![frmSwitchboard]![lstSpecialist]

Specialist is a text field in my main table; lstSpecialist is a listbox on my switchboard.

I get no results at all. The source of the report was originally a query, so I tried changing it to the main table itself. No dice.

Any suggestions?

 

[spikepl]

What does lstSpecialist contain in the first column? or rather, in the bound column?

 

[ChrisCione]

What does lstSpecialist contain in the first column? or rather, in the bound column?

Names. It's only 1 column.

 

[boblarson]

I don't work in macros much anymore but I still think you need to send it as text and include the quotes for the form reference since the specialist is text and not a number. So try this instead:

"[Specialist]='" & [Forms]![frmSwitchboard]![lstSpecialist] & "'"

or it might need to be

[Specialist]= ' & [Forms]![frmSwitchboard]![lstSpecialist] & "'"

 

[ChrisCione]

I don't work in macros much anymore but I still think you need to send it as text and include the quotes for the form reference since the specialist is text and not a number. So try this instead:

"[Specialist]='" & [Forms]![frmSwitchboard]![lstSpecialist] & "'"

or it might need to be

[Specialist]= ' & [Forms]![frmSwitchboard]![lstSpecialist] & "'"

The first suggestion returned all records (didn't filter); the second suggestion, it couldn't parse.

Could this be accomplished via another method? I want to print a "filtered" report using this criteria:

1) a checkbox on one form [frmRecruitment] - only those records with the checkbox checked

2) the text item(s), which are people's names, selected in a listbox on another form [frmSwitchboard] WHICH MATCH match a field in the main table (the people's names)

In other words, if "John Smith" was selected in the listbox, print the records belonging to "John Smith". If John Smith AND Mary Jones were selected, print the records belonging to JS and print the records belonging to MJ.

 

[boblarson]

Okay, something just hit me - You say

I have an OpenReport macro which currently uses a query as a filter, which works 100%. I attempted to add a where condition to further "filter" results.

So, my question is this - WHY? Why are you attempting to use both instead of just one or the other. Personally I would NOT be using the query as a filter but instead just use the where clause.

 

[ChrisCione]

Okay, something just hit me - You say


So, my question is this - WHY? Why are you attempting to use both instead of just one or the other. Personally I would NOT be using the query as a filter but instead just use the where clause.

Sorry, but I had to laugh at your "WHY?" question.

Because I'm an idiot, that's why. Really, it's because I am learning on the fly (and barely, at that). It's neither here nor there to me how this is accomplished.

I did try modifying the query, from this

[Forms]![frmSwitchboard]![cboSpecialist]

to this

[Forms]![frmSwitchboard]![lstSpecialist]

which, using logic, I believe should have worked for 3 reasons: 1) the original statement worked without error (it filtered from the one item selected in a combobox) ; 2) I indeed change the combobox to a listbox called lstSpecialist and 3) I am able to use the listbox as a filter to open/view select records, so I should be able to use it as criteria for a report.

But it didn't work, so I was trying out where the clause.

But I can't get that to work, either.

So here I be.