Date Conversion

[andyj-84]

Hi,

I'm trying to create an append query using the standar query builder. A couple of the fields are date formats but the source data has them has dd.mm.yyyy

Is there a way that I can convert, and append, the data as a normal date format without amending it at source.

I've tried Format, Datevalue, Cdate and combinations of the three but at best I get 30/12/1899.

Thanks,

Andrew

 

[RainLover]

Is there a way that I can convert, and append, the data as a normal date format without amending it at source.

What is Normal?

Normal to me may not be normal to you or the Spanish, French, Dutch or Russians.

How have you applied your Functions?

 

[andyj-84]

Hi RainMan,

By normal I mean dd/mm/yyyy. I've been applying the function in the Field row of the Query Designer e.g. Appt: Format([F3],"Short Date").

Thanks

 

[pr2-eugin]

andyj-84, I think dd.mm.yyyy is not a valid date (I guess !!).. IMO a custom function would be in order to get the date and then add it to a new field..

 

[RainLover]

Andy

The format to use is American. mm/dd/yyyy

http://allenbrowne.com/ser-36.html#Format

Have a look at Allen Browne's site. It has a good article on Dates as well as many other subjects. I would suggest that you bookmark the site for future reference.

The Dot separator is something I have never had to deal with. If you can't find an answer in this link post back and we will look harder.

Just one question. What is the source? A Table, Query a Form or something else.

 

[RainLover]

andyj-84, I think dd.mm.yyyy is not a valid date (I guess !!).. IMO a custom function would be in order to get the date and then add it to a new field..

Paul,

I think Norwegian uses the dot separator. Go to Control Panel and have a play with Regional settings. You will find a few different Formats.

 

[RainLover]

Andy

Have you looked at Access Help.

You could find your answer there.

 

[ChrisO]

If the 'Dates' are really in the format of dd.mm.yyyy try:-

Public Function ConvertToDate(ByVal vntIn As Variant) As Variant
    Dim vntTemp As Variant

    If Len(vntIn) Then
        vntTemp = Split(vntIn, ".")
        
        If UBound(vntTemp) = 2 Then
            On Error Resume Next
                vntTemp = DateSerial(vntTemp(2), vntTemp(1), vntTemp(0))
            On Error GoTo 0
            
            If IsDate(vntTemp) Then ConvertToDate = vntTemp
        End If
    End If

End Function

Call as:-
Appt: ConvertToDate([F3])

Chris.

 

[RainLover]

Thanks Chris.

I thought it might be tricky but not as tricky as you have shown.

Thanks again for jumping in.

 

[ChrisO]

Let’s wait and see if it works.

Chris.

 

[nanscombe]

Alternatively the OP might simply like to try ...

NewDate: CDate(Replace([dateField], ".", "/"))

 

[pr2-eugin]

What if the dateField is Null?

 

[nanscombe]

Here are two possible options:

1) Default Date:
NewDate: CDate(Replace(Nz([dateField], "1/1/1980"), ".", "/"))

2) Null field:
NewDate: IIf(IsNull([dateField]), Null, CDate(Replace([dateField], ".", "/")))

 

[pr2-eugin]

Nigel, please do not get offended.. Am not trying to prove anything here... Just a few suggestions

Your first solution would return a Default date 1/1/1980 if there is a Null value.. If the OP is happy that's well and good.. Else we might have a problem..

The second solution will fail.. As an Immediate If evaluates both True and False statements before actually considering the condition..

That is the real need of a Function here (as ChrisO has posted) rather than a single line entry..

 

[nanscombe]

Having used Access since version 2, I tend to test solutions before suggesting them if I can.

ID dateField NewDate NewDate2
12 1/1/1980
13 1.2.2013 1/2/2013 1/2/2013
14 2.5.2013 2/5/2013 2/5/2013
15 3.2.2012 3/2/2012 3/2/2012
16 1/1/1980
17 1/1/1980
18 1.4.2013 1/4/2013 1/4/2013
19 1/1/1980
20 1/1/1980
21 1/1/1980



