[AccessD] Selection by Textbox Entries

Keith,

A quick(kinda brutal) stab at the building the SQL.  Assuming one of the 
fields will be populated and default value of all fields is ""

Good luck! Mark

Dim MySQL
MySQL = "Select A.* from tblMyTable A Where "
If Me!Month = "" Then
Else
    MySQL = MySQL & "A.month = '" & Me!Month & "' and "
End If
If Me!Year = "" Then
Else
    MySQL = MySQL & "A.year = '" & Me!Year & "' and "
End If
If Me!Entity = "" Then
Else
    MySQL = MySQL & "A.entity = '" & Me!Entity & "' and "
End If

MySQL = Left(MySQL, Len(MySQL) - 5) & ";"


>From: "Keith Williamson" <Kwilliamson at rtkl.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 12:42:40 -0500
>
>Well...not exactly.
>
>The current fields I will be using (and I might add more) are:
>
>Month
>Year
>Entity
>
>So, for example....we could populate only Year and Entity...meaning I
>want all records for that Year and Entity.  Or, only input
>Entity.....meaning all records for that Entity only (which would include
>all months and years.)
>
>
>But I don't know how to convert a null (or even "ALL"..if I were to use
>a dropdown box) to mean "select all".  Normally...if you want all...you
>simply don't include that field in your query.  But....once it is
>included as a variable....how do you tell the query that Null (or "All")
>means don't filter this field?  That is, without creating a nested IF
>for every possible scenario?
>
>Am I overcomplicating this?
>
>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
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Beach Access
>Software
>Sent: Friday, January 05, 2007 12:10 PM
>To: 'Access Developers discussion and problem solving'
>Subject: Re: [AccessD] Selection by Textbox Entries
>
>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
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
>From photos to predictions, The MSN Entertainment Guide to Golden Globes has 
it all. http://tv.msn.com/tv/globes2007/?icid=nctagline1