Trying to Display All Records in a Yes/No Field

[kopy]

I am trying to use the value of a table as criteria for a yes/no field in a query. It works correctly for the Yes (-1) and No (0) values. My problem is that if the value of the field is All only the -1 records are returned int he results. I'd like to have the All value return both 0 and -1 records.

Any help will be greatly appreciated. Thanks, Kopy
===========================================================

IIf(DLookUp("PDDNOS","tblQBF")="No",0,IIf(DLookUp("PDDNOS","tblQBF")="Yes",-1,IIf(DLookUp("PDDNOS","tblQBF")="All",([tblPreScreeningInfo].[PDDNOS])=0 Or ([tblPreScreeningInfo].[PDDNOS])=-1)))

 

[genomon]

I'm not real fond of nested IIfs, but if you must, have you tried changing the OR to an AND here?:
([tblPreScreeningInfo].[PDDNOS])=0 Or ([tblPreScreeningInfo].[PDDNOS])=-1)

Ever notice how fast Windows runs? Me neither.

 

[kopy]

Yes I've tried using and AND. It still didn't display both Yes and No vlaues.

 

[dhookom]

Try:

IIf(DLookUp("PDDNOS","tblQBF")="No",0,-1) OR (DLookUp("PDDNOS","tblQBF")="All")

Duane
Hook'D on Access
MS Access MVP

 

[kopy]

Duane, thanks for the help. This change does allow me to display either Yes or No or All records. My current problem is that I have to apply it to 40 fields and when I apply you suggestion, the query becomes too large and I get an error message that I have insufficent memory.

Kopy

 

[dhookom]

It sounds like you have 40 fields and are using DLookup() multiple times per field. I think you have an issue with your table structure.

Have you considered creating the SQL dynamically?

Duane
Hook'D on Access
MS Access MVP

 

[kopy]

What I'm trying to do is query by form. First I made the form unbound and after doing DlookUps to it, got an out of memory error. Next I broke the query into two with the source of the second being the first. Got a SQL statement to complex to be evaluated. So I bound the form to a table with only a single record. This worked except I wasn't getting records with both a Yes and a No value at the same time. It was either Yes or No.

I assume that when you mention creating the SQL dynamically, you mean via VBA. But won't this statement have the same drawbacks as the other approaches?

 

[dhookom]

I'm still concerned about your table structure. Do you understand normalization?

You might want to take a look at my QBF at DH Query By Form. This is an "applet" that I generally drop into every application I create. It dynamically builds the SQL for a saved query. There will be no out of memory or other errors.

BTW: did I mention I don't think your table structure is normalized ;-)

Duane
Hook'D on Access
MS Access MVP

 

[kopy]

Duane, I do believe that my table is normaized. This morning I took a different approach. I thought about what you said about multiple DLookUps for each field. Indeed the version that I have running, but doesn't display both Yes/No records at the same time, has only one Dlookup per field. I modified tblQBF so that the citeria of 0, -1, 0 or -1 are stored in it. When the field is set to either 0 or -1 it works fine. when I set it to 0 or -1, I get a data mismatch or no result at all. Here's what I have so far with this approach:

IIf(DLookUp("PDDNOS","tblQBF") Like "0 Or -1",([tblPreScreeningInfo].[PDDNOS])=-1 Or ([tblPreScreeningInfo].[PDDNOS])=0,DLookUp("PDDNOS","tblQBF"))

Thank you for your QBF, its very interesting. Unfortunatley I'm developing this for some doctors and they want things as simple as possible.

Thanks for the help, Kopy

 

[dhookom]

How many times do you need to repeat the DLookup()? There is no way you can use [red]Like "0 Or -1" inside the expression.

I think you need to provide your table structure and what you are attempting to accomplish. I believe you are going about this wrong. Do you have multiple fields similar to PDDNOS in tblQBF?

How about some context?

Duane
Hook'D on Access
MS Access MVP

 

[kopy]

Here's the documentation on tblQBF:

Columns
Name Type Size
StartDate Date/Time 8
EndDate Date/Time 8
StartInitialAge Long Integer 4
EndInitialAge Long Integer 4
StartCurrentAge Long Integer 4
EndCurrentAge Long Integer 4
PDDNOS Yes/No 1
Autism Yes/No 1
Aspergers Yes/No 1
HFA Yes/No 1
ASD Yes/No 1
ADD Yes/No 1
ADHD Yes/No 1
Anxiety Yes/No 1
Depression Yes/No 1
LanguageImpairment Yes/No 1
OCD Yes/No 1
Tourettes Yes/No 1
FutureContact Yes/No 1
ShareInfo Yes/No 1
SitQuietly Yes/No 1

There's a look up for each field.

 

[dhookom]

First, Name is a reserved word and shouldn't be used as a field name. I would probably use ClientName or PatientName.

Your table structure is not normalized. What would you do if you needed to add another "attribute" field? You would need to modify tables, forms, queries, etc. That's not how sound applications are created.

I would add a primary key field [ClientID] to the table and then create a couple tables'
[tt][blue]
tblCharacteristics
=====================
CharID Primary key autonumber
CharName values liek SitQuietly, Tourettes, ShareInfo,...
[/blue][/tt]
And a junction table
[tt][blue]
tblClientChars
=====================
CliCharID Primary key autonumber
ClientID links to the ClientID of your original table
CharID links to the CharID of tblCharacteristics
[/blue][/tt]
When you want to add another characteristic like obsessive compulsive Access user, you would add a record to tblCharacteristics.

The 15 yes/no fields would be replaced by up to 15 records in the junction table.

IMHO, I wouldn't go any further until the structure was normalized.

Duane
Hook'D on Access
MS Access MVP