Access module to convert date

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

officework13

Thread Starter
Joined
Mar 2, 2004
Messages
85
Problem: Access table was provided to me with the date fields as integer format (20040625). There are 20 tables with various different date fields in each one.
I need to run various queries that will convert the date into date format (06/25/2004).
I have developed the following formula to do this (the field name was [HireDate]

DateSerial((Mid([HireDate],1,4)),(Mid([HireDate],5,2)),(Mid([HireDate],7,2)))

This returns the correct result and so I know that the query works.

What I need.
I need a module that will enable me to access the formula from within the expression builder and apply the same formula to the various different date fields within the different tables that I may require

Example
table name – T1
Date field name – D1

Your assistance is much appreciated
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Take a look at this database, the query you need is convertdate, the module is module1 and the Function is Function mydate(Data As String)
 

Attachments

officework13

Thread Starter
Joined
Mar 2, 2004
Messages
85
late last night after looking at some other websites and reading some books i came across some other information helping me to develop the solution so i thought i would post it.

Public Function ConvertDate(varDate As Variant) As Date

Dim strDate As String

strDate = Nz(varDate, "0")

ConvertDate = 0

If strDate <> "0" Then ConvertDate = CDate(Mid(strDate, 1, 4) & "/" & Mid(strDate, 5, 2) & "/" & Mid(strDate, 7, 2))
End Function

The formula noted here works near identical to the one i had above, but this one seems work just a bit faster.

thanks for the reply.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top