[AccessD] Selection by Textbox Entries

Keith,

2 scenarios...1 Rocky already addressed...use IF statements to created the 
needed SQL.

Another that I've used in the past(directory type lookup: First Name, Last 
Name, Phone,ect)...where I need to use wild cards was to create an unbound 
hidden text box(txtWildBox) for each of the required search fields.  Default 
value would be "*". The after update of your input boxes would populate your 
txtWildBox with "*YourCriteria*".  The query/record_source would then be 
something like:

SELECT DISTINCTROW tblComplaintRecords.LastName, 
tblComplaintRecords.FirstName, tblComplaintRecords.LogNumber
FROM tblComplaintRecords
WHERE (((tblComplaintRecords.LastName) Like 
[Forms]![frmChooseRecord]![lastwildbox]) AND 
((tblComplaintRecords.FirstName) Like 
[Forms]![frmChooseRecord]![firstwildbox]))
ORDER BY tblComplaintRecords.LastName, tblComplaintRecords.FirstName;

The only issue I ran into recently was that if one of the fields being 
queried had a null in the data...it would not return.  To get around 
this...I changed the query to something like:

SELECT tblData.ID, tblData.CustName, tblData.CCMS_User, tblData.Status
FROM tblData
WHERE (((IIf(IsNull([CCMS_CaseID])=True,"",[CCMS_CaseID])) Like 
[Forms]![frmLookup]![WildCCMS]) AND 
((IIf(IsNull([AuthNumber])=True,"",[AuthNumber])) Like 
[Forms]![frmLookup]![WildAuthNumber]) AND 
((IIf(IsNull([CustName])=True,"",[CustName])) Like 
[Forms]![frmLookup]![WildName]))
;

All I did here was to take the field that would potentially have nulls(and 
before someone asks...I had to have nulls in these fields for other 
reasons), and created a field like:
IIf(IsNull([AuthNumber])=True,"",[AuthNumber])

and queried this field.

I'm not sure the best approach...but this is what I use.  I hope it helps.  
If you have any questions, please let me know.

Thanks,

Mark A. Matte

>From: "Beach Access Software" <bchacc at san.rr.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "'Access Developers discussion and problem 
>solving'"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Selection by Textbox Entries
>Date: Fri, 5 Jan 2007 09:09:32 -0800
>
>Keith:
>
>Assuming you're creating the SQL statement for the record source on the 
>fly,
>you've only got two conditions to check to see if you want to include the
>contents of the text boxes in the WHERE clause - null (or blank) and "ALL".
>
>Rocky
>
>
>Rocky Smolin
>Beach Access Software
>858-259-4334
>www.e-z-mrp.com
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith Williamson
>Sent: Friday, January 05, 2007 8:58 AM
>To: Access Developers discussion and problem solving
>Subject: [AccessD] Selection by Textbox Entries
>
>Happy New Year All!!
>
>
>
>I possibly have a simple question.  I have a form, that has at least (3)
>input textboxes.  Based on criteria in these boxes, I am having the form
>populate the recordsource with the appropriate data.  What I am not sure
>of, is how to setup any of these boxes to NOT be included in the
>criteria either A) if they don't input anything; or B) input "All".  I
>could also do this as drop boxes, with one of the options being "All."
>But short of a bunch of nested IF statements....is there an easier way
>to create the select statement to NOT filter on one of these fields if
>that field either isnull or = "All"??
>
>
>
>Thanks,
>
>
>
>Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com
>
>RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland
>21231-3305
>
>410-537-6098 direct | 410-276-2136 fax | www.rtkl.com
><http://www.rtkl.com/>
>
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>--
>No virus found in this incoming message.
>Checked by AVG Free Edition.
>Version: 7.5.432 / Virus Database: 268.16.5/616 - Release Date: 1/4/2007
>1:34 PM
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
The MSN Entertainment Guide to Golden Globes is here.  Get all the scoop. 
http://tv.msn.com/tv/globes2007/?icid=nctagline2