[AccessD] SQL statement problem

Liz Doering liz at symphonyinfo.com
Thu May 8 15:02:09 CDT 2003


Nancy,

About the time I think I'm losing my mind on this sort of thing, I dim a
string (strSQL), then set

strSQL="my problematical statement here".

Then set the next line of code to be

debug.print strSQL

Then you can view the immediate window, and your icky piece of SQL will be
right there, exactly as you would have been about to run it.  Copy and paste
it into the SQL view of a new query, and go to work straightening out
whatever mess there is.  It will be much more obvious from this view!

Liz




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Nancy Lytle
Sent: Thursday, May 08, 2003 2:38 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] SQL statement problem


John's version gave me:

insert into tblUserLog([ErrNumber], [ErrDescription], [ErrDate], [UserName],
[ShowUser], [CallingProc]) values ('2102' ,'The form name 'This Form' is
misspelled or refers to a form that doesn't exist.' ,'5/8/2003 3:10:56 PM',
'nlytle', 'True', 'Form_Open()')

And Andy's gave me:
insert into tblUserLog([ErrNumber], [ErrDescription], [ErrDate], [UserName],
[ShowUser], [CallingProc]) values ('2102' ,'The form name 'This Form' is
misspelled or refers to a form that doesn't exist.' ,'5/8/2003 3:12:39 PM',
'nlytle', 'True', 'Form_Open()')

Both of which seem identical to what I got.  I think I am going crazy!  It
has to be something terribly simple, but I just can't get it.

By the way I am using A2000 on Win2000.

I have used this same technique many times in the past, but this time it
just won't work.

It has to be the single quotes inside the Err_Description.

Nancy L.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Andy Lacey
Sent: Thursday, May 08, 2003 2:12 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] SQL statement problem


Nancy
How about

strSQLInsert = "insert into tblUserLog([ErrNumber], [ErrDescription],
[ErrDate], [UserName], [ShowUser], [CallingProc]) values "
strSQLInsert = strSQLInsert & "(" & lngErrNumber & ", '" &
Left(strErrDescription, 255) & "' ,Now(), '" & fcnOSUserName & "', '" &
vShowUser & "', '" & strCallingProc & "')"

How does that do?

Andy Lacey
http://www.minstersystems.co.uk




> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Nancy Lytle
> Sent: 08 May 2003 19:02
> To: accessd at databaseadvisors.com
> Subject: RE: [AccessD] SQL statement problem
>
>
> Thank you for your reply but it still doesn't want to work
>
> Using your code this is the insert statement produced:
>
> insert into tblUserLog([ErrNumber], [ErrDescription],
> [ErrDate], [UserName], [ShowUser], [CallingProc]) values
> ('2102' ,'The form name 'This Form' is misspelled or refers
> to a form that doesn't exist.' ,'5/8/2003 1:55:33 PM',
> 'nlytle', 'True', 'Form_Open()')
>
> THe message I get is Syntax error (missing operator) in query
> expression:
>
>  "The form name 'This Form' is misspelled or refers to a form
> that doesn't exist.' ,'5/8/2003 1:55:33 PM', 'nlytle',
> 'True', 'Form_Open()')'.
>
> It may have to do with the single quotes inside the
> ErrDescription, notice the double quotes at the beggining and
> single quote ending Any ideas?
>
> Nancy L
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Ruff
> Sent: Thursday, May 08, 2003 1:35 PM
> To: accessd at databaseadvisors.com
> Subject: RE: [AccessD] SQL statement problem
>
>
> strSQLInsert = "insert into tblUserLog([ErrNumber],
> [ErrDescription], [ErrDate], [UserName], [ShowUser],
> [CallingProc]) values "
>
> Error in this second line, the lngErrNumber is a number so
> remove the apostrophe before and after lngErrNumber.  Now()
> is a date so place a # before and after Now().  The SQL
> statement should be; strSQLInsert = strSQLInsert & "(" &
> lngErrNumber & ",'" & Left(strErrDescription, 255) & "',#" &
> Now() & "#', '" & fcnOSUserName & "', '" & vShowUser & "', '"
> & strCallingProc & "')"
>
>
>
> John V. Ruff - The Eternal Optimist :-)
> Always Looking For Contract Opportunities
>
> Home: 253.588.2139
> Cell: 253.307/2947
> 9306 Farwest Dr SW
> Lakewood, WA 98498
>
> "Commit to the Lord whatever you do,
>                 and your plans will succeed." Proverbs 16:3
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Nancy Lytle
> Sent: Thursday, May 08, 2003 10:31 AM
> To: Accessd
> Subject: [AccessD] SQL statement problem
>
>
> Can anyone see what is wrong with this statement?
> I have a feeling it has to do with quote marks but I have
> been pulling my hair out trying to get it,  I guess I just
> can't see the forrest for the trees.
>
> strSQLInsert = "insert into tblUserLog([ErrNumber],
> [ErrDescription], [ErrDate], [UserName], [ShowUser],
> [CallingProc]) values " strSQLInsert = strSQLInsert & "('" &
> lngErrNumber & "' ,'" & Left(strErrDescription, 255) & "' ,'"
> & Now() & "', '" & fcnOSUserName & "', '" & vShowUser & "',
> '" & strCallingProc & "')"
>
> This is how it reads when I try and do the insert:
> insert into tblUserLog([ErrNumber], [ErrDescription],
> [ErrDate], [UserName], [ShowUser], [CallingProc]) values
> ('2102' ,'The form name 'This Form' is misspelled or refers
> to a form that doesn't exist.' ,'5/8/2003 1:28:00 PM',
> 'nlytle', 'True', 'Form_Open()')
>
> Thanks,
> Nancy L
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/a> ccessd
> Website:
> http://www.databaseadvisors.com
>
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/a> ccessd
> Website:
> http://www.databaseadvisors.com
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/a> ccessd
> Website:
> http://www.databaseadvisors.com
>

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list