can I prompt the user via a query to tell me which column to put a piece of data in

[Abacus1234]

I have an amount in a form control called L1201m. I have added a field in the table (but not in the form) called L1201mdesc. When I go to print a document with mergefields based on a query containing both fields, can I get user input via the query to tell me whether the amount should go in the buyer column or the seller column. I could create L1201mbuyer: L1201m if L1201mdesc = buyer.

I do not want to change the form.

[rpeare]

you don't have to, in your query you could have a formula:

BuyerAmt: iif([L1201mdesc = 'buyer', [L1201m], 0)

[Abacus1234]

Its the value buyer for L1201mdesc that I want to collect through the query. It is not collected on the form. I only have an empty field for it in the table . When I open the query to merge the document, only 1 record is referenced. At that point I would like the user to somehow input the value buyer or seller for the field L1201mdesc. Then I could use a statement like you suggest. Thanks for the response.

[rpeare]

Create a from, save it as TESTFORM
put a combo box on that form name the combo box CBOTEST
make it a value list with the values "BUYER";"SELLER"

In your query reference the 'choice' using

[forms]![TESTFORM]![CBOTEST] anywhere you currently have the prompt for [enter buyer or seller] or whatever you're currently using.

[Abacus1234]

This looks really promising.... thanks....
I have my field name L1201mdesc in the Query field
Table name pops up underneath TSettlementPage2

I put [forms]![ftestform]![cbotest] in Criteria

without this field the query produces data for the record,
with it, no data at all, thinks it is a new record,
when I run the query it asks for Parameter value for Forms!ftestform!cbotest

[rpeare]

if your form is named fTestForm you need to have it open when you run the query. If your form is open you've misspelled either the form name (fTestForm in your case) or the field name (cbotest in your case).

[Abacus1234]

I have the form open, the spellings are correct, query still has no data, thinks it is a new record.
I'm testing on Access 2007, could this effect it?

I finally went back and added a field on the form to collect "buyer" or "seller", I had to use IIf statements in the field in the query to get L1201mbuyer and L1201mseller be populated with the amount in L1201m depending on whether it is buyer or seller, I could not do it as criteria.

Thanks for your help. One thing I have faith in "there is always a way....