Adding a bogus timestamp to existing dated field formatted as mm/dd/yyyy 2

[08211987]

Hi,
I have a date field that is defined in mainframe as mm/dd/yyyy and it represents the Add Date of a vendor. I am automating a process that will update a table of vendors with all new vendors have been added since the last time the process ran. Everytime the process runs I store the current date and look in a history table for the previous run date and pull all vendors that have been added between those dates. The problem that can be encountered is since the timestamp is not in the Add Date I could miss some vendors added the same day the process was run but after the process ran.

I'd like to create a 1 time query to update all the add dates to have their mm/dd/yyyy add date and concatenate " 12:00:00 AM" to the date. I've changed the date format to General and created an update query with this value in the "Update To:" [tbl_Vendor_Tracker_AddDateTime].[Added_Date] & " 12:00:00 AM" but it only leaves the mm/dd/yyyy - so something is wrong with that.

Going forward when pulling the new vendors from the mainframe, I will update their Add Date mm/dd/yyyy to also concantenate the TIME from NOW() so that all vendors in the table will be "mm/dd/yyyy hh:mm:ss AM".

Can someone point me in the right direction of the correct syntax?
Thanks!

 

[SkipVought]

Hi,

A DateTime value is a DateTime value. They are merely NUMBERS.

What you are referring to is a Display Format. ANY Integer Date can be Displayed with a Time Value of 12:00 AM, but NOTHING is added or concatenated or appended. Normally this Display Format is generated at a report event, in a form or a report

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[08211987]

So there is nothing one can do to change a Short date "mm/dd/yyyy" field to a General Date "mm/dd/yyyy hour:min:sec AM" and provide the time manually?
Thanks.

 

[dhookom]

08211987,
You are asking the same as changing 123 to 123.0000. They are the same.

A date field always stores the time element. You can display them any way you want by applying a format in a control.

Now() = 7/25/2013 8:24:17 AM
Date() = 7/25/2013
Format(#7/25/2013 8:24:17 AM#,"mm/dd/yyyy") = 07/25/2013
Format(Date(),"mm/dd/yyyy hh:nn:ss") = 07/25/2013 00:00:00



Duane
Hook'D on Access
MS Access MVP

 

[08211987]

I appreciate both of your feedback but I still can't determine how I can manipulate the Time component of a field called Added_Date. It is currently formatted as Short Date mm/dd/yyyy and I want to change the format to a General Date and force the Time Component to 12:00:00 AM. I have a history table from mainframe that I need to update all records to begin my new process.

Can this be done in an update query? I tried by putting the folling in the "Update To:" [tbl_Vendor_Tracker_AddDateTime].[Added_Date] & " 12:00:00 AM", but no change.

Sorry if this should be obvious.

 

[SkipVought]

Can this be done in an update query? I tried by putting the folling in the "Update To:" [tbl_Vendor_Tracker_AddDateTime].[Added_Date] & " 12:00:00 AM", but no change.

This is like saying "I've got 123 and I want to APPEND .0000 to it in order to get 123.0000"

As Duane stated, "123 ... 123.0000. They are the same."

Dates are NOT strings. They are NUMBERS, that are DISPLAYED as a string.

Your issue is a DISPLAY FORMAT. At report time, FORMAT these dates any way you like!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

The Added_Date value already includes 12:00:00 AM. There is absolutely nothing to add. It's like adding 0.0 to any number. The result is the same.

Date/time fields are stored as floating numbers with the whole number part being the number of days since Dec 30 1899. The fraction part of the floating number is the fraction of a day. Noon is 0.5 and 6 PM is 0.75. Midnight/12:00 AM is 0.0. Adding midnight to a date is the same as adding 0.0. Why would you want to add 0.0 to a number?

You should only need to change the displayed format of the date. You can't change the format by adding a number to a number.

Duane
Hook'D on Access
MS Access MVP

 

[08211987]

Great info from both - thanks! I just realized I was concerned that I might add a vendor twice during 2 separate runs if the process ran in the middle of the day, but due to the primary keys in the query that shouldn't be an issue.
Thanks Again!