Using IN

[PQSIK]

Hi everyone,

I don't seem to be able get this code to work, can anyone help.

SQL = "Select Client.Client_Name, Contractor.Contractor_Name, Deliveries.Fuel_Vat + Deliveries.Total_Charge_Tax AS TempTotalVat, Deliveries.* from Client, Contractor, Deliveries where Delivery_Date > "
SQL = SQL & "#" & DateAdd("m", -1, Date) & "# order by Delivery_Date"
SQL = SQL & " where Client.ClientID, Contractor.ContractorID IN (Select ClientID, ContractorID) from Deliveries where Delivery_Date >"
SQL = SQL & " #" & DateAdd("m", -1, Date) & "#"

I am not sure if I can use (IN) with access query.

I have the clientID and contractorID fields in the deliveries table but I want to display the names of them witch is in the client and contractor tables.

Thanks

 

[PQSIK]

Cancel this post

I must have been tired last night. I had one look at it this morning and type in the right query.

thank anyway.

PQSIK.

 

[Mile-O]

Yes, you can use in. Don't forget that semi-colon at the end, either.

With names that use underscores, reserved words, or special characters you are still advised to surround each table and field name in square brackets ( i.e. [Deliveries].[Fuel_Vat] ).

 

[dcx693]

I think there's a misplaced final ) surrounding the subquery. Try this:
SQL = "Select Client.Client_Name, Contractor.Contractor_Name, Deliveries.Fuel_Vat + Deliveries.Total_Charge_Tax AS TempTotalVat, Deliveries.* from Client, Contractor, Deliveries where Delivery_Date > "
SQL = SQL & "#" & DateAdd("m", -1, Date) & "# order by Delivery_Date"
SQL = SQL & " where Client.ClientID, Contractor.ContractorID IN (Select ClientID, ContractorID from Deliveries where Delivery_Date >"
SQL = SQL & " #" & DateAdd("m", -1, Date) & "#);"