handling unknown dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have tables that store month, day, year in separate fields to accomodate unknown months and unknown days. I want a user to to enter a date in a form in a single text box, then parse the date into the month, day, year fields of the underlying table. Any ideas?
 
Here's how I parse a text box where the user can enter a
Last name and First name (separated by commas). You
should be able to use this as an example (i.e., instead of
looking for commas, look for "/" in the date text box.
Make sure you use an input mask so that you can anticipate
the input). If you use only one mask, you should be able
to just use the Mid command to get the information, rather
than look for "/".

Hope this helps, john


If Me.ctlLName = "" Then
MsgBox "You didn't enter a last name to find."
Else
blnComma = False
strFirstName = ""
For i = 1 To Len(Me.ctlLName)
If Mid(Me.ctlLName, i, 1) = "," Or blnComma Then
blnComma = True
If Mid(Me.ctlLName, i, 1) <> "," And Mid
(Me.ctlLName, i, 1) <> " " Then
strFirstName = strFirstName & Mid
(Me.ctlLName, i, 1)
End If
Else
If Mid(Me.ctlLName, i, 1) <> " " Then
strLastName = strLastName & Mid(Me.ctlLName, i, 1)
End If

Next i

If strFirstName = "" Then
strCriteria = "Lname = '" & Me.ctlLName & "'"
Else
strCriteria = "Lname = '" & strLastName & "' And
Fname = '" & strFirstName & "'"
End If

rst.FindFirst strCriteria

If rst.NoMatch Then
MsgBox "Couldn't find the last name " & Me.ctlLName
Else
Forms![frmDemographics].Bookmark = rst.Bookmark
End If


End If
 
Make the textbox an unbound textbox. Place 3 more textboxes on the form
bound to the 3 fields and their Visible property set to No. In the Current
event of the form you would take the values from these 3 textboxes and fill
in the visible one. In the AfterUpdate event of the visible textbox, you
would fill in the 3 hidden ones.

Some functions that may come in handy to do this.
DateSerial
DatePart
Month
Year
Day

--
Wayne Morgan
Microsoft Access MVP


JohnC said:
I have tables that store month, day, year in separate fields to accomodate
Click to expand...
unknown months and unknown days. I want a user to to enter a date in a form
in a single text box, then parse the date into the month, day, year fields
of the underlying table. Any ideas?
 
Put the following code in the AfterUpdate event of the single textbox where
users enter the date:
Me!MonthFieldname = Month([NameOfSingleTextBox])
Me!DayFieldname = Day([NameOfSingleTextBox])
Me!YearFieldname = Year([NameOfSingleTextBox])


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com



JohnC said:
I have tables that store month, day, year in separate fields to accomodate
Click to expand...
unknown months and unknown days. I want a user to to enter a date in a form in a
single text box, then parse the date into the month, day, year fields of the
underlying table. Any ideas?
 
I have tables that store month, day, year in separate fields
to accomodate unknown months and unknown days. I want a user
to to enter a date in a form in a single text box, then parse
the date into the month, day, year fields of the underlying table.
Click to expand...

Whether this is a good idea at all depends on a) the facts you need to
capture, and b) on how much bad data you will tolerate. At the very
least, you need constraints to guarantee no negative numbers in those
integer columns. You also need to make sure February 29 falls in a
leap year.

In terms of facts, three integer columns can represent "It was
sometime in 1997", but they can't represent "It was either in December
1997 or January 1998".

Finally, suppose the user enters "3/04" in the text box. Does that
mean an unknown day in March, 2004; an unknown day in March, 1904;
April 3 in an unknown year; or March 4 in an unknown year?
 
Back
Top