[AccessD] SQL statement problem

I think you made a typo there; the opening double quote has no corresponding
close. It was probably intended to be a single. So the real problem is the
nested single quotes around form_name, I think. But in playing around, I
discovered an interesting problem:

? Replace("The form name 'This Form' is misspelled or refers to a form that
doesn't exist.", Chr(39), Chr(34))
The form name "This Form" is misspelled or refers to a form that doesn"t
exist.

Note the double quote in doesn't.

I was playing around with variations on the above Replace() when suddenly it
occurred to me that there's a much slicker way:

? Replace("The form name 'This Form' is misspelled or refers to a form that
doesn't exist.", "doesn't", "does not")
The form name 'This Form' is misspelled or refers to a form that does not
exist.

Admittedly it's limited to this particular error message and would fail on
strings such as "The form name 'This Form' was created by Michael O'Hara and
Patty O'Furniture."

Too bad Access doesn't do regex.

Arthur

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Nancy Lytle
Sent: May 8, 2003 2:02 PM
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/accessd
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