• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

MS ACCESS String to date conversion including formatting.

I have 2 fields,  YYYYMMDD and HH.  I can combine the 2 in an expression field and add minutes so it looks like this YYYYMMDD HHMM.  Now how do I make it a true date field?  I need to be able to pull data such as 2011/10/10 23:00 to 2011/10/11 13:00.  I added 00 to put minutes on the hour field but I can add MMSS if that is what Access is looking for.   Can this be done in one expression field something like this? CDATE(FORMAT([DATETIME],YYYYMMDD HHMMSS).  I get an error saying wrong number of arguments but I don't know how to get the time part added in.  I need it in military time if that makes any difference.

Thanks!
LindaOKState
0
LindaOKSTATE
Asked:
LindaOKSTATE
  • 5
  • 4
  • 3
2 Solutions
 
als315Commented:
If your field is MyDate (string, like "20111024 1100" try to use function:

Function DateConv(MyDate As String) As Date
DateConv = DateSerial(CInt(Mid(MyDate, 1, 4)), CInt(Mid(MyDate, 5, 2)), CInt(Mid(MyDate, 7, 2))) + TimeValue(CInt(Mid(MyDate, 10, 2)) & ":" & CInt(Mid(MyDate, 12, 2)))
End Function

Open in new window

0
 
LindaOKSTATEAuthor Commented:
Where do I put this is a MS Access query?
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
this is what I tested:


Dim d As Date
    Dim str As String
    
    d = #10/21/2011 2:15:00 PM#
    str = Format(d, "YYYYMMDD HHMM")
    MsgBox str

    d = CDate(Mid(str, 5, 2) & "/" & Mid(str, 7, 2) & "/" & Left(str, 4) & " " & Mid(str, 10, 2) & ":" & Right(str, 2))
    
    MsgBox d

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Mike EghtebasDatabase and Application DeveloperCommented:
if the field name is: [YDM] containing data like 20110915 2134

Date: CDate(Mid(YDM, 5, 2) & "/" & Mid(YDM, 7, 2) & "/" & Left(YDM, 4) & " " & Mid(YDM, 10, 2) & ":" & Right(YDM, 2))

Will give you   9/15/2011 9:34:00 PM
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
use  MyDate:

Date is reserved word
0
 
LindaOKSTATEAuthor Commented:
OK Folks, I'm getting a little lost here.  I haven't used DIm or Function- End Function in any MS Access queries.  I need to be able to make this field a date field in a query so that the client can enter the begin date and end date for a search criteria.  Since I need to be able to put the begin and end date on a report, I suppose I will need a form with controls on it.  I was looking to be able to put something like this in the converted-formatted field in the query:

>=Format([Forms]!frm_SelectDate]![ctlStart],"yyyymmdd") And <=Format([Forms]![frm_SelectDate]![ctlEnd],"yyyymmdd")

I realize this doesn't have the time part, but this is sort of what I need.  Could it be done if I did a query on a query?
0
 
als315Commented:
Add module to your DB, copy-paste function DateConv to module and call function from any query like:
DateField: DateConv(MyDate)
0
 
als315Commented:
Where will be:
>=[Forms]!frm_SelectDate]![ctlStart] And <=[Forms]![frm_SelectDate]![ctlEnd]
or, may be:
>=Datevalue([Forms]!frm_SelectDate]![ctlStart]) And <=Datevalue([Forms]![frm_SelectDate]![ctlEnd])
0
 
LindaOKSTATEAuthor Commented:
als315: I will try adding a module.

eghtebas:  I am also trying your way.  I am doing the concatenation of date and time fields and calling it MyDateTime.  Then i am trying to do your part in another field like this:

MyConDtTm: CDate(Mid([MyDateTime],5,2) & "/" & Left([MyDateTime],4) & " " & Right([MyDateTime],2))  

It thinks MyDateTIme is a parameter and asks for a value.  Do I have to do these in separate queries?
0
 
LindaOKSTATEAuthor Commented:
I have gotten both methods to work, thanks to you both.  I created a second query to run them and the parameter problem went away.  It is returning as AM-PM.  Is there a way to get Military time?  This is for use in a medical facility and the nurses are used to asking in Military time.

Thanks
0
 
als315Commented:
Output depends from your regional settings. You can manually set any format in form or in report
0
 
LindaOKSTATEAuthor Commented:
Thank you both.

LindaOKState
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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