query a field based on user input

[melaniecarr23]

I have fields in a table called jan, feb, etc. and would like to run a query based on the month a user inputs. I would not like to have 12 different queries and reports for each month. Is there a way to pull a field in a query based on the info the user inputs? If so, how?

Thanks,
Melanie

 

[Remou]

Hi
You can do this either directly from a query or from a form. Under Month, on the criteria line, put
[Please Enter Month: ]
Or using a form:
=Forms!frmForm!txtMonth

 

[PHV]

You have to normalize your design.
A work around is to create a saved normalization union query named, say, qryMonths:
SELECT field1, ..., fieldN, 'jan' AS txtMonth, jan AS valMonth
FROM yourTable WHERE jan Is Not Null
UNION SELECT field1, ..., fieldN, 'feb', feb
FROM yourTable WHERE feb Is Not Null
...
UNION SELECT field1, ..., fieldN, 'dec', dec
FROM yourTable WHERE dec Is Not Null
;
And now your parametized query:
SELECT field1, ..., fieldN, valMonth
FROM qryMonths
WHERE txtMonth = [Enter month: jan, feb, etc.]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[melaniecarr23]

I'm just not getting it. My plans table has the following fields:

id autonumber
jan currency
feb currency
mar currency
etc.

When I do a query for January, I need to select the jan field where jan>0. and so forth for each month. You know how people can put ["Enter month"] in the criteria section? I want to do this, but use the data entered to have the query pull that particular month. I've tried using an expression in the field section and it doesn't work. Is there some kind of work around?

Thanks,
Melanie

 

[PHV]

Have you even read my post ?

 

[grnzbra]

You could also create a field with a series of nested IIf statements:

Currency:=IIf(Month = 'Jan', Jan,IIf(Month = 'Feb', Feb, IIf(Month = 'Mar', etc ))))))))))))

 

[melaniecarr23]

PHV I did read your post, I'm just not understanding what you mean. It's been years since I've worked in access, and I was probably better at it back then (like using more than one table when I probably should have set it up another way to begin with).

Sorry if you thought I didn't read it - I did - it's me who's not getting it.

Melanie