DatePart Function

[Vergy39]

I have a database with a form where someone will be entering the received date and the received date and time. This is necessary to simplify the reporting as they wanted a Turn Around Time by Weekending Date. So I use the received date to determine the weekending date and the received date and time to deterine the Turn Around time. My problem is I dont want the person to have to enter the same date twice. I want the field that will be the received date to get the date (mm/dd/yyy) from the received Date and Time field. I have tried the DatePart function, but cannot get it to work. I have used the default value of the received Date field like (=DatePart("mm/dd/yyy",[ReceivedDateTime]). Any assistance is greatly appreciated.

Thanks
David

 

[pr2-eugin]

I do not thnik DatePart has the functionality of providing your desired result.. DatePart can only give you; as its name suggests only parts of date.. like the date or month or year not all of them together.. (well if you want you can.. the long winded way..)

The method you should use here would be either DateValue or Format.. Something like..

DateValue([ReceivedDateTime]) 
OR
Format([ReceivedDateTime], "mm/dd/yyyy")

For more information on the different Date functions available with access use the following link.. (Look under Date Funcations)

http://www.techonthenet.com/access/functions/index.php

 

[Pat Hartman]

If the two dates are always the same, don't enter the date in the second field at all. Just enter the time. Then when you need to calculate elapsed hours, you would add the "date" field to the "time" field to get the complete date/time value. Format the controls on the form so they only show their respective parts.

Remember dates are stored as double precision numbers. So you will be adding
41051.0 + 0.5468287037
to get
05/22/12 1:07:26 PM

This of course brings up the question of why have two fields at all when one will do?

 

[Vergy39]

Thank you both (pr2-eugin and Pat Hartmen). I tried both examples, DateValue and Format, and did not work. I tried entering these in the Default Value portion of the text box. The date I put in the ReceivedDateTime field did not populate.

For Pat, the reason I have 2 fields is it is easier for me to calculate a weekending date with just a date then it is with a datetime. I use WeekEndingDate: [ReceivedDate]-Weekday([ReceivedDate])+7. Since I am somewhat of a novice with access, I could not figure out how to extract just the date from the datetime field to get the weekending date.

Thanks
David

 

[Pat Hartman]

Go back to a single date field. Then use DateValue() or TimeValue() to pull out only the date or only the time.

To merge a date without a time and a time without a date, add them together as I showed in my example.