If, however, you still wanted to use a function then it could probably simplified as ...

Public Function ConvertToDate(ByVal vntIn As Variant) As Variant
    Dim vntTemp As Variant

    If Len(vntIn) Then
        
        vntIn = Replace(vntIn, ".", "/")
        If IsDate(vntIn) Then ConvertToDate = CDate(vntIn)
    
    End If

End Function

 

[Pat Hartman]

If you import the data manually using the wizard, click the advanced button. You can change the format of dates and this might be an option. If so, you won't have to write any code. Just save the import spec and reference it in your TransferSpreadsheet/TransferText method.

 

[ChrisO]

Nigel.

Try changing your regional settings to English (United States).
You will find that 2.5.2013 is still converted to 2/5/2013.
In the US, with a known date format of dd.mm.yyyy, 2.5.2013 should produce 5/2/2013.

The reason for this is that Access only sees 2.5.2013 and not the intended date format of dd.mm.yyyy. So Access makes a guess based on regional settings and thinks that, for the US, 2.5.2013 means 5 February 2013 when in fact we know that 2.5.2013 means 2 May 2013.

That was the reason I used the Split function and forced the date format with the DateSerial function. It forced 2.5.2013 to be interpreted as day, month, year.

If you wish to check this, wrap your function and my function in the CDbl function.
If you then switch from English (United Kingdom) to English (United States) regional settings you will find that your function changes in numerical value. With a known date format of dd.mm.yyyy and a value of 2.5.2013 the return value should be the same for all regional settings.

Chris.

 

[nanscombe]

Some European countries, like Germany, use dd.mm.yyyy as their date format.

We don't know where the OP is from but, since he says the dates are in the format of dd.mm.yyyy rather than mm.dd.yyyy, he is possibly more likely to be European.

andyj-84 (Andy) that's why it may be useful to at least have a country in your profile.

 

[ChrisO]

Nigel.

This is what we know:-
----------
The source data is in the format of dd.mm.yyyy
You have supplied a sample of 2.5.2013
That is the 2nd of May 2013
The double raw data for that date is 41396
The double raw data for that date does not change with regional settings.
----------

With a format of dd.mm.yyyy and data of 2.5.2013 the raw data is 41396 irrespective of regional settings.

When we open a table or query we are looking at a Form in datasheet view. The dates shown in that Form have already been formatted according to regional settings.

If we are using UK regional settings we will see 2/5/2013.
If we are using US regional settings we will see 5/2/2013.
But the date is still 2nd of May 2013 because the raw double data is still 41396 in both cases.

If we compare the results of your function and my function then the date format should change but the raw double data must not change.

Demo attached.

So to test it properly, open the query and leave it open. Switch between UK and US regional settings in control panel. Note how your function maintains the displayed date format but changes the raw double data. That is totally incorrect. The raw double data must remain the same and the displayed date should change depending on the regional settings selected.

Because of the specification given by Andrew (OP) and the sample data given by you the date is fixed at 2nd of May 2013. The stored double data for that date must not change with regional settings.

The date is 41396 and that is it, period. The fat lady has sung. It does not matter what regional settings Andrew is using. The date is displayed correctly as a Double. How the date is displayed as a Date is a completely different matter.

If you intend to respond then please do so carefully; others can see the demo I posted and prove it for themselves. At the moment, all you are doing is creating more work for me to explain it again and again.

If you do not understand then by all means ask and, if I can, I will try to explain what is going on in this thread. Dates with regional settings in VBA happens to be one of my pet subjects in Access.

Chris.

 

[nanscombe]

People should be careful though as passing dates to your code in mm.dd.yyyy format as the results would be incorrect.

The line

vntTemp = DateSerial(vntTemp(2), vntTemp(1), vntTemp(0))

Would need to be changed to

vntTemp = DateSerial(vntTemp(2), vntTemp(0), vntTemp(1))