select statement

[antonyx]

SELECT tblInvoice.InvoiceRef, tblInvoice.Status FROM tblInvoice WHERE tblInvoice.InvoiceRef (first three letters) = (tblAccount.Accountref Where tblAccount.AccountID = cboAccount);

InvoiceRef and AccountRef are both text datatypes

here are sample records from the 2 tables concerned:

tblInvoice
InvoiceRef (MCD98978)
Status (Paid)

tblAccount
AccountID (Autonumber) (1)
AccountName (McDonalds)
AccountRef (MCD)


On my form

I want this query to select the:

InvoiceRef & Status fields from tblInvoice

Where the first three letters of tblInvoice.InvoiceRef..

match the first three letters of the tblAccount.AccountRef

The account ref is derived from a drop down called 'cboAccount'..
However.. at the moment the AccountRef is not actually in the drop down.. the only values selected in that drop down are the AccountID and the AccountName

that is why i have tried something like this..

SELECT tblInvoice.InvoiceRef, tblInvoice.Status FROM tblInvoice WHERE tblInvoice.InvoiceRef (first three letters) = (tblAccount.Accountref Where tblAccount.AccountID = cboAccount);

 

[Treason]

SELECT tblInvoice.InvoiceRef, tblInvoice.Status FROM tblInvoice, tblAccount
WHERE Left([tblInvoice].[InvoiceRef],3)=Left([tblAccount].[AccountRef],3)

 

[antonyx]

ok.. here is what i am using at the moment

"SELECT tblInvoice.InvoiceRef, tblInvoice.Status FROM tblInvoice, tblAccount WHERE left([tblInvoice].[InvoiceRef],3)=Left([tblAccount].[AccountRef],3)"

what this is doing is selecting any invoice number in the database.. basically this code is used in my drop down list (cboAccount)

on the on change event it requeries my listbox (lstInvoiceRefs)..

here is the drop down code..

Private Sub cboaccount_Change()
Me.lstAccountJobs.Requery
Me.lstInvoiceRefs.RowSource = "SELECT tblInvoice.InvoiceRef, tblInvoice.Status FROM tblInvoice, tblAccount WHERE left([tblInvoice].[InvoiceRef],3)=Left([tblAccount].[AccountRef],3)"
Me.lstInvoiceRefs.Requery
End Sub

in the statement you gave me there is no mention of the (cboaccount) control..

and remember that in the (cboaccount) drop down i am using this rowsource only..

SELECT tblAccount.accountid, tblAccount.accountname FROM tblAccount;

i am only selecting the id and account name from the account table.. not the account ref. i know i could select the account ref aswell and use that as the bound column.. but i am already using the accountid as the bound column to update another listbox..

so is there now way to choose the 'accountref' from the 'tblaccount' where the 'tblaccount.accountid' = cboaccount.tblaccountid


i hope that makes sense

 

[antonyx]

yeah ive thought of the way around it.. wasnt rocket science.. just add another drop down.. thanks

 

[Treason]

Glad you got it sorted.