I have a table that stores two sets of information one or the other is retrieved depending upon two fields: DEFAULT_NSI and DEFAULT_TENANT (both True/False fields). Only one of these True/False fields can be set to True and at least one needs to be set to True.

I need to select from the table the information for the one that is True. I was thinking something like the following as part of the WHERE clause

WHERE IIF(A.DEFAULT_TENANT = TRUE, A.DEFAULT_TENANT, A.DEFAULT_NSI)

However, this doesn't seem to be working. The result of the IIF needs to be that A.DEFAULT_TENANT is True or A.DEFAULT_NSI is True and I don't think this phrase does that.

Thanks.

Hi,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

I'm not sure I understand your question without knowing the context. It seems that it would be easier to just have one Yes/No field and just select either the True or the False records depending on which one is required.

Just my 2 cents...

Access 2007.

With regards to the True/False fields, it has been this way for a long time, and it has worked well. However, you might be right, perhaps only one field would work. There are some other considerations that I didn't mention that factor in to the design choic of using two True/False fields, but are not relevant to my question here.

I'd rather not change the table at this point, because it will also require substantial code changes.

What about some type of string concatenation. Could I have the IIF select the field name, instead of field values, and then concatenate " = True"?

Hi,


There's got to be. I just didn't understand the question. Can you give more details?

Just my 2 cents...

Hi dbGuy:

You responded so fast and I was in the midst of editing my first response. Would appreciate it if you would re read it.

Thanks.

Hi,


Sorry about the confusion. But, instead of asking if what you're trying to do is possible, maybe you can just describe the actual goal so that we can tell you the proper way to accomplish it.

Just my 2 cents...

I'm trying to select information in the table for sites and buildings (New York, Bldg 1; Boston, Bldg 1 and so on). For each site and building combination, the goal is that within the VBA SQL statement if DEFAULT_TENANT = True then select the row(s) where DEFAULT_TENANT = True, but if DEFAULT_NSI = True, then select the rows where DEFAULT_NSI = true.

I wasn't able to get a concatenation idea to work. Now I'm considering a subselect, but it doesn't seem obvious to me how to make that work.

Hi,

Thanks for the additional information but I'm afraid it didn't quite get me there yet. How about posting some sample data to maybe illustrate the issue better.

The scenario may seem simple to you but that's because you work with your db everyday. We are not there with you so we'll need a little more hand-holding to get the whole picture.

Are we talking about creating a query based on multiple tables? For example, do you have the site and building and default fields in one table and then want to pull records from another table based on the yes/no field values for each record in the main table?

PMFJI:

Like the DB Guy, I'm a bit puzzled by the concept and can't quite see how to do this querying.

Also I know you've said this has worked for a long time, but I am pretty sure there is a more appropriate way to get to the same data result that doesn't involve two competing yes/no fields. But we have to see data and business rules to be sure. Of course, if you've built an entire database around this construct, it may well be more work to fix the problem than to try to do a workaround to get you by.

Yes. I'm afraid you are correct regarding the workaround. The design was done over five years ago and this issue has not come up before. I keep thinking there was a reason why I needed the two True/False fields (possibly because the data is imported from a database that I have no control over and I need to combine it with data I do have control over), but it does now seem that I could have just used the one True/False field.

I figure it would take about a half a day to remove one of the True/False fields, but I'm worried that there will be a problem with other code not displaying the correct results.

I can probably achieve what I want to do by doing a more high-level select, then examining the results of the two True/False fields and select again. This will be an iterative process as opposed to one SQL select that brings back the results very fast. I'm sure I could get it to work, but it would provide the results slower. Still if I want an accurate results list, it might be the best way.

This has been a good lesson regarding competing True/False fields.

Thanks.

I implemented the high-level select and then examining the DEFAULT_TENANT and DEFAULT_NSI flags. It all works fine now and is not obnoxiously slow.

Thanks for the help.

Actually I THINK you need a field that can accept (at this point in time, anyway) two different values: DEFAULT_TENANT or DEFAULT_NSI. Neither of those is specifically "true" or "false"' It's an either/or, close but not the same. Isn't it the case that the Default is one of two possible types (tenant or NSI), or am I simply missing the point? Obviously, since we don't know your business rules, I could still be wrong.

This sentence seems to be the "tell" on this point: "Only one of these True/False fields can be set to True and at least one needs to be set to True." i.e., Either a Tenant or an NSI has to be selected. It's currently a binary choice (e.g. a smoking or non-smoking section) , but not a boolean choice (e.g. lights on or lights off). If you add "drinking and smoking" and "drinking and non-smoking" to the previously binary chioces, it becomes 1) smoking and drinking, 2) non-smoking and non-drinking, 3) non-smoking and drinking or 4) smoking and non-drinking. Far-fetched, I agree, but the point is that a true boolean choice admits only two possibilities: on or off, whereas a seemingly binary choice can--in some changed circumstances--become something else. I believe you have a binary choice, not a boolean choice. The business rules which CURRENTLY render it as a binary choice can change.

It is possible, is it not, that some day you'll need a third value here? It's not happened in 5 years, so it's probably a low possibility, but it could happen, could it not? You could end up with DEFAULT_OWNER? or something like that? hence, this field is NOT yes/no, but a number field that can accept one of 2 or more options.

I don't think speed is going to be a significant problem for you with a subquery for each of the current choices, but give it a try and see.

Hi,


Glad to hear you found a way to make it work. Good luck with your project.