Solved Criteria in query to return all records (2 Viewers)

[mistyinca1970]

Hello,
I have a query that is tied to a form that will show records based on the user's division in the org. The criteria comes from a TempVars captured at login. What value would I need to get in this field to return records for all divisions?

SELECT tblContracts.ID, tblContracts.Division, tblContracts.ContractNo, tblContracts.ContractEntity, tblContracts.AssignedMonitor, tblContracts.ContractType, tblContracts.OriginalDate, tblContracts.ContractDate, tblContracts.ExpirationDate, tblContracts.Parity, tblContracts.ParityExpiration, tblContracts.EffectiveExpDate, tblContracts.Documents, tblContracts.OriginalAmount, tblContracts.TotalAmount, tblContracts.RenewalTerms, tblContracts.Comments, tblContracts.CurrentAmount, tblContracts.AmendedAmount, tblContracts.Funding, tblContracts.FundingAmt, tblContracts.Amendments, tblContracts.Closed, tblContracts.FileLink, tblContracts.ClosedDate, tblContracts.Status
FROM tblContracts
WHERE (((tblContracts.Division)=[TempVars]![UserDivision]) AND ((tblContracts.Status)<>"Closed"));

...so in this WHERE statement above with the TempVars, what value will not restrict the records return. I tried "*" and that didn't work.

Thank you,

 

[theDBguy]

Hi. Try...

WHERE (Division=TempVars!UserDivision OR TempVars!UserDivision Is Null) AND Status<>"Closed"

Then make sure you assign Null to the TempVar if you want to see all divisions.

 

[mistyinca1970]

Hi. Try...

WHERE (Division=TempVars!UserDivision OR TempVars!UserDivision Is Null) AND Status<>"Closed"

Then make sure you assign Null to the TempVar if you want to see all divisions.

Perfect! This works great!

Thank you!

 

[theDBguy]

Perfect! This works great!

Thank you!

You're welcome. Glad we could assist. Cheers!

 

[arnelgp]

WHERE (((tblContracts.Division)=[TempVars]![UserDivision]) AND ((tblContracts.Status)<>"Closed"))

you can also use Criteria:

WHERE (((tblContracts.Division) Like [TempVars]![UserDivision]) AND ((tblContracts.Status)<>"Closed"));

then you can assign "*" to Tempvars.