Andy Lacey
andy at minstersystems.co.uk
Thu May 8 16:18:14 CDT 2003
Nancy
I'm sorry but I missed the fact that your description contained an
apostrophe. In that case this will (I think) work
strSQLInsert = "insert into tblUserLog([ErrNumber], [ErrDescription],
[ErrDate], [UserName], [ShowUser], [CallingProc]) values " strSQLInsert
= strSQLInsert & "(" & lngErrNumber & ", """ & Left(strErrDescription,
255) & """ ,Now(), '" & fcnOSUserName & "', '" & vShowUser & "', '" &
strCallingProc & "')"
But if you have descriptions containing double quotes those will then
fail.
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 20:58
> To: accessd at databaseadvisors.com
> Subject: RE: [AccessD] SQL statement problem
>
>
> Thanks to all for your help.
> I'm going to give it a fresh start in the morning and see
> what happens. Nancy L
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
> Wortz, Charles
> Sent: Thursday, May 08, 2003 3:49 PM
> To: accessd at databaseadvisors.com
> Subject: RE: [AccessD] SQL statement problem
>
>
> Nancy,
>
> There is an odd number of single quotes in this "'The form
> name 'This Form' is misspelled or refers to a form that
> doesn't exist.'" so it does not look like it will work. One
> or more of the single quotes in that field should be replaced
> by double quotes, Chr(34). To quote materiel with quotes you
> have to either use single quotes inside double quotes(" ' "),
> or double quotes inside single quotes (' " ').
>
> Charles Wortz
> Software Development Division
> Texas Education Agency
> 1701 N. Congress Ave
> Austin, TX 78701-1494
> 512-463-9493
> CWortz at tea.state.tx.us
>
>
>
> -----Original Message-----
> From: Nancy Lytle [mailto:nancy.lytle at auatac.com]
> Sent: Thursday 2003 May 08 14:38
> 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
>