CurrentProject.Connection.Execute

[msmuzila]

Having trouble with some syntax

CurrentProject.Connection.Execute "insert into CBS_Reviews
(Bracket,INV_NO,zDate,CUST_1,EMAIL_1)" & _
"values(" & BRACKET_REVIEW & ",'" & "INV_NO" & "',#" &
DateAdd("d", 30, SHIP_DATE) & "#," & CUST_1 ," & EMAIL_1 &")"


where bracket, cust_1, email_1 are text field and zDate is a date
field and INV_NO is a number field


Also, can someone explain how to use the proper syntax.


Many Thanks
Matt

 

[Jason Lepack]

Try this:

dim strSQL as string
strSQL = "insert into CBS_Reviews " & _
"(Bracket,INV_NO,zDate,CUST_1,EMAIL_1) " & _
"values(" & BRACKET_REVIEW & ",'" & INV_NO & ",#" & _
DateAdd("d", 30, SHIP_DATE) & "#,'" & CUST_1 & "','" & EMAIL_1
&"')"

CurrentProject.Connection.Execute strSQL

Cheers,
Jason Lepack

 

[Douglas J. Steele]

Since Bracket is a text field, you need quotes around the value being passed
for the field. As well, since there's a chance that the user might be using
dd/mm/yyyy as their Short Date format, it's always a good idea to explicitly
format the date as either mm/dd/yyyy or yyyy-mm-dd:

dim strSQL as string

strSQL = "insert into CBS_Reviews " & _
"(Bracket,INV_NO,zDate,CUST_1,EMAIL_1) " & _
"values('" & BRACKET_REVIEW & "','" & INV_NO & "," & _
Format(DateAdd("d", 30, SHIP_DATE, "\#yyyy\-mm\-dd\#") & ", " & _
"'" & CUST_1 & "','" & EMAIL_1 &"')"

CurrentProject.Connection.Execute strSQL

As well, if Cust_1 can contain apostrophes (say it's the customer name, and
the name is O'Reilly), you'll want to handle that possibility:

dim strSQL as string

strSQL = "insert into CBS_Reviews " & _
"(Bracket,INV_NO,zDate,CUST_1,EMAIL_1) " & _
"values('" & BRACKET_REVIEW & "','" & INV_NO & "," & _
Format(DateAdd("d", 30, SHIP_DATE, "\#yyyy\-mm\-dd\#") & ", " & _
"'" & Replace(CUST_1, "'", "''") & "','" & EMAIL_1 &"')"

CurrentProject.Connection.Execute strSQL