Deleting first character based on the character used.

[wally2321]

Everyone:

I will try to be very specific relative to my question, but if there are any additional questions, please feel free to post the question.

I am trying to create a make query using the Design view of the make query. I am using the following information for my query.

Source Table Name: FLORENCE SCRUBBER - MAKE

Make Query Name: MAKE - HIBE [FLORENCE]

Designation Table Name (Table being made): HIBE- MAKE [FLORENCE]

The field that needs to be used is: MATERIAL NUMBER

This is what I would like to accomplish. I would like to take the first character of the material number if it begins with a "F", "Z" or an "S", and remove the character. Only these characters should be removed, all other beginning characters should not be removed.

S10001 would look like 10001

T10001 would look like T10001 (because it is not a F, Z , or S)

Once again, this should be a Make query using the design view.

Can this be accomplished using a module? Does anyone have any idea on how I can do this. Any help would be greatly appreciated.

Steve

 

[tlbroadbent]

Include this in the Field cell of the Make Query.

Expr1: IIf(Left([MATERIAL NUMBER],1)="F" Or Left([MATERIAL NUMBER],1)="Z" Or Left([MATERIAL NUMBER],1)="S",Right([MATERIAL NUMBER],Len([MATERIAL NUMBER])-1),[MATERIAL NUMBER]) Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[JoeMiller]

If you go into a query and paste this you will get what you want:

Expr1: IIf(Left([Material Number],1)="F" Or Left([Material Number],1)="S" Or Left([Material Number],1)="Z",Right([Material Number],Len([Material Number])-1),[Material Number])

Then all you have to do is get it into your module by either calling the query, or running the SQL via the RunSQL command.

HTH Joe Miller
joe.miller@flotech.net

 

[wally2321]

Thank you for your fast reply!

Steve

 

[JoeMiller]

Terry:

Sure looks like I copied you eh? Great minds think alike methinks...

Joe Joe Miller
joe.miller@flotech.net