Runtime 424, Object Required Error

[adubberkegre]

Hello,

I'm getting a RunTime 424, Object Required error with the below code:

Function testqindexsales()
Dim strSQL As String
Dim param As Integer
strSQL = "INSERT INTO TempCurrentQSalesBAC (CurrentQBACLookupLogic, CurrentQBACDateLkup, CurrentQSales) " _
& "SELECT TempQIndexSalesBAC.LookupLogic, TempQIndexSalesBAC.DateLkup, Nz(DLookUp('[Sales]','TempQIndexSalesBAC','[DateLkup] = '" & Tables!TempQIndexSalesBAC!Table!QIndex & "''),0) AS CurrentQSales " _
& "FROM BACList LEFT JOIN TempQIndexSalesBAC ON BACList.BACLkup = TempQIndexSalesBAC.LookupLogic " _
& "GROUP BY TempQIndexSalesBAC.LookupLogic, TempQIndexSalesBAC.DateLkup, BACList.BACLkup, TempQIndexSalesBAC.QIndex " _
& "HAVING (((TempQIndexSalesBAC.DateLkup)=" & param & "));"
DoCmd.RunSQL strSQL
End Function

I believe the issue is with the section colored Red, but I can't figure out where I'm going wrong. Any insight would be greatly appreciated.

Thank you!

[SamT]

Edited to add/remove CrLf's as I see fit. Also added CODE Tags to prevent the Forum software from inserting CrLf's as it see fit.


Function testqindexsales()
Dim strSQL As String
Dim param As Integer

strSQL = "INSERT INTO TempCurrentQSalesBAC (CurrentQBACLookupLogic, CurrentQBACDateLkup, CurrentQSales) " _
& "SELECT TempQIndexSalesBAC.LookupLogic, TempQIndexSalesBAC.DateLkup, Nz(DLookUp('[Sales]',' _
& "TempQIndexSalesBAC','[DateLkup] = 'Tables!TempQIndexSalesBAC!Table!QIndex & "''),0) AS CurrentQSales " _
& "FROM BACList LEFT JOIN TempQIndexSalesBAC ON BACList.BACLkup = TempQIndexSalesBAC.LookupLogic " _
& "GROUP BY TempQIndexSalesBAC.LookupLogic, TempQIndexSalesBAC.DateLkup, BACList.BACLkup, TempQIndexSalesBAC.QIndex " _
& "HAVING (((TempQIndexSalesBAC.DateLkup)=" & param & "));"

DoCmd.RunSQL strSQL
End Function
while doing that I found a mismatched quote mark

[adubberkegre]

Hi Sam,

Thanks for your reply. So I copy/pasted your code into my module, and the text turned green, meaning, on of the quotes turned the text into a comment. (I've noted that section with the green font color)


Function testqindexsales()
Dim strSQL As String
Dim param As Integer
strSQL = "INSERT INTO TempCurrentQSalesBAC (CurrentQBACLookupLogic, CurrentQBACDateLkup, CurrentQSales) " _
& "SELECT TempQIndexSalesBAC.LookupLogic, TempQIndexSalesBAC.DateLkup, Nz(DLookUp('[Sales]',' _
& "TempQIndexSalesBAC','[DateLkup] = 'Tables!TempQIndexSalesBAC!Table!QIndex & " ''),0) AS CurrentQSales " _
& "FROM BACList LEFT JOIN TempQIndexSalesBAC ON BACList.BACLkup = TempQIndexSalesBAC.LookupLogic " _
& "GROUP BY TempQIndexSalesBAC.LookupLogic, TempQIndexSalesBAC.DateLkup, BACList.BACLkup, TempQIndexSalesBAC.QIndex " _
& "HAVING (((TempQIndexSalesBAC.DateLkup)=" & param & "));"

DoCmd.RunSQL strSQL
End Function

Additionally, I believe maybe one of the "&" was left out between in the dlookup function as pertaining to the DateLkup criteria. I may be wrong though.

Thanks for any insight you can provide.

[SamT]

"TempQIndexSalesBAC','[DateLkup] = 'Tables!TempQIndexSalesBAC!Table!QIndex & " ' '),0)

[/code] not [\code]

[adubberkegre]

Hi Sam,

The below code I've highlighted in green is turning green in my module.


Function testqindexsales() Dim strSQL As String
Dim param As Integer
strSQL = "INSERT INTO TempCurrentQSalesBAC (CurrentQBACLookupLogic, CurrentQBACDateLkup, CurrentQSales) " _
& "SELECT TempQIndexSalesBAC.LookupLogic, TempQIndexSalesBAC.DateLkup, Nz(DLookUp('[Sales]','" _
& "TempQIndexSalesBAC','[DateLkup] = 'Tables!TempQIndexSalesBAC!Table!QIndex & " ' '),0) AS CurrentQSales " _
& "FROM BACList LEFT JOIN TempQIndexSalesBAC ON BACList.BACLkup = TempQIndexSalesBAC.LookupLogic " _
& "GROUP BY TempQIndexSalesBAC.LookupLogic, TempQIndexSalesBAC.DateLkup, BACList.BACLkup, TempQIndexSalesBAC.QIndex " _
& "HAVING (((TempQIndexSalesBAC.DateLkup)=" & param & "));"

DoCmd.RunSQL strSQL
End Function

