Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Queries and JET SQL FAQ

Using Functions in Queries

How do you pass multiple textbox values to query criteria when one or more values may be null? by cariengon
Posted: 13 Feb 03

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!!!

Warmest Regards -

Back to Microsoft: Access Queries and JET SQL FAQ Index
Back to Microsoft: Access Queries and JET SQL Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close