Subquery Error

[sanjna000]

Hi there,

I've got a problem with the following query. It raised an error and i cannot figure out what is wrong. This is the first time i used subqueries. I don't know whether my syntax is wrong. Could anyone help me with this?

strSQL = "SELECT Client.[Client Forename], Client.[Client Surname], Client.[Client Addr Ln1], ClientWklyVisit.Date, ClientWklyVisit.[Visit Length], ClientWklyVisit.[From Time], ClientWklyVisit.[Invoice To Account]," & _
" Val(Left(ClientWklyVisit.[Visit Length],2)) + Val(Right(ClientWklyVisit.[Visit Length],2))/60 AS Exp1, ClientWklyVisit.[Visit Type]," & _
" ClientWklyVisit.[Number Of Carers Alloc], ClientWklyVisit.[NR Code]" & _
" FROM ClientWklyVisit INNER JOIN Client ON ClientWklyVisit.[Client ID] = Client.[Client ID]" & _
" WHERE (((ClientWklyVisit.Date)>=#10/11/2004# And (ClientWklyVisit.Date)<=#10/17/2004#) AND ((ClientWklyVisit.[Visit Type])<>'rc') And (ClientWklyVisit.[Invoice To Account]) In" & _
" (SELECT ClientAccountMapping.[InvoiceToType] FROM ClientAccountMapping INNER JOIN InvoicePeriod ON ClientAccountMapping.[InvoiceCode] = InvoicePeriod.[InvoiceCode]" & _
" WHERE ((ClientAccountMapping.[InvoiceCode])=" + Frm_Invoice.CB_InvCode.SelText + "))" & _
" ORDER BY Client.[Client Surname] Asc, Client.[Client Forename], Client.[Client Addr Ln1], ClientWklyVisit.Date, ClientWklyVisit.[From Time] Desc;"

Thanks a lot for u r help in advance,
Sanjna...

 

[PHV]

It raised an error
Any chance you could post the error message ?
I guess the error is here:
" WHERE ((ClientAccountMapping.[InvoiceCode])=" + Frm_Invoice.CB_InvCode.SelText + "))"
You may try to replace it by this:
" WHERE ((ClientAccountMapping.[InvoiceCode])='" & Frm_Invoice.CB_InvCode.Value & "'))"
IF InvoiceCode is defined as numeric get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sanjna000]

Cheers PH
It works!