Post reply

Return only non-numeric fields (dynamic select?)

  • March 8, 2005 at 5:58 am

    #92622

    I'm trying to figure out a way to only return non-zero fields in a specific record. I already have the record criteria (an easy where clause). I'm trying to do the following (only want A, B, C, or D if they are <>0):

    (Select A FROM XYZ if A is <>0) AND (Select B FROM XYZ if B<>0) AND (Select C FROM XYZ if C<>0) and ....

    I can grab the values and do the comparison in codes, but the return values are actually going into a report displaying the first three non-zero fields. If there are more than three non-zero fields, an additional report is generated and those values are displayed. It is a tax application and I'm generating the actual reports in Access (making it much easier if I can just put the values in a recordsource).

    Thanks for any help!

  • March 8, 2005 at 3:04 pm

    #546017

    I don't completely understand your explaination and it has been some time since I played with Access.  But, I would suggest you look up using a CASE statement.  Maybe someone with more Access experience can give you a better answer.

    I wasn't born stupid - I had to study.

  • March 9, 2005 at 7:15 am

    #546146

    TSQL handles this if you're using a sql query.

    Select * from orders where isnumeric(Order) = 0

     

  • March 9, 2005 at 7:59 am

    #546155

    good addition jon!  that way a CASE statement can go through the numerous fields she/he desires and determine whether 3 instances match her/his logic. 

    I wasn't born stupid - I had to study.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply