asked on

Date and IF statement

Experts,

I need a formula that will return the [tblLetterOfCredit.ExpireyDate] for the first record but then for all else then return the [tblPayments.PaymentDate].
Where clause is tblLetterOfCredit.LCID = tblPayments.LCID and I think the formula would need to reference [tblPayments.ID] somehow.

It would be better to see the attached pared down db.
There are 2 tables and 1 query only.

Here is a screenshot of what I am looking for:

EE_Date.accdb

SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pdvsa

ASKER

Hey Jim... thank you very much for your response. Could you kindly double check? I have looked over the data and from what I see the tblPayments.ID doesnt seem to have the tblLetterOfCredit.ExpireyDate as the first record and instead the tblPayments.PaymentDate is populating that first record. What I need Expr1 to return is tblLetterOfCredit.ExpireyDate for only the first tblPayments.ID but then every tblPayment.ID after that that field (Expr1) should return tblPayments.PaymentDate.

Let me know if i have missed something. Thank you once again.

pdvsa

ASKER

Oh and to answer your question, I would rather have the data in a query. I could have in a report but sometimes it doesnt export to excel so I thought to have as raw data.

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Máté Farkas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Dettman (EE MVE)

<<Hey Jim... thank you very much for your response. Could you kindly double check? >>

Worked fine here:

pdvsa

ASKER

Hi Jim, I see that it does work perfectly in the sample db however, when I transferred it over to the production db I had the issue. I made no changes. Very strange. I imagine I made a mistake somewhere.

Gustav: that worked. Thank you!

Mate: thank you for that crafty tip. It seems pretty simple and I understand it 100% and can eliminate any subquery, which is good in my case ;) I didnt test it however and I feel confident results are what I need.

pdvsa

ASKER

Mate, FYI: I did test it and worked perfectly like the others. I appreciate your response. I actually applied your logic successfully to another completely separate issue. Thanks for the help!