Data Type Issues

HelenLLL

Registered User.
Local time
Today, 05:43
Joined
Mar 10, 2014
Messages
18
I have two tables(see below). I want to set up a query, link these 2 tables together. I set a one-to-one relationship between Client ID in two table. But got error message :"Type mismatch in expression".

I tried to change Client ID data type from "Text" to "Number", then Access deleted some data under Client ID in Order table.

How can I make this work, but not having to re-type in all data?

Client Table:

Client ID(Autonumber)
Client Name (Text)
Client Address (Text)


Order Table:

Order ID(Autonumber)
Client ID(Short Text)
Unit Order(Number)
Unit Price
 
I set a one-to-one relationship between Client ID in two table.
Surely you want a one-to-many as one clientswould be likely to have many orders.

But got error message :"Type mismatch in expression".
Yes, joining a (auto)Number field to a Text field will do that. (auto)Number to Number is fine.

What you are not saying, however, is what sort of values you appear to have in your ClientID field on the orders table. If data is being deleted, then it doesn't sound as if the values are matching.
 
depends which way you want to go

if your unique id is numeric then I would create a temporary (long) numeric field in the order table and an update query to update it with the numeric value of the current contents of ClientID.

Then go back into table design, remove the existing clientID field (or rename it until you are happy everything is OK) and rename your temporary field to ClientID

If you require a text value, you'll need to do the equivalent in the client table, converting from autonumber/long to text - but you will then need to write code to update this field every time you add a new record.

A better solution is probably to create a new text field called say ClientNo in the client table and run an update query to populate it with the the values in the auto number field. Then for clarity, rename the clientid field in the order table as clientNo.

Whichever way you go, your current design won't work since if you are losing data when you convert the field you must have non numeric characters which will not link to an autonumber field
 
I'd write a query to filter out any records in that table with non-numeric data in them, and modify those records to get them acceptable for the number data type. Then delete the relationship for that field in the relationships window, and reopen the table in design view. You should then be able to modify the type to NUMBER instead of TEXT.
 

Users who are viewing this thread

Back
Top Bottom