Syntax Error

[auujxa2 via AccessMonster.com]

Just when I think I'm getting good at SQL, I get humbled. I'm getting a
syntax error, and I know it's the last field, because if I remove it, it
works. And when I debg and hover over the last field, I get the path. So
it's just a matter of figuring out the correct placement of the quotations I
guess. Please help. Thank you in advance.

strSQL = "INSERT INTO MasterTbl ( When, Who, What, Where)" & _
" SELECT Now()" & "," & " """ & CurrentUser & """," & " " & "'S1'" &
" AS Str" & "," & " " & Application.CurrentProject.Path & ""

DoCmd.RunSQL strSQL

 

[Douglas J. Steele]

strSQL = "INSERT INTO MasterTbl ( When, Who, What, Where)" & _
" SELECT Now(),'" & CurrentUser & "', 'S1' & _
",'" & Application.CurrentProject.Path & "'"

Exagerated for clarity, that's

strSQL = "INSERT INTO MasterTbl ( When, Who, What, Where)" & _
" SELECT Now(),' " & CurrentUser & " ', 'S1' & _
",' " & Application.CurrentProject.Path & " ' "

 

[auujxa2 via AccessMonster.com]

It didn't work, so I did this instead: (i changed Where to Path)

strSQL = "INSERT INTO MasterTbl ( When, Who, What)" & _
" SELECT Now()" & "," & " """ & CurrentUser & """," & " " & "'S1'" &
" AS Str"

DoCmd.RunSQL strSQL

strSQL = " UPDATE [MasterTbl] SET [Path] = Application.CurrentProject.Path" &
_
" WHERE [Path] is Null"

DoCmd.RunSQL strSQL

strSQL = "INSERT INTO MasterTbl ( When, Who, What, Where)" & _
" SELECT Now(),'" & CurrentUser & "', 'S1' & _
",'" & Application.CurrentProject.Path & "'"

Exagerated for clarity, that's

strSQL = "INSERT INTO MasterTbl ( When, Who, What, Where)" & _
" SELECT Now(),' " & CurrentUser & " ', 'S1' & _
",' " & Application.CurrentProject.Path & " ' "

Just when I think I'm getting good at SQL, I get humbled. I'm getting a
syntax error, and I know it's the last field, because if I remove it, it

[quoted text clipped - 9 lines]

DoCmd.RunSQL strSQL

 

[Douglas J. Steele]

I'm absolutely shocked that that would work. I didn't think Jet knew
anything about Application.CurrentProject.Path, so I thought the query would
fail.

I just noticed a typo in my previous reply (which would explain why it
didn't work for you), but to be honest, I wouldn't use the SELECT style, I'd
use

strSQL = "INSERT INTO MasterTbl ( When, Who, What, Where)" & _
" VALUES (Now(),'" & CurrentUser & "', 'S1', '" & _
Application.CurrentProject.Path & "')"

Exagerated for clarity, that's

strSQL = "INSERT INTO MasterTbl ( When, Who, What, Where)" & _
" VALUES (Now(),' " & CurrentUser & " ', 'S1' , ' " & _
Application.CurrentProject.Path & " ' ) "

In case you care, there should have been a double quote between 'S1' and the
ampersand.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

It didn't work, so I did this instead: (i changed Where to Path)

strSQL = "INSERT INTO MasterTbl ( When, Who, What)" & _
" SELECT Now()" & "," & " """ & CurrentUser & """," & " " & "'S1'"
&
" AS Str"

DoCmd.RunSQL strSQL

strSQL = " UPDATE [MasterTbl] SET [Path] =
Application.CurrentProject.Path" &
_
" WHERE [Path] is Null"

DoCmd.RunSQL strSQL

strSQL = "INSERT INTO MasterTbl ( When, Who, What, Where)" & _
" SELECT Now(),'" & CurrentUser & "', 'S1' & _
",'" & Application.CurrentProject.Path & "'"

Exagerated for clarity, that's

strSQL = "INSERT INTO MasterTbl ( When, Who, What, Where)" & _
" SELECT Now(),' " & CurrentUser & " ', 'S1' & _
",' " & Application.CurrentProject.Path & " ' "

Just when I think I'm getting good at SQL, I get humbled. I'm getting a
syntax error, and I know it's the last field, because if I remove it, it

[quoted text clipped - 9 lines]

DoCmd.RunSQL strSQL