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!

 

[Joe4]

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

How do you figure? I explained exactly how the code works in my previous post, and it is not backwards.
To prove it, here is a grid I copied right out of Access with the original values and what each formula returns:

123GHTCCC:XX:Y	123GHTCCC	123GHTCCC:XX:Y
ABC123FFFUI:ZZ:QQ:1	ABC123FFFUI	ABC123FFFUI:ZZ:QQ:1
edfrtg	edfrtg	#Error

<tbody>
[TH="bgcolor: #c0c0c0"] Task Code [/TH]
[TH="bgcolor: #c0c0c0"] JoesFormula [/TH]
[TH="bgcolor: #c0c0c0"] JamesEdit [/TH]

</tbody>

 

[james_lankford]

wow

I must be blind
I just stared at your formula for 5 minutes

what you typed is not what I saw LOL

I saw the true and false part reversed

I guess all those ( [ ] , ) threw me off

DOH !
https://www.youtube.com/watch?v=OjYoNL4g5Vg

 

[Joe4]

wow

I must be blind
I just stared at your formula for 5 minutes

No worries! As the kids say these days "BTDTBTTS!"

I am curious as to whether or not he was able to get this problem sorted out though. Doesn't seem to make much sense why it wouldn't work. I am curious to find out what the resolution is.

 

[Dundee Lad]

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]

Joe4 apologies, had an issue with something else!, back on this one for now and I have tried everything but still do not get the correct results so I decided rather than waste much more time I would do this in excel before it goes into the database. Thanks for your help though I do appreciate it

 

[Joe4]

Joe4 apologies, had an issue with something else!, back on this one for now and I have tried everything but still do not get the correct results so I decided rather than waste much more time I would do this in excel before it goes into the database. Thanks for your help though I do appreciate it

Thanks for coming back. I figured if it wasn't working, that there probably was something else going on, as it was a pretty straightforward question (and I cannot think of any possible way it would not work, if taken at face value).