473,320 Members | 1,936 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Access select query

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
Jul 24 '08 #1
3 1842
Stewart Ross
2,545 Expert Mod 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. 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
Jul 24 '08 #2
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
Jul 24 '08 #3
Stewart Ross
2,545 Expert Mod 2GB
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(busnumberA)...) 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
Jul 25 '08 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

14
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB...
7
by: Bob | last post by:
Currently I am using this statement to translate 3 fields in my db thru Visual Basic. I import the data from one table to another then call the IFF statements and the NewDate to translate the...
1
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a...
8
by: Alfonso Esteban Gonzalez Sencion | last post by:
I am trying to use Access as a front end for extracting information from an Oracle database. I started using linked tables but I am getting a very curious behaviour. When I consult the linked...
3
by: Dan | last post by:
I hate it when people think that their own misunderstandings are bugs in the program, but this time I think I've got something. If I run the following SQL code in Access 2000, I get unexpected...
3
by: StBond | last post by:
Hi everyone, I am new to Access and Visual Basic so things my be getting across a bit cloudy. I only started using VB for one week. I am having a little problem with the database that I am...
2
by: Arif | last post by:
Very strange problem: Executing my query against MS Access database using OleDbProvider for Access, I am getting the value for first two columns '0' instead of '1' in DataGrid. But if I connect to...
4
by: ShastriX | last post by:
Getting a weird error while trying out a query from Access 2003 on a SQL Server 2005 table. Want to compute the amount of leave taken by an emp during the year. Since an emp might be off for...
12
by: zwasdl | last post by:
Hi, I'm using MS Access to query against Oracle DB via ODBC. Is it possible to use HINT in Access? Thanks, Wei
0
MMcCarthy
by: MMcCarthy | last post by:
Rather than using the Access design view change the view to SQL. I am going to attempt to outline the general syntax used for SQL queries in Access. Angle brackets <> are used in place of some...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.