Deleting partial data in fields

[rileyma]

Newbie question.

I have query that has two columns of data each coming from the same table. Each are defined as short text. Examples of the data in each column are: 4623A, 4847/9847, 3754, 4523/9523...all I wish to do is in one of the columns, remove any data starting with the "/" and everything to the right.

I have been on the web and found all kinds of helpful solutions. I've tried to follow the solutions by using the code in the "Builder" screen but get error messages about the code not being correct. It just can't be that difficult.

Going nuts in Phoenix,

Mark

[June7]

Problem is the / is not in every string. One shows 5 characters with no /. Another shows 4 characters and no /. Others show 4 characters followed by / and 4 more characters.

So each field will have some records with the / character and other records not?

Consistency is critical in string manipulation otherwise gets complicated.

Do you want to actually alter the data in the table or just the display in query?

[pbaldy]

Offhand:

Left(FieldName, IIf(InStr(1, FieldName,"/")>0,InStr(1, FieldName,"/")-1,Len(FieldName)))

[NTC]

if the column/field is always this character count and format: 4847/9847
then you just need: NewName: Left([OldName],4)

but if the character count and format is variable i.e. 123/1111 , 123456/2 then you need to follow's PB's advice - -what he is giving you is that you first need to find the / and count its position using the InStr & Len method - - then once you have that number then take all to the left of it. One can take the elements of his statement and put them in separate columns for easier trouble shooting.

[rileyma]

That is correct. Not all data has the / character. What I'm trying to do is to create a mapping table (e.g. one column will have 4847/9847 the other will have 4847). So that when I come across tables with multiple formats, I can map them to a common format. I have other tables with formats differing from the examples given so in the grand scheme, I'm trying to create a master mapping table to accomodate all formats. Does that Make sense? Does this change PB's solution?

And to be clear, PB's solution should be in a module, in the "builder" screen or in the criteria fields of the query itself?

[June7]

PB expression can be in the Update To row of an UPDATE query if you want to populate another field with the modified value.

Or it can be expression in query to construct a field whenever needed and not bother updating a field.

[rileyma]

As a followup, I have added the following to the criteria line using the actual field name: Left([Division], IIf(InStr(1, [Division],"/")>0,InStr(1, [Division],"/")-1,Len([Division]))) . I receive the following message each time i try to run the query (actually won't allow me to leave the field) "There was an errror compiling this function. The Visual Basic module contains a syntax error. Check the code and then recompile it."

[June7]

Don't want it in the criteria line. This expression is not intended to be a filter parameter.

Put it in the Field row and construct a field with the expression.

Or in an UPDATE query, put it in the Update To row.