Select the text before the first column

[Dundee Lad]

Hi Board, I need some help if possible.

I have the following in my query

Left([Task Code],InStr([Task Code],":")-1)

but I am getting a data type mismatch when running

Here are some samples below and the result I am hoping to get

123GHTCCC:XX:Y ------- 123GHTCCC
ABC123FFFUI:ZZ:QQ:1 -------- ABC123FFFUI

Any help you can give would be greatly appreciated!

 

[Dundee Lad]

I should have also added, apologies, but there are occurrences where there is no colon, in those cases I need to just show what is there.

example -

edfrtg ------- would still be displayed as edfrtg

 

[Joe4]

This should handle that:

IIF(InStr([Task Code],":")>0,Left([Task Code],InStr([Task Code],":")-1),[Task Code])

 

[Dundee Lad]

Thanks Joe4 I have tried your suggestion, it is only showing the values that have no colon, it does not seem to be recognising the colon, any ideas?

 

[Joe4]

Did you copy and paste my code exactly "as-is", or did you manually try to type it or make other edits to it?
I suspect a typo.
I tested it on your three examples, and I confirmed that in every case it returned exactly what you wanted.

 

[Dundee Lad]

just used the copy and paste Joe4

 

[Joe4]

You are putting this in a calculated field in a query, right?

Try creating a new calculated field that locates the comma like this and see what it returns.

Test: InStr([Task Code],":")

Does this return any values other than zero?

Also, are you are on version of Access that perhaps uses semi-colons instead of commas to separate function arguments, i.e.

Test: InStr([Task Code];":")

I have seen some version used outside of the States that use semi-colons instead of commas.

 

[Dundee Lad]

yes, I get 9 on the rows with the colon in it (used your first example)

 

[Joe4]

And you don't see anything other than numbers and zeroes, right (no errors or anything like that)?

An IIF statement is made of of three clauses:
- a condition to check
- what to return if the condition is true
- what to return if the condition if false

So, here is what we have with the formula:

condition
InStr([Task Code],":")>0
This checks to see if the place holder where it finds the colon is greater than zero. If it finds a comma, it will return a non-zero number, so this condition will be TRUE. If it does not find a colon, it should return a zero, which makes this condition FALSE.

what to return if TRUE
Left([Task Code],InStr([Task Code],":")-1)
This will return all the characters to the left of the colon.

what to return if FALSE
[Task Code]
This just return the whole string.

So I do not understand how it could return nothing at all (unless there is nothing at all in Task Code or the comma is in the first space.

Can you post an actual example that is returning nothing, and tell me what the formula I gave you in the previous post returns for that particular value?

[COLOR=#574123]Test: InStr([Task Code],":")[/COLOR]

 

[james_lankford]

I should have also added, apologies, but there are occurrences where there is no colon, in those cases I need to just show what is there.

example -

edfrtg ------- would still be displayed as edfrtg

I believe Joe's code is backwards, but its easy to fix by just changing >0 to =0
IIF(InStr([Task Code],":")=0,Left([Task Code],InStr([Task Code],":")-1),[Task Code])