First query run does not produce results.

G

Guest

I have built query of a query of a query of a table. Each level also has one
field linked to a table. (see SQL below) I use an input form (frmtest) with
check boxes as query input.

If the boxes on the form are unchecked, it does not return any results the
FIRST time I run the query after opening the frmtest. HOWEVER if I check all
the boxes and then execute it, it returns accurate results. THEN if I
uncheck all the boxes and execute it, it returns accurate results until I
close the frmtest.

What is the deal here? Thanks for your help.

SELECT qryMCAFOandAAFAR.OfficialAANumber, qryMCAFOandAAFAR.OfficialFONumber,
qryMCAFOandAAFAR.FODescription, qryMCAFOandAAFAR.RiskRating,
qryMCAFOandAAFAR.FOStatus, qryMCAFOandAAFAR.OfficialMCANumber,
qryMCAFOandAAFAR.RespDir, qryMCAFOandAAFAR.RespDept,
qryMCAFOandAAFAR.RespDiv, qryMCAFOandAAFAR.RespGroup,
qryMCAFOandAAFAR.RespPerson, qryMCAFOandAAFAR.[Recommended MCA],
qryMCAFOandAAFAR.Determination, qryMCAFOandAAFAR.DueDate,
qryMCAFOandAAFAR.MCAStatus, qryMCAFOandAAFAR.MCAFAR, qryMCAFOandAAFAR.CAPR,
qryMCAFOandAAFAR.CAPRDate, qryMCAFOandAAFAR.AATitle,
qryMCAFOandAAFAR.AADescription, qryMCAFOandAAFAR.AANoticeDate,
qryMCAFOandAAFAR.AAOrg, qryMCAFOandAAFAR.AAAuditorAssessor,
qryMCAFOandAAFAR.AAStatus, qryMCAFOandAAFAR.FOFAR, qryMCAFOandAAFAR.AAFAR,
tblAAOrganizations.[ITS Tracked]
FROM tblAAOrganizations INNER JOIN qryMCAFOandAAFAR ON
tblAAOrganizations.AAOrg = qryMCAFOandAAFAR.AAOrg
WHERE (((tblAAOrganizations.[ITS
Tracked])=[Forms]![frmTest]![cbxITsTracked])) OR
((([Forms]![frmTest]![cbxITsTracked])=0));
 
G

Guest

My guess would be that the check boxes are 'triple state', i.e. they can be
True, False or Null. Any comparison operation involving Null evaluates to
Null, so if the check boxes are Null when the form opens (they'll be greyed
out) the query's WHERE clause will evaluate to Null and not return any rows.
Either set the default value property of each of the check boxes to False or
set them to False in code in the form's Load event procedure:

Me. cbxITsTracked = False
Me.cbxSomeOtherCriterion = False
< and so on >

Ken Sheridan
Stafford, England
 
G

Guest

Thanks Ken - They were not set to triple state, however the settig default
value to False did the trick as you suggested.

Ken Sheridan said:
My guess would be that the check boxes are 'triple state', i.e. they can be
True, False or Null. Any comparison operation involving Null evaluates to
Null, so if the check boxes are Null when the form opens (they'll be greyed
out) the query's WHERE clause will evaluate to Null and not return any rows.
Either set the default value property of each of the check boxes to False or
set them to False in code in the form's Load event procedure:

Me. cbxITsTracked = False
Me.cbxSomeOtherCriterion = False
< and so on >

Ken Sheridan
Stafford, England

John said:
I have built query of a query of a query of a table. Each level also has one
field linked to a table. (see SQL below) I use an input form (frmtest) with
check boxes as query input.

If the boxes on the form are unchecked, it does not return any results the
FIRST time I run the query after opening the frmtest. HOWEVER if I check all
the boxes and then execute it, it returns accurate results. THEN if I
uncheck all the boxes and execute it, it returns accurate results until I
close the frmtest.

What is the deal here? Thanks for your help.

SELECT qryMCAFOandAAFAR.OfficialAANumber, qryMCAFOandAAFAR.OfficialFONumber,
qryMCAFOandAAFAR.FODescription, qryMCAFOandAAFAR.RiskRating,
qryMCAFOandAAFAR.FOStatus, qryMCAFOandAAFAR.OfficialMCANumber,
qryMCAFOandAAFAR.RespDir, qryMCAFOandAAFAR.RespDept,
qryMCAFOandAAFAR.RespDiv, qryMCAFOandAAFAR.RespGroup,
qryMCAFOandAAFAR.RespPerson, qryMCAFOandAAFAR.[Recommended MCA],
qryMCAFOandAAFAR.Determination, qryMCAFOandAAFAR.DueDate,
qryMCAFOandAAFAR.MCAStatus, qryMCAFOandAAFAR.MCAFAR, qryMCAFOandAAFAR.CAPR,
qryMCAFOandAAFAR.CAPRDate, qryMCAFOandAAFAR.AATitle,
qryMCAFOandAAFAR.AADescription, qryMCAFOandAAFAR.AANoticeDate,
qryMCAFOandAAFAR.AAOrg, qryMCAFOandAAFAR.AAAuditorAssessor,
qryMCAFOandAAFAR.AAStatus, qryMCAFOandAAFAR.FOFAR, qryMCAFOandAAFAR.AAFAR,
tblAAOrganizations.[ITS Tracked]
FROM tblAAOrganizations INNER JOIN qryMCAFOandAAFAR ON
tblAAOrganizations.AAOrg = qryMCAFOandAAFAR.AAOrg
WHERE (((tblAAOrganizations.[ITS
Tracked])=[Forms]![frmTest]![cbxITsTracked])) OR
((([Forms]![frmTest]![cbxITsTracked])=0));
Click to expand...
Click to expand...
 
Top