Should work but doesn't

Right on, glad you found it useful!
Cheers,
Well almost...

Trying to adapt it to use with an INSERT statement:

Code:
strSQL = INSERT into tblCombinedStatus ([FolderNumber], [CombStatus]) VALUES ('" & strFolderNumber & "','" & strStatus & "')"
With CurrentDb.CreateQueryDef("", SQL)
        .Parameters(0) = strFolderNumber
        .Parameters(1) = strStatus
        .Execute dbFailOnError
        .Close
    End With

But I get an "Item not found in this collection" error. I know it is because I didn't set the fields to anything but everything I have tried cant get past the compiler...

I originally just used the Replace() function to remove all the Chr(34) and Chr(39) characters and it runs until it hits some other BS character I didn't account for.

What really chaps my behind is that I found something a few years ago that handled this issue but cant find it now.

Any ideas?

Edit: The error I get is 3075 - Syntax error (missing operator) blah, blah, blah
 
Last edited:
Untested, but try:
Code:
strSQL = INSERT into tblCombinedStatus ([FolderNumber], [CombStatus]) VALUES (p1, p2)"
' ...
 
Well almost...

Trying to adapt it to use with an INSERT statement:

Code:
strSQL = INSERT into tblCombinedStatus ([FolderNumber], [CombStatus]) VALUES ('" & strFolderNumber & "','" & strStatus & "')"
With CurrentDb.CreateQueryDef("", SQL)
        .Parameters(0) = strFolderNumber
        .Parameters(1) = strStatus
        .Execute dbFailOnError
        .Close
    End With

But I get an "Item not found in this collection" error. I know it is because I didn't set the fields to anything but everything I have tried cant get past the compiler...

I originally just used the Replace() function to remove all the Chr(34) and Chr(39) characters and it runs until it hits some other BS character I didn't account for.

What really chaps my behind is that I found something a few years ago that handled this issue but cant find it now.

Any ideas?

Edit: The error I get is 3075 - Syntax error (missing operator) blah, blah, blah
I thought the idea was you used P0, P1 etc instead of the actual variables in the string.
You appear to be combining both?
 
I thought the idea was you used P0, P1 etc instead of the actual variables in the string.
You appear to be combining both?
I think that is what CB was telling me but his "air code" didn't do the trick either. I KNOW it is something obvious but I am missing it.
 
Well your string does not even start with a " ?

Fom Markk's post that was
Code:
"UPDATE Leave As t SET t.Comments = p0 " & _
        "WHERE t.[Start Date] = p1 AND t.[End Date] = p2"

try
Code:
" INSERT into tblCombinedStatus ([FolderNumber], [CombStatus]) VALUES (p0, p1)"
 
In a nutshell, I looped through a table that had a ShortText field and combined them all into a string that I want to insert into a LongText field. Works for some but not all. I knew apostrophes and double quotes were going to be an issue and I accounted for them but now there is something else gumming up the works.
 
Well your string does not even start with a " ?

Fom Markk's post that was
Code:
"UPDATE Leave As t SET t.Comments = p0 " & _
        "WHERE t.[Start Date] = p1 AND t.[End Date] = p2"

try
Code:
" INSERT into tblCombinedStatus ([FolderNumber], [CombStatus]) VALUES (p0, p1)"
Bad transposing on my part. My syntax is good, I just need a way to wrap the string so that it inserts no matter what is inside it.
 
In a nutshell, I looped through a table that had a ShortText field and combined them all into a string that I want to insert into a LongText field. Works for some but not all. I knew apostrophes and double quotes were going to be an issue and I accounted for them but now there is something else gumming up the works.
Try my last post.
Else I would change the single quote ' to double double quotes "" ? , then you can debug.print the string
 
Try my last post.
Else I would change the single quote ' to double double quotes "" ? , then you can debug.print the string
I did, same issue as with CB's suggestion.

I removed all single and double quotes from the string. There is something else in there that Access does not like. I have debug.printed the culprit and even if I could narrow it down, there HAS to be a way to "wrap" the string so that Access doesn't choke on it.

I found something The DB Guy wrote on another forum a few years ago that fixed this but I cant find it now. Really pissing me off....
 
What going wrong here? I don't understand the problem.
 
Rather than continue on this thread, let me start a new one. It is an age-old problem that I have gotten around before but for the life of me, cannot remember how I did it and I cannot find the solution I found about 3 years ago,
 
Maybe if you attach a sample file it would be easier to understand and help you.
 
John,
I have only just noticed that you use strSQL to create the statement, yet use sql for the CreateDef ? :(

When I copied your post, my system complained I had not defined sql. That is how I eventually spotted it. :(
 

Users who are viewing this thread

Back
Top Bottom