IF in criteria, help with syntax please! 1

[topwaya]

I'm trying to figure out how to add another criteria to the query below, but I can't get the syntax right.

In addition to the criteria already present, I want to say:

Criteria:
If tblFCForms.PHYSAuth is not null AND Age is >39.99

SELECT tblPersonal.PID, tblPersonal.LocW, qryFullName.FullName, (Date()-[BDAY])/365.25 AS Age, tblPersonal.User, tblPersonal.Mtype, tblPersonal.FCSD, tblPersonal.LocFC1, tblFCForms.FCMbr, tblFCForms.Agree, tblFCForms.PHYSAuth
FROM (tblPersonal INNER JOIN qryFullName ON tblPersonal.PID=qryFullName.PID) INNER JOIN tblFCForms ON tblPersonal.PID=tblFCForms.PID
WHERE (((tblPersonal.FCSD) Is Not Null) AND ((tblFCForms.FCMbr) Is Not Null) AND ((tblFCForms.Agree) Is Not Null) AND ((tblPersonal.XLD) Is Null));

Thanks for any help!

 

[fredericofonseca]

You already have a few "and", the two criteria you are adding are, on this case, jost another two, so added accordingly to the others.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[topwaya]

Thank you for your quick response.
I have tried to add the criteria in design view as well as sql view and I keep getting syntax errors, could you please type out the correct syntax for me?
Thank you!

 

[fredericofonseca]

No.

If you have an error then post the code you have tried and we will try and see what is wrong.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[topwaya]

Thanks again for your help, I can't get out of design view after entering the wrong syntax, so here is what I tried in sql view:

SELECT tblPersonal.PID, tblPersonal.LocW, qryFullName.FullName, (Date()-[BDAY])/365.25 AS Age, tblPersonal.User, tblPersonal.Mtype, tblPersonal.FCSD, tblPersonal.LocFC1, tblFCForms.FCMbr, tblFCForms.Agree, tblFCForms.PHYSAuth
FROM (tblPersonal INNER JOIN qryFullName ON tblPersonal.PID = qryFullName.PID) INNER JOIN tblFCForms ON tblPersonal.PID = tblFCForms.PID
WHERE (((tblPersonal.FCSD) Is Not Null) AND ((tblFCForms.FCMbr) Is Not Null) AND ((tblFCForms.Agree) Is Not Null) AND ((tblPersonal.XLD) Is Null)) AND ((tblFCForms.PHYSAuth)Is Not Null IFF(Age > 39.99));

 

[fredericofonseca]

Why did you use IFF(Age > 39.99)) if on your original code you have a AND ?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[topwaya]

Only the tblFCForms.PHYSAuth field needs to not be null if the age is above 39.99, none of the other criteria is related to the age. So I can't use AND >39.99 because it only applies to the tblFCForms.PHYSAuth field.

 

[lespaul]

First, it's iif. Second, the syntax is:

iif(some logical comparison, value if true, value if false)

third, you've added the IFF condition without an AND or an OR.

Why don't you tell us what you are actually trying to do?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[topwaya]

Please accept my apologies as to not being descriptive enough or trying stupid things - I'm a newbie and I'm doing my best, I appreciate any help.

This query figures out who is an FCMember and who is not.

In order to be an FCMember they must:
have a date in the tblPersonal.FCSD field (start date)
have a date in the tblFCForms.FCMbr field (date member form completed)
have a date in the tblFCForms.Agree field (date agreement signed)

NOT have a date in the tblPersonal.XLD field (date membership cancelled)

and if they are 40 year old or older, they need a date in the tblFCForms.PHYSAuth field (date physician's authorization completed)

My most recent wrong sql is below. I have to enter an Age parameter manually, instead of it refering to the Age that has been calculated in the query. Dont know if the IIF statement is correct or not.

SELECT tblPersonal.PID, tblPersonal.LocW, qryFullName.FullName, (Date()-[BDAY])/365.25 AS Age, tblPersonal.User, tblPersonal.Mtype, tblPersonal.FCSD, tblPersonal.LocFC1, tblFCForms.FCMbr, tblFCForms.Agree, tblFCForms.PHYSAuth
FROM (tblPersonal INNER JOIN qryFullName ON tblPersonal.PID = qryFullName.PID) INNER JOIN tblFCForms ON tblPersonal.PID = tblFCForms.PID
WHERE (((tblPersonal.FCSD) Is Not Null) AND ((tblFCForms.FCMbr) Is Not Null) AND ((tblFCForms.Agree) Is Not Null) AND ((tblPersonal.XLD) Is Null)) AND iif(Age>39.99, tblFCForms.PHYSAuth Is Not Null, tblFCForms.PHYSAuth Is Null);

Again thanks so much for your help - I keep learning!!

 

[PHV]

WHERE tblPersonal.FCSD Is Not Null AND tblFCForms.FCMbr Is Not Null AND tblFCForms.Agree Is Not Null AND tblPersonal.XLD Is Null AND (tblFCForms.PHYSAuth Is Not Null OR (Date()-[BDAY]) < 14610);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

That works!!! cool! One question, isn't the

(Date()-[BDAY]) <14610)

an absolute number? Meaning - doesn't it check to see if they are 40 or over THIS YEAR, and not every year or during the year?

That's why I converted their BDAY to "Age" in the SELECT statement

(Date()-[BDAY])/365.25 AS Age

because it could be checked any day or time and the result would be relative to the date that the query was run.

How would I go about checking to see if anyone is 40 or over relative to the date that the query is run? Would I have to do a separate query to get the age first?

Thanks much!

 

[PHV]

Take a look at the result of this operation:
365.25 * 40

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

Oops! Thank you sooooooo much!!