cant change the data type frustration (1 Viewer)

merlin777 · Sep 7, 2017

I was building a simple query and got a type mismatch error. I tracked it back to my main table where a couple of fields which had key numbers in them (foreign keys?) had the data type of text - I guess that's the problem anyway because whatever I try access wont let me change it to a number datatype.

its saying it's part of one or more relationships so I can't change it. I deleted ALL relationships and even the ones displayed in my query. I've saved everything and closed everything but still repeats the error.

Surely, if you get a data type wrong early on you don't have to delete half your database to change the data type?

RuralGuy · Sep 7, 2017

Have you done a Compact and Repair yet?

merlin777 · Sep 8, 2017

RuralGuy said:
Have you done a Compact and Repair yet?

I hadn't but I have now and I'm still getting the error.

merlin777 · Sep 8, 2017

merlin777 said:
I hadn't but I have now and I'm still getting the error.

OK. I managed to lose the error.

I needed to 'show all relationships' and then not delete the tables in the window but actually delete the relationship lines between them or they don't actually get deleted. Not very obvious!

Now I've been able to change the datatype to numbers.... but I've still got the type mismatch error! :banghead:

AccessBlaster · Sep 8, 2017

I would first do a backup,

Then right click on the table in question. Copy then paste back with structure only. This will give you an empty table, make your changes to the new structure. When finished save and append old data into new table. Rename old table, rename new table. Reset relationships.

If you are dependent on primary keys this might not work for you. Or if this is a SQL table this will probably not work for you.

merlin777 · Sep 8, 2017

AccessBlaster said:
I would first do a backup, Then right click on the table in question. Copy then paste back with structure only. This will give you an empty table, make your changes to the new structure. When finished save and append old data into new table. Rename old table, rename new table. Reset relationships.

If you are dependent on primary keys this might not work for you. Or if this is a SQL table this will probably not work for you.

Backup - check

I've copied the table in the navigation pane but I can't see how to 'paste with structure only'. I can't see this option on the right click menu or a 'paste as' option etc - and when I do find it, where do I paste it to?
Appending the data is just a cut and paste job?

merlin777 · Sep 8, 2017

can I just do a sanity check, too?
My main table has an ID field (autonumber), therapist field referring to the therapisttable (number field), patient field referring to the patienttable (number field), therapy field referring to the therapytable (number field).

The patienttable, tehrapisttable and therapy table all contain an id field (number) and a field for the names or therapies which are text fields.

that's right, surely?

AccessBlaster · Sep 8, 2017

Not sure why the menu isn't visible, see attached. Try copying then quickly paste back in the same place.

AccessBlaster · Sep 8, 2017

merlin777 said:
can I just do a sanity check, too?
My main table has an ID field (autonumber), therapist field referring to the therapisttable (number field), patient field referring to the patienttable (number field), therapy field referring to the therapytable (number field).

The patienttable, tehrapisttable and therapy table all contain an id field (number) and a field for the names or therapies which are text fields.

that's right, surely?

You are correct to be careful because those keys are dependent. That's why you have to backup an experiment on backed up databases.

merlin777 · Sep 8, 2017

AccessBlaster said:
Not sure why the menu isn't visible, see attached. Try copying then quickly paste back in the same place.

I'm using 2007 - does that make a difference? Doesn't look at all like your picky.

CJ_London · Sep 8, 2017

just a thought - they need to be the same types of numbers - you won't be able to match on double and a long for example

merlin777 · Sep 8, 2017

CJ_London said:
just a thought - they need to be the same types of numbers - you won't be able to match on double and a long for example

Thanks, CJ. All the number fields in my main table are 'long integer'.

What is it they need to match with? The type mismatch error is in a query and I get a type mismatch in expression error even if I just put the ID field in 'field' in the first query column and the main table in the table row underneath it.

merlin777 · Sep 8, 2017

AccessBlaster said:
Not sure why the menu isn't visible, see attached. Try copying then quickly paste back in the same place.

Aha! Schoolboy error. For anyone else who's thick like me, when you right click to find the paste structure option, it doesn't appear until you click on 'paste'. Slightly counter-intuitive. Don't like clicking on 'paste' because you assume that's what it do, rather than offering more options.

merlin777 · Sep 8, 2017

Fixed! Thanks for all your help guys. I've learned a lot.

I tracked it down by selective deletion. Turned out to be i'd been clumsy when dragging and dropping my relationships and one of the tables was related by a field other than its ID field.

Phew.

cant change the data type frustration (1 Viewer)

merlin777

Registered User.

RuralGuy

AWF VIP

merlin777

Registered User.

merlin777

Registered User.

AccessBlaster

Registered User.

merlin777

Registered User.

merlin777

Registered User.

AccessBlaster

Registered User.

Attachments

AccessBlaster

Registered User.

merlin777

Registered User.

CJ_London

Super Moderator

merlin777

Registered User.

merlin777

Registered User.

merlin777

Registered User.

Similar threads

Users who are viewing this thread