[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

Date Conversion in Access YYYYMMDD

I need to use a function or nested function to convert a string value from the format of

YEARMONTHDAY   to the true date format.

I've hit a brick wall with all the traditional Access functions I normally use.....STRVAL.....DATEVAL,   MID()   etc.

Thanks in advance for any help.
0
James Powell
Asked:
James Powell
  • 3
2 Solutions
 
Jack LeachCommented:
Try this:

Public Function GetDateFromYYYYMMDD(ValueIn As String) As Date

  Dim s As String

  s = Left(ValueIn, 4) & "-" & Mid(ValueIn, 5, 2) & "-" & Right(ValueIn, 2)

  GetDateFromYYYYMMDD = CDate(s)

End Function

Open in new window

0
 
Helen FeddemaCommented:
Supposing the text dates are in this format:  "20140222", then this function will take the text string as an argument, and return a Date value:
Public Function TextToDate(strTextDate As String) As Date

   Dim strDate As String
   
   strDate = Mid(strTextDate, 5, 2) & "/" & Right(strTextDate, 2) _
      & "/ " & Left(strTextDate, 4)
   Debug.Print strDate
   TextToDate = CDate(strDate)
   
End Function

Open in new window

0
 
James PowellAuthor Commented:
awesome guys...thank you...I'm trying that...
after I create the public function...how do I reference it, in subsequent queries I might build?  (i.e. that aren't in SQL format)
0
 
James PowellAuthor Commented:
Never mind...figured that second question out....works like a CHARM...thank you very much!
0
 
James PowellAuthor Commented:
It just works!  Thank you for opening my eyes to the power of this.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now