query parameter issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark - 23 May 2005 11:36 GMT

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

Reply to this Message

Allen Browne - 23 May 2005 13:11 GMT

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.

Signature

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.

> Hello all,
>
[quoted text clipped - 20 lines]
>
> Is it a bug, or something I'm doing

Reply to this Message

Mark - 23 May 2005 14:46 GMT

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.
[quoted text clipped - 27 lines]
> >
> > Is it a bug, or something I'm doing

Reply to this Message

David Lloyd - 23 May 2005 13:35 GMT

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.

Signature

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

Reply to this Message

Mark - 23 May 2005 14:49 GMT

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.

> Mark:
>
[quoted text clipped - 29 lines]
>
> Is it a bug, or something I'm doing

Reply to this Message