Database engine does not recognize 'payment' as a valid field name or expression

[EdK]

Hi all --

I've tried various searches for my probably simple problem but no success.
I have a small database for producing various financial reports, by date period (from/to). It works perfectly except when there are either no payment records or no receipt records for the chosen period. Naturally enough, MS Access comes up with the message "Database engine does not recognize 'payment' as a valid field name or expression" --- !!!

Is there some way I can tell MS Access that I don't mind if eg the payment column result is zero?

The structure of the table on which the report is based (via a crosstab query) is :

transaction date
auto number ID
transaction type (either payment or receipt, chosen by form's drop down box) - TEXT
amount - CURRENCY
receipt type - TEXT
payment type - TEXT
fundno - TEXT

The crosstab query design is as per the attached jpeg file


Hope you can help. If you need further information, I'm happy to provide (but know only basic stuff)

EdK

 

[plog]

Is there some way I can tell MS Access that I don't mind if eg the payment column result is zero?

Sort of. I wouldn't use a crosstab query for this. Since you only have 2 payment types*, I would use an aggregate query with calculated fields. Here's how:

1. Change that from a crosstab query to a Select query.
2. Delete the paymenttype field from the query.
3. Change the amount field to this:

payment: iif([paymenttype]="payment", [amount], 0)

4. Add this field at the end:

receipt: iif([paymenttype]="receipt", [amount], 0)

5. Underneath that new field change the Group By to Sum.

6. Run it and it should work*.

* assumes you've provided correct information about the spelling of the 2 payment type values and you only have 2 that you want to report on.

 

[EdK]

Thank you, Plog, for your quick reply, it was very kind of you. I've had trouble applying what you have said to do. Never mind, I have found a rough workaround of sorts that will do for now. Thanks again and if I do get sick of the workaround I might put up the thread again.

EdK