Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    add value to insert statement (a2k3)

    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

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add value to insert statement (a2k3)

    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.
    Francois

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add value to insert statement (a2k3)

    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".

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add value to insert statement (a2k3)

    Sorry for misunderstanding your question.
    This suppose lngProviderOrgID is numeric as the name indicate it:
    <font face="Georgia">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 & ");"</font face=georgia>

    " & 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
    Francois

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add value to insert statement (a2k3)

    perfect. thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •