QUERY EXACT NUMBER

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey I saw a couple posts that were kinda like this. But not quite. I have a
form that merges multiple queries together to filter down the results to find
exactly what I am looking for. Problem is, If I search for "2" it will bring
back anything that starts with 2 or even has a 2. How do I fix this so it
only looks for what is exactly 2?
Here is a copy of my SQL. Its kinda long.


SELECT [ANGLE IRON].[ANGLE LEG 1], [ANGLE IRON].[ANGLE LEG 2], [ANGLE
IRON].[ANGLE THICKNESS], [ANGLE IRON].[MATERIAL TYPE], [ANGLE IRON].[COST PER
POUND], [ANGLE IRON].SUPPLIER, [ANGLE IRON].CONTACT, [ANGLE IRON].[ORDERED
DATE], [ANGLE IRON].[EXPECTED DATE], [ANGLE IRON].QUANTITY, [ANGLE
IRON].[QUOTE DATE], [ANGLE IRON].[PURCHASE ORDER], [ANGLE IRON].COMMENTS
FROM [ANGLE IRON]
WHERE ((([ANGLE IRON].[ANGLE LEG 1]) Like "*" & [Forms]![ANGLE IRON
INQUIRY]![ANGLE LEG 1] & "*" & Not Null) AND (([ANGLE IRON].[ANGLE LEG 2])
Like "*" & [Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 2] & "*" & Not Null) AND
(([ANGLE IRON].[ANGLE THICKNESS]) Like "*" & [Forms]![ANGLE IRON
INQUIRY]![ANGLE THICKNESS] & "*" Or ([ANGLE IRON].[ANGLE THICKNESS]) Is Null)
AND (([ANGLE IRON].[MATERIAL TYPE]) Like "*" & [Forms]![ANGLE IRON
INQUIRY]![MATERIAL TYPE] & "*" Or ([ANGLE IRON].[MATERIAL TYPE]) Is Null) AND
(([ANGLE IRON].SUPPLIER) Like "*" & [Forms]![ANGLE IRON INQUIRY]![SUPPLIER] &
"*" Or ([ANGLE IRON].SUPPLIER) Is Null) AND (([ANGLE IRON].[ORDERED DATE])
Like "*" & [Forms]![ANGLE IRON INQUIRY]![ORDER DATE] & "*" Or ([ANGLE
IRON].[ORDERED DATE]) Is Null) AND (([ANGLE IRON].[QUOTE DATE]) Like "*" &
[Forms]![ANGLE IRON INQUIRY]![QUOTE DATE] & "*" Or ([ANGLE IRON].[QUOTE
DATE]) Is Null) AND (([ANGLE IRON].[PURCHASE ORDER]) Like "*" &
[Forms]![ANGLE IRON INQUIRY]![PURCHASE ORDER] & "*" Or ([ANGLE
IRON].[PURCHASE ORDER]) Is Null) AND (([Forms]![ANGLE IRON INQUIRY]![ANGLE
LEG 1]) Like "*" & [Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 1] & "*" & Not
Null));
 
Problem is, If I search for "2" it will bring
back anything that starts with 2 or even has a 2. How do I fix this so it
only looks for what is exactly 2?
Click to expand...

Use a criterion

= <forms reference>

rather than the wildcard criterion

LIKE "*" & <forms reference> & "*"

The LIKE operator uses wildcards; * means "any string of any length"
so you're specifically asking for any series of characters, a 2, and
then any other string of characters. In other words - you're getting
precisely what you are asking for, rather than what you want!

If the field is indexed, you'll also find that the equality search may
run substantially faster.

John W. Vinson[MVP]
 
In your Where clause,
Like .... & "*" returns anything beginning with 2;
Like "*" & ..... returns anything ending with 2; and
Like "*" & ..... & "*" returns anything with a 2 in it.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1175 users have come to me from the newsgroups requesting help
(e-mail address removed)





ZigZagZak said:
Hey I saw a couple posts that were kinda like this. But not quite. I
have a
form that merges multiple queries together to filter down the results to
find
exactly what I am looking for. Problem is, If I search for "2" it will
bring
back anything that starts with 2 or even has a 2. How do I fix this so it
only looks for what is exactly 2?
Here is a copy of my SQL. Its kinda long.


