SQL statements

[Art Vandaley]

Hi,

I have following code in order to Append data into a table and have two
questions:

DoCmd.RunSQL "INSERT INTO [tablename] ( [field1], [field2], [field3] )" & _
"VALUES (" & Z & ", 'aaa'," & Date & ")"

Z is variant as integer. "aaa" is a string. Date is Date function.

Q1) I want to add current date but Date function is creating problem
something like syntax error. What can I do?
Q2) I want to cancel message box coming every time in FOR...NEXT loop and
telling "1 row will be added". How?

Thanks a lot for the answers in advance. Regards.

PS. Thanks to Van T. Dinh, John Vinson, Steve Schapel and GM for their
answer to my prior questions.

 

[John Vinson]

Hi,

I have following code in order to Append data into a table and have two
questions:

DoCmd.RunSQL "INSERT INTO [tablename] ( [field1], [field2], [field3] )" & _
"VALUES (" & Z & ", 'aaa'," & Date & ")"

Z is variant as integer. "aaa" is a string. Date is Date function.

Q1) I want to add current date but Date function is creating problem
something like syntax error. What can I do?

Add the syntactically required # date delimiters:

"VALUES (" & Z & ", 'aaa', #" & Date & "#)"

Q2) I want to cancel message box coming every time in FOR...NEXT loop and
telling "1 row will be added". How?

Put a line before the loop

DoCmd.SetWarnings False

and be sure to put a line

DoCmd.SetWarnings True

to turn warning messages back on.

Thanks a lot for the answers in advance. Regards.

PS. Thanks to Van T. Dinh, John Vinson, Steve Schapel and GM for their
answer to my prior questions.

You're most welcome!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Douglas J. Steele]

Hi,

I have following code in order to Append data into a table and have two
questions:

DoCmd.RunSQL "INSERT INTO [tablename] ( [field1], [field2], [field3] )" & _
"VALUES (" & Z & ", 'aaa'," & Date & ")"

Z is variant as integer. "aaa" is a string. Date is Date function.

Q1) I want to add current date but Date function is creating problem
something like syntax error. What can I do?

Add the syntactically required # date delimiters:

"VALUES (" & Z & ", 'aaa', #" & Date & "#)"

Sorry to be picky, John, but "VALUES (" & Z & ", 'aaa', #" & Date & "#)"
won't work properly for a large percentage of the world.

Art: The problem is that Access will not recognize dates that are in
dd/mm/yyyy format (unless it's after the 12th of the month). While that may
not seem like a problem to you now, you never know when you might have a
user who prefers that format.

To be sure, use:

"VALUES (" & Z & ", 'aaa', " & Format(Date, "\#mm\/dd\/yyyy\#" & ")"

 

[Art Vandaley]

Hi John,

I have still syntax error about using date function. Do you know why? I have
Access 2002

Regards.

Hi,

I have following code in order to Append data into a table and have two
questions:

DoCmd.RunSQL "INSERT INTO [tablename] ( [field1], [field2], [field3] )" &
_
"VALUES (" & Z & ", 'aaa'," & Date & ")"

Z is variant as integer. "aaa" is a string. Date is Date function.

Q1) I want to add current date but Date function is creating problem
something like syntax error. What can I do?

Add the syntactically required # date delimiters:

"VALUES (" & Z & ", 'aaa', #" & Date & "#)"

Q2) I want to cancel message box coming every time in FOR...NEXT loop and
telling "1 row will be added". How?

Put a line before the loop

DoCmd.SetWarnings False

and be sure to put a line

DoCmd.SetWarnings True

to turn warning messages back on.

Thanks a lot for the answers in advance. Regards.

PS. Thanks to Van T. Dinh, John Vinson, Steve Schapel and GM for their
answer to my prior questions.

You're most welcome!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Art Vandaley]

Hi Douglas,

Thanks a lot for solution it worked very well. You just forget one
phrantesis to close. I put it and ok. Thanks once more.

Hi,

I have following code in order to Append data into a table and have two
questions:

DoCmd.RunSQL "INSERT INTO [tablename] ( [field1], [field2], [field3] )"
& _
"VALUES (" & Z & ", 'aaa'," & Date & ")"

Z is variant as integer. "aaa" is a string. Date is Date function.

Q1) I want to add current date but Date function is creating problem
something like syntax error. What can I do?

Add the syntactically required # date delimiters:

"VALUES (" & Z & ", 'aaa', #" & Date & "#)"

Sorry to be picky, John, but "VALUES (" & Z & ", 'aaa', #" & Date & "#)"
won't work properly for a large percentage of the world.

Art: The problem is that Access will not recognize dates that are in
dd/mm/yyyy format (unless it's after the 12th of the month). While that
may
not seem like a problem to you now, you never know when you might have a
user who prefers that format.

To be sure, use:

"VALUES (" & Z & ", 'aaa', " & Format(Date, "\#mm\/dd\/yyyy\#" & ")"

 

[John Vinson]

Sorry to be picky, John, but "VALUES (" & Z & ", 'aaa', #" & Date & "#)"
won't work properly for a large percentage of the world.

Art: The problem is that Access will not recognize dates that are in
dd/mm/yyyy format (unless it's after the 12th of the month).

Right you are - thanks Douglas! Glad we got Art's problem fixed
between us!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Douglas J. Steele]

Right you are - thanks Douglas! Glad we got Art's problem fixed
between us!

I'll make you Yanks remember if it's the last thing I do! <g>