Remove Time from ShortDate Field

Question

Hi
I would really appreciate your help

I have built a Form to fill in data using an "insert into" command button

The Table Data Type is "Date/Time"
The field Format is "Short Date"
The Input Mask is "Short Date"

Yet, every-time I insert a date value such as "21/08/2016",
the cell value comes out as "30/12/1899"  and its value is "00:01:52"

Only if I insert the value "21/08/2016 00:00:00" it comes out well.

What can I change so the the user will only need to enter the date WITHOUT the time?

Here is part of the code behind my add button:
CurrentDb.Execute "insert into [discount_table] ( [record_date])" & " values (" & Me.frecord_date  & " );"

Thank you

Answer 1

Set the form field data type to text and let Access do the conversion. Tested ok here.

---

Best regards, George

Answer 2

The reason is that the expression 21/08/2016 is being interpreted as an arithmetical expression, not a date.  You can see this in the debug window:

? 21/08/2016
 1.30208333333333E-03

If the return value is then formatted as date/time you get the value which your SQL statement is inserting:

? Format(1.30208333333333E-03,"dd/mm/yyyy hh:nn:ss")
30/12/1899 00:01:52

This is because the date/time data type in Access is implemented as a 64 bit floating point number, with zero representing 30 December 1899 00:00:00, so the number returned by your expression, which is 0.00130208333333333 in decimal notation, represents the point in time which is that fractional part of a day after 30 December 1899 00:00:00.

Date literals in Access are enclosed with the # character.  Moreover, they must be either in US short date format, mm/dd/yyyy, or an otherwise internationally unambiguous format such as the ISO standard for date notation of YYYY-MM-DD.  This does not mean that your users have to enter the date in such a format; you merely need to format the value they enter in the local format of dd/mm/yyyy to such a format when building the SQL statement, i.e.

CurrentDb.Execute "INSERT INTO discount_table(record_date)" & _
    " VALUES(#" & FORMAT(Me.frecord_date,"yyyy-mm-dd")  & "#)", dbFailOnError

---

Ken Sheridan, Stafford, England

Answer 3

Thank you George

I do not know why, but in my case it didn't work well.
Ken's reply however, did work for me.

Anyhow, I thank you for your reply.
And I will try it again, in similar situations

Answer 4

Thank you Ken, adding format to the code DID help! :)