SQL in Access VB code

[greymonkey]

Hi there,

i have this sql code which works fine in a query in access but when I copy it into the vb code to make a query on a button click it comes up with an error saying " the select statment includes reserved word or an argument name that is misspelled or missing"

if any one can spot an error in this i would be greatfull.

the code:


sqlstring = &quot;SELECT Month([BookDate]) AS [Month], Year([bookdate]) AS [Year], TblParent.*, TblChildInfo.*, TblChildInfo.Class, TblDayChildLink.ChildRef, TblDayChildLink.BookDate, TblDayChildLink.Book1, TblDayChildLink.Book2, TblDayChildLink.Book3, TblDayChildLink.Actual1, TblBookingDetails.Price, TblDayChildLink.Actual2, TblBookingDetails_1.Price, TblDayChildLink.Actual3, TblBookingDetails_2.Price, TblDayChildLink.Actual4, TblBookingDetails_3.Price, IIf(([startDate]<=[bookdate]) And ([endDate]>=[bookdate]),1,0) AS DoDiscount, TblDiscount.discount, IIf([DoDiscount]=1,IIf([discount]=1,0.1,0.2),0) AS DiscountRate&quot;

sqlstring = sqlstring & &quot;FROM (TblParent INNER JOIN (TblChildInfo LEFT JOIN ((((TblDayChildLink LEFT JOIN TblBookingDetails ON TblDayChildLink.Actual1 = TblBookingDetails.BookingRef) LEFT JOIN TblBookingDetails AS TblBookingDetails_1 ON TblDayChildLink.Actual2 = TblBookingDetails_1.BookingRef) LEFT JOIN TblBookingDetails AS TblBookingDetails_2 ON TblDayChildLink.Actual3 = TblBookingDetails_2.BookingRef) LEFT JOIN TblBookingDetails AS TblBookingDetails_3 ON TblDayChildLink.Actual4 = TblBookingDetails_3.BookingRef) ON TblChildInfo.ChildRefNum = TblDayChildLink.ChildRef) ON TblParent.ParentRef = TblChildInfo.ParentRef)&quot;

sqlstring = sqlstring & &quot;LEFT JOIN TblDiscount ON TblChildInfo.ChildRefNum = TblDiscount.childRef&quot;

sqlstring = sqlstring & &quot;WHERE (((Month([BookDate]))=&quot; & Month & &quot AND ((Year([bookdate]))=&quot; & Year & &quot AND ((IIf([actual1] Is Null And [actual2] Is Null And [actual3] Is Null And [actual4] Is Null,1,2))=2))&quot;


Set dbs = CurrentDb
Set qdf = dbs.createquerydef(&quot;QryInvoice&quot;, sqlstring)
DoEvents

 

[dhookom]

It looks like you aren't placing spaces between your expressions when you concatenate them together.

You could add a line of code like
debug.Print sqlstring
to see the output.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[greymonkey]

Cheers,

works fine.

it is always the simple things that you miss after hours of staring at a screen.

:>