Query Question

[gjennings1]

I am stumped on trying to create a query. This may be a juvenile question but I am trying to create a query from data out of a table. In this query I am wanting to return the original value if the cell does not contain a "/". Otherwise if it does contain a "/" I want to remove it and everything behind it. Any help will be greatly appreciated.

[Robeen]

Does the "/" always appear in the same Position in the string in that field?

[gjennings1]

It's not always in the same position.

[Robeen]

I created a Text Field in a table and put this in one row:
asdl/as345
In the other rows, I didn't have any occurrences of "/".

Then I created a Query and put this in a new field in design view:

 
Search_String: IIf(InStr(1,[Test],"/")=0,[Test],Left([Test],(InStr(1,[Test],"/")-1)))

When I ran the query, it gave me "asdl" from the row with the ? in it - and all the other rows unchanged.

Hope this helps!

If you haven't used the IIf functoin, here's how it works (in this case):

IIf(InStr(1,[Test],"/")=0, [Test], Left([Test],(InStr(1,[Test],"/")-1)))

If there isn't a "/" then use the value in [Test] otherwise use All characters to the left of the "/"

[gjennings1]

I used the IIF function as you have said and it worked except I have some fields that are blank and I get #Error for those.

[Robeen]

Are your fields 'Blank' - or are they 'Null'?

What would you like to see when there is a blank or null?