Should work but doesn't

[NauticalGent]

Right on, glad you found it useful!
Cheers,

Well almost...

Trying to adapt it to use with an INSERT statement:

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

 

[cheekybuddha]

Untested, but try:

strSQL = INSERT into tblCombinedStatus ([FolderNumber], [CombStatus]) VALUES (p1, p2)"
' ...

 

[Gasman]

Well almost...

Trying to adapt it to use with an INSERT statement:

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?

 

[NauticalGent]

Untested, but try:

strSQL = INSERT into tblCombinedStatus ([FolderNumber], [CombStatus]) VALUES (p1, p2)"
' ...

Thanks CB, that gets me RT Error 3271 - Invalid property value

 

[NauticalGent]

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.

 

[Gasman]

Well your string does not even start with a " ?

Fom Markk's post that was

"UPDATE Leave As t SET t.Comments = p0 " & _
        "WHERE t.[Start Date] = p1 AND t.[End Date] = p2"

try

" INSERT into tblCombinedStatus ([FolderNumber], [CombStatus]) VALUES (p0, p1)"

 

[NauticalGent]

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.

 

[NauticalGent]

Well your string does not even start with a " ?

Fom Markk's post that was

"UPDATE Leave As t SET t.Comments = p0 " & _
        "WHERE t.[Start Date] = p1 AND t.[End Date] = p2"

try

" 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.

 

[Gasman]

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

 

[NauticalGent]

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....

 

[MarkK]

What going wrong here? I don't understand the problem.

 

[NauticalGent]

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,

 

[CarlettoFed]

Maybe if you attach a sample file it would be easier to understand and help you.

 

[Gasman]

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.