cant change the data type frustration

[merlin777]

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]

Have you done a Compact and Repair yet?

 

[merlin777]

Have you done a Compact and Repair yet?

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

 

[merlin777]

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]

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]

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]

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]

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

 

[AccessBlaster]

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]

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]

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]

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]

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]

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.