Remove Characters (1 Viewer)

scgoodman

scgoodman
Local time
Today, 13:12
Joined
Jun 6, 2008
Messages
87
I have a query that I would only like to see certain data in a field.
FieldDLY: PAR, UNA
I only want to see PAR and not UNA.

Help
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:12
Joined
Aug 30, 2003
Messages
36,118
Based on the sample data:

NewField: Left(OriginalField, 3)
 

scgoodman

scgoodman
Local time
Today, 13:12
Joined
Jun 6, 2008
Messages
87
Some are two characters...i.e.

Delay Codes are i.e. (AD, PAR, etc)

some may have two delay codes.

i,e, AD, PAR but I only need to show the first.

Thoughts
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:12
Joined
Aug 30, 2003
Messages
36,118
I figured it would get more complicated. You can use the Left() function in conjunction with the InStr() to find the position of the comma (which tells you how many characters you want). More info on both in Help.
 

boblarson

Smeghead
Local time
Today, 10:12
Joined
Jan 12, 2001
Messages
32,059
It sounds also that you are storing data with multiple values in the same field (definitely NOT recommended) which is not good for a relational database. Why are there multiples in one field?
 

scgoodman

scgoodman
Local time
Today, 13:12
Joined
Jun 6, 2008
Messages
87
This is from a .csv file. I would like to strip the characters. something like
But I keep getting an error.

I did it to a field like this.
MAWB: 16061279455(HKHKG-USATL)
Left([MAWB],Instr(1,[MAWB],"(")-1)

How can I do it for this one...
AD, PAR?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:12
Joined
Aug 30, 2003
Messages
36,118
What have you tried?
 

Mike_Burns67

Registered User.
Local time
Today, 17:12
Joined
Oct 8, 2008
Messages
11
Try this;

Left([yourtext],InStr([yourtext],",")-1)

to get the first half, and

Mid([text],InStr([text],",")+2,10)

to get the back half

the "+2" assumes there is a space after the comma and the "10" is the max length of the field.
 

scgoodman

scgoodman
Local time
Today, 13:12
Joined
Jun 6, 2008
Messages
87
This errors out... #Error.

Data Example:

Could be: AD,DLY,
or
Could be: ADD,DL

I need the characters before the first comma.

Help please...
 

boblarson

Smeghead
Local time
Today, 10:12
Joined
Jan 12, 2001
Messages
32,059
If you want the characters before the first comma, use:

Left([YourfieldNameHere], Instr(1, [YourFieldNameHere], ",")-1)
 

boblarson

Smeghead
Local time
Today, 10:12
Joined
Jan 12, 2001
Messages
32,059
Do you have any records that are Null for that field?
 

boblarson

Smeghead
Local time
Today, 10:12
Joined
Jan 12, 2001
Messages
32,059
Then change it to:

IIf(IsNull([YourFieldName]),"",Left([YourfieldNameHere], Instr(1, [YourFieldNameHere], ",")-1))

Or, if there could be no comma, then

IIf(IsNull([YourFieldName]),"",IIf(Instr(1, [YourFieldNameHere], ",")>0,Left([YourfieldNameHere], Instr(1, [YourFieldNameHere], ",")-1),""))
 

scgoodman

scgoodman
Local time
Today, 13:12
Joined
Jun 6, 2008
Messages
87
I am still getting this #Error. The field of sample data is:

DLY (Header Field) Data below.
DM
DM, ECL, GAD, GSH, HAO, HED
HAO, HED

I need to pull only the first one, to pull the new:
DLY (Header Field) Data below.
DM
DM
HAO
 

MSAccessRookie

AWF VIP
Local time
Today, 13:12
Joined
May 2, 2008
Messages
3,428
I am still getting this #Error. The field of sample data is:

DLY (Header Field) Data below.
DM
DM, ECL, GAD, GSH, HAO, HED
HAO, HED

I need to pull only the first one, to pull the new:
DLY (Header Field) Data below.
DM
DM
HAO


I think the first line of your data explains at least part of your problem. The solutions proposed all assumed that there would be two or more items on each line, separated by ",". When the IIf Statement parses the data it cannot find a ",". As a result, the subsequent Instr() and Mid() statements are doomed to failure. The case where no "," is found must also be taken into consideration.
 

boblarson

Smeghead
Local time
Today, 10:12
Joined
Jan 12, 2001
Messages
32,059
I think the first line of your data explains at least part of your problem. The solutions proposed all assumed that there would be two or more items on each line, separated by ",". When the IIf Statement parses the data it cannot find a ",". As a result, the subsequent Instr() and Mid() statements are doomed to failure. The case where no "," is found must also be taken into consideration.

That's what the extended IIF statement I responded with is supposed to do, but it might not be formulated completely. I didn't test it.
 

MSAccessRookie

AWF VIP
Local time
Today, 13:12
Joined
May 2, 2008
Messages
3,428
That's what the extended IIF statement I responded with is supposed to do, but it might not be formulated completely. I didn't test it.

Sorry, I never even saw your reply or I would have made reference. The extended IIf looks like it was exactly what I was referring to.
 

boblarson

Smeghead
Local time
Today, 10:12
Joined
Jan 12, 2001
Messages
32,059
I am still getting this #Error. The field of sample data is:

DLY (Header Field) Data below.
DM
DM, ECL, GAD, GSH, HAO, HED
HAO, HED

I need to pull only the first one, to pull the new:
DLY (Header Field) Data below.
DM
DM
HAO

It's not clear about your data. Are you saying that you only want the latest ROW but also only the part before any existing comma? If so, how do you define latest and does your data store a date/time stamp to determine the latest?
 

Users who are viewing this thread

Top Bottom