Can I Extraxt Data from a Field without using VBA

[Joanchka]

I have a field called Size. The data in it looks like:


8.9 x 12.7 cm
14.1 x 20.4 cm
22.7 x 30.5 cm


I have two new fields called Width and Height. They will hold numerical data only.



Is there a way using an update querie to extract just the numerical data for each dimension to populate the new fields?


I've never used VBA (I know, I know) so I'm finding this a bit of a challenge.

 

[plog]

Yes, as long as your example data always conforms to the same pattern:

Number x Number cm

What you would do is use the Mid function (https://www.techonthenet.com/access/functions/string/mid.php) to extract a substring from your field. Inside of that Mid you will use an InStr(https://www.techonthenet.com/access/functions/string/instr.php) to determine where Mid should start/stop in retrieving the sub string.

My advice is to create a new query bring in your Size field and then add 2 new calculated fields to it like this:

Width: Mid([Size], 1, 7)
WidthTest: Instr([Size], " x ")

Read up on both those functions I posted and then get the above Width field to work correctly.

 

[Galaxiom]

You need to use functions.

Expression you could use:

Width:Val([Size])
Height:Val(Mid([Size],InStr([Size],"x")+1))

Watch out for Width and Height though because they are properties of objects and may cause problems when you refer to them on a form.

 

[Joanchka]

Thanks plog,


Although it follows the same pattern, the number of characters can vary. In the 1st record the first dimension is 8.9, and in the 2nd record the first dimension is 14.1. Doesn't Mid work with a fixed length?

 

[plog]

Doesn't Mid work with a fixed length?

Yes, thus the InStr.

 

[Joanchka]

You need to use functions.

Expression you could use:

Width:Val([Size])
Height:Val(Mid([Size],InStr([Size],"x")+1))

Watch out for Width and Height though because they are properties of objects and may cause problems when you refer to them on a form.

Thank you very much for this - I used this this morning and it worked perfectly.

 

[WayneRyan]

Joanchka,

You should really break your column into length, width and units.

Failing that you can use the Replace function to get rid of the unit part.

Wayne