Strange date format in table

[RichO]

I have a table that contains a date field. Normally this field is populated from a form where the user types in a date in the format of mm/dd/yyyy. This works just fine.

In some instances I am using VBA/SQL with DateAdd to populate the field to contain a date 3 weeks prior to another date field on the form.

DateAdd("d", -21, Me. Job_Date)

Say the job date field contains 12/28/2014, so then I want the resulting value to be 12/7/2014. When I view this value within the SQL it is correct, but once that value is inserted into the table and I view the table it comes out as

12:01:04 AM

I went into the table properties and changed the format to short date (mm/dd/yyyy) but then this entry now displays as

12/30/1899

...and when I click on that field in the table it then changes to the above time based value.

All of the rest of the dates in the table display as mm/dd/yyyy.

What do I need to do to fix this?

Thanks

 

[Rx_]

This is the time. The date of 0.0 is equal to 1900 Midnight
Let me ask, is this a local Access table or a SQL Linked table?

The short date is just showing a representation of the underlying data.
Dates are actually numeric, representing the number of days since 1900 midnight. The fraction is part of the day e.g. 0.25 is 1900 6AM

For starters, try to take the Me. Job_Date field
and put that into a variable. do you get something like 41929.4101041667
In your immediate window run this ? cdbl(now())

You want to get the value of the date and then convert. Something is missing that conversion along the way.

Interesting Note The format of the return value for DateAdd is determined by Control Panel settings, not by the format that is passed in the date argument.

The format is:
=DateAdd("d", -7, [PromisedDate]) ' field name of the form

 

[Minty]

It sounds as if the date is actually being stored as a time? 12/30/1899 is the default date for a time stored field. (All times or dates are stored as a datetime in the background.
Is it an access table or SQL linked table?

Doh _RX beat me to it

 

[Brianwarnock]

will that space between me. and job_date cause a problem?

Brian

 

[RichO]

OK, this was just an momentary lapse of brain on my part. When I was writing the date value to the table I failed to put it between ##. It all works fine now. Thanks for the replies and sorry for the false alarm :banghead:

 

[Rx_]

Probably too much chedder in your diet?
Wisconsin is warmly known as the Chedder Head state.
Mental Laspe: I resemble that remark.

Would you post the code or location of the obligatory pair of #
I had searched the internet and msdn looking for that. Nobody seemed to mention it.

 

[RichO]

I did have pizza last night, that must have done it

This is the code I am using, now functional with the ##

    Dim sql As String
            
    sql = "INSERT INTO tb_Alarm_Clock (Job_Number, Due_Date, Notes, Sent) VALUES ("
    sql = sql & Me.Job_Number & ", #" & DateAdd("d", -21, Me.Job_Date) & "#, 'Service fee reminder', 0)"
    
    CurrentDb.Execute sql