Date Error Handling

tlugdon · #1 (**permalink**) 07-06-09, 09:26

Hey All,

I created a program that sends data to an Access table. I want to add in some sort of date error handling so if they put in a future date, or a date that it is in 2008 it will pop-up an error. Im not sure how to work the code in VB. Thanks

Code:

Private Sub CommandButton12_Click()
Set objAccess = CreateObject("Access.Application")
Set accApp = CreateObject("Access.Application")

' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
    Set db = OpenDatabase("S:\Senior Operations Supervisors\Subscriber\Subscriber Survival\attendance.mdb")
    ' open the database
    Set rs = db.OpenRecordset("Attendance", dbOpenTable)
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("Lawson") = Range("C42").Value
            .Fields("Date") = Range("D42").Value
            .Fields("Name") = Range("E42").Value
            .Fields("Occurence") = Range("F42").Value
            .Fields("Comments") = Range("C45").Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With

Ax238 · #2 (**permalink**) 07-06-09, 10:21

So you just want to make sure the date is in the current year? Use the Year function on the date and compare it with the current year, if they are different, raise an error.

Ax

loquin · #3 (**permalink**) 07-06-09, 16:29

And, DATE is a horrible name for your date field. DATE is a reserved word in Access, VB, as well as every database known to man, along with most programming languages...

Try to use a more descriptive column name. AttendanceDate. SubscriptionDate. Etc.

loquin · #4 (**permalink**) 07-06-09, 16:33

Also, for date comparisons, you can compare dates to see if the date entered is greater than the current date.

Code:

If CDate(Date1) > CDate(Date2) then
  ' Date1 is greater than date 2.  The CDate function used in case the entered value is a string
End If

Internet Services

Web Development

Digital Marketing

Consumer Tech