Help with Form passing parameter to query.

[Mark]

Hello all,

I am wanting to have a form with 1 field that once the criteria is enter
they can click a button and see the results on the same form. I am using
access97. Here are the problems I am encountering.

I have been trying to use a text field on a form that is the parameter for
the criteria in a query. The field is text in the table, but it contains
numbers (nothing I can do, I’m linking to the database). The search criteria
I am using will always have leading zero’s, so the query never finds any of
the data. I have tried:
ï‚· Changing the parameter in the query to text
 Adding “†to the search criteria
ï‚· And countless other things,

But nothing I tried has worked.

Here’s are my questions.

ï‚· Is there a better way to approach this?
ï‚· How can I make what I am doing to work?
ï‚· If I use SQL in code and format the criteria to text I think I could get
it to work, but how do I set pointers in SQL to read one record at a time.
There are normally 3 records.
ï‚· Is there a way to format the field criteria in the query itself?
ï‚· Is there a way to format it in code then pass it to the query?


Here is my query. The first on works, but the second one does not. Right
below these queries is how I am handling the results of the query.


(VIAWARE_WCS_TO_VIA_T.CONT) Is a text field

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"));

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)="[Enter in Container Number]"));



Dim db As Database
Dim rs As Recordset
Const strQueryName = "Item Lookup"

Set db = CurrentDb() ' Open pointer to current database
Set rs = db.OpenRecordset(strQueryName) ' Open recordset on saved query

Do While Not rs.EOF
Me.txtresults = Me.txtresults & "Container: " & rs![Cont] & " Status: "
& rs![op_code] & vbCrLf
rs.MoveNext
Loop
rs.Close
db.Close


Really, I guess the only problem I am having is getting the query to except
my text data using numbers with leading zero's. Everything else works.

Thanks for any help.

 

[Mark]

Okay, I must have a bug here. I just used a parameter[Enter Data] on a field
that is text and it will not return any data, however if I enter the correct
data in the criteria it works.

Anyone know of this bug in Access97?

 

[Marshall Barton]

This too vague to be sure, but I don't think there's a bug.

I believe you can achieve the desired result in your
parameter query by using the Format function:

WHERE VIAWARE_WCS_TO_VIA_T.CONT =
Format([Enter in Container Number], "000000000000")
--
Marsh
MVP [MS Access]

Okay, I must have a bug here. I just used a parameter[Enter Data] on a field
that is text and it will not return any data, however if I enter the correct
data in the criteria it works.

Anyone know of this bug in Access97?

I am wanting to have a form with 1 field that once the criteria is enter
they can click a button and see the results on the same form. I am using
access97. Here are the problems I am encountering.

I have been trying to use a text field on a form that is the parameter for
the criteria in a query. The field is text in the table, but it contains
numbers (nothing I can do, I’m linking to the database). The search criteria
I am using will always have leading zero’s, so the query never finds any of
the data. I have tried:
? Changing the parameter in the query to text
? Adding “” to the search criteria
? And countless other things,

But nothing I tried has worked.

Here’s are my questions.

? Is there a better way to approach this?
? How can I make what I am doing to work?
? If I use SQL in code and format the criteria to text I think I could get
it to work, but how do I set pointers in SQL to read one record at a time.
There are normally 3 records.
? Is there a way to format the field criteria in the query itself?
? Is there a way to format it in code then pass it to the query?


Here is my query. The first on works, but the second one does not. Right
below these queries is how I am handling the results of the query.


(VIAWARE_WCS_TO_VIA_T.CONT) Is a text field

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"));

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)="[Enter in Container Number]"));



Dim db As Database
Dim rs As Recordset
Const strQueryName = "Item Lookup"

Set db = CurrentDb() ' Open pointer to current database
Set rs = db.OpenRecordset(strQueryName) ' Open recordset on saved query

Do While Not rs.EOF
Me.txtresults = Me.txtresults & "Container: " & rs![Cont] & " Status: "
& rs![op_code] & vbCrLf
rs.MoveNext
Loop
rs.Close
db.Close