Access select query

[josh456]

I am having trouble with the following expression in a select query not working.

Expr5: IIf([Forms]![frmreports]![cbBusNumber]="All","All" ,[busnumberA] Or [busnumberB])

If I change the query to the following I am able to retrieve data from my query

Expr5: IIf([Forms]![frmReports]![cbBusnumber]="All","All" ,[busnumberA])

But only if the bus number is in "busnumberA " field.


Expr5: IIf([Forms]![frmReports]![cbBusnumber]="All","All" ,[busnumberB])

But only if the bus number is in "busnumberB " field.


I do need to enter a bus number from either "busnumberA " or "BusnumberB " fields


I do require the second field from my table "busnumberB " to also be part of the expression as I am querying a particular bus number where the passengers may be on either "busnumberA " and/or "busnumberB "

Thanks Josh

[Stewart Ross]

Hi, and welcome to Bytes!

You can't use OR to select between two different fields, returning one or the other. OR is not a selector; it is a boolean logic operator - like '+' in arithmetic - which returns True if the expression on either side of it is True.

The expression [busnumberA] OR [busnumberB] in your IIF is bound to evaluate as False. Your IIF will in this case return the value 0 (which represents the boolean value False; -1 would represent True).

To return one or other field value you will need some kind of further test. It is simply not possible to return the two fields together (which you mention at the end of your post as busnumbera and/or busnumberb), and I cannot see why you would want to.

The simplest test you can apply to return one or the other is to use another IIF with the function IsNull to test whether field busnumberA is null or not and return busnumberB if it is, like this:

Expr5: IIf([Forms]![frmreports]![cbBusNumber]="All","All",IIF(IsNull([busnumberA]), [busnumberB], [busnumberA]))

but this presumes that if there is no value in the busnumberA field there will always be a value in the busnumberB field - which may well be untrue.

I'd rethink what you are trying to do, as I think there are many issues here which you have not really thought through, and that you would benefit from brushing up a bit on boolean algebra, which underlies all comparisons and logical expressions in all computer languages.

-Stewart

[josh456]

Thanks for your thoughts Stewart,

I am unable to get your expression to work in the folowing example.

If bus number 38 is in "busnumberB " but not in "busnumberA " of my table. I am unable to retrieve this information for bus number 38. Although I am able to get any bus numbers which exist from "busnumberA ".

Thanks Josh

[Stewart Ross]

Hi. What type of field is involved? I assumed that your busnumber fields were text, but if they are numbers they will most likely default to 0, not null, if not filled in.

If so, you could change IIF(IsNull(busn umberA)...) to IIF([busnumberA]=0, [busnumberB], [busnumberA])

Please let me know what the field types are, and what default values (if any) are set.

I would also welcome you posting some sample data from your table to see how the data is structured and what range of values you are dealing with.

-Stewart