Insert Into from entries in a form works great, except with dates

[sheckay]

Could anyone tell me what I'm doing wrong? I've looked it up and I can't figure it out. But, basically, there are two dates on the entry form that I want copied to other tables. There's Me.DateofOrder and Me.DateRequired. They should be copied to fields of the same name in other tables. I managed to get past errors, but nothing has shown up in the fields of those tables. This example inserts the data into the shipRECtbl. The entries in question are in red.




DoCmd.RunSQL "INSERT INTO shipRECtbl (JobIDfk, Customername, SalesOrderNumber, CustomerPO, DateofOrder, DateRequired) VALUES ('" & Me.JOB_ID & "','" & Me.CustomerName & "','" & Me.SalesOrderNumber & "','" & Me.CustomerPO & "','" & "#Me.DateofOrder#" & "','" & "#Me.DateRequired#" & "' );"


End Sub

Thanks in advance.

[kd2017]

Dim my_sql As String
    my_sql = "INSERT INTO shipRECtbl (JobIDfk, Customername, SalesOrderNumber, CustomerPO, DateofOrder, DateRequired) VALUES ('" & Me.JOB_ID & "','" & Me.CustomerName & "','" & Me.SalesOrderNumber & "','" & Me.CustomerPO & "',#" & Me.DateofOrder & "#,#" & Me.DateRequired & "# );"
    Debug.Print my_sql 'verify the sql statement is built correctly
    DoCmd.RunSQL my_sql 'execute the sql statement

The debug window that comes in handy in these situations.

[sheckay]

Thank you for your reply. When I entered the first two lines of your code, and I run the form. Upon clicking the command button, I get "Compile ErrorMethod or Data Member Not Found" Highlighting ".DateofOrder" of Me.DateofOrder. If I put quotes around both Me.DateofOrder and Me.DateRequired, the compile error doesn't happen, but the dates are not copied over.

[Welshgasman]

No
You put all the sql into a string variable strSQL
THEN you Debug.Print strSQL to see what it contains. Correct until it is valid and correct. If you cannot see the error, post back here the poutput from debug.print.

Get into the habit of checking what you have before trying to use it, as opposed to just running what you *think* you have. It will save you so much time down the road.

Dates will need to be in mm/dd/yyyy (or yyyy/mm/dd )format (normally using the Format function) which also converts the date to a string for concatenation.

I have used this constant in the past, as I can never remember the whole syntax.
Public Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Needed for dates in queries as Access expects USA format.


then Format(MyDateControl,strcJetDate)

HTH

[kd2017]

Thank you for your reply. When I entered the first two lines of your code, and I run the form. Upon clicking the command button, I get "Compile ErrorMethod or Data Member Not Found" Highlighting ".DateofOrder" of Me.DateofOrder.

This tells me that Me.DateofOrder is not the name of a control on your form. Double check spelling of your controls and field names for typos.

If I put quotes around both Me.DateofOrder and Me.DateRequired, the compile error doesn't happen, but the dates are not copied over.

I'm afraid this is incorrect. Putting quotes around those object names simply turns them into a string that literally adds 'Me.DateofOrder' to your sql statement rather than "injecting" the value of the control into the sql statement.

I've triple checked the sql statement that I posted for you and I can't see an error with the code itself. (Of course I could have missed something). So again, that tells me there is a typo somewhere for you to find. Also, I noticed that you've put quotes around the value for the JobIDfk, was that intentional? If JobIDfk is a number datatype you want to remove those single quotes. Typically keys are numbers and not strings.

[sheckay]

Thanks. I'll check for mispellings. I had gone through a lot of that when I was beginning the troubleshooting process. I had copied field names from the tables to make sure. But there's a lot that's gone on since. The syntax that I used for the other fields is through what I had researched, and it works for those fields. Just not for date fields. So I was tweaking the part of the value area to get past the errors I had from the date fields.