The constants are the stub (SELECT and FROM clauses etc) and the tail (ORDER
BY clause) of the SQL stament, which you need so you can patch the WHERE
clause between them. You can mock up a query, and swith it to SQL View (View
menu) to see an example of the statement you are trying to create.
The IsNull() has to wrap around the name of an unbound control (text box) on
your form.
The extra quotes are intended exactly as they are.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<dskillingstad@gmail.com> wrote in message
news:1116456374.600980.19970@o13g2000cwo.googlegro ups.com...[color=blue]
> Thanks for the reponse. I changed the code to match my textbox names
> and had several errors and could not get it to work.First there were
> problems with the WHERE ("Const strcTail...) line, and then errors on
> every "If Not IsNull(.....") line. By the way, I'm assuming (bad idea
> on my part) that """ should be "*" and ""*" should be
> "*"...right....wrong??
>
> I'm not a programming person, but I can follow what you are doing, I am
> just not proficient enough in coding to understand syntax etc.
>
> Thanks for your help. Looks like I'll be creating multiple queries.
>
> dskillingstad
>
>
> Allen Browne wrote:[color=green]
>> Yes, we regularly see people doing this kind of thing, but it is not[/color]
> very[color=green]
>> efficient approach.
>>
>> Here's another alternative. This search form consists of lots of[/color]
> unbound[color=green]
>> controls in the Form Header, where the user can enter a value to[/color]
> match for[color=green]
>> FullName, Address, and so on. It has a Search button, and clicking[/color]
> this[color=green]
>> button build the SQL statement, and assigns it to the search form. If[/color]
> you[color=green]
>> set up the search form to be a continuous form, you can then see all[/color]
> the[color=green]
>> results in the detail section, and then double-click one to move your[/color]
>[color=green]
>> original form to the found record.
>>
>> The Click of the command button builds the WHERE clause from the[/color]
> non-blank[color=green]
>> boxes like this:
>>
>> Private Sub cmdSearch_Click()
>> dim strWhere As String 'The WHERE clause of the SQL statement.
>> Dim lngLen As Long 'Length of string.
>> Const strcStub = "SELECT FullName, Address, State, Zip FROM[/color]
> Entity WHERE[color=green]
>> ("
>> Const strcTail = ") ORDER BY FullName;"
>>
>> If Not IsNull(Me.txtFindFullName) Then
>> strWhere = strWhere & "([FullName] = """ & Me.txtFindFullName[/color]
> & """)[color=green]
>> AND "
>> End If
>>
>> If Not IsNull(Me.txtFindAddress) Then
>> strWhere = strWhere & "([Address] Like ""*" &[/color]
> Me.txtFindAddress &[color=green]
>> "*"") AND "
>> End If
>>
>> 'etc for the other boxes.
>>
>> lngLen = Len(strWhere) - 5 'without trailing " AND "
>> If lngLen <= 0 Then
>> MsgBox "No criteria"
>> Else
>> strWhere = Left$(strWhere, lngLen)
>> Me.RecordSource = strcStub & strWhere & strTail
>> End If
>> End Sub
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> Tips for Access users -
http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> <dskillingstad@gmail.com> wrote in message
>> news:1116393888.339678.175640@o13g2000cwo.googlegr oups.com...[color=darkred]
>> > I'd appreciate any help I can get. I'm not sure what I'm doing[/color][/color]
> wrong,[color=green][color=darkred]
>> > but....
>> > I've searched these groups for some solutions but no luck.
>> >
>> > I have an unbound form (frmSearch), with several unbound text boxes[/color][/color]
> on[color=green][color=darkred]
>> > it and a command button bound to a macro which fires off a[/color][/color]
> parameter[color=green][color=darkred]
>> > query based on the criteria/string that the user types into the[/color][/color]
> text[color=green][color=darkred]
>> > boxes on frmSearch. My goal is to create a search form where the[/color][/color]
> user[color=green][color=darkred]
>> > can search by any of the fields on the search form, hit the[/color][/color]
> "Search"[color=green][color=darkred]
>> > button. I've set everything up according to Microsoft Article[/color][/color]
> 304428,[color=green][color=darkred]
>> > but no luck.
>> >
>> > I have it all set up, but after I run the query one time, the[/color][/color]
> criteria[color=green][color=darkred]
>> > in the query changes. Below is the original code before I run the
>> > query for the first time.
>> >
>> > SELECT Entity.FullName, Entity.Address, Entity.State, Entity.Zip
>> > FROM Entity
>> > WHERE (((Entity.FullName) Like "*" & [Forms]![frmSearch]![FullName][/color][/color]
> &[color=green][color=darkred]
>> > "*" Or (Entity.FullName) Like [Forms]![frmSearch]![FullName] Is[/color][/color]
> Null)[color=green][color=darkred]
>> > AND ((Entity.Address) Like "*" & [Forms]![frmSearch]![Address] &[/color][/color]
> "*" Or[color=green][color=darkred]
>> > (Entity.Address) Like [Forms]![frmSearch]![Address] Is Null) AND
>> > ((Entity.State) Like [Forms]![frmSearch]![State] Or (Entity.State)[/color][/color]
> Like[color=green][color=darkred]
>> > [Forms]![frmSearch]![State] Is Null) AND ((Entity.Zip) Like "*" &
>> > [Forms]![frmSearch]![Zip] & "*" Or (Entity.Zip) Like
>> > [Forms]![frmSearch]![Zip] Is Null));
>> >
>> > After I fire off the query the first time, the criteria in the[/color][/color]
> query is[color=green][color=darkred]
>> > all changed. I'd paste it here but it'd take several screen[/color][/color]
> scrolls to[color=green][color=darkred]
>> > get it all. Basically, Access places several "Like Forms!....."
>> > criteria within each field, then creates additional fields for each[/color][/color]
> Is[color=green][color=darkred]
>> > Null criteria. Below is a sample of the SQL Access creates:
>> >
>> > SELECT Entity.FullName, Entity.Address, Entity.State, Entity.Zip
>> > FROM Entity
>> > WHERE (((Entity.FullName) Like "*" & [Forms]![frmSearch]![FullName][/color][/color]
> &[color=green][color=darkred]
>> > "*") AND ((Entity.Address) Like "*" & [Forms]![frmSearch]![Address][/color][/color]
> &[color=green][color=darkred]
>> > "*") AND ((Entity.State) Like [Forms]![frmSearch]![State]) AND
>> > ((Entity.Zip) Like "*" & [Forms]![frmSearch]![Zip] & "*")) OR
>> > (((Entity.Address) Like "*" & [Forms]![frmSearch]![Address] & "*")[/color][/color]
> AND[color=green][color=darkred]
>> > ((Entity.State) Like [Forms]![frmSearch]![State]) AND ((Entity.Zip)
>> > Like "*" & [Forms]![frmSearch]![Zip] & "*") AND[/color][/color]
> ((([Entity].[FullName])[color=green][color=darkred]
>> > Like [Forms]![frmSearch]![FullName]) Is Null)) OR[/color][/color]
> (((Entity.FullName)[color=green][color=darkred]
>> > Like "*" & [Forms]![frmSearch]![FullName] & "*") AND[/color][/color]
> ((Entity.State)[color=green][color=darkred]
>> > Like [Forms]![frmSearch]![State]) AND ((Entity.Zip) Like "*" &
>> > [Forms]![frmSearch]![Zip] & "*") AND ((([Entity].[Address])
>> > bla bla bla
>> >
>> > I'm just trying to create a simple query based on a form, with 1-5
>> > criteria that the user types in. What am I doing wrong? I know[/color][/color]
> it's[color=green][color=darkred]
>> > possible because others have done it and I've followed the tips[/color][/color]
> from[color=green][color=darkred]
>> > others in this groups, but I can't get it for some reason.
>> >
>> > Any help would be appreciated.
>> >
>> > dskillingstad[/color][/color]
>[/color]