I copy/pasted your code directly into my module. Am I missing something?

Thank you again.

[SamT]

You still haven't paired all the double quotes between ampersands
"TempQIndexSalesBAC','[DateLkup] = 'Tables!TempQIndexSalesBAC!Table!QIndex & " ' '),0)

Always look at the code before any errors, not after them.

[adubberkegre]

Hi Sam,

Are you not posting corrected code? If not, I apologize for the confusion. If you are, then I don't see what I'm missing.

Thanks.

[adubberkegre]

I'm awfully sorry, but I'm getting the run time error. I've counted all of my single and double quotes, and everything seems to match up. So, I'm just confused as to what I'm missing.


Function testqindexsales() Dim strSQL As String
Dim param As Integer
strSQL = "INSERT INTO TempCurrentQSalesBAC (CurrentQBACLookupLogic, CurrentQBACDateLkup, CurrentQSales) " _
& "SELECT TempQIndexSalesBAC.LookupLogic, TempQIndexSalesBAC.DateLkup, Nz(DLookUp('[Sales]'," _
& "'TempQIndexSalesBAC','[DateLkup] = '" & Tables!TempQIndexSalesBAC!Table!QIndex & "' '),0) AS CurrentQSales " _
& "FROM BACList LEFT JOIN TempQIndexSalesBAC ON BACList.BACLkup = TempQIndexSalesBAC.LookupLogic " _
& "GROUP BY TempQIndexSalesBAC.LookupLogic, TempQIndexSalesBAC.DateLkup, BACList.BACLkup, TempQIndexSalesBAC.QIndex " _
& "HAVING (((TempQIndexSalesBAC.DateLkup)=" & param & "));"

DoCmd.RunSQL strSQL
End Function

[SamT]

Are you not posting corrected code?No I'm not, I am posting where the String needs repair.

And I showed it to you in red. Twice

When you use Ampersands in a String, always put the Ampersand at the beginning of a new line and end the preceding line with a Space+Underscore
& ". . . . . . . . " _
& ". . . " _
& ". . . . . . . . . . . . . . . . . . . " _
& ". . . . . . . . " _
& ". . . " _
& ". . . "

Then just look to see if there is a double quote at the beginning and end of each line. Then you can concatenate lines as desired by deleting the Underscore+CrLf+Ampersand

& ". . . . . . . . " & ". . . " & ". . . . . . . . . . . . . . . . . . . " _
& ". . . . . . . . " & ". . . " & ". . . " _

Another trick I use is: Because using quotes and double quotes inside strings can get confusing

Const SQ as String = "'"
Const DQ As String= """

Then inside Strings, where the quot is actually part of the String, I use the Constants.

MsgBox "This " & SQ & "String" & SQ & " uses Single Quotes inside the msg."
Message Displays as: This 'String' uses single Quotes inside the msg.

[SamT]

Another way to develop these long and complex SQL Strings is to break them down into discrete chunks

'Module level variables
'Various constructs that fit between custom Strings
Const SQ As String = "'"
Const DQ As String= """
Const SP As String = " "
Const CS As String = ", "

Function testqindexsales()

Dim strSQL As String
Dim strInsert As Sgtring
Dim strSelect As String

Dim stsNzLookUp As String
dim strDateLookup As String

Dim strFrom As String
Dim strGroup As String
Dim strHaving as String
Dim param As Integer

'Develop each piece individually
strInsert = "INSERT INTO TempCurrentQSalesBAC (CurrentQBACLookupLogic, CurrentQBACDateLkup, CurrentQSales)

StrSelect = "SELECT TempQIndexSalesBAC.LookupLogic, TempQIndexSalesBAC.DateLkup" & CS

strDateLookUp = "'[DateLkup]' ='Tables!TempQIndexSalesBAC!Table!QIndex'' & CS

strNzLookUp = "Nz(DLookUp('[Sales]','TempQIndexSalesBAC', & strDateLookup & "),0)" AS CurrentQSales "


'Etc 'I don't speak SQL, but as you can see, it is easier to get short specific strings right.

strSQL = strInsert & strSelect & strNzLookup & strFrom & strGroup & strHaving

DoCmd.RunSQL strSQL
End Function

[adubberkegre]

Hi Sam. I truly appreciate your assistance. As you can probably tell, I'm very new to this whole VBA thing. I copy/pasted what you provided into my module, and I'm getting a syntax error on the following line.


strNzLookUp = "Nz(DLookUp('[Sales]','TempQIndexSalesBAC', & strDateLookup & "),0)" AS CurrentQSales "

I know this is the one that's been giving me problems, and I'm sure you're frustrated as this repeat question, but what am I missing?

[jonh]

strNzLookUp = "Nz(DLookUp('[Sales]','TempQIndexSalesBAC'," & strDateLookup & "),0) AS CurrentQSales "

Why not use the query editor and paste the sql into VBA?

[SamT]

I copy/pasted what you provided into my module,
Gee, Don't do that. I have never programmed VBA for SQL. All I did was copy and paste some of your terms in what looked like a sensible manner.

I was only trying to give you ideas how you could do it

VBA is VBA, and creating long and complex Strings is the same no matter where the Strings are used.

Jonh can give you a whole lot better idea of how to do the same thing. I am just wandering around in the SQL swamp wondering what that strange thing is floating in front of me.