For my domicile collection I have used the Perforation field to store the dimensions for the objects.
Dimensions are stored as Height by Width (don't ask why) and in millimeter measurements, i.e. 89x103 or 130x180.

Are there any way to get the dimensions over from "Perforation_horiz" field to the new "Height" and "Width" fields by SQL statements?
And if so how to do it?
If not, are there any other solutions except manually? Can I export to Excel forth and back?

I've got more than 2800 object registered in the database and hope to find an easy way to solve this task.

1. Backup your database!

2. Download and run http://www.fnprg.com/dl/sqlupdate.exe

3. Click ... and select your database file.

4. Run this SQL update query:



5. To clear the Perforation Horiz field, run this SQL update query:



6. And finally, to empty the Performation table:

Hello Frederik,

I have almost the same problem with the perforation-field.
I entered the following values:
'Kamtanding 13½:14½'
'Lijntanding 13½:15½'
and
'Boven, links en onder ongetand'

Now I have to spilt this values in 13½,14½ and 13½,15½
In the third case, without ':', I want to store the values in the field Custom 08.

How do have to change the query?

With kind regards,
Ronald de Waal

Hi Fredrik!
Thank you for your time!
Unfortunately this SQL code resulted in a error.



Below I have attached a partly screen shot from my Perforation table and the error message itself, I hope this is enough to troubleshoot the error in executing the SQL code.



And the error message:



Problem mixing Text and Number??

Hm!

Leaving out the second part of the sql statement ", Width = MID(Perforation.Perforation, INSTR(Perforation.Perforation, 'x') + 1)" resulting in a successful copying to the "Heigth" field.

Running the sql code with above statement alone, resulting in error "ikke samsvar mellom datatyper i vilkårsuttrykk (not consistent between data types in terms of expression)"
Any ideas why?

Most likely. Do any of the entries in the Perforation table contain anything else than a number after the "x" (comma, letters, etc.)?

It isn't really possible to use a sql update query here since you want to transfer the data to a lookup field.

Hello Frederik,

I shell try to transfer the data with a VB-routine. Thanks.

With kind regards,
Ronald de Waal

Yes, you are right again!
I have overlooked that record 241 contained two x's.
Once I removed one of the x's, then the sql code ran well.

Case solved!
Thank you!

Please report back if you succeed, maybe I to will have a try!