Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 26th, 2007, 12:05 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default Convert entry to non-standard date

I have a need to be able to enter "feb 06" into a text field on a form and get "Feb/2006" stored.

Also if they enter just "feb" then the current year be used, so if it was 2007 then "Feb/2007" would get stored.

I tried to use this code, in the fields after update, but it does not deal with the "feb" or text part.

Code:
Function fGetDate(txtDate As Access.TextBox) As Boolean

'Used to get proper date format in the after update event. Must be used with a text box

Dim strGetDateMessage As String
Dim strGetDateError As String
Dim dteDate As Date
Dim strDate As String

On Error GoTo Err_Proc

strDate = txtDate

If IsNumeric(strDate) Then 'if only numbers, then insert slash

    Select Case Len(strDate)

        Case 1, 2 'if numeric and only one or two, interpet as day

            'strDate = Month(Date) & "/" & strDate & "/" & Year(Date)
            strDate = Month(Date) & "/" & Year(Date)

        Case 3, 4 'at least two numbers, then interpret first two as 
            month, after as day strDate = Mid(strDate, 1, 2) & "/" & 
           Mid(strDate, 3) & "/" & Year(Date)

        Case Else

        strDate = Mid(strDate, 1, 2) & "/" & Mid(strDate, 3, 2) & "/" & Mid(strDate, 5)

    End Select

End If

'strDate = DateValue(strDate) 'cdate could also be used

'if the above works, then everything is fine otherwise error 13
    Debug.Print strDate
'txtDate = DateValue(strDate) 'this will raise an error 2115 if it doesn 't work
txtDateJunk = DateValue(strDate) 'this will raise an error 2115 if it doesn 't work
txtDate = strDate
    Debug.Print txtDate
fGetDate = True

Exit_Proc:

Exit Function

Err_Proc:

Select Case Err.Number

Case 13

strGetDateError = "Date entered improperly @" & _
"There are a number of formats you may use:" & vbCrLf & vbCrLf & _
"1. Numbers and slashes, eg, MM/DD/YY (or YYYY), M/D/YY" & vbCrLf & vbCrLf & _
"2. Name of a month (3 or more letters) day, year (in any order) with spaces or commas between" & vbCrLf & vbCrLf & _
" eg, 12 Apr 1999, Apr 2000 12, April 1, 99, etc" & vbCrLf & vbCrLf & _
"(for 1 & 2, if the year is left out, the current year will be assumed)" & vbCrLf & vbCrLf & _
"3. Numbers with no delimiters:" & vbCrLf & _
"* 1 or 2 numbers is day (current month & year added)" & vbCrLf & _
"* 3 or 4 numbers is month/day (current year added)" & vbCrLf & _
"* 5 to 8 numbers will be interpeted as month/day/year."

strGetDateMessage = MsgBox(strGetDateError, 0 + 64, "Improper Date Format")

txtDate = Null

fGetDate = False

GoTo Exit_Proc

End Select

End Function


Mitch
__________________
Mitch
 
Old September 26th, 2007, 02:15 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

First of all, why do you want users to enter whatever they want? Why not give them a date picker, or allow them to select months and years from combo boxes, or use an input mask?

It would be hard to code this if a user could enter any sort of date format they wanted.

First, what are the business rules? is there a reason to allow users to enter whatever they want for a date rather than impose input masks?


mmcdonal
 
Old October 1st, 2007, 11:43 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Did you find a resolution to this problem?


mmcdonal




Similar Threads
Thread Thread Starter Forum Replies Last Post
Date entry textbox as / / adamusufi SQL Server 2000 5 February 22nd, 2009 12:23 AM
date entry Tim Johnson Access 1 December 26th, 2007 08:18 AM
Tracking record entry by date hikinfool Access 1 August 9th, 2006 09:42 PM
strictly for date entry only Rudner Pro VB 6 6 February 28th, 2006 09:25 AM
C#(ASP.NET) Validate date entry richie86 ASP.NET 1.0 and 1.1 Basics 1 November 3rd, 2005 01:58 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.