Access 2007 Listbox Problem

[Mj-Egerton]

Hello all,
im working with access 2007 and i have a listbox that should display all the records from a table
the problem i have is one of the fields is called Index1 and another called File Title

as the listbox is displayed it goes:

(Table.Field)

Loan.LoanID - Loan.Name - Loan.Index1 - Loan.CopyNo - Loan.Start Date - Loan.Finish Date

now my problem is that i need to swap the "Index1" field for a "File Title" in another table BUT there is another field called Index2 which holds the same values as Index1. what i need to happen is where Index1 = Index2 display File Title

so the listbox ends up looking like

(Table.Field)

Loan.LoanID - Loan.Name -File.File Title- Loan.CopyNo - Loan.Start Date - Loan.Finish Date

Thanks in advance for any help and sorry if its long winded or confusing, ps there is no code this is from scratch i have found out how to retreive the File name from selecting a record in the listbox which displays Index1 but it requires the user to click a record on a listbox and it only returns a single title, this isnt what i want

Cheers

 

[Red17]

Try changing the List Box's Row Source to the following:

SELECT Loan.LoanID, Loan.Name, File.File Title, Loan.CopyNo, Loan.Start Date, Loan.Finish Date FROM Loan LEFT JOIN File ON Loan.Index1 = File.Endex2;

 

[Mj-Egerton]

Cheers Red, your a saint, Thanks and Rep given!

just discovered the w3schools site, brilliant guides to loads of SQL commands
Thanks again!

PS sorry for posting in the VBA section the code was later put into VBA code if its any consolation