parameter query not returning correct results

  • Thread starter Thread starter Darlene
  • Start date Start date
D

Darlene

We have a parameter query that functioned correctly in
Access 97, that produces different results in Access 2003.
We are queryint a text field and looking for values
between 2 parameters. When run, the parameters entered
would result in the sql reading between 1-1 and 1-140.
In the old version everything worked fine. In the new
version, we have values like 11-1 and 11-12 which is wrong.
Any ideas? I've tried enclosing in quotes to no avail.
Thanks,
DH
 
SQL view of query is as follows
SELECT DISTINCTROW STORERM.ITEMNUMBER, INVENT.ITEMNUMBER,
INVENT.SHORTDESC, STORERM.BIN, STORERM.ROW1,
STORERM.AISLE, STORERM.QTYONHAND, INVENT.ISSUEUNIT,
INVENT.AVGPRICE, [ENTER START NO] AS EXPR1, [ENTER END
NUMBER] AS EXPR2, INVENT.ITEMTYPE
FROM STORERM INNER JOIN INVENT ON STORERM.ITEMNUMBER =
INVENT.ITEMNUMBER
WHERE (((INVENT.ITEMNUMBER) Between [ENTER START NO] And
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'd try this: put a PARAMETERS statement in the query:

PARAMETERS [ENTER START NO] TEXT, [ENTER END NUMBER] TEXT;
SELECT DISTINCTROW STORERM.ITEMNUMBER, INVENT.ITEMNUMBER,
INVENT.SHORTDESC, STORERM.BIN, STORERM.ROW1,
.... etc. ...

This will ensure the query parses the parameter value as the correct
data type.

Sorting string numbers will not get the same result as sorting numeric
numbers. E.g., string numbers sort like this:

1
11
111
2
22
222

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQVG1J4echKqOuFEgEQLlWwCg7Zt7/JlIrfr0bTHhf1QVgU4a4aYAoMpy
H8wUxrXP7qD99MK3kApZ9OWC
=sP3B
-----END PGP SIGNATURE-----

SQL view of query is as follows
SELECT DISTINCTROW STORERM.ITEMNUMBER, INVENT.ITEMNUMBER,
INVENT.SHORTDESC, STORERM.BIN, STORERM.ROW1,
STORERM.AISLE, STORERM.QTYONHAND, INVENT.ISSUEUNIT,
INVENT.AVGPRICE, [ENTER START NO] AS EXPR1, [ENTER END
NUMBER] AS EXPR2, INVENT.ITEMTYPE
FROM STORERM INNER JOIN INVENT ON STORERM.ITEMNUMBER =
INVENT.ITEMNUMBER
WHERE (((INVENT.ITEMNUMBER) Between [ENTER START NO] And
[ENTER END NUMBER]) AND ((INVENT.ITEMTYPE)<>"ZZZ"))
ORDER BY INVENT.ITEMNUMBER;

-----Original Message-----
What does your query look like?



2003.

wrong.
Click to expand...
Click to expand...
 
MS changed the sorting tables in MS 2000 and the dashes are now treated as not
existing in most searches and order by clauses. For instance, they wanted to
treat Co-operative and Cooperative as the same value.

You might be able to work around the problem by splitting your fields on the
dash and searching against the two parts. Or replacing the dashes with some
other character(s) to.
 
MS changed the sorting tables in MS 2000 and the dashes are now treated as not
existing in most searches and order by clauses. For instance, they wanted to
treat Co-operative and Cooperative as the same value.

You might be able to work around the problem by splitting your fields on the
dash and searching against the two parts. Or replacing the dashes with some
other character(s) to.
 
Hi John,

Thanks..I did not realize that.

table "Dash"

ID TextWithDash
1 1-1
2 1-2
3 1-3
4 1-4
5 1-5
6 1-6
7 1-7
8 1-8
9 1-9
10 11
11 12
12 13
13 14
14 15
15 16


SELECT Dash.TextWithDash
FROM Dash
WHERE (((Dash.TextWithDash) Between "1-1" And "1-3"));

returns:

TextWithDash
1-1
1-2
1-3
12
13

This had me even more confused thinking
it should return "11", but I did a simple
sort query...

SELECT Dash.TextWithDash
FROM Dash
ORDER BY Dash.TextWithDash;

returned

TextWithDash
11
1-1
12
1-2
13
1-3
14
1-4
15
1-5
16
1-6
1-7
1-8
1-9

If only it truly "ignored the dashes"
then all my book apps that have ISBN's
with dashes might make parameter entry
easier, but it didn't work in simple test.

SELECT Dash.TextWithDash
FROM Dash
WHERE (((Dash.TextWithDash)="11"));

returned only

TextWithDash
11

(no "1-1")

(Actually, stripping off the check digit and
saving as indexed number is more efficient
anyway)

Thanks again John.

I know this will probably bite me someday.

Gary Walter
 
Back
Top