Return only non-numeric fields (dynamic select?)
-
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!
-
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.
-
TSQL handles this if you're using a sql query.
Select * from orders where isnumeric(Order) = 0
-
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