Query to filter data based on Text Box on a form

G

Guest

Hi, I have a form with 2 Text Box acting as control to filter data in a
query, however 3 of 4 possible combinations are working fine but the 4th one
it does not. The two text box are TxStreet (street name) and TxNo (Street
number). When I search by:

1) If I live both controls empty I dont' get any record, which is OK
2) Street number and Street name, the result is OK
3) Street number only, the result is OK
4) When I search by street name only I don’t get any record

So the 4th combination is my problem, I have tried several different
combination of criteria without lock. What I am doing wrong? This is what I
have in my query:


SELECT tblData.*
FROM tblData
WHERE (((tblData.Street) Like [Forms]![FrmSearch]![TxStreet] & "*") AND
((tblData.[No])=[Forms]![FrmSearch]![TxNo]));
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

SELECT *
FROM tblData
WHERE Street Like [Forms]![FrmSearch]![TxStreet] & "*"
AND ([No]=[Forms]![FrmSearch]![TxNo] OR [Forms]![FrmSearch]![TxNo] IS
NULL)

If the TxNo control is null you have to consider that in the query's
criteria. The criteria "([No]=[Forms]![FrmSearch]![TxNo] OR
[Forms]![FrmSearch]![TxNo] IS NULL)" means use the value in the control,
but, if it is null ignore it.

The criteria "Street Like [Forms]![FrmSearch]![TxStreet] & '*'" will
always work, because when the txStreet is empty (NULL) the criteria is
just "*", which means Street equals anything, which will always return a
row.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUBUF4echKqOuFEgEQIuUwCdHK3xu0LBBn8+aJRaKPEnysoqQm0AnRVi
h5QUmcE2mymeYODZlq3N0hj3
=0KlI
-----END PGP SIGNATURE-----
 
G

Guest

Thank you much Sir. However, when I open the form the subform in my seach
form showns all the records from my table and it should NOT because both
controls are empty, what can I do to solve this problem?

Thank you for your assistance.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

SELECT *
FROM tblData
WHERE Street Like [Forms]![FrmSearch]![TxStreet] & "*"
AND ([No]=[Forms]![FrmSearch]![TxNo] OR [Forms]![FrmSearch]![TxNo] IS
NULL)

If the TxNo control is null you have to consider that in the query's
criteria. The criteria "([No]=[Forms]![FrmSearch]![TxNo] OR
[Forms]![FrmSearch]![TxNo] IS NULL)" means use the value in the control,
but, if it is null ignore it.

The criteria "Street Like [Forms]![FrmSearch]![TxStreet] & '*'" will
always work, because when the txStreet is empty (NULL) the criteria is
just "*", which means Street equals anything, which will always return a
row.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUBUF4echKqOuFEgEQIuUwCdHK3xu0LBBn8+aJRaKPEnysoqQm0AnRVi
h5QUmcE2mymeYODZlq3N0hj3
=0KlI
-----END PGP SIGNATURE-----
Hi, I have a form with 2 Text Box acting as control to filter data in a
query, however 3 of 4 possible combinations are working fine but the 4th one
it does not. The two text box are TxStreet (street name) and TxNo (Street
number). When I search by:

1) If I live both controls empty I dont' get any record, which is OK
2) Street number and Street name, the result is OK
3) Street number only, the result is OK
4) When I search by street name only I don’t get any record

So the 4th combination is my problem, I have tried several different
combination of criteria without lock. What I am doing wrong? This is what I
have in my query:


SELECT tblData.*
FROM tblData
WHERE (((tblData.Street) Like [Forms]![FrmSearch]![TxStreet] & "*") AND
((tblData.[No])=[Forms]![FrmSearch]![TxNo]));
Click to expand...
Click to expand...
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ah, when both controls are empty my criteria mean "select all rows."
You can try this (untested):

SELECT *
FROM tblData
WHERE IIf([Forms]![FrmSearch]![TxStreet] IS NULL
AND [Forms]![FrmSearch]![TxNo] IS NULL, FALSE,
Street Like [Forms]![FrmSearch]![TxStreet] & "*"
AND [No]=Nz([Forms]![FrmSearch]![TxNo],[No]))

The IIf() function is supposed to prevent the criteria from working when
both controls do not have a value. If one, or the other, does have a
value, the query will return rows based on the values in the controls.

The [No]=Nz(...) function is a quicker way to say:

[No]=[Forms]![FrmSearch]![TxNo] OR [Forms]![FrmSearch]![TxNo] IS
NULL)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **


-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUF0NoechKqOuFEgEQJEiwCeLaYk4zyVl44jEmZTLGp4mtzq0dkAoPxH
KmRs2Ip+KuDjSIRb7+ONmxBj
=flPj
-----END PGP SIGNATURE-----
Silvio said:
Thank you much Sir. However, when I open the form the subform in my seach
form showns all the records from my table and it should NOT because both
controls are empty, what can I do to solve this problem?

Thank you for your assistance.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

