Format question in query

[travismp]

I need a quick fix help. I need help with a format issue at the query level. I have data that looks like this in a column

Red - Policy
Blue - Paperwork
Red - Forms
Green - Prototype
Yellow - Files

ect.

I need to show everything AFTER the " - " so everything after the space hyphen space needs to be shown. So I need to see

Policy
Paperwork
Forms
Prototypes
Files

So I would guess something like, but I am sure this is off.

Clean: Right([TYPE],-InStr([TYPE]," - "))

 

[Brianwarnock]

So I would guess something like, but I am sure this is off.

Clean: Right([TYPE],-InStr([TYPE]," - "))

That sentence suggests that you haven't tried it, why not?

Then after correcting the syntax error try again, then you will have definite question to ask perhaps, but as a hint you will need the Len function, but you could switch to Mid rather than Right as it assumes a length to the end of the field if none quoted.

Its a useful learning curve as parsing is a very common need.

Brian

 

[travismp]

I did try that and it gave me an error. Is my syntax incorrect.

 

[Brianwarnock]

Yes
Clean: Right([TYPE],-InStr([TYPE]," - "))

no hyphen
Clean: Right([TYPE],InStr([TYPE]," - "))

however that gives the position of the first space, that is not the Length you require even if you accidentally missed out Len([TYPE]) before it.

Brian

 

[Brianwarnock]

For travismp if he is still puzzled, or any body who has searched the forum and found this thread, the correct solution is

Clean: Right([TYPE],Len([TYPE])-(InStr([TYPE]," - ")+2))

FWIW The Mid solution is

Mid(TYPE,InStr([TYPE]," - ")+3)


Brian