Date Function

[lachesis]

Im trying to write a Date Function that strips the date from its source format (DD/MM/YYYY) with datatype Date, and converts it into the format (YYYY-MM-DD) with datatype String.

eg. xDate = 01/10/2002 (1 OCT 2002) should convert to string '2002-10-01'


Here's my code to do this:

Function ParseDate(xDate As Date) As String
ParseDate = CStr(Format(xDate, "yyyy-mm-dd", vbMonday))
Debug.Print ParseDate
End Function

When i pass a date, instead of seeing a date it treats the passed parameter like an equation. So parameter 01/10/2002 is the same as 0.00004995 which has a Date equivalent of 30-DEC-1899. Not what I was after !?

What's missing from my function?

L.

 

[PaulBricker]

I ran your function and got two different answers. If I enter my dates using the American format of mm/dd/yy, the function works fine. If I enter the dates using the format you specify dd/mm/yy, I get the same type of error you did. It may have something to do with your regional settings in your Control Panel. Try checking that first to see what your settings are.

Paul

 

[lachesis]

Funny becz I tried changing the Date settings in the Control Panel, and it didnt make any difference at all.

parsedate 10/01/2002

always equals

1899-12-30

in the Debug window. I want to see...

2002-01-10 (for parsedate 10/01/2002)
2002-10-01 (for parsedate 01/10/2002)

L.

 

[VBslammer]

The problem is that you are indeed using an equation rather than a date. You need to enclose dates between hash marks or double-quotes and it will work fine:

parsedate "10/01/2002"

parsedate #10/01/2002#

Other than that your function works beautifully.



VBSlammer

 

[lachesis]

Thnks VB. Its rather interesting I would have to enclose my dates in double-quotes?? Wouldn't that make the parameter a string by default?? eg "01/10/2002"

Perhaps further info is needed. My dates come from a form using an input mask. Dates are collected in the shortdate form 01/10/2002 :where 01 is the day, 10 is the month, 2002 is the.. well u get the idea.

The answer seems to be to make 01/10/2002 into a date literal ie. #01/10/2002# BEFORE it is passed to the function.

Any ideas??

Ive tried the "#" & way but clearly that's trying to add a string value to a date value = type mismatch...

hmmm...
L.

 

[VBslammer]

If you're using a recordset field as an argument to your function it should work without a hitch:

Public Sub ShowInvoiceDates()
  Dim rst As DAO.Recordset
  
  Set rst = CurrentDb.OpenRecordset("Select * From Invoices;")
  
  While Not rst.EOF
    Debug.Print ParseDate(rst![InvoiceDate])
    rst.MoveNext
  Wend
  
  rst.Close
  Set rst = Nothing
  
End Sub

What I was referring to is the assignment of a date literal to a variable, which would follow the same rules for any VB environment. If you dimension a Date variable and assign a value to it, you should use:

Dim dt As Date

dt = #10/2/02# 

   Or

dt = "10/2/02"

If you're using a date in a SQL string you follow the same logic:

Set rst = CurrentDb.OpenRecordset("Select * From Invoices Where Invoices.DatePaid > #" & [ThisDate] & "#;")

If you use: dt = 10/10/2002 then it's a math equation - as demonstrated by the following immediate-window snippets:

? 10/01/2002 
 0.004995004995005 

 ? Format(0.004995004995005,"yyyy-mm-dd" )
1899-12-30

? ParseDate(10/01/2002)
1899-12-30

These work fine:

? "10/1/02"
10/1/02

? ParseDate(#10/01/2002#)
2002-10-01

? ParseDate("10/01/02")
2002-10-01

Toodles,


VBSlammer

 

[nagyf]

That damned date constants dt = #10/2/02#!
Which is the year, the month, the day?
In Hungarian the order is 1. year, 2. month. 3. day =ANSI!
That is why I like the Function DateSerial(year,month,day).
When I pass date parameters to queries, I convert them to DateSerial(year,month,day) function calls intead of #date# constants.
If I need more exact date then I use
DateSerial(year,month,day)+TimeSerial(hour,minute,second)
[tt]
Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' "The goal of the life is the struggle itself"
[/tt]

 

[gerard1979]

In the past I used the follow function for re-ordering a date, maybe the idea is helpful for u,
give it a try:

convertDatum = Split(CStr(Date), "-&quot
vsDatum = "" & convertDatum(1) & "-" & convertDatum(0) & "-" & convertDatum(2)

This function Make a VSdate from My Dutch defaults, u can give it a try in ur situation

Hope it helps
Gerard

 

[raskew]

When working in a multi-national environment, we solved the data-format problem by always using the medium-date format, e.g., 1 Oct 2002. A bit awkward for some, but never confusing.

Keep in mind that, regardless of format, Access stores Date/Time data types as a double-precision, floating-point number (up to 15 decimal places). The integer portion of the double-precision number represents the date. The decimal portion represents the time as a portion of a whole day. To see this in action, from the debug window type: ? cdbl(now()), which will return something like 37538.2098726852.

The format() function will return a string representation of the stored number so, using your example:

mydate = #1 Oct 2002#
? mydate
10/1/02
? cdbl(mydate)
37530
parsedate = format(mydate, "yyyy-mm-dd&quot
? parsedate
2002-10-01

 

[lachesis]

Thanks for the feedback. Im not using recordsets, Im using a form to capture an unbound Date. I suppose its not my function which is the problem.
As far as I can determine it is the way Access passes dates internally.

Im no programmer but Access doesnt do this as I would have expected. This is my process:

1. I have an unbound field in a form. The field "DateFrom" has a Format property of "Short Date" and an Input Mask 99/99/0000;0;_
2. I take the date "DateFrom" from this field and pass it as a parameter to the Function parsedate (see prev posts)
3. Parsedate converts the parameter xDate to a string

The problem seems to be at step 2 bcz the Dates are being passed as equations: eg 10/01/2002 instead of literals #10/01/2002#.

How would I force Access to make my unbound date field get passed to the function as a date literal, instead of an equation??

Hoping...
L.

 

[lachesis]

Hey... thanks again.

I forgot to declare my Date variables as Public so they could be passed to my function in a seperate module. ... sorted now.

Live and learn.

L.