SELECT *
FROM tblData
WHERE Street Like [Forms]![FrmSearch]![TxStreet] & "*"
AND ([No]=[Forms]![FrmSearch]![TxNo] OR [Forms]![FrmSearch]![TxNo] IS
NULL)

If the TxNo control is null you have to consider that in the query's
criteria. The criteria "([No]=[Forms]![FrmSearch]![TxNo] OR
[Forms]![FrmSearch]![TxNo] IS NULL)" means use the value in the control,
but, if it is null ignore it.

The criteria "Street Like [Forms]![FrmSearch]![TxStreet] & '*'" will
always work, because when the txStreet is empty (NULL) the criteria is
just "*", which means Street equals anything, which will always return a
row.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUBUF4echKqOuFEgEQIuUwCdHK3xu0LBBn8+aJRaKPEnysoqQm0AnRVi
h5QUmcE2mymeYODZlq3N0hj3
=0KlI
-----END PGP SIGNATURE-----
Hi, I have a form with 2 Text Box acting as control to filter data in a
query, however 3 of 4 possible combinations are working fine but the 4th one
it does not. The two text box are TxStreet (street name) and TxNo (Street
number). When I search by:

1) If I live both controls empty I dont' get any record, which is OK
2) Street number and Street name, the result is OK
3) Street number only, the result is OK
4) When I search by street name only I don’t get any record

So the 4th combination is my problem, I have tried several different
combination of criteria without lock. What I am doing wrong? This is what I
have in my query:


SELECT tblData.*
FROM tblData
WHERE (((tblData.Street) Like [Forms]![FrmSearch]![TxStreet] & "*") AND
((tblData.[No])=[Forms]![FrmSearch]![TxNo]));
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thank you so much, it works just fine now :)

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ah, when both controls are empty my criteria mean "select all rows."
You can try this (untested):

SELECT *
FROM tblData
WHERE IIf([Forms]![FrmSearch]![TxStreet] IS NULL
AND [Forms]![FrmSearch]![TxNo] IS NULL, FALSE,
Street Like [Forms]![FrmSearch]![TxStreet] & "*"
AND [No]=Nz([Forms]![FrmSearch]![TxNo],[No]))

The IIf() function is supposed to prevent the criteria from working when
both controls do not have a value. If one, or the other, does have a
value, the query will return rows based on the values in the controls.

The [No]=Nz(...) function is a quicker way to say:

[No]=[Forms]![FrmSearch]![TxNo] OR [Forms]![FrmSearch]![TxNo] IS
NULL)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **


-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUF0NoechKqOuFEgEQJEiwCeLaYk4zyVl44jEmZTLGp4mtzq0dkAoPxH
KmRs2Ip+KuDjSIRb7+ONmxBj
=flPj
-----END PGP SIGNATURE-----
Silvio said:
Thank you much Sir. However, when I open the form the subform in my seach
form showns all the records from my table and it should NOT because both
controls are empty, what can I do to solve this problem?

Thank you for your assistance.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

SELECT *
FROM tblData
WHERE Street Like [Forms]![FrmSearch]![TxStreet] & "*"
AND ([No]=[Forms]![FrmSearch]![TxNo] OR [Forms]![FrmSearch]![TxNo] IS
NULL)

If the TxNo control is null you have to consider that in the query's
criteria. The criteria "([No]=[Forms]![FrmSearch]![TxNo] OR
[Forms]![FrmSearch]![TxNo] IS NULL)" means use the value in the control,
but, if it is null ignore it.

The criteria "Street Like [Forms]![FrmSearch]![TxStreet] & '*'" will
always work, because when the txStreet is empty (NULL) the criteria is
just "*", which means Street equals anything, which will always return a
row.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUBUF4echKqOuFEgEQIuUwCdHK3xu0LBBn8+aJRaKPEnysoqQm0AnRVi
h5QUmcE2mymeYODZlq3N0hj3
=0KlI
-----END PGP SIGNATURE-----

Silvio wrote:
Hi, I have a form with 2 Text Box acting as control to filter data in a
query, however 3 of 4 possible combinations are working fine but the 4th one
it does not. The two text box are TxStreet (street name) and TxNo (Street
number). When I search by:

1) If I live both controls empty I dont' get any record, which is OK
2) Street number and Street name, the result is OK
3) Street number only, the result is OK
4) When I search by street name only I don’t get any record

So the 4th combination is my problem, I have tried several different
combination of criteria without lock. What I am doing wrong? This is what I
have in my query:


SELECT tblData.*
FROM tblData
WHERE (((tblData.Street) Like [Forms]![FrmSearch]![TxStreet] & "*") AND
((tblData.[No])=[Forms]![FrmSearch]![TxNo]));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Nz problem 9
Form using a query to look up values 5
search facility 8
sort numbers in a query 3
Writing data to table from listbox/text box 1
Query based on Form Fields 7
Data Type mismatch error 3
Query using text from form or anything if empty 2

Top