"Nested If Statement" syntax question

[FlexCrush]

I'm trying to use a nested statement and I can't get the syntax
correct. My goal is to return a value for the this statement:

If STATUS = Active and If GENDER = F and If VENDOR = abc, then return
#PATIENTS


Thank you in advance

 

[Douglas J Steele]

If STATUS = "Active" And GENDER = "F" And VENDOR = "abc" Then
' do what you want
Else
' do something else
End If

That's not actually a Nested If statement.

 

['69 Camaro]

Hi.

You can create a function that returns a value, or you can use an IF block
to conditionally assign the value to a variable or a control, such as a text
box. For example, here's an IF block that assigns the value from the
#PATIENTS text box to myVariable:

If ((STATUS = "Active") AND (GENDER = "F") AND (VENDOR = "abc")) Then
myVariable = Me![#PATIENTS].Value
End If

If the value you want needs to be calculated (as in "how many patients")
instead of copied from a text box, then of course you'll have to code for
that. If this is the part you need help with, then you need to give us more
information. For example, are you trying to determine how many patients
listed in the table meet the above criteria? Or are you trying to determine
how many patients listed in the table meet the above criteria this month?
Or something else?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[FlexCrush]

I need to meet three conditions to return the value.

Status = Active
Gender = F
Vendor = abc

If all conditions are met, return the number of patients (# patients).
If all conditions are not met, return "0".

 

[FlexCrush]

For example, are you trying to determine how many patients
listed in the table meet the above criteria?

Answer: YES

 

['69 Camaro]

Hi.

Without knowing the name of your table or query, the names of your fields,
or where the status, gender, and vendor values are coming from (control
names? form's record source? variables?), and what is being assigned by
the resulting value, the best I can give you is something generic that you
can replace with your own names. It would be more efficient to use a
Recordset than to use a domain function, such as DCount, but I suspect that
DCount will be less confusing for you. Try the following (watch out for
word wrap):

If ((STATUS = "Active") AND (GENDER = "F") AND (VENDOR = "abc")) Then
myVariable = DCount("*", "tblPatients", "(Field1 = 'Active') AND (Field2
= 'F') AND (Field3 = 'abc')")
Else
myVariable = 0
End If

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[FlexCrush]

Gunny,

I"ve almost solve the problem. The following statement
=IIf([Status]="active",IIf([Gender]="f",IIf([Vendor]="uhc",[#
patients],0))) isn't returning the correct answer. The answer should
be "26". I'll attach the query results for you to review.

Look at the following conditions:

Status = Active
Age Grp = 0-18
Vendor = UHC
Gender = F

Status Age Grp CPT code Description Vendor # patients Gender
Retiree 0-18 88141 Pap-F Annually UHC 1 F
Retiree 0-18 88141 Pap-F Annually UHC 0 M
Retiree 0-18 88141 Pap-F Annually HBS 0 M
Retiree 0-18 88141 Pap-F Annually HBS 0 F
Active 0-18 88141 Pap-F Annually UHC 26 F
Active 0-18 88141 Pap-F Annually HBS 16 F
Retiree 0-18 90703 Tetanus (1) UHC 0 M
Retiree 0-18 90703 Tetanus (1) UHC 0 F
Retiree 0-18 90703 Tetanus (1) HBS 0 M
Retiree 0-18 90703 Tetanus (1) HBS 0 F
Active 0-18 90703 Tetanus (1) UHC 0 M
Active 0-18 90703 Tetanus (1) UHC 0 F
Active 0-18 90703 Tetanus (1) HBS 1 M
Active 0-18 90703 Tetanus (1) HBS 0 F

 

['69 Camaro]

Hi.

Now I understand. You want the value in a field in a given record, not the
count of patients who meet specific criteria. Try:

= IIF(((Status = 'active') AND (Gender = 'F') AND (Vendor = 'uhc')),
DLookup("[# patients]", "tblPatients", "(Status = 'active') AND (Gender =
'F') AND (Vendor = 'uhc')"), 0)

.. . . where tblPatients is the name of the table.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[FlexCrush]

Gunny,

Thank You!!!



Scientia est Potentia

 

['69 Camaro]

Then I take it that it worked. You're welcome!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.