?? use string value from form in query

  • Thread starter Thread starter MNJoe
  • Start date Start date
What I am trying to do is on my form I have 3 check boxes for the various
status of work orders, "F" for Firmed, "R" for released and "U" for
unreleased. The user will check which one or combo of status they want to see
in the report. I have a string value strStatus in the form that I set
according to what they check. If I put directley into the query In
("F","R","U") in the criteria for the status to test it and this works. How
can I pull the string value from the form and put it in the query. I have
thought of the long way around was to create a query for each possible combo
and then set the record source for the report to which ever query. Hope there
is an easier way.
 
Use the large white space to post a more complete description of the issue
you are trying to get help on.

Include the version of Access you are using.

Assume we can't see your PC and don't know your situation (we can't, and we
don't!).

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Sorry, I accidently hit the return key on the first post and can not undo it.
Please read second post.
 
By using checkboxes, the implication is that it is possible for a record to
be ALL of those options at once.

Another approach to chosing a SINGLE option is using an option group, in
which only one choice at a time is possible. If you don't like how the
option group looks, you could also create a combobox ("dropdown") that lists
only those three choices.

If you are trying to use this to control how a report is run, it would help
to know how your data is stored. Where is the information about the status
being stored? In what manner (?one yes/no field per "status"? - eek! a
spreadsheet!)?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I am programming in Access 2003 and running the reports using the runtime for
Access 2003 or Access 2007.

The data is stored as a single Character of which it can be "F" or "R" or
"U" or "X"
"F" = firmed work order purchase components
"R" = released work order to the shop floor
"U" = unreleased work order, do not purchase components yet.
"X" = canceled work order ( will not use this one on report) just FYI

Some users will want to see all work orders with the status of just "F",
Some want to see just "R", some want to see all 3 of "F","R","U". So I will
need to get 1 or 2 or 3 statuses. Not just one or the other. That is why I
am using check boxes and not option group. There could be more that one
status they want to see. I am trying to create a string value or values that
can be inserted into the query to get all possible combo's. Yes I understand
this is not going to be easy. I am trying right now to use a text box and set
the text box to "F" and use that to test in getting just the status of "F"
out. Have not been able to do this. Not sure why, have been able to use text
box to do other things that are similar. have tried several things in the
criteria for status with no results.

[forms]![Open_WO_Form]![txtStatus]

[forms]![Open_WO_Form]![txtStatus].[Value]

[forms]![Open_WO_Form]![txtStatus].Value

like "%" & [forms]![Open_WO_Form]![txtStatus]

like [forms]![Open_WO_Form]![txtStatus]

and some others.
 
One approach might be to dynamically create the SQL statement, rather than
rely on a canned query. That way, Access can evaluate which checkboxes are
checked and include appropriate status flag(s) as checked.

You'd do this in an event procedure, probably connected to a button click on
the form. You'd build (dynamically) a SQL string, then execute that SQL to
return records.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

MNJoe said:
I am programming in Access 2003 and running the reports using the runtime
for
Access 2003 or Access 2007.

The data is stored as a single Character of which it can be "F" or "R" or
"U" or "X"
"F" = firmed work order purchase components
"R" = released work order to the shop floor
"U" = unreleased work order, do not purchase components yet.
"X" = canceled work order ( will not use this one on report) just FYI

Some users will want to see all work orders with the status of just "F",
Some want to see just "R", some want to see all 3 of "F","R","U". So I
will
need to get 1 or 2 or 3 statuses. Not just one or the other. That is why
I
am using check boxes and not option group. There could be more that one
status they want to see. I am trying to create a string value or values
that
can be inserted into the query to get all possible combo's. Yes I
understand
this is not going to be easy. I am trying right now to use a text box and
set
the text box to "F" and use that to test in getting just the status of "F"
out. Have not been able to do this. Not sure why, have been able to use
text
box to do other things that are similar. have tried several things in the
criteria for status with no results.

[forms]![Open_WO_Form]![txtStatus]

[forms]![Open_WO_Form]![txtStatus].[Value]

[forms]![Open_WO_Form]![txtStatus].Value

like "%" & [forms]![Open_WO_Form]![txtStatus]

like [forms]![Open_WO_Form]![txtStatus]

and some others.


--
MNJoe


Jeff Boyce said:
By using checkboxes, the implication is that it is possible for a record
to
be ALL of those options at once.

Another approach to chosing a SINGLE option is using an option group, in
which only one choice at a time is possible. If you don't like how the
option group looks, you could also create a combobox ("dropdown") that
lists
only those three choices.

If you are trying to use this to control how a report is run, it would
help
to know how your data is stored. Where is the information about the
status
being stored? In what manner (?one yes/no field per "status"? - eek! a
spreadsheet!)?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
Click to expand...
 
Back
Top