Datevalue

[Angelflower]

In Excel there is a function called Datevalue() that will return the dates computer serial number. See below example:
Formula Description (Result)
=DATEVALUE("8/22/2008") Serial number of the text date, using the 1900 date system (39682)
=DATEVALUE("22-AUG-2008") Serial number of the text date, using the 1900 date system (39682)
=DATEVALUE("2008/02/23") Serial number of the text date, using the 1900 date system (39501)
=DATEVALUE("5-JUL") Serial number of the text date, using the 1900 date system, and assuming the computer's built-in clock is set to 2008 (39634)

Is there a function in Access that will do the same thing?

 

[GaryPanic]

the now() fuction does this - you then format this to your hearts content
check for date formating in samples - this was done about 2 weeks ago

 

[rainman89]

dateserial i believe it what u are looking for

Edit: nope im wrong

 

[Rabbie]

There is a DateValue function in Access. I think is the same as in Excel. Check in Access Help. Try this link http://msdn2.microsoft.com/en-us/library/Aa262719(VS.60).aspx for more info.

 

[Angelflower]

nope...looking the dates' serial number

Thanks for the suggestions but that is not what I am looking for. I am looking for a function that will assign a number to the date as the my example shows.

 

[Rabbie]

The Date type in Access stores the date as a number as in your examples. You can change the display format to see it as a date or as a number. DATValue takes a string and changes it into a Date type (ie the number you want) but you will need to change the display format to show it as a number.

 

[Angelflower]

hum... So how would I get that display value and combine it with a client name to give me a unique id. I have two tables that I need to match up. In the first table I have members who have a letter code assigned to them for a date range. In the other table I have members with say visits on several different date ranges. I need to find out what code was assigned to them on the day of their visit. Maybe I am going about this the wrong way.

Table 1: Member Name - Code - Start Date - End Date

Table 2: Member Name - Visit Number - Start Date - End Date

Query results: Member Name - Visit Number - Start Date - End Date - Code

 

[Angelflower]

Ok.. this is what I used to get something simliar to what I want.

Format([SERVICE_START_DATE],"ww" & "y" & "mm" & "d")

 

[CraigDolphin]

You could use CLng([DateField]) to achieve the serial number of the date using the 1900 system if [Datafield] is the Date datatype. If your date is stored as a string, then you would use CLng(CDate([StringDateField])).

 

[Angelflower]

You could use CLng([DateField]) to achieve the serial number of the date using the 1900 system if [Datafield] is the Date datatype. If your date is stored as a string, then you would use CLng(CDate([StringDateField])).

Thank you! that is what I was looking for!

 

[CraigDolphin]

Glad to help.

 

[Angelflower]

Yeah... after waking up a bit I figured out that it would be better to get my results with the following query:

UPDATE WorkSheet INNER JOIN RateCode_DOS ON WorkSheet.PIC = RateCode_DOS.PIC SET WorkSheet.CategoryStartDate = RateCode_DOS!Condition
WHERE (((WorkSheet.SERVICE_START_DATE)>=[RateCode_DOS]![effdate] And (WorkSheet.SERVICE_START_DATE)<=[RateCode_DOS]![termdate]));

For some reason I like to find the hardest way frist..... lol...