Query by form problem

[dskillingstad]

I'd appreciate any help I can get. I'm not sure what I'm doing wrong,
but....
I've searched these groups for some solutions but no luck.

I have an unbound form (frmSearch), with several unbound text boxes on
it and a command button bound to a macro which fires off a parameter
query based on the criteria/string that the user types into the text
boxes on frmSearch. My goal is to create a search form where the user
can search by any of the fields on the search form, hit the "Search"
button. I've set everything up according to Microsoft Article 304428,
but no luck.

I have it all set up, but after I run the query one time, the criteria
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] &
"*" Or (Entity.FullName) Like [Forms]![frmSearch]![FullName] Is Null)
AND ((Entity.Address) Like "*" & [Forms]![frmSearch]![Address] & "*" Or
(Entity.Address) Like [Forms]![frmSearch]![Address] Is Null) AND
((Entity.State) Like [Forms]![frmSearch]![State] Or (Entity.State) Like
[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 query is
all changed. I'd paste it here but it'd take several screen scrolls to
get it all. Basically, Access places several "Like Forms!....."
criteria within each field, then creates additional fields for each Is
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] &
"*") AND ((Entity.Address) Like "*" & [Forms]![frmSearch]![Address] &
"*") AND ((Entity.State) Like [Forms]![frmSearch]![State]) AND
((Entity.Zip) Like "*" & [Forms]![frmSearch]![Zip] & "*")) OR
(((Entity.Address) Like "*" & [Forms]![frmSearch]![Address] & "*") AND
((Entity.State) Like [Forms]![frmSearch]![State]) AND ((Entity.Zip)
Like "*" & [Forms]![frmSearch]![Zip] & "*") AND ((([Entity].[FullName])
Like [Forms]![frmSearch]![FullName]) Is Null)) OR (((Entity.FullName)
Like "*" & [Forms]![frmSearch]![FullName] & "*") AND ((Entity.State)
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 it's
possible because others have done it and I've followed the tips from
others in this groups, but I can't get it for some reason.

Any help would be appreciated.

dskillingstad

[Allen Browne]

Yes, we regularly see people doing this kind of thing, but it is not very
efficient approach.

Here's another alternative. This search form consists of lots of unbound
controls in the Form Header, where the user can enter a value to match for
FullName, Address, and so on. It has a Search button, and clicking this
button build the SQL statement, and assigns it to the search form. If you
set up the search form to be a continuous form, you can then see all the
results in the detail section, and then double-click one to move your
original form to the found record.

The Click of the command button builds the WHERE clause from the non-blank
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 Entity WHERE
("
Const strcTail = ") ORDER BY FullName;"

If Not IsNull(Me.txtFindFullName) Then
strWhere = strWhere & "([FullName] = """ & Me.txtFindFullName & """)
AND "
End If

If Not IsNull(Me.txtFindAddress) Then
strWhere = strWhere & "([Address] Like ""*" & Me.txtFindAddress &
"*"") 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.

<ds***********@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

I'd appreciate any help I can get. I'm not sure what I'm doing wrong,
but....
I've searched these groups for some solutions but no luck.

I have an unbound form (frmSearch), with several unbound text boxes on
it and a command button bound to a macro which fires off a parameter
query based on the criteria/string that the user types into the text
boxes on frmSearch. My goal is to create a search form where the user
can search by any of the fields on the search form, hit the "Search"
button. I've set everything up according to Microsoft Article 304428,
but no luck.

I have it all set up, but after I run the query one time, the criteria
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] &
"*" Or (Entity.FullName) Like [Forms]![frmSearch]![FullName] Is Null)
AND ((Entity.Address) Like "*" & [Forms]![frmSearch]![Address] & "*" Or
(Entity.Address) Like [Forms]![frmSearch]![Address] Is Null) AND
((Entity.State) Like [Forms]![frmSearch]![State] Or (Entity.State) Like
[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 query is
all changed. I'd paste it here but it'd take several screen scrolls to
get it all. Basically, Access places several "Like Forms!....."
criteria within each field, then creates additional fields for each Is
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] &
"*") AND ((Entity.Address) Like "*" & [Forms]![frmSearch]![Address] &
"*") AND ((Entity.State) Like [Forms]![frmSearch]![State]) AND
((Entity.Zip) Like "*" & [Forms]![frmSearch]![Zip] & "*")) OR
(((Entity.Address) Like "*" & [Forms]![frmSearch]![Address] & "*") AND
((Entity.State) Like [Forms]![frmSearch]![State]) AND ((Entity.Zip)
Like "*" & [Forms]![frmSearch]![Zip] & "*") AND ((([Entity].[FullName])
Like [Forms]![frmSearch]![FullName]) Is Null)) OR (((Entity.FullName)
Like "*" & [Forms]![frmSearch]![FullName] & "*") AND ((Entity.State)
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 it's
possible because others have done it and I've followed the tips from
others in this groups, but I can't get it for some reason.

Any help would be appreciated.

dskillingstad

[dskillingstad]

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:

Yes, we regularly see people doing this kind of thing, but it is not very efficient approach.

Here's another alternative. This search form consists of lots of unbound controls in the Form Header, where the user can enter a value to match for FullName, Address, and so on. It has a Search button, and clicking this button build the SQL statement, and assigns it to the search form. If you set up the search form to be a continuous form, you can then see all the results in the detail section, and then double-click one to move your original form to the found record.

The Click of the command button builds the WHERE clause from the non-blank 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 Entity WHERE ("
Const strcTail = ") ORDER BY FullName;"

If Not IsNull(Me.txtFindFullName) Then
strWhere = strWhere & "([FullName] = """ & Me.txtFindFullName & """) AND "
End If

If Not IsNull(Me.txtFindAddress) Then
strWhere = strWhere & "([Address] Like ""*" & Me.txtFindAddress & "*"") 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.

<ds***********@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

I'd appreciate any help I can get. I'm not sure what I'm doing wrong, but....
I've searched these groups for some solutions but no luck.

I have an unbound form (frmSearch), with several unbound text boxes on it and a command button bound to a macro which fires off a parameter query based on the criteria/string that the user types into the text boxes on frmSearch. My goal is to create a search form where the user can search by any of the fields on the search form, hit the "Search" button. I've set everything up according to Microsoft Article 304428, but no luck.

I have it all set up, but after I run the query one time, the criteria 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] & "*" Or (Entity.FullName) Like [Forms]![frmSearch]![FullName] Is Null) AND ((Entity.Address) Like "*" & [Forms]![frmSearch]![Address] & "*" Or (Entity.Address) Like [Forms]![frmSearch]![Address] Is Null) AND
((Entity.State) Like [Forms]![frmSearch]![State] Or (Entity.State) Like [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 query is all changed. I'd paste it here but it'd take several screen scrolls to get it all. Basically, Access places several "Like Forms!....."
criteria within each field, then creates additional fields for each Is 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] & "*") AND ((Entity.Address) Like "*" & [Forms]![frmSearch]![Address] & "*") AND ((Entity.State) Like [Forms]![frmSearch]![State]) AND
((Entity.Zip) Like "*" & [Forms]![frmSearch]![Zip] & "*")) OR
(((Entity.Address) Like "*" & [Forms]![frmSearch]![Address] & "*") AND ((Entity.State) Like [Forms]![frmSearch]![State]) AND ((Entity.Zip)
Like "*" & [Forms]![frmSearch]![Zip] & "*") AND ((([Entity].[FullName]) Like [Forms]![frmSearch]![FullName]) Is Null)) OR (((Entity.FullName) Like "*" & [Forms]![frmSearch]![FullName] & "*") AND ((Entity.State) 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 it's possible because others have done it and I've followed the tips from others in this groups, but I can't get it for some reason.

Any help would be appreciated.

dskillingstad

[Allen Browne]

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.

<ds***********@gmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

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:

Yes, we regularly see people doing this kind of thing, but it is not

very

efficient approach.

Here's another alternative. This search form consists of lots of

unbound

controls in the Form Header, where the user can enter a value to

match for

FullName, Address, and so on. It has a Search button, and clicking

this

button build the SQL statement, and assigns it to the search form. If

you

set up the search form to be a continuous form, you can then see all

the

results in the detail section, and then double-click one to move your

original form to the found record.

The Click of the command button builds the WHERE clause from the

non-blank

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

Entity WHERE

("
Const strcTail = ") ORDER BY FullName;"

If Not IsNull(Me.txtFindFullName) Then
strWhere = strWhere & "([FullName] = """ & Me.txtFindFullName

& """)

AND "
End If

If Not IsNull(Me.txtFindAddress) Then
strWhere = strWhere & "([Address] Like ""*" &

Me.txtFindAddress &

"*"") 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.

<ds***********@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

> I'd appreciate any help I can get. I'm not sure what I'm doing wrong, > but....
> I've searched these groups for some solutions but no luck.
>
> I have an unbound form (frmSearch), with several unbound text boxes on > it and a command button bound to a macro which fires off a parameter > query based on the criteria/string that the user types into the text > boxes on frmSearch. My goal is to create a search form where the user > can search by any of the fields on the search form, hit the "Search" > button. I've set everything up according to Microsoft Article 304428, > but no luck.
>
> I have it all set up, but after I run the query one time, the criteria > 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] & > "*" Or (Entity.FullName) Like [Forms]![frmSearch]![FullName] Is Null) > AND ((Entity.Address) Like "*" & [Forms]![frmSearch]![Address] & "*" Or > (Entity.Address) Like [Forms]![frmSearch]![Address] Is Null) AND
> ((Entity.State) Like [Forms]![frmSearch]![State] Or (Entity.State) Like > [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 query is > all changed. I'd paste it here but it'd take several screen scrolls to > get it all. Basically, Access places several "Like Forms!....."
> criteria within each field, then creates additional fields for each Is > 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] & > "*") AND ((Entity.Address) Like "*" & [Forms]![frmSearch]![Address] & > "*") AND ((Entity.State) Like [Forms]![frmSearch]![State]) AND
> ((Entity.Zip) Like "*" & [Forms]![frmSearch]![Zip] & "*")) OR
> (((Entity.Address) Like "*" & [Forms]![frmSearch]![Address] & "*") AND > ((Entity.State) Like [Forms]![frmSearch]![State]) AND ((Entity.Zip)
> Like "*" & [Forms]![frmSearch]![Zip] & "*") AND ((([Entity].[FullName]) > Like [Forms]![frmSearch]![FullName]) Is Null)) OR (((Entity.FullName) > Like "*" & [Forms]![frmSearch]![FullName] & "*") AND ((Entity.State) > 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 it's > possible because others have done it and I've followed the tips from > others in this groups, but I can't get it for some reason.
>
> Any help would be appreciated.
>
> dskillingstad