Remove Characters

[scgoodman]

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]

Based on the sample data:

NewField: Left(OriginalField, 3)

 

[scgoodman]

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]

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]

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]

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]

What have you tried?

 

[Mike_Burns67]

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]

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]

If you want the characters before the first comma, use:

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

 

[scgoodman]

Still getting #error...know why?

 

[boblarson]

Do you have any records that are Null for that field?

 

[scgoodman]

Possible yes!

 

[boblarson]

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]

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]

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]

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]

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]

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.

No worries

 

[boblarson]

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?