Using Switch function within an Access Query 1

[TCARPENTER]

I have a query (both for a form and a report) which display racks and their X, Y, Z dimensions. The problem is, not every dimension is entered. So in my expresion field, I'm hoping to use the switch function to correctly display the rack dimensions i.e., Instead of Rack (24x48x) I want it to read Rack (24x48).

First, for some reason I'm confused on my combinations, if I have 3 dimensions, shouldn't there be 9 combinations - I only have 8, I'm hoping another pair of eyes will catch my mistake:

  Key: X = Not Null (X) = Null
    X   Y   Z
    X   Y  (Z)
    X  (Y) (Z)
   (X)  Y   Z
   (X) (Y)  Z
   (X) (Y) (Z)
   (X)  Y  (Z)
    X  (Y)  Z

Second, is there maybe a better way to handle this than a lengthy switch statement - I'm O.K. with SQL but definitely no guru.

Any help is greatly appreciated.

Thanks
Todd

 

[dhookom]

I think this works but it might not be the best
Dimensions: IIf(IsNull([X]),[y],[X] & "x"+[Y]) & IIf(IsNull([y]) And IsNull([X]),"","x")+[z]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[TCARPENTER]

Duane,

The function you threw together was almost perfect, it missed X, (Y), Z. The ironic thing is this is BETTER than what I was hoping for as this immediately flags out data entry errors.

So your solution was actually was more than perfect - stars to you Mr. Man!

Thanks
Todd