add value to insert statement (a2k3)

Topic

New Reply

I would like to insert the value in the control cboProviderOrgID into tblPymt in the field lngProviderOrgID. I amhave having a great deal of difficulty deciphering the syntax that comes after “values” in the statement below. Would someone be willing to break it down for me?

CurrentDb.Execute “Insert into tblPymt (lngWkOrderID,dtmBillStartDate,dtmBillEndDate, ” _
& ” strUtilityID) values (” & lngWkOrderID.value & “,#” & Format(dtmBillStartDate, “mm/dd/yyyy”) _
& “# , #” & Format(dtmCurrentDate – 1, “mm/dd/yyyy”) & “#, ‘” & strUtilityID & “‘);”

E

Reply | Quote

Replies

Use :
Dim strSQL As String
strSQL =”Insert into tblPymt (lngWkOrderID,dtmBillStartDate,dtmBillEndDate, ” _
& ” strUtilityID) values (” & lngWkOrderID.value & “,#” & Format(dtmBillStartDate, “mm/dd/yyyy”) _
& “# , #” & Format(dtmCurrentDate – 1, “mm/dd/yyyy”) & “#, ‘” & strUtilityID & “‘);”
MsgBox strSQL
CurrentDb.Execute strSQL

When the message box appears look at the sql string and look for the error, or post it here so we can help.

Reply | Quote

The statement executes correctly as it is written. However, I would like to insert another new value not currently included in the statement into an existing field. The new value is found in cboProviderOrgID and I want to insert that value into the field lngProviderOrgID in tblPymt. I am unable to revise the code as I am having a great deal of difficulty deciphering the syntax that comes after “values”.

Reply | Quote

Sorry for misunderstanding your question.
This suppose lngProviderOrgID is numeric as the name indicate it:
CurrentDb.Execute “Insert into tblPymt (lngWkOrderID,dtmBillStartDate,dtmBillEndDate, ” _
& ” strUtilityID,lngProviderOrgID) values (” & lngWkOrderID.value & “,#” & Format(dtmBillStartDate, “mm/dd/yyyy”) _
& “# , #” & Format(dtmCurrentDate – 1, “mm/dd/yyyy”) & “#, ‘” & strUtilityID & “‘,” & cboProviderOrgID & “);”

” & lngWkOrderID.value & “, : the value if the control or variable lngWkOrderId (.value is not strictly necessary)
#” & Format(dtmBillStartDate, “mm/dd/yyyy”) & “# , : The date of the control or dtmBillStartdate (Dates, in SQL have always to be in a mm/dd/yyyy format and surrounded by #)
#” & Format(dtmCurrentDate – 1, “mm/dd/yyyy”) & “#, : idem for the dtmCurrentDate control or variable but the day before this date (-1)
‘” & strUtilityID & “‘);” : The content of the control or variable strUtilityID. This is a text (string) and has to be surrounded by ‘
cboProviderOrgID : the value of cboProviderOrgID which is a combobox I suppose.
“);” close the parantheses.

Hope this help

Reply | Quote

perfect. thanks.

Reply | Quote

perfect. thanks.

Reply | Quote

Sorry for misunderstanding your question.
This suppose lngProviderOrgID is numeric as the name indicate it:
CurrentDb.Execute “Insert into tblPymt (lngWkOrderID,dtmBillStartDate,dtmBillEndDate, ” _
& ” strUtilityID,lngProviderOrgID) values (” & lngWkOrderID.value & “,#” & Format(dtmBillStartDate, “mm/dd/yyyy”) _
& “# , #” & Format(dtmCurrentDate – 1, “mm/dd/yyyy”) & “#, ‘” & strUtilityID & “‘,” & cboProviderOrgID & “);”

” & lngWkOrderID.value & “, : the value if the control or variable lngWkOrderId (.value is not strictly necessary)
#” & Format(dtmBillStartDate, “mm/dd/yyyy”) & “# , : The date of the control or dtmBillStartdate (Dates, in SQL have always to be in a mm/dd/yyyy format and surrounded by #)
#” & Format(dtmCurrentDate – 1, “mm/dd/yyyy”) & “#, : idem for the dtmCurrentDate control or variable but the day before this date (-1)
‘” & strUtilityID & “‘);” : The content of the control or variable strUtilityID. This is a text (string) and has to be surrounded by ‘
cboProviderOrgID : the value of cboProviderOrgID which is a combobox I suppose.
“);” close the parantheses.

Hope this help

Reply | Quote

The statement executes correctly as it is written. However, I would like to insert another new value not currently included in the statement into an existing field. The new value is found in cboProviderOrgID and I want to insert that value into the field lngProviderOrgID in tblPymt. I am unable to revise the code as I am having a great deal of difficulty deciphering the syntax that comes after “values”.

Reply | Quote

Use :
Dim strSQL As String
strSQL =”Insert into tblPymt (lngWkOrderID,dtmBillStartDate,dtmBillEndDate, ” _
& ” strUtilityID) values (” & lngWkOrderID.value & “,#” & Format(dtmBillStartDate, “mm/dd/yyyy”) _
& “# , #” & Format(dtmCurrentDate – 1, “mm/dd/yyyy”) & “#, ‘” & strUtilityID & “‘);”
MsgBox strSQL
CurrentDb.Execute strSQL

When the message box appears look at the sql string and look for the error, or post it here so we can help.

Reply | Quote