Im new here so hoping someone can help.

I have two tables, A05 and E05

E05 contains two fields (DR & CR) which contain the corresponding A05.ID in.

I wish to select and display the E05 records but showing A05.Code rather than DR and CR (which contain A05.ID)

AY is A05.ID (set from loading card)

SELECT Date, A05.Code AS DR, A05.Code AS CR FROM E05 INNER JOIN A05 ON A05.ID=E05.DR OR A05.ID=E05.CR WHERE E05.DR=" & AY & " OR E0505.CR=" & AY & " ORDER BY Date, ID"

My data is as follows

E05
DR CR
101 104

A05
101 = 1234
104 = 4321

Firstly DATE is a reserved word in Access and should NEVER be used as a field name. It is a built in function and returns today's date.

Secondly can you clarify exactly what you are trying to acheive as I am afraid I got a bit lost following your SQL

Date is shown as an example, the field is called EnteredDate

Im trying to show the E05 record, with the A05 Code's showing rather than the A05.ID.

The E05 record has two different A05.ID's (called DR and CR)

Try using the Query Design Grid to build your query. It is still not clear which table EnteredDate is held in but it needs to stated explicitly in your query.

Im using VB to do this with.

Basically, I need to show the E05 records with the A05.Code, not the A05.ID.

So the tables need to be linked.

E05 has EnteredDate, CR, DR (DR and CR are the A05.ID and are never the same per E05 record), but I want to show the A05.Code rather than the A05.ID in E05.DR and E05.CR using an SQL statement to show in a databound grid.