Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How do you pass multiple textbox values to query criteria when one or more values may be null?
Using Functions in Queries
I was having the problem of passing several textboxes from a form to a the criteria in a query. If the textbox was null or empty in any of the fields, it wouldn't properly pull the data for the textboxes that were populated. I have 3 textboxes and 1 combobox, only 1 of the textboxes need to be populated by the user.
The posting by tlbroadbent on July 13, 2002 (Thread 701-304412 - Selecting Query Criteria from forms) was my answer - but only after pulling my hair out trying to figure the exact context to use in my critera line in my query... (Terry posted in the SQL context and not what should be entered in the Criteria line in the query).
So, for those of you needed this solution - for passing multiple textbox values to a Query Criteria line - try the following:
[Forms]![FrmCustom]![Hzip] Or Nz([Forms]![FrmCustom]![Hzip], "") = ""
([Forms]![frmCustom][HZip] is the textbox or combobox on the form)
What this does is return ANY value in the column, just as if there was nothing put in the Criteria, if the value in the textbox that is being refered to is Null or Empty ("").
I actually expanded mine and included the Like "*", because I didn't want the user to have to know the exact number they were looking for... This is my syntax exactly as I put it in the criteria:
(Like "*" & [Forms]![7000_ManualSettle_Select_frm]![txtContractID] & "*") Or nz([Forms]![7000_ManualSettle_Select_frm]![txtContractID],"")="")
I'm amazed at how simple this solution was - and actually worked. I tried every other combination out there and kept getting no results.
THANKS Terry!!!![[2thumbsup]](/data/assets/smilies/2thumbsup.gif "[2thumbsup] [2thumbsup]")
Warmest Regards -
Carie
The posting by tlbroadbent on July 13, 2002 (Thread 701-304412 - Selecting Query Criteria from forms) was my answer - but only after pulling my hair out trying to figure the exact context to use in my critera line in my query... (Terry posted in the SQL context and not what should be entered in the Criteria line in the query).
So, for those of you needed this solution - for passing multiple textbox values to a Query Criteria line - try the following:
[Forms]![FrmCustom]![Hzip] Or Nz([Forms]![FrmCustom]![Hzip], "") = ""
([Forms]![frmCustom][HZip] is the textbox or combobox on the form)
What this does is return ANY value in the column, just as if there was nothing put in the Criteria, if the value in the textbox that is being refered to is Null or Empty ("").
I actually expanded mine and included the Like "*", because I didn't want the user to have to know the exact number they were looking for... This is my syntax exactly as I put it in the criteria:
(Like "*" & [Forms]![7000_ManualSettle_Select_frm]![txtContractID] & "*") Or nz([Forms]![7000_ManualSettle_Select_frm]![txtContractID],"")="")
I'm amazed at how simple this solution was - and actually worked. I tried every other combination out there and kept getting no results.
THANKS Terry!!!
Warmest Regards -
Carie