Using Criteria in Queries from Values in a Form

Question

Post reply

Using Criteria in Queries from Values in a Form

jwellington

Ten Centuries

Points: 1062
More actions
June 12, 2008 at 5:08 am

#72125

Hi,
I would like to filter a query in Access based on the values of a couple of combo boxes on a form.
Trouble is, if the combo box is blank (or null), then I would like it to show all records, rather than those equal to null.
It seems that if I have a combo box or text box value as criteria in a query, then there is no way I can pass anything so that it does not apply the criteria on that field.
I have to apply the query outcome to a report.
A few possibilities might be:
1. Apply a programmed SQL statement as the record source for the report - But I do not know how to do this.
2. Somehow, set up the query in Access to do what I want
Any ideas on how to do either of these, or even an alternative solution would be great.
Thanks
P.S. I have tried to use the filter parameter in the report on the OnOpen command of the report. This works OK on the report, but not for a SubReport. For some reason, it will not recognise the Fillter parameter when Opening a Subform (which I have tested as working when opening the subform separately). So I am stuck to finding an alternative solution above.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

HI,

Have you tried something like this in your query

SELECT *

FROM Mytable

WHERE Col1 = [TextboxValue] or [TextboxValue]= ''

That way if the text box is empty then everything comes back

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

jwellington Ten Centuries Points: 1062 More actions · Answer 2

Hi,

Is

[TextboxValue] or [TextboxValue] = "

something that I can put in a query? and is the syntax correct? It seems strange that you have put only one inverted comma (").

I am not 100% convinced. Could you explain further how this is interpreted in the query in Access? How will that return all the records if the [TextValue] is Null?

Thanks

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

Well you need to change the syntax for the name of the textbox and the form that it belongs to ...

The reason it works is because [TextBoxValue] = "" will be true for every row if the textbox has a value of empty string.

Does that help?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

jwellington Ten Centuries Points: 1062 More actions · Answer 4

Hi,

Thanks so much for your help. Saved me lots of time.

I ended up using [TextValue] is Null, but same concept. This then works directly with the function of Combo boxes.

Thanks again.

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 5

I used union queries to populate my combo boxes so that the value of "left arrow (the sign won't show up in the post) All right arrow "). By arrows I mean caps + , and caps + . .

Then if all was selected, you essentially get all the records. I may have missed a detail, as it's been years since I've done one, but that should get you on that path if you choose to go down it.