String build query - inner join problem

[Hayton]

I copied this syntax fro a query:

' Build SQL string
strSQL = "SELECT tblCustomer.CustID, tblCustomer.Customer, tblCustomer.AccNo, tblCustomer.Depot," & _
"tblCustomer.Notes, tblFlt.FlNo, tblFlt.Desc, tblData.DateFit, tblData.DateRem," & _
"tblData.HoursFit, tblData.HoursRem, nz([HoursRem]-[HoursFit]) AS Hours," & _
"tblData.Tyre, tblData.Qty, tblData.SerCost, tblData.TyreCost," & _
"nz([SerCost]+[TyreCost]) AS TotCost, tblData.Notes, tblData.Completed," & _
"nz(format([TotCost]/[Hours],:###0,0000")) AS CPH"
FROM (tblCustomer INNER JOIN tblFlt ON tblCustomer.CustID = tblFlt.CustID) INNER JOIN tblData ON tblFlt.FlID = tblData.FlID
WHERE (((tblCustomer.AccNo) = [Forms]![frmCustomer]![AccNo]) And ((tblFlt.FlNo) = [Forms]![sfrmFlt]![cboFlt]) And ((tblData.Completed) = Yes))

and the VBA compiler throws a fluffy on the Inner Join in the FROM string.

Can some one please help with this.

The compiler does not like this string as well:
"nz(format([TotCost]/[Hours],:###0,0000")) AS CPH"

Any help would be much appreciated.

Hayton McGregor

 

[Corsica]

If this syntax works in the query design window then why not just save it and run the query via the VB editor by simply calling its name [as apparently you don't actually have any VB variables in the SQL string].

 

[Hayton]

Thanks Corsica I will have a look at that.

Hayton McGregor

 

[PHV]

strSQL = "SELECT tblCustomer.CustID, tblCustomer.Customer, tblCustomer.AccNo, tblCustomer.Depot," & _
"tblCustomer.Notes, tblFlt.FlNo, tblFlt.Desc, tblData.DateFit, tblData.DateRem," & _
"tblData.HoursFit, tblData.HoursRem, nz([HoursRem]-[HoursFit]) AS Hours," & _
"tblData.Tyre, tblData.Qty, tblData.SerCost, tblData.TyreCost," & _
"nz([SerCost]+[TyreCost]) AS TotCost, tblData.Notes, tblData.Completed," & _
"nz(format([TotCost]/[Hours],[highlight]'[/highlight]###0,0000[highlight]'[/highlight])) AS CPH"[highlight] & _
" [/highlight]FROM (tblCustomer INNER JOIN tblFlt ON tblCustomer.CustID = tblFlt.CustID) INNER JOIN tblData ON tblFlt.FlID = tblData.FlID[highlight]" & _
" [/highlight]WHERE (((tblCustomer.AccNo) = [Forms]![frmCustomer]![AccNo]) And ((tblFlt.FlNo) = [Forms]![sfrmFlt]![cboFlt]) And ((tblData.Completed) = Yes))[highlight]"[/highlight]

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