Prompting for field when already supplied

[melaniecarr23]

I have a query auto_cc which pull from tbl_auto_cc

One field gives the current month. today: DatePart("m",Now())
Two fields are using switch based on the today field's value.

currency: Switch([today]=1,[Jan],[today]=2,[Feb],[today]=3,[Mar],[today]=4,[Apr],[today]=5,[May],[today]=6,[Jun],[today]=7,[Jul],[today]=8,[Aug],[today]=9,[Sep],[today]=10,[Oct],[today]=11,[Nov],[today]=12,[Dec])

paid: Switch([today]=1,[Jan_paid],[today]=2,[Feb_paid],[today]=3,[Mar_paid],[today]=4,[Apr_paid],[today]=5,[May_paid],[today]=6,[Jun_paid],[today]=7,[Jul_paid],[today]=8,[Aug_paid],[today]=9,[Sep_paid],[today]=10,[Oct_paid],[today]=11,[Nov_paid],[today]=12,[Dec_paid])

When I had the one field currency, everything worked out fine. When the paid field was added, I keep getting prompted for today when I run the query. I can't figure out why. Can someone please help me?

Thanks!

 

[melaniecarr23]

Okay, I think I know what might be causing the problem, but don't know why. In my query, I have the paid field criteria set as 0 (false). It's when I use the criteria that I have issues with the today field popping up. Is there a way to not have this happen?

Here is the SQL view of the query:
SELECT [AUTO CC].id, [AUTO CC].patient_name, [AUTO CC].card_type, [AUTO CC].card_num, [AUTO CC].exp_date, [AUTO CC].vcode, [AUTO CC].start_date, [AUTO CC].[end date], [AUTO CC].eclipse_amt, [AUTO CC].Notes, [AUTO CC].hold_charge, DatePart("m",Now()) AS today, Switch([today]=1,[Jan],[today]=2,[Feb],[today]=3,[Mar],[today]=4,[Apr],[today]=5,[May],[today]=6,[Jun],[today]=7,[Jul],[today]=8,[Aug],[today]=9,[Sep],[today]=10,[Oct],[today]=11,[Nov],[today]=12,[Dec]) AS [currency], Switch([today]=1,[Jan_paid],[today]=2,[Feb_paid],[today]=3,[Mar_paid],[today]=4,[Apr_paid],[today]=5,[May_paid],[today]=6,[Jun_paid],[today]=7,[Jul_paid],[today]=8,[Aug_paid],[today]=9,[Sep_paid],[today]=10,[Oct_paid],[today]=11,[Nov_paid],[today]=12,[Dec_paid]) AS paid, [AUTO CC].Jan, [AUTO CC].Feb, [AUTO CC].Mar, [AUTO CC].Apr, [AUTO CC].May, [AUTO CC].Jun, [AUTO CC].Jul, [AUTO CC].Aug, [AUTO CC].Sep, [AUTO CC].Oct, [AUTO CC].Nov, [AUTO CC].Dec, [AUTO CC].Jan_paid, [AUTO CC].Feb_paid, [AUTO CC].Mar_paid, [AUTO CC].Apr_paid, [AUTO CC].May_paid, [AUTO CC].Jun_paid, [AUTO CC].Jul_paid, [AUTO CC].Aug_paid, [AUTO CC].Sep_paid, [AUTO CC].Oct_paid, [AUTO CC].Nov_paid, [AUTO CC].Dec_paid
FROM [AUTO CC]
WHERE ((([AUTO CC].hold_charge)=No) AND ((Switch([today]=1,[Jan_paid],[today]=2,[Feb_paid],[today]=3,[Mar_paid],[today]=4,[Apr_paid],[today]=5,[May_paid],[today]=6,[Jun_paid],[today]=7,[Jul_paid],[today]=8,[Aug_paid],[today]=9,[Sep_paid],[today]=10,[Oct_paid],[today]=11,[Nov_paid],[today]=12,[Dec_paid]))=0))
ORDER BY [AUTO CC].patient_name;


thanks

 

[dhookom]

You are working way too hard. Try

...,Month(Date()) as Today, Choose(Month(Date()), [Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) as [Currency],Choose(Month(Date()), [Jan_paid], [Feb_paid], [Mar_paid], [Apr_paid], [May_paid], [Jun_paid], [Jul_paid], [Aug_paid], [Sep_paid], [Oct_paid], [Nov_paid], [Dec_paid]) as Paid , ...

Your root issue stems from the un-normalized table structure.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[melaniecarr23]

Thanks Duane!