troubleshooting Question

Query fails on db.Execute but goes through with the exact same syntax in the query designer???

Avatar of Eddie Antar
Eddie AntarFlag for United States of America asked on
Microsoft Access
6 Comments4 Solutions26 ViewsLast Modified:
Hi all,
I've got a strange situation I've never encountered. I have to execute a series of Insert Into query statements. I have the parameters that I need in table. I loop through the table and execute the following statement:

db.Execute "Insert Into tblAllocVGrossTotalsPerCheck (PayHistoryID,TotalAmount, QryName, Category) " & _
              "Select  " & !qryPrimaryKey & ", " & !QryTotalFld & ", '" & !QryName & "' As QueryName, '" & !Category & "' As Category From " & !QryName & " Order By " & !qryPrimaryKey


As you can see many of the values that I need are fields from a table. But with some of the records I'm getting...


Too few Parameters, Expected 1.

Here's where it gets really strange. In the immediate window, I get the result of the full string expression  I'm trying to execute, then copy and paste the exact text into the query designer, IN ACTUAL TIME, and the query executes without a problem. It gets stranger when you consider that some of the records in the loop go through without any problem. I keep trying to compare the ones that have no problem to the ones that do, but I'm not seeing anything different.
 
I'm lost. Anyone have any clues what might be going on?
Any ideas where to look would be greatly appreciated.

Thanks.

ASKER CERTIFIED SOLUTION
Bembi
CEO

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 4 Answers and 6 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 4 Answers and 6 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004