Insert text with a commas and parentheses

S

Samantha

Is there any restriction with MsAccess database with inserting a text with
commas and parenthesis in the text ?
My sample Desc text is: "Side, Top, (10)+(1)"

I insert the same Desc text (with commas) into a SQL database and it worked
fine.
And I also tried the INSERT statement without the Desc text and it works
fine.
The error message simply says:
Run-time error '3134':
Syntax error into INSERT INTO statement

Here's my insert statement:
sql = "INSERT INTO [PanelPN] ( PartNumber, Rev, BSlot, CSlot,
DSlot, DateCreated, CreatedBy, Desc ) "
sql = sql & " VALUES ( '" & panelPN & "', 'A', '" & panelB & "',
'" & panelC & "', '" & panelD & "', #" & Now() & "#, '" & AlUser & "', '" &
panelDesc & "' ) "
db.Execute sql, dbFailOnError

Can anyone provide me with some pointers? Very much appreciated.
 
M

Matthias Klaey

Samantha said:
Is there any restriction with MsAccess database with inserting a text with
commas and parenthesis in the text ?
My sample Desc text is: "Side, Top, (10)+(1)"

I insert the same Desc text (with commas) into a SQL database and it worked
fine.
And I also tried the INSERT statement without the Desc text and it works
fine.
The error message simply says:
Run-time error '3134':
Syntax error into INSERT INTO statement

Here's my insert statement:
sql = "INSERT INTO [PanelPN] ( PartNumber, Rev, BSlot, CSlot,
DSlot, DateCreated, CreatedBy, Desc ) "
sql = sql & " VALUES ( '" & panelPN & "', 'A', '" & panelB & "',
'" & panelC & "', '" & panelD & "', #" & Now() & "#, '" & AlUser & "', '" &
panelDesc & "' ) "
db.Execute sql, dbFailOnError

Can anyone provide me with some pointers? Very much appreciated.

There shouldn't be such a restriction. The only two characters that
pose problems are the ' = Chr(39) and the | = Chr(129) characters.

How about if you rename your "Desc" field to something like
"Description", or if you use "[Desc]" instead of "Desc"? Just an idea.
There might be a conflict with the "Descending" keyword.

Also, you might want to check if the

#" & Now() & "#

expression gives a correct date/time literal in the form of

#mm/dd/yyyy hh:nn:ss AM# (respectively PM)

for us non-US people this normmaly doesn't work :)

HTH
Matthias Kläy
 
S

Samantha

Placing brackets in [Desc] works like a charm! Thank you Matthias. You saved
me a lot of time!

Matthias Klaey said:
Samantha said:
Is there any restriction with MsAccess database with inserting a text with
commas and parenthesis in the text ?
My sample Desc text is: "Side, Top, (10)+(1)"

I insert the same Desc text (with commas) into a SQL database and it worked
fine.
And I also tried the INSERT statement without the Desc text and it works
fine.
The error message simply says:
Run-time error '3134':
Syntax error into INSERT INTO statement

Here's my insert statement:
sql = "INSERT INTO [PanelPN] ( PartNumber, Rev, BSlot, CSlot,
DSlot, DateCreated, CreatedBy, Desc ) "
sql = sql & " VALUES ( '" & panelPN & "', 'A', '" & panelB & "',
'" & panelC & "', '" & panelD & "', #" & Now() & "#, '" & AlUser & "', '" &
panelDesc & "' ) "
db.Execute sql, dbFailOnError

Can anyone provide me with some pointers? Very much appreciated.

There shouldn't be such a restriction. The only two characters that
pose problems are the ' = Chr(39) and the | = Chr(129) characters.

How about if you rename your "Desc" field to something like
"Description", or if you use "[Desc]" instead of "Desc"? Just an idea.
There might be a conflict with the "Descending" keyword.

Also, you might want to check if the

#" & Now() & "#

expression gives a correct date/time literal in the form of

#mm/dd/yyyy hh:nn:ss AM# (respectively PM)

for us non-US people this normmaly doesn't work :)

HTH
Matthias Kläy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top