convert a short text yyyymmdd in dd-mm-yyyy

[killerflappy]

Hi guys,

I have a short text field with a date (yyyymmdd) and want to convert it to a short text field as dd-mm-yyyy. So 20171128 must be converted to 28-11-2017.

I can do this with the CDATE, but then it converts it to a date, and I want it to be a short text field.

I want to use an update query in VBA to do this. Can someone point me in the right direction?

 

[plog]

string -> date -> string

Use the Format function for the last step of that. https://www.techonthenet.com/access/functions/date/format.php

 

[plog]

I want to use an update query in VBA to do this

Just caught the last part of that. Why?

It's a date correct? Then store it as a date, no reason to store this thing as the wrong data type. Store it correctly.

 

[Cronk]

@killerflappy

You can reformat using left, right and mid functions.

However, your example
20172811
is not consistent with
yyyymmdd

 

[static]

In a standard module

Public Function xdate(dt As String) As String
    If Len(dt) = 8 Then
        s = Left(dt, 4) & " " & Mid(dt, 5, 2) & " " & MonthName(CLng(Right(dt, 2)))
        If IsDate(s) Then xdate = Format(CDate(s), "dd-mm-yyyy")
    End If
End Function

e.g.

Debug.Print xdate("20172811")

update table1 set mydate=xdate(mydate)

 

[MarkK]

Hey, one little trick you can do if you have a string like 20171128 is Format() it so it is a valid date, which saves you all the Left(), Mid(), Right() string extraction work, like...

Public Function xdate(ds As String) As String
[COLOR="Green"]    'format the cryptic string into a valid date format[/COLOR]
    ds = Format(ds, "0000-00-00")
[COLOR="green"]    'if the date is still valid, convert and reformat it[/COLOR]
    If IsDate(ds) Then xdate = Format(CDate(ds), "dd-mm-yyyy")
End Function

Mark

 

[Pat Hartman]

What is the point of storing a date as a string? Do you realize that if you store a date as a string, it acts like a string in comparisons and sorting rather than acting like a date.

01-01-2017 is LESS than 01-02-2016 -- is that the behavior you are looking for?

 

[killerflappy]

Just caught the last part of that. Why?

It's a date correct? Then store it as a date, no reason to store this thing as the wrong data type. Store it correctly.

To clearify. It's used as an import table. I receive the date format froma customer. Make changes to the import table and then proces it to a combined table. When I change it to a date field, the next import failed.

 

[sonic8]

I receive the date format froma customer. Make changes to the import table and then proces it to a combined table. When I change it to a date field, the next import failed.

There is a little trick to make it work anyway.

• Create an Import Specification with the date order YMD and a random date separator. Check four digit years and leading zeroes in dates.
• Go to the Navigation Options (in the context menu of the Navigation Pane) and enable "Show System Objects".
• Open the MSysIMEXSpecs table, find your import spec, and delete whatever there is in the column DateDelim.
• Now run the import with that spec and Access will recognize the yyyymmdd dates from your import table as dates.

A word of warning: If you open the import spec details from the import wizard it will automatically add a date separator again. So, you need to repeat the above steps whenever you change anything in the spec via the UI.

By chance, I just very recently published a video tutorial on text file import and and an extensive text on date and time in Access/VBA. The do not address this very specific problem (yet), but they might still be worth your time to take look.

 

[killerflappy]

Thanks for putting me in the right direction you guys!

The SQL below does work and still seems to be short tekst.

UPDATE tblImport SET tblImport.date = Right([date],2) & '-' & Mid([date],5,2) & '-' & Left([date],4);

When I was testing the column one time changed into a date field.
How can I be sure that the column for date stays as short text?

 

[sonic8]

When I was testing the column one time changed into a date field.
How can I be sure that the column for date stays as short text?

In Access databases table columns do not just randomly change their data type. Unless you explicitly change the table design, either manually or by code, it will stay the way it is.

 

[isladogs]

Unless you specify the datatypes in your import spec, Access will look at the first few records in the import & use them to determine the datatype.

So with new data, it may see one or more fields as a different datatype ...
...if you allow it to do so.

 

[Pat Hartman]

Open the MSysIMEXSpecs table, find your import spec, and delete whatever there is in the column DateDelim.

Nice trick but unnecessary. The ImportSpec wizard allows you to do this directly. Just press the Advanced button. That gives you access to the arguments for defining a date. you can specify ymd, dmy, or mdy as the order, specify 2 or 4 digit years, and specify a separator or no separator.

To call up an existing Import/Export spec, start the import/export manually. make the changes to the spec and save it. Then close the wizard. There is no need t complete the process and actually import or export data.

PS - it is best to not use tricks when there is a legitimate method available.

 

[sonic8]

The ImportSpec wizard allows you to [...] specify a separator or no separator.

Pat, thank you for the correction. My memory failed me there. Of course you can specify no separator at all, even though the UI creates the impression of an undeletable blank due to the input mask on that control.

What does not work, is to actually enter a blank as date separator, for it is removed automatically in general by the Access UI. If you need the blank, you have to update the MSysIMEXSpecs table with SQL and then not touch the spec in the UI again. - I guess, I mixed that up with the no-separator situation.

 

[Pat Hartman]

What does not work, is to actually enter a blank as date separator

That's because "blank" is something. What you want is nothing and that means you need to remove (delete) the default value.

 

[sonic8]

That's because "blank" is something. What you want is nothing and that means you need to remove (delete) the default value.

Yes, of course. That was not related to the particular problem in this thread but a general remark about limitations of the ImEx-Spec-UI.