Need quick help for IIF function. Returning results of a certain format

[Aero11]

Hello everyone. I have been trying to figure out queries for a little while now at work, and usually I can find my answer by myself. However, I have been unable to find my answer this time, and I'm hoping to get some help here.

What I am trying to do is to create an IIF function that returns results in a certain format. I'm sure this can be done, but I don't think I am using the right commands. Specifically, I want the query to return the result "Commercial" if the search result is in the format 12-34-56, and return "Military" if the number is in any other format.

Something like...

IIF((Wo_headc.ATA_MAN_NBR) LIKE "**-**-**",'Commercial','Military')

??

Please help. Thanks! This is driving me crazy!

 

[Aero11]

Change the ? to %, I think.

We're getting closer now! It is separating the results, but it isn't following the exact format that I requested.

For example, "15A1-2-38-3-1" still returns "Commercial". Other results like "39-27972" are returning "Military".

I think it is looking for any number that has 2 "-"s in it and returning "Commercial". It isn't looking at the location of the "-"s

 

[Aero11]

Is there any way to make the "Like" command stricter? It's working, but it is including results that I don't want it to.

 

[Aero11]

Hopefully somebody is still willing to help,

It appears that the LIKE '%%-%%-%%' is returning the same results as LIKE '%-%-%'. So it is not counting the number of "%"s that I use. "%" looks for any number of digits.

Is there to way to program this so that it counts a specific number of digits in the format?

Thanks again.

 

[cstimart]

and LIKE '##-##-##" didn't work?

 

[Aero11]

and LIKE '##-##-##" didn't work?

It returns the "fail" result for all the entries.

 

[cstimart]

It returns the "fail" result for all the entries.

On a hunch, try Like '00-00-00'

 

[Aero11]

The problem is that the wildcards are not being counted in the query. Whether I use one or 10, it doesn't matter.

Is there a way to return results where a "-" is the 3rd, and 5th character in every result? That will probably achieve the same result.

 

[Aero11]

On a hunch, try Like '00-00-00'

No luck, there.

 

[Aero11]

I heard something about a substring argument?

Could I implement this substring into my iif statement? If it returned results where the 3rd and 5th character is a "-", I should be getting the correct results.

 

[Aero11]

Anyone? I could really use some help with this one.