SELECT [ANGLE IRON].[ANGLE LEG 1], [ANGLE IRON].[ANGLE LEG 2], [ANGLE
IRON].[ANGLE THICKNESS], [ANGLE IRON].[MATERIAL TYPE], [ANGLE IRON].[COST
PER
POUND], [ANGLE IRON].SUPPLIER, [ANGLE IRON].CONTACT, [ANGLE IRON].[ORDERED
DATE], [ANGLE IRON].[EXPECTED DATE], [ANGLE IRON].QUANTITY, [ANGLE
IRON].[QUOTE DATE], [ANGLE IRON].[PURCHASE ORDER], [ANGLE IRON].COMMENTS
FROM [ANGLE IRON]
WHERE ((([ANGLE IRON].[ANGLE LEG 1]) Like "*" & [Forms]![ANGLE IRON
INQUIRY]![ANGLE LEG 1] & "*" & Not Null) AND (([ANGLE IRON].[ANGLE LEG 2])
Like "*" & [Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 2] & "*" & Not Null)
AND
(([ANGLE IRON].[ANGLE THICKNESS]) Like "*" & [Forms]![ANGLE IRON
INQUIRY]![ANGLE THICKNESS] & "*" Or ([ANGLE IRON].[ANGLE THICKNESS]) Is
Null)
AND (([ANGLE IRON].[MATERIAL TYPE]) Like "*" & [Forms]![ANGLE IRON
INQUIRY]![MATERIAL TYPE] & "*" Or ([ANGLE IRON].[MATERIAL TYPE]) Is Null)
AND
(([ANGLE IRON].SUPPLIER) Like "*" & [Forms]![ANGLE IRON
INQUIRY]![SUPPLIER] &
"*" Or ([ANGLE IRON].SUPPLIER) Is Null) AND (([ANGLE IRON].[ORDERED DATE])
Like "*" & [Forms]![ANGLE IRON INQUIRY]![ORDER DATE] & "*" Or ([ANGLE
IRON].[ORDERED DATE]) Is Null) AND (([ANGLE IRON].[QUOTE DATE]) Like "*" &
[Forms]![ANGLE IRON INQUIRY]![QUOTE DATE] & "*" Or ([ANGLE IRON].[QUOTE
DATE]) Is Null) AND (([ANGLE IRON].[PURCHASE ORDER]) Like "*" &
[Forms]![ANGLE IRON INQUIRY]![PURCHASE ORDER] & "*" Or ([ANGLE
IRON].[PURCHASE ORDER]) Is Null) AND (([Forms]![ANGLE IRON INQUIRY]![ANGLE
LEG 1]) Like "*" & [Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 1] & "*" & Not
Null));
Click to expand...
 
That would make it simple, but it doesn't work. The reason why is that the
wild cards make it so that on my form I can search by any piece of the
information that I know. Like If I know the pipe size I can type that in.
If I only know the purchase order number I can type that in.....or any
combination of the fields. Any other ideas?
 
That would make it simple, but it doesn't work. The reason why is that the
wild cards make it so that on my form I can search by any piece of the
information that I know. Like If I know the pipe size I can type that in.
If I only know the purchase order number I can type that in.....or any
combination of the fields. Any other ideas?
Click to expand...

Aha. Rereading the query, you've got some other issues! The syntax

& Not Null

will not work, at all. & is a String operator which concatenates text
strings, and "not null" in this context is a query criterion, not a
text string at all.

If you want each control to find exact values for that field you don't
need any LIKE operators AT ALL. Try

SELECT [ANGLE IRON].[ANGLE LEG 1], [ANGLE IRON].[ANGLE LEG 2],
[ANGLE IRON].[ANGLE THICKNESS], [ANGLE IRON].[MATERIAL TYPE],
[ANGLE IRON].[COST PER POUND], [ANGLE IRON].SUPPLIER,
[ANGLE IRON].CONTACT, [ANGLE IRON].[ORDERED DATE],
[ANGLE IRON].[EXPECTED DATE], [ANGLE IRON].QUANTITY,
[ANGLE IRON].[QUOTE DATE], [ANGLE IRON].[PURCHASE ORDER],
[ANGLE IRON].COMMENTS

FROM [ANGLE IRON]

WHERE
([ANGLE IRON].[ANGLE LEG 1] =
[Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 1]
OR [Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 1] IS NULL)
AND
([ANGLE IRON].[ANGLE LEG 2] =
[Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 2]
OR [Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 2] IS NULL)
AND
([ANGLE IRON].[ANGLE THICKNESS] =
[Forms]![ANGLE IRON INQUIRY]![ANGLE THICKNESS]
Or [Forms![ANGLE IRON INQUIRY].[ANGLE THICKNESS] Is Null)
....

etcetera. For each field you're asking that *EITHER* the field exactly
equal the value in the form control, *OR* that the form control be
empty.

An alternative approach is to use VBA code to poll through the
controls on the form, building up a SQL string with an additional
criterion for each form control which is not empty.

John W. Vinson[MVP]
 
The reason I have the "or is not null is because it makes it so that it won't
bring listings that don't have a entry for that field. Like if I changed
that to "or is null" it brings back what I want plus everything that has a
null value in that field. So I changed it to "or is not null" and it started
working correctly. The only thing that doesn't work correctly is the whole
decimal thing. Where it see 2 as being the same as 2.5 and so on. I still
need to try your new criteria though. Thanks for all the help
John Vinson said:
That would make it simple, but it doesn't work. The reason why is that the
wild cards make it so that on my form I can search by any piece of the
information that I know. Like If I know the pipe size I can type that in.
If I only know the purchase order number I can type that in.....or any
combination of the fields. Any other ideas?
Click to expand...

Aha. Rereading the query, you've got some other issues! The syntax

& Not Null

will not work, at all. & is a String operator which concatenates text
strings, and "not null" in this context is a query criterion, not a
text string at all.

If you want each control to find exact values for that field you don't
need any LIKE operators AT ALL. Try

SELECT [ANGLE IRON].[ANGLE LEG 1], [ANGLE IRON].[ANGLE LEG 2],
[ANGLE IRON].[ANGLE THICKNESS], [ANGLE IRON].[MATERIAL TYPE],
[ANGLE IRON].[COST PER POUND], [ANGLE IRON].SUPPLIER,
[ANGLE IRON].CONTACT, [ANGLE IRON].[ORDERED DATE],
[ANGLE IRON].[EXPECTED DATE], [ANGLE IRON].QUANTITY,
[ANGLE IRON].[QUOTE DATE], [ANGLE IRON].[PURCHASE ORDER],
[ANGLE IRON].COMMENTS

FROM [ANGLE IRON]

WHERE
([ANGLE IRON].[ANGLE LEG 1] =
[Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 1]
OR [Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 1] IS NULL)
AND
([ANGLE IRON].[ANGLE LEG 2] =
[Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 2]
OR [Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 2] IS NULL)
AND
([ANGLE IRON].[ANGLE THICKNESS] =
[Forms]![ANGLE IRON INQUIRY]![ANGLE THICKNESS]
Or [Forms![ANGLE IRON INQUIRY].[ANGLE THICKNESS] Is Null)
....

etcetera. For each field you're asking that *EITHER* the field exactly
equal the value in the form control, *OR* that the form control be
empty.

An alternative approach is to use VBA code to poll through the
controls on the form, building up a SQL string with an additional
criterion for each form control which is not empty.

John W. Vinson[MVP]
Click to expand...
 
Ok. I just re-wrote my query as you specified and it now shows "query is to
complex"

John Vinson said:
That would make it simple, but it doesn't work. The reason why is that the
wild cards make it so that on my form I can search by any piece of the
information that I know. Like If I know the pipe size I can type that in.
If I only know the purchase order number I can type that in.....or any
combination of the fields. Any other ideas?
Click to expand...

Aha. Rereading the query, you've got some other issues! The syntax

& Not Null

will not work, at all. & is a String operator which concatenates text
strings, and "not null" in this context is a query criterion, not a
text string at all.

If you want each control to find exact values for that field you don't
need any LIKE operators AT ALL. Try

SELECT [ANGLE IRON].[ANGLE LEG 1], [ANGLE IRON].[ANGLE LEG 2],
[ANGLE IRON].[ANGLE THICKNESS], [ANGLE IRON].[MATERIAL TYPE],
[ANGLE IRON].[COST PER POUND], [ANGLE IRON].SUPPLIER,
[ANGLE IRON].CONTACT, [ANGLE IRON].[ORDERED DATE],
[ANGLE IRON].[EXPECTED DATE], [ANGLE IRON].QUANTITY,
[ANGLE IRON].[QUOTE DATE], [ANGLE IRON].[PURCHASE ORDER],
[ANGLE IRON].COMMENTS

FROM [ANGLE IRON]

WHERE
([ANGLE IRON].[ANGLE LEG 1] =
[Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 1]
OR [Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 1] IS NULL)
AND
([ANGLE IRON].[ANGLE LEG 2] =
[Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 2]
OR [Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 2] IS NULL)
AND
([ANGLE IRON].[ANGLE THICKNESS] =
[Forms]![ANGLE IRON INQUIRY]![ANGLE THICKNESS]
Or [Forms![ANGLE IRON INQUIRY].[ANGLE THICKNESS] Is Null)
....

etcetera. For each field you're asking that *EITHER* the field exactly
equal the value in the form control, *OR* that the form control be
empty.

An alternative approach is to use VBA code to poll through the
controls on the form, building up a SQL string with an additional
criterion for each form control which is not empty.

John W. Vinson[MVP]
Click to expand...
 
Ok I did some more testing and here is what I'm getting. I can run my query
perfectly using up to 6 fields. The original design has 8 and acess says
that the query is to complex and won't run. On this table I can get away
with just 6, however Im not sure on all my other tables. Is this a common
problem?

ZigZagZak said:
Ok. I just re-wrote my query as you specified and it now shows "query is to
complex"

John Vinson said:
That would make it simple, but it doesn't work. The reason why is that the
wild cards make it so that on my form I can search by any piece of the
information that I know. Like If I know the pipe size I can type that in.
If I only know the purchase order number I can type that in.....or any
combination of the fields. Any other ideas?
Click to expand...

Aha. Rereading the query, you've got some other issues! The syntax

& Not Null

will not work, at all. & is a String operator which concatenates text
strings, and "not null" in this context is a query criterion, not a
text string at all.

If you want each control to find exact values for that field you don't
need any LIKE operators AT ALL. Try

SELECT [ANGLE IRON].[ANGLE LEG 1], [ANGLE IRON].[ANGLE LEG 2],
[ANGLE IRON].[ANGLE THICKNESS], [ANGLE IRON].[MATERIAL TYPE],
[ANGLE IRON].[COST PER POUND], [ANGLE IRON].SUPPLIER,
[ANGLE IRON].CONTACT, [ANGLE IRON].[ORDERED DATE],
[ANGLE IRON].[EXPECTED DATE], [ANGLE IRON].QUANTITY,
[ANGLE IRON].[QUOTE DATE], [ANGLE IRON].[PURCHASE ORDER],
[ANGLE IRON].COMMENTS

FROM [ANGLE IRON]

WHERE
([ANGLE IRON].[ANGLE LEG 1] =
[Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 1]
OR [Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 1] IS NULL)
AND
([ANGLE IRON].[ANGLE LEG 2] =
[Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 2]
OR [Forms]![ANGLE IRON INQUIRY]![ANGLE LEG 2] IS NULL)
AND
([ANGLE IRON].[ANGLE THICKNESS] =
[Forms]![ANGLE IRON INQUIRY]![ANGLE THICKNESS]
Or [Forms![ANGLE IRON INQUIRY].[ANGLE THICKNESS] Is Null)
....

etcetera. For each field you're asking that *EITHER* the field exactly
equal the value in the form control, *OR* that the form control be
empty.

An alternative approach is to use VBA code to poll through the
controls on the form, building up a SQL string with an additional
criterion for each form control which is not empty.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Ok. I just re-wrote my query as you specified and it now shows "query is to
complex"
Click to expand...

Please post the SQL again. It may BE too complex - when you start
testing multiple form references for NULL, Access puts each of them
into a new calculated field and the whole query gets really snarky.

I don't understand your 2 problem at all it appears. LIKE "*" & "2" &
"*" should (very inefficiently) find 12.5, 8.20, 200, 2, 3.1415926535
or any number containing the digit 2 anywhere in it. Wildcards simply
are NOT suitable for searching numeric fields!

John W. Vinson[MVP]
 
I decided to just make less possible fields so the sql statment doesn't get
so large after acess re-writes it. I found that 6 search fields works, and I
can get all the forms to have that or less.....so no biggy....Thank you for
all your help with the problem, it does everything I need it to now.
 

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

Query HELP !! 2
SQL query construction 2
Measure out this geometry right-angle for me 7
QUESTION ? - How to provide (Lookup List) with 2 colums + picture 2
Intermediate value of a set of array. 5
How do I use inverse trigonometry in access? 4
Latest Core i7-10875H & RTX 2070 Super–AERO 17 HDR Creator Laptop Unboxing 3
High-Efficient Creator Laptop - GIGABYTE AERO 15 OLED (9980HK/RTX 2080) 3

Back
Top