Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access Date Format using Dot or Period as Separator

Posted on 2013-11-21
10
Medium Priority
?
858 Views
Last Modified: 2013-11-22
We have Office 2010.  In Access 2010, I have a table named Docket.  In this table I have a field called DktDate.

The field DktDate is formatted as a Short Date.  I created a Form named frmDocket.

When I enter dates into the DktDate field of this form...
   I can type 11/21/2013 and it displays as 11/21/2013 - This is perfect.
   I can type 11/21/13 and it displays as 11/21/2013 - This is perfect.
   I can type 11.21.2013 and it displays as 11/21/2013 - This is perfect.
   I can type 11.21.13 and it displays as 12/30/1899 - This is a major issue!

I need my users to also be able to type 11.21.13 and I need it to display 11/21/2013.

Can anyone assist me with this?  

Also, I don't want to have to alter my settings under the Windows Regional and Language Options.

Thanks in advance!
0
Comment
Question by:Senniger1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39666390
you can do this by adding an unbound textbox to your form "txtDate"

in the afterupdate of this textbox, set the value of your DktDate

me.DktDate=replace(me.txtDate,".","/")
0
 

Author Comment

by:Senniger1
ID: 39666434
capricorn1 - I always appreciate your help.

I want to mention I simplified my form so everyone could understand my question.  My actual form has at least 50 date fields.

With that being said, what field are you proposing the user enters the date into?  The DktDate field or the txtDate field.  And if it's the latter, will it still format all the other entry variations (listed above) correctly?

Thanks!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39666446
<And if it's the latter, will it still format all the other entry variations (listed above) correctly?>

Yes

better using the cdate() function

me.DktDate=cdate(replace(me.txtDate,".","/") )
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:Senniger1
ID: 39666475
So sorry.  I'm still confused.  

Will the user be entering the date in the DktDate field on the form or on the txtDate field on the form?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39666591
the date will entered in txtDate,

in the afterupdate of this textbox, set the value of your DktDate

me.DktDate=cdate(replace(me.txtDate,".","/") )
0
 

Author Comment

by:Senniger1
ID: 39666751
Okay, I see that logic works, however I'm concerned with my form.  If I have to add an unbound textbox for every date field I have on my form then I will have two fields showing for every field.  

My form is quite involved with 50+ dates on it and barely has room for all the fields already on it.  Also I think it would be quite confusing to see to date fields for each item on the form.

Am I missing something?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39667381
you don't need to show the bound controls for dates in your form,
just  show the unbound controls

you can place the unbound textboxes on top of the bound controls
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 39668310
You should take another route.

Your issue is that when using dots only and small numbers, the input is read a time.
So all you need is to check for this and convert in the AfterUpdate event of DktDate using the components of the entered time value. No extra textboxes are needed.
As you have many date fields, you will need to call a function:

Private Sub DktDate_AfterUpdate()
    
    Call ConvertTimeToDate(Me!DktDate)

End Sub

Private Sub ConvertTimeToDate(ByRef ctl As Control)

    Dim varDate As Variant
    
    varDate = ctl.Value
    If IsDate(varDate) Then
        If DateValue(varDate) = #12:00:00 AM# Then
            ctl.Value = DateSerial(Second(varDate), Hour(varDate), Minute(varDate))
        End If
    End If

End Sub

Open in new window

/gustav
0
 

Author Closing Comment

by:Senniger1
ID: 39668628
This was exactly what I needed.  Thanks to everyone for your help!
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39668661
You are welcome!

/gustav
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question