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!

 

[cstimart]

Not sure, but did you try changing the *'s to #'s?

 

[Aero11]

Not sure, but did you try changing the *'s to #'s?

Thanks for the suggestion.

I got a little closer. The Query is accepting the following

IIF(ATA_MAN_NBR) LIKE '**-**-**','Commercial','Military)

But it returned EVERY result as "Military". Even the ones that fit the format did not return the correct result.

I tried using #'s instead of *'s and it did the same thing.

 

[VoG]

Try

IIF(ATA_MAN_NBR) LIKE '??-??-??','Commercial','Military)

 

[Aero11]

Try

IIF(ATA_MAN_NBR) LIKE '??-??-??','Commercial','Military)

Still returning "Military" for every result.

I think the LIKE function is not looking at the format of the expression, but rather the numbers themselves. Maybe I should not be using the LIKE function? I don't know of any others, though.

 

[VoG]

Ah, you did't say what ATA_MAN_NBR was. If it is a cell reference try like this

=IIf(ATA_MAN_NBR.NumberFormat like ...

 

[Aero11]

Ah, you did't say what ATA_MAN_NBR was. If it is a cell reference try like this

=IIf(ATA_MAN_NBR.NumberFormat like ...

Sorry, I didn't explain this correctly.

The ATA_MAN_NBR column contains manual numbers. The Commercial manual numbers always have the format 12-34-56. They are always numbers. The Military manuals can be anything else, including 12A-3F-333, etc. So it's not strictly numbers.

I tried

IIF((ATA_MAN_NBR.NumberFormat) Like '??-??-??','Commercial','Military')

but it returned "Table or Alias not found"

 

[VoG]

I'm still not sure what ATA_MAN_NBR is.

Try like this

=IIF(Range("A1").NumberFormat Like '??-??-??','Commercial','Military')

 

[Aero11]

I'm still not sure what ATA_MAN_NBR is.

Try like this

=IIF(Range("A1").NumberFormat Like '??-??-??','Commercial','Military')

I should have mentioned that I am not using Access, I am using the Microsoft Query tool that comes with Excel.

ATA_MAN_NBR is just just a column within one of the tables I am working from.

 

[cstimart]

I should have mentioned that I am not using Access, I am using the Microsoft Query tool that comes with Excel.

ATA_MAN_NBR is just just a column within one of the tables I am working from.

Change the ? to %, I think.