asked on

Insert Into and use Current Date

Experts, I am using the below code to update a table. I need to adjust it. The issue is fldDate is being updated with the value of [Date] but I need it to update to the current date. I have a field named [Date] and that is the issue. The “VALUES (#" & Format(Date, "m\/d\/yyyy") ” part in the code below would need to be adjusted to insert the current date (not the date found in the field [Date]). I know that [Date] is not ideal name but I am importing from excel. I do not want to change the name. thank you....

Private Sub cboStatus2_AfterUpdate()
    Dim strSQL As String
     DoCmd.RunCommand acCmdSaveRecord
  strSQL = "INSERT INTO tblStatusHistory (fldDate, id, Buy_CP, Batch, TP_No, Status2) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & ",'" & Me!Buy_CP & "','" & Me!Batch & "','" & Me!Trade_No & "','" & Me!cboStatus2.Column(1) & "')"
  
  'Debug.Print strSQL
  
  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings True
  
End Sub

Open in new window

Norie

In the code you posted the function Date, which you are using, returns the current date not the value from a field so that's what the value of fldDate should be updated to.

Is that not what's happening?

Jim Dettman (EE MVE)

"INSERT INTO tblStatusHistory ([Date], id, Buy_CP, Batch, TP_No, Status2) VALUES (#" & Format(Date, "mm/dd/yy") & "#," & Me!ID & ",'" & Me!Buy_CP & "','" & Me!Batch & "','" & Me!Trade_No & "','" & Me!cboStatus2.Column(1) & "')"

Jim.

pdvsa

ASKER

thanks for the responses.
Jim: doesnt seem to do the trick though. its not updating to current date. It is still pulling the date from the [Date] field. I did change "([Date], id, to [fldDate] though but I dont think that had anything to do with the issue. The field [fldDate] is the field in the tblStatusHistory.

Norie:
In the code you posted the function Date, which you are using, returns the current date not the value from a field so that's what the value of fldDate should be updated to.
==>its not the case though. It is not updating to current date. Its pulling the value from the field [Date].

example: lets say teh value in [Date] = 4/19 but the current date is 4/10. The InsertInto is inserting 4/19 into [fldDate].

I hope it makes sense. thank you ....

Jim Dettman (EE MVE)

<<example: lets say teh value in [Date] = 4/19 but the current date is 4/10. The InsertInto is inserting 4/19 into [fldDate]. >>

I don't understand why that would be the case, but to avoid the problem, do it this way:

"INSERT INTO tblStatusHistory ([fldDate], id, Buy_CP, Batch, TP_No, Status2) VALUES (#" & Format(Fix(Now()), "mm/dd/yy") & "#," & Me!ID & ",'" & Me!Buy_CP & "','" & Me!Batch & "','" & Me!Trade_No & "','" & Me!cboStatus2.Column(1) & "')"

Jim.

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Norie

You could also try use the ISO date format.

 strSQL = "INSERT INTO tblStatusHistory (fldDate, id, Buy_CP, Batch, TP_No, Status2) VALUES (#" & Format(Date, "yyyy-mm-dd") & "#," & Me!ID & ",'" & Me!Buy_CP & "','" & Me!Batch & "','" & Me!Trade_No & "','" & Me!cboStatus2.Column(1) & "')"

Open in new window

pdvsa

ASKER

Hi Ryan, not at computer but I think that is the solution. I also like your idea to set the field to current date. Its actually easier that way.