Go Premium for a chance to win a PS4. Enter to Win

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

Problem with Type Mismatch error in Access 2007

I have an unbound form that adds a record to a table. One of the fields is a date/time type and is not required. Whenever I fill out the form and hit add record I get a type mismatch error. The field I am having a problem with is separation_date. Below is the code to add the record:

' Open a connection.
    Set cnn1 = New ADODB.Connection
   mydb = "c:\barnett\barnett.accdb"
strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & mydb
    cnn1.Open strCnn

' Open employee table.
    Set rstcontact = New ADODB.Recordset
    rstcontact.CursorType = adOpenKeyset
    rstcontact.LockType = adLockOptimistic
    rstcontact.Open "employee", cnn1, , , adCmdTable

'get the new record data
        rstcontact!empid = empid
        rstcontact!fname = fname
        rstcontact!lname = lname
        rstcontact!hiredate = hiredate
        rstcontact!i9 = i9
        rstcontact!dl_id = dl_id
        rstcontact!birth_certificate = birth_certificate
        rstcontact!sscard = sscard
        rstcontact!currently_working = currently_working
        rstcontact!temp_agency = temp_agency
        rstcontact!separation_date = separation_date
        rstcontact!separation_reason = separation_reason
        rstcontact!notes = notes
        rstcontact!eligible_for_rehire = eligible_for_rehire
        rstcontact!eaddress = eaddress
        rstcontact!ecity = ecity
        rstcontact!estate = estate
        rstcontact!ezip = ezip
        rstcontact!ephone = ephone
        rstcontact!ecell = ecell
        rstcontact!emergency_name = emergency_name
        rstcontact!emergency_number = emergency_number
        rstcontact!payrate = payrate
        rstcontact!employee_status = employee_status

' Show the newly added data.
        'MsgBox "New contact: " & rstcontact!Name & " has been successfully added"

'close connections

Open in new window

Thanks in advance for your help.
4 Solutions
Randy PooleCommented:
replace in your code rstcontact!separation_date = separation_date with this:
if Not IsNull(seperation_date) then rstcontact!separation_date = separation_date

Open in new window

Rey Obrero (Capricorn1)Commented:
try this, using the Nz() function

rstcontact!separation_date = nz(separation_date,null)
Dale FyeCommented:
Where does the value for the variable 'separation_date' come from?

How is that variable (I assume it is a variable) declared.  If it is not a variable, but is actually a control, I would strongly recommend you use some form of naming convention to name your controls so you can distinguish them from variables.

I would generally go with a technique similar to Randy's but would also test for blanks, something like:
If Trim(separation_date & " ") <> "" then 
    rstcontact!separation_date = separation_date
End If

Open in new window

When you use an unbound form, Access will not do any automatic validation of data types unless you give it a clue what is in the field.  Set the format of the date control to be a date and Access will only allow null or a valid date.

Checking for not null is not sufficient for a date field from an unbound control.  If you don't do what I suggested above and even if you do and want to validate further, then use the IsDate() function.

If IsDate(separation_date)  then 
    rstcontact!separation_date = separation_date
End If

Open in new window

princeserviceAuthor Commented:
I figured out that I didn't need that field in the "add employee" form in the first place. The field works good in the "edit employee" form and that is a bound form. I will use all of the information in this question for any possible future problems. Thanks everyone for the lesson.

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now