Solved

Problem with Type Mismatch error in Access 2007

Posted on 2014-09-08
5
574 Views
Last Modified: 2014-09-09
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.AddNew
        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
        rstcontact.Update

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

'close connections
rstcontact.close

Open in new window


Thanks in advance for your help.
0
Comment
Question by:princeservice
5 Comments
 
LVL 21

Accepted Solution

by:
Randy Poole earned 125 total points
ID: 40310594
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

0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 125 total points
ID: 40310636
try this, using the Nz() function

rstcontact!separation_date = nz(separation_date,null)
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 125 total points
ID: 40310674
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

0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
ID: 40310879
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

0
 

Author Comment

by:princeservice
ID: 40312142
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.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now