Sql String being truncated

[raziel3]

Hi all,
This is the statement I'm trying to pass through VBA

BioClockSql = "SELECT EmpTimes.USERID " & _
                ",EmpTimes.EID " & _
                ",EmpTimes.CHECKDATE " & _
                ",Format([CHECKDATE],'ddd') AS DOW " & _
                ",EmpTimes.INTIME " & _
                ",EmpTimes.OUTTIME " & _
                ",EmpTimes.TIMEWORKED " & _
                ",EmpTimes.TIMEDURATION " & _
                ",TimeSheet.WDate " & _
                "FROM EmpTimes " & _
         "LEFT JOIN TimeSheet ON (EmpTimes.CHECKDATE = TimeSheet.WDate) " & _
         "AND (EmpTimes.EID = TimeSheet.EID) " & _
         "WHERE (((EmpTimes.CHECKDATE) Between [Forms]![frmPay1]![txtPSTART] " & _
         "AND [Forms]![frmPay1]![txtPEND]) " & _
         "AND ((TimeSheet.WDate) Is Null))"""

In the locals window it is being trimmed off at

"LEFT JOIN TimeSheet ON (EmpTimes.CHECKDATE = TimeSheet.WDate) " & _

What am I doing wrong?

 

[cheekybuddha]

When you are at that breakpoint, what happens if you type in the Immediate Window:
?BioClockSql
and hit enter?

 

[raziel3]

SELECT EmpTimes.USERID ,EmpTimes.EID ,EmpTimes.CHECKDATE ,Format([CHECKDATE],'ddd') AS DOW ,EmpTimes.INTIME ,EmpTimes.OUTTIME ,EmpTimes.TIMEWORKED ,EmpTimes.TIMEDURATION ,TimeSheet.WDate FROM EmpTimes LEFT JOIN TimeSheet ON (EmpTimes.CHECKDATE = TimeSheet.WDate) AND (EmpTimes.EID = TimeSheet.EID) WHERE (((EmpTimes.CHECKDATE) Between [Forms]![frmPay1]![txtPSTART] AND [Forms]![frmPay1]![txtPEND]) AND ((TimeSheet.WDate) Is Null))"

I'm getting the full statement but runtime error 3075

 

[raziel3]

The query runs fine using the GUI


but if I reference it like this
Set BioClockRS = db.OpenRecordset("qryBioClock", dbOpenSnapshot)



Error 3061

 

[cheekybuddha]

Why do you need the extra double quotes at the end?

Anyway, you need to concatenate the form references when using VBA:

BioClockSql = "SELECT EmpTimes.USERID " & _
                ",EmpTimes.EID " & _
                ",EmpTimes.CHECKDATE " & _
                ",Format([CHECKDATE],'ddd') AS DOW " & _
                ",EmpTimes.INTIME " & _
                ",EmpTimes.OUTTIME " & _
                ",EmpTimes.TIMEWORKED " & _
                ",EmpTimes.TIMEDURATION " & _
                ",TimeSheet.WDate " & _
                "FROM EmpTimes " & _
         "LEFT JOIN TimeSheet ON (EmpTimes.CHECKDATE = TimeSheet.WDate) " & _
         "AND (EmpTimes.EID = TimeSheet.EID) " & _
         "WHERE (((EmpTimes.CHECKDATE) Between " & Format([Forms]![frmPay1]![txtPSTART], "\#yyyy\-mm\-dd hh:nn:ss\#") & " " & _
         "AND " & Format([Forms]![frmPay1]![txtPEND], "\#yyyy\-mm\-dd hh:nn:ss\#") & " " & _
         "AND ((TimeSheet.WDate) Is Null))"

 

[raziel3]

Yeah, used a different online sql to vba converter. The extra quotes and brackets were the problem.

Thanks

"WHERE (((EmpTimes.CHECKDATE) Between #4/23/2023# AND #4/29/2023#) " & _
         "AND ((TimeSheet.WDate) Is Null))"

 

[cheekybuddha]

Yeah, sorry, missed the brackets

You can actually remove all of them from your WHERE clause!

' ...
         "WHERE EmpTimes.CHECKDATE Between " & Format([Forms]![frmPay1]![txtPSTART], "\#yyyy\-mm\-dd hh:nn:ss\#") & " " & _
         "AND " & Format([Forms]![frmPay1]![txtPEND], "\#yyyy\-mm\-dd hh:nn:ss\#") & " " & _
         "AND TimeSheet.WDate Is Null"