Data Manipulation

I

IanT

Registered User.

Local time

Today, 01:47

Joined

Nov 30, 2001

Messages

191

• Dec 2, 2004

• #1

I have data in a field which looks like this 2-2.27kg. Is it possible to split this down so I can then automate calculating 2x2.27.

Thanks in advance!

 

KenHigg

Registered User

Local time

Yesterday, 20:47

Joined

Jun 9, 2004

Messages

13,291

• Dec 2, 2004

• #2

How consistent is the format? For example, is there always one numeric then the dash or could the be two numerics the the dash, etc...

kh

 

I

IanT

Registered User.

Local time

Today, 01:47

Joined

Nov 30, 2001

Messages

191

• Dec 2, 2004

• #3

The data is not in a consistent format, it represents quantitys and weights of food items.

 

KenHigg

Registered User

Local time

Yesterday, 20:47

Joined

Jun 9, 2004

Messages

13,291

• Dec 4, 2004

• #4

Can you post a sampling of several hundred rows?

kh

 

T

trucktime

Registered User.

Local time

Yesterday, 18:47

Joined

Oct 24, 2004

Messages

556

• Dec 4, 2004

• #5

Splitting

You may want to check out this link, it show how to split a field in a query.

http://www.microsoft-accesssolutions.co.uk/splitting_fields.htm

 

R

raskew

AWF VIP

Local time

Yesterday, 19:47

Joined

Jun 2, 2001

Messages

2,731

• Dec 5, 2004

• #6

Take a look at the attachment:

HTH - Bob

 

Attachments

• tblStuff.doc

  60 KB · Views: 206

john471

Registered User.

Local time

Today, 12:47

Joined

Sep 10, 2004

Messages

392

• Dec 6, 2004

• #7

Nice work Bob !

I would offer the following observations, however:- (only because I'm totally Anal)

"intWeight" is not an integer (as suggested by "int" prefix), rather a floating point decimal.

ShowLeft = IIf(IsMissing(ShowLeft), True, ShowLeft)
is redundant. IsMissing only pertains to optional parameters of type Variant. In your procedure definition, you defined it as boolean, and set it =True if not supplied.

Of course that said; looks like you've solved IanT's problem for him.

 

I

IanT

Registered User.

Local time

Today, 01:47

Joined

Nov 30, 2001

Messages

191

• Dec 6, 2004

• #8

Thanks "raskew" this has been a great help!

 

R

raskew

AWF VIP

Local time

Yesterday, 19:47

Joined

Jun 2, 2001

Messages

2,731

• Dec 7, 2004

• #9

Ian - Glad it was of assistance.

John471 - How long since you last washed your hands? There are germs everywhere, you know. Look forward to your next solution.

Bob

 

john471

Registered User.

Local time

Today, 12:47

Joined

Sep 10, 2004

Messages

392

• Dec 15, 2004

• #10

Bob,

Just got back from an extended hand-wash

Hope you like my Next Solution