Cleaning expressions

[Petra]

Hello.

I am a newbie in cleaning junk out of a MFG_Number field. This field
contains 1, 2, 3, and 4 plus spaces prior to the valid number, which I would
like to clean. This field also contains leading and trailing zeros, which I
would also like to clean. This field also contains dashes with numbers that
I would like to separate and place in a new field. Are there expressions
that will accomplish these tasks?

Your help is greatly appreciated.
Thanks, Petra

 

[Ofer Cohen]

Hi Petra

To remove space, you can use
Trim([FieldName])

To remove leading zero's from a number field, you can use
Int([FieldName])
Remember that it will work on numeric value only

To split the field according to dashe's,
How many dashe's are there?
Can you give an example?

 

[Petra]

Thanks Ofer....the MFG_Numbers sometimes are indicated with a unit of
measurement. An example would be 054837-06, the six meaning 6 yards. I'd
like to separate the measurement number from the manufacturing number. Will
Trim([FieldName]) know how many spaces to remove or will it remove all?
Thank you for your help. It is much appreciated.

Hi Petra

To remove space, you can use
Trim([FieldName])

To remove leading zero's from a number field, you can use
Int([FieldName])
Remember that it will work on numeric value only

To split the field according to dashe's,
How many dashe's are there?
Can you give an example?

--
Good Luck
BS"D

Hello.

I am a newbie in cleaning junk out of a MFG_Number field. This field
contains 1, 2, 3, and 4 plus spaces prior to the valid number, which I would
like to clean. This field also contains leading and trailing zeros, which I
would also like to clean. This field also contains dashes with numbers that
I would like to separate and place in a new field. Are there expressions
that will accomplish these tasks?

Your help is greatly appreciated.
Thanks, Petra

 

[Douglas J. Steele]

Trim removes spaces (" "), not characters.

If you're saying you need to remove everything in front of the dash, use:

CInt(Mid([FieldName], InStr([FileName], "-") + 1))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Ofer....the MFG_Numbers sometimes are indicated with a unit of
measurement. An example would be 054837-06, the six meaning 6 yards. I'd
like to separate the measurement number from the manufacturing number.
Will
Trim([FieldName]) know how many spaces to remove or will it remove all?
Thank you for your help. It is much appreciated.

Hi Petra

To remove space, you can use
Trim([FieldName])

To remove leading zero's from a number field, you can use
Int([FieldName])
Remember that it will work on numeric value only

To split the field according to dashe's,
How many dashe's are there?
Can you give an example?

--
Good Luck
BS"D

Hello.

I am a newbie in cleaning junk out of a MFG_Number field. This field
contains 1, 2, 3, and 4 plus spaces prior to the valid number, which I
would
like to clean. This field also contains leading and trailing zeros,
which I
would also like to clean. This field also contains dashes with numbers
that
I would like to separate and place in a new field. Are there
expressions
that will accomplish these tasks?

Your help is greatly appreciated.
Thanks, Petra

 

[Petra]

Thank you for your help Doug. I greatly appreciate the expression to remove
everything in front of the dash. This is a tremendous help. What is the
difference between CInt and Int? In removing spaces, will I need to indicate
the number of spaces or does the Trim([FieldName]) automatically remove all
spaces?

Trim removes spaces (" "), not characters.

If you're saying you need to remove everything in front of the dash, use:

CInt(Mid([FieldName], InStr([FileName], "-") + 1))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Ofer....the MFG_Numbers sometimes are indicated with a unit of
measurement. An example would be 054837-06, the six meaning 6 yards. I'd
like to separate the measurement number from the manufacturing number.
Will
Trim([FieldName]) know how many spaces to remove or will it remove all?
Thank you for your help. It is much appreciated.

Hi Petra

To remove space, you can use
Trim([FieldName])

To remove leading zero's from a number field, you can use
Int([FieldName])
Remember that it will work on numeric value only

To split the field according to dashe's,
How many dashe's are there?
Can you give an example?

--
Good Luck
BS"D


:

Hello.

I am a newbie in cleaning junk out of a MFG_Number field. This field
contains 1, 2, 3, and 4 plus spaces prior to the valid number, which I
would
like to clean. This field also contains leading and trailing zeros,
which I
would also like to clean. This field also contains dashes with numbers
that
I would like to separate and place in a new field. Are there
expressions
that will accomplish these tasks?

Your help is greatly appreciated.
Thanks, Petra

 

[Douglas J. Steele]

CInt converts an expression (either string or numeric) to an Integer (if
it's possible: it won't, for instance, work with "abc", but it will with
"06")

Int returns the integer portion of a number: in other words, Int(6.5) will
return 6.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you for your help Doug. I greatly appreciate the expression to
remove
everything in front of the dash. This is a tremendous help. What is the
difference between CInt and Int? In removing spaces, will I need to
indicate
the number of spaces or does the Trim([FieldName]) automatically remove
all
spaces?

Trim removes spaces (" "), not characters.

If you're saying you need to remove everything in front of the dash, use:

CInt(Mid([FieldName], InStr([FileName], "-") + 1))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Ofer....the MFG_Numbers sometimes are indicated with a unit of
measurement. An example would be 054837-06, the six meaning 6 yards.
I'd
like to separate the measurement number from the manufacturing number.
Will
Trim([FieldName]) know how many spaces to remove or will it remove all?
Thank you for your help. It is much appreciated.

:

Hi Petra

To remove space, you can use
Trim([FieldName])

To remove leading zero's from a number field, you can use
Int([FieldName])
Remember that it will work on numeric value only

To split the field according to dashe's,
How many dashe's are there?
Can you give an example?

--
Good Luck
BS"D


:

Hello.

I am a newbie in cleaning junk out of a MFG_Number field. This
field
contains 1, 2, 3, and 4 plus spaces prior to the valid number, which
I
would
like to clean. This field also contains leading and trailing zeros,
which I
would also like to clean. This field also contains dashes with
numbers
that
I would like to separate and place in a new field. Are there
expressions
that will accomplish these tasks?

Your help is greatly appreciated.
Thanks, Petra

 

[John Spencer]

And to answer the second question.

TRIM removes all leading and trailing spaces. You can't specify how many spaces
it should remove. Also, it does not remove spaces that are in the middle of the string.

CInt converts an expression (either string or numeric) to an Integer (if
it's possible: it won't, for instance, work with "abc", but it will with
"06")

Int returns the integer portion of a number: in other words, Int(6.5) will
return 6.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you for your help Doug. I greatly appreciate the expression to
remove
everything in front of the dash. This is a tremendous help. What is the
difference between CInt and Int? In removing spaces, will I need to
indicate
the number of spaces or does the Trim([FieldName]) automatically remove
all
spaces?

Trim removes spaces (" "), not characters.

If you're saying you need to remove everything in front of the dash, use:

CInt(Mid([FieldName], InStr([FileName], "-") + 1))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks Ofer....the MFG_Numbers sometimes are indicated with a unit of
measurement. An example would be 054837-06, the six meaning 6 yards.
I'd
like to separate the measurement number from the manufacturing number.
Will
Trim([FieldName]) know how many spaces to remove or will it remove all?
Thank you for your help. It is much appreciated.

:

Hi Petra

To remove space, you can use
Trim([FieldName])

To remove leading zero's from a number field, you can use
Int([FieldName])
Remember that it will work on numeric value only

To split the field according to dashe's,
How many dashe's are there?
Can you give an example?

--
Good Luck
BS"D


:

Hello.

I am a newbie in cleaning junk out of a MFG_Number field. This
field
contains 1, 2, 3, and 4 plus spaces prior to the valid number, which
I
would
like to clean. This field also contains leading and trailing zeros,
which I
would also like to clean. This field also contains dashes with
numbers
that
I would like to separate and place in a new field. Are there
expressions
that will accomplish these tasks?

Your help is greatly appreciated.
Thanks, Petra

 

[Douglas J. Steele]

D'oh. Missed that second question. Thanks, John.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

And to answer the second question.

TRIM removes all leading and trailing spaces. You can't specify how many
spaces
it should remove. Also, it does not remove spaces that are in the middle
of the string.

CInt converts an expression (either string or numeric) to an Integer (if
it's possible: it won't, for instance, work with "abc", but it will with
"06")

Int returns the integer portion of a number: in other words, Int(6.5)
will
return 6.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you for your help Doug. I greatly appreciate the expression to
remove
everything in front of the dash. This is a tremendous help. What is
the
difference between CInt and Int? In removing spaces, will I need to
indicate
the number of spaces or does the Trim([FieldName]) automatically remove
all
spaces?

:

Trim removes spaces (" "), not characters.

If you're saying you need to remove everything in front of the dash,
use:

CInt(Mid([FieldName], InStr([FileName], "-") + 1))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks Ofer....the MFG_Numbers sometimes are indicated with a unit
of
measurement. An example would be 054837-06, the six meaning 6
yards.
I'd
like to separate the measurement number from the manufacturing
number.
Will
Trim([FieldName]) know how many spaces to remove or will it remove
all?
Thank you for your help. It is much appreciated.

:

Hi Petra

To remove space, you can use
Trim([FieldName])

To remove leading zero's from a number field, you can use
Int([FieldName])
Remember that it will work on numeric value only

To split the field according to dashe's,
How many dashe's are there?
Can you give an example?

--
Good Luck
BS"D


:

Hello.

I am a newbie in cleaning junk out of a MFG_Number field. This
field
contains 1, 2, 3, and 4 plus spaces prior to the valid number,
which
I
would
like to clean. This field also contains leading and trailing
zeros,
which I
would also like to clean. This field also contains dashes with
numbers
that
I would like to separate and place in a new field. Are there
expressions
that will accomplish these tasks?

Your help is greatly appreciated.
Thanks, Petra

 

[Petra]

Dear Doug , I greatly appreciate this info. Thank you!

CInt converts an expression (either string or numeric) to an Integer (if
it's possible: it won't, for instance, work with "abc", but it will with
"06")

Int returns the integer portion of a number: in other words, Int(6.5) will
return 6.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you for your help Doug. I greatly appreciate the expression to
remove
everything in front of the dash. This is a tremendous help. What is the
difference between CInt and Int? In removing spaces, will I need to
indicate
the number of spaces or does the Trim([FieldName]) automatically remove
all
spaces?

Trim removes spaces (" "), not characters.

If you're saying you need to remove everything in front of the dash, use:

CInt(Mid([FieldName], InStr([FileName], "-") + 1))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks Ofer....the MFG_Numbers sometimes are indicated with a unit of
measurement. An example would be 054837-06, the six meaning 6 yards.
I'd
like to separate the measurement number from the manufacturing number.
Will
Trim([FieldName]) know how many spaces to remove or will it remove all?
Thank you for your help. It is much appreciated.

:

Hi Petra

To remove space, you can use
Trim([FieldName])

To remove leading zero's from a number field, you can use
Int([FieldName])
Remember that it will work on numeric value only

To split the field according to dashe's,
How many dashe's are there?
Can you give an example?

--
Good Luck
BS"D


:

Hello.

I am a newbie in cleaning junk out of a MFG_Number field. This
field
contains 1, 2, 3, and 4 plus spaces prior to the valid number, which
I
would
like to clean. This field also contains leading and trailing zeros,
which I
would also like to clean. This field also contains dashes with
numbers
that
I would like to separate and place in a new field. Are there
expressions
that will accomplish these tasks?

Your help is greatly appreciated.
Thanks, Petra

 

[Petra]

Thank you very much John. Truly appreciated.

And to answer the second question.

TRIM removes all leading and trailing spaces. You can't specify how many spaces
it should remove. Also, it does not remove spaces that are in the middle of the string.

CInt converts an expression (either string or numeric) to an Integer (if
it's possible: it won't, for instance, work with "abc", but it will with
"06")

Int returns the integer portion of a number: in other words, Int(6.5) will
return 6.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you for your help Doug. I greatly appreciate the expression to
remove
everything in front of the dash. This is a tremendous help. What is the
difference between CInt and Int? In removing spaces, will I need to
indicate
the number of spaces or does the Trim([FieldName]) automatically remove
all
spaces?

:

Trim removes spaces (" "), not characters.

If you're saying you need to remove everything in front of the dash, use:

CInt(Mid([FieldName], InStr([FileName], "-") + 1))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks Ofer....the MFG_Numbers sometimes are indicated with a unit of
measurement. An example would be 054837-06, the six meaning 6 yards.
I'd
like to separate the measurement number from the manufacturing number.
Will
Trim([FieldName]) know how many spaces to remove or will it remove all?
Thank you for your help. It is much appreciated.

:

Hi Petra

To remove space, you can use
Trim([FieldName])

To remove leading zero's from a number field, you can use
Int([FieldName])
Remember that it will work on numeric value only

To split the field according to dashe's,
How many dashe's are there?
Can you give an example?

--
Good Luck
BS"D


:

Hello.

I am a newbie in cleaning junk out of a MFG_Number field. This
field
contains 1, 2, 3, and 4 plus spaces prior to the valid number, which
I
would
like to clean. This field also contains leading and trailing zeros,
which I
would also like to clean. This field also contains dashes with
numbers
that
I would like to separate and place in a new field. Are there
expressions
that will accomplish these tasks?

Your help is greatly appreciated.
Thanks, Petra