Date format in finland

[hermanlaksko]

I have an ADP front that is used i vaious countryes, however when used in finland it comes up with all kinds of errors on dates.

I.E. when creating a recordset it will give an error although the format looks correct.
The finish date format is dd.mm.yyyy so I figured that it might be the date format or the "." in the date, and I tryed various like format(DateFld,"mm-dd-yyyy") but to no awail.

If there is a variable like "MyDay As Date", the program will give an error on that, if I remove the "As Date" and use the "format(..." on MyDay, instead it will give an error there.....

Can anyone help - Thanks in advance!

Herman
Say no to macros

 

[LisaWay]

Herman,

I set my Regional and Language Options so that the current format is "Finnish (Finland)". I then created a table containing a datetime field in an ADP front end. I was able to enter a date into the field, no problem. The date looks like this: 22.11.2008

I then tried creating a module containing the following routine:

Public Sub test()
    Dim mydate As Date
    
    mydate = #11/1/2003#
    Debug.Print mydate
    
End Sub

It worked properly, outputting "1.11.2003". Can you give a specific example that you have found [red]doesn't[/red] work?

(I am using Windows Vista and Access 2007.)

Regards,
Lisa

 

[RoyVidar]

The problem is seldom related to throwing dates between two "objects" recognizing dates. A date variable can usually play with a text control formatted with date, which can usually play with a field of the data type Datetime, as they will usually understand the underlying date datatype. The format is just ... the format.

What usually constitutes a problem, is when stuffing a valid date into a string, and then pass that string to the database engine for evaluation/excecution.

SQL-server, I think, expects such parameter for instance like this 'yyyymmdd', while ISO 8601, should also work 'yyyy-mm-dd'

Could you try using either
format(DateFld,"yyyy-mm-dd")
or
format(DateFld,"yyyymmdd")

'nother note, if you're using decimal numbers, and use such for criteria for dynamic SQL, you might want to use the Str function on them ...> " & Str(TheNumber) & " AND ...

Roy-Vidar

 

[hermanlaksko]

Thank you both for your replyes.

Hi Lisa I did try that myself on my pc. But could not generate the problem. Err occours in ie.
WHERE Fees.FeeDate >='" & Format(StartDate, "mm-dd-yyyy") & "' And Fees.FeeDate <='" & Format(EndDate, "mm-dd-yyyy") & "' etc.

Hi Roy
I'll try the 2 suggested formats.
I am also conciddering turning the dates into a decimals like:

WHERE Cast(Fees.FeeDate As Numeric(10)) >='" & CDec(StartDate) & "' And Cast(Fees.FeeDate As Numeric(10)) <'" & CDec(EndDate) & " etc.
Would that do the trick I wonder.


Herman
Say no to macros

 

[RoyVidar]

Be careful with the number-conversion, as it might show different result in different circumstances, due to difference in 0 date in SQL server vs VBA/Jet.

SELECT Cast(0 As DateTime) -> 01/01/1900
Debug.Print Format(0, "mm\/dd\/yyyy") -> 12/30/1899

As I mentioned, you must also take care with decimal numbers, especially if the decimal separator of the country in question is comma, in stead of period. To ensure correct decimal separator, use the Str function when concatenating decimal numbers into such strings.

Roy-Vidar

 

[hermanlaksko]

Hi Roy

I was aware about this oddity...
Why m$ - who is the manufactor of both the sql-server and access - has decided to place date 0 a 2 different dates I'll never know... its just m$ I guess.

But thanks for pointing eys to this fact, so I would not overlook it.

Herman
Say no to macros