Hard coding query works, code using form data does not.

  • Thread starter Thread starter MNJoe
  • Start date Start date
M

MNJoe

I have a user who wants to enter in the different work order numbers that are
not in sequence. I have the report working for a range of work orders using
"between" in the query when pulling the input from the form. I can not get it
to work when the user just enters in the work order numbers wanted. I can
hard code the work order numbers into the query and it works. If I try to
enter the numbers on the form then run the query it comes back with nothing.
Is this a limitation of access or is there a way to get around this. Is there
a way to right code to create the query completely and then update the query?
Will this work or could it work?


Thanks
 
using a query design view/grid I don't think you'll be able to toggle back
and forth between your two different parameter modes: between vs
discrete values

you need to build the query using vb look up the RunSQL method in VB and
on this site

or two different queries and have then select which type query they want to
do..

via either method you'll need to get creative with the user interface as to
how to gently handle everyone correctly...
 
I have a user who wants to enter in the different work order numbers that are
not in sequence. I have the report working for a range of work orders using
"between" in the query when pulling the input from the form. I can not get it
to work when the user just enters in the work order numbers wanted. I can
hard code the work order numbers into the query and it works. If I try to
enter the numbers on the form then run the query it comes back with nothing.
Is this a limitation of access or is there a way to get around this. Is there
a way to right code to create the query completely and then update the query?
Will this work or could it work?


Thanks
Click to expand...

This can be difficult. Parameter queries support only values, not operators
such as BETWEEN or >= or the like; and paramters containing lists of values
don't work directly. For instance you can use

IN ("WO315", "WO229")

but you cannot use

IN ([Enter workorder numbers:])

What you might want to consider is a "criteria table" - a little onefield
table into which the user can enter the desired workorder numbers; your query
would then Join this table to retrieve the needed values. You would need code
to run a Delete query on the criteria table to start fresh.
 
well you can simulate

IN ([Enter workorder numbers:])

by using something like

expression: "," & [Enter workorder numbers:] & ","

criteria: LIKE "*[ ,]" & [workordernumberfield] & "[ ,]*"

entering them as comma delimitered

ie

enter

WO315 , WO229

in the prompt

hope this helps

Regards
Kelvan
 
Back
Top