Need Report to pick a field based on another field

[mmsbasd]

I have a table with the following fields:

PAYCODE (D=Discount N=Net P=Promoted
PAYDATE
PRICEDISCOUNT
PRICENET
PRICEPROMOTED

I have a query made that allows me to choose a PAYDATE range. My ultimate goal is to create a fixed field text file that will ONLY show the proper price, based on the PAYCODE that is entered. It does not even have to be printed out, since this information will be fed into ANOTHER database afterwards, so working it all within the query is fine as well.
I'm thinking that if I create a REPORT, I can put in an expression that will determine which price to display. I'm in a time crunch, however, and am not familiar with Access to figure this out quickly. Any help would be GREATLY appreciated. Thanks

 

[BanditWk]

How about displaying a query result based on the PAYDATE expression and the PAYCODE needed? Or am I not following?
Roy
aka BanditWk
Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

 

[mmsbasd]

Roy,
I could do that but then I would have to create (3) reports, one for each price. I'm looking for the query or report to essentially choose which field to display based on what is in the PAYCODE section.

For example, if my original data is:

PAYCODE - PAYDATE - PRICEDISCOUNT - PRICENET - PRICEPROMOTED
D - 10/01/2001 - 05 - 07 - 09
D - 10/15/2001 - 06 - 08 - 10
N - 10/24/2001 - 11 - 13 - 15
P - 10/23/2001 - 12 - 14 - 16

I need my final query to show

D - 10/01/2001 - 05
D - 10/15/2001 - 06
N - 10/24/2001 - 13
P - 10/23/2001 - 16

Thanks for your help.

 

[Guest_imported]

For those who may run into this issue in the future and look at this thread, here is how I resolved my problem.

I created a query for each type of combination (D+Discount, N+Net, P+Promoted) and then created a UNION QUERY that put them all together in one list. There are probably more efficient ways of doing this with SQL but it got me through my issue.