query parameter issue

[Guest]

Hello all,

I am using access 97. I seem to have a problem when using a parameter in
the query criteria and would like to know if this is a bug, or am I doing
something wrong. What is happening is I get no data if I use the parameter,
but if I go inside the query and add the container number I get data. The
field is a 20 char, text.

Here is the code I’m using.

This will not work:

SELECT VIAWARE_WCS_TO_VIA_T.CONT, VIAWARE_WCS_TO_VIA_T.NEST_TO_CONT
FROM VIAWARE_WCS_TO_VIA_T
WHERE (((VIAWARE_WCS_TO_VIA_T.CONT)=[Container Number]));

But this will.

SELECT VIAWARE_WCS_TO_VIA_T.CONT, VIAWARE_WCS_TO_VIA_T.NEST_TO_CONT
FROM VIAWARE_WCS_TO_VIA_T
WHERE (((VIAWARE_WCS_TO_VIA_T.CONT)="000654035500"));

Is it a bug, or something I’m doing

 

[Allen Browne]

Chances are, Access is treating the parameter value as a number, stripping
the leading zeros, and then not making the match.

Try declaring the parameter.
In query design view, choose Parameters on the Query menu.
Specify a parameter of type Text.

 

[David Lloyd]

Mark:

One possible issue is the leading zeros on the Container Number. Since your
field is text it will expect those leading zeros to be there. If the value
you are supplying in the [Container Number] parameter is numeric it will
remove those leading zeros and you will not get a match. You can use the
Format function to add back the leading zeros to get a text match. If all
your container numbers are in fact numbers, you may also want to consider
changing the data type for this field in the table to a numeric data type.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hello all,

I am using access 97. I seem to have a problem when using a parameter in
the query criteria and would like to know if this is a bug, or am I doing
something wrong. What is happening is I get no data if I use the parameter,
but if I go inside the query and add the container number I get data. The
field is a 20 char, text.

Here is the code I'm using.

This will not work:

SELECT VIAWARE_WCS_TO_VIA_T.CONT, VIAWARE_WCS_TO_VIA_T.NEST_TO_CONT
FROM VIAWARE_WCS_TO_VIA_T
WHERE (((VIAWARE_WCS_TO_VIA_T.CONT)=[Container Number]));

But this will.

SELECT VIAWARE_WCS_TO_VIA_T.CONT, VIAWARE_WCS_TO_VIA_T.NEST_TO_CONT
FROM VIAWARE_WCS_TO_VIA_T
WHERE (((VIAWARE_WCS_TO_VIA_T.CONT)="000654035500"));

Is it a bug, or something I'm doing

 

[Guest]

I have tried the parameter set to text, but it still doesn't return any data.
unfortunaley I am ODBCing into another datbase and unable to change the
field to numeric, even though all the data is numeric and all records start
with 000...
Any other suggestions?

Chances are, Access is treating the parameter value as a number, stripping
the leading zeros, and then not making the match.

Try declaring the parameter.
In query design view, choose Parameters on the Query menu.
Specify a parameter of type Text.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello all,

I am using access 97. I seem to have a problem when using a parameter in
the query criteria and would like to know if this is a bug, or am I doing
something wrong. What is happening is I get no data if I use the
parameter,
but if I go inside the query and add the container number I get data. The
field is a 20 char, text.

Here is the code I'm using.

This will not work:

SELECT VIAWARE_WCS_TO_VIA_T.CONT, VIAWARE_WCS_TO_VIA_T.NEST_TO_CONT
FROM VIAWARE_WCS_TO_VIA_T
WHERE (((VIAWARE_WCS_TO_VIA_T.CONT)=[Container Number]));

But this will.

SELECT VIAWARE_WCS_TO_VIA_T.CONT, VIAWARE_WCS_TO_VIA_T.NEST_TO_CONT
FROM VIAWARE_WCS_TO_VIA_T
WHERE (((VIAWARE_WCS_TO_VIA_T.CONT)="000654035500"));

Is it a bug, or something I'm doing

 

[Guest]

Can I use the format function within a query, or do I have to take it to VB?
I looked at FF in the help section, but it seems to do more for day of the
week. I look at the FF for strib, but unsure of how to use it within a
query. Thanks for any help and direction you can give.