Date formatting YYYYMMDD 4

[laina222]

Hi all,
I need help formatting a date in a table. Right now they appear as YYYYMMDD so today's date would look like 20050906. I want it in short date format, i.e., 09/06/2005. Is there a way to do this in the input mask, or anywhere else so they appear how I'd like?
Thanks for any help in advance!
Laina

 

[cmmrfrds]

Is this a datetime data type? If so, look up Format in the help.

Format(yourdate,"Short Date")

 

[SkipVought]

FYI,

Take a look at Why do Dates and Times seem to be so much trouble? faq68-5827

Your "DATE" may ONLY be a string of digits which is virtually useless as a REAL DATE.

As cmmrfrds suggested, "Is this a datetime data type? If so, look up Format in the help."


Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[BoxHead]

Laina,

I've seen that format in electronic bills imported from vendors.

To change it to an actual date, I used a function like:

Public Function goodDate(txtDate As String) As Date
If Len(txtDate) = 8 Then
goodDate = Mid(txtDate, 5, 2) & "/" & Mid(txtDate, 7, 2) & "/" & Left(txtDate, 4)
End If
End Function

I saved it in a module and called it in a query for forms and reports.

HTH




John

Use what you have,
Learn what you can,
Create what you need.

 

[SkipVought]

John,

You may or may not get a REAL DATE out of your goodDate function. It depends on the txtDate string AND the Regional Settings for your PC.

What you are forcing VB to do, is convert a string in the form mm/dd/yyyy, to a NUMBER, and VB is "smart" enough to figgure it out and make the conversion.

For instance the string 09/07/2005, converted to a REAL DATE is 38602.

So a better approch for you function would be to explicitly create a date...

Public Function goodDate(txtDate As String) As Date
    If Len(txtDate) = 8 Then
    goodDate = DateSerial(Left(txtDate, 4), Mid(txtDate, 5, 2), Mid(txtDate, 7, 2))
    End If
End Function

You can run a test and observe that this code runs much faster.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[BoxHead]

Thanks, Skip.

Speed is our friend.


John

Use what you have,
Learn what you can,
Create what you need.

 

[laina222]

Thanks all!

I have the module written but how do I call it in a query?

 

[PHV]

In the SQL pane:
SELECT goodDate([your field]) As theDate
FROM yourTable

In the Query grid:
theDate: goodDate([your field])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[laina222]

Thanks, all! It works beautifully!