Between parameter with null values

[jderrig]

I am trying to alter this parameter to bring back all records if either beginningsalesrange or ending salesrange is left blank. I can't quite get it right. Thanks

Between [forms]![frmState]![BeginningSalesRange] And [Forms]![frmState]![EndingSalesRange]

 

[plog]

I would suggest moving the criteria into a field. It would look like this:

Include: Iif(Isnull([forms]![frmState]![BeginningSalesRange]) OR IsNull([Forms]![frmState]![EndingSalesRange]) OR Between [forms]![frmState]![BeginningSalesRange] And [Forms]![frmState]![EndingSalesRange], 1,0)

Then in the criteria section under that field, you put in 1. That way if it meets any of your 3 criteria the result is included.

 

[GSSDevelopment]

Try this:

WHERE ISNULL([Forms]![frmState]![BeginningSalesRange]) OR ISNULL([Forms]![frmState]![EndingSalesRange])

 

[pbaldy]

An alternative is the NZ() function on each form reference, each with a date before/after any possible date in the data.

 

[jderrig]

Thanks for the replys but I am getting invalid syntax on both of these:

Include: Iif(Isnull([forms]![frmState]![BeginningSalesRange]) OR IsNull([Forms]![frmState]![EndingSalesRange]) OR Between [forms]![frmState]![BeginningSalesRange] And [Forms]![frmState]![EndingSalesRange], 1,0)

WHERE ISNULL([Forms]![frmState]![BeginningSalesRange]) OR ISNULL([Forms]![frmState]![EndingSalesRange])

 

[plog]

Post the full SQL that you have.

 

[jderrig]

SELECT Profitable.CHSAcctNo, Profitable.GrossRevenue, Profitable.FarmSupplyRev, Profitable.GrainRev, Profitable.Expenses, Profitable.OtherIncome, Profitable.OtherExp, Profitable.CHSAcctNo AS CountOfCHSAcctNo, Profitable.City, Profitable.State, Profitable.[MS/CO], Profitable.[Grain/FS], Profitable.FYE, Profitable.MemberEquity, Profitable.WorkingCapital, Profitable.LocalSavings, Profitable.TotalSavings, Profitable.TDtoLE, Profitable.ROLE, Profitable.BSMId, Profitable.[Acct name], Profitable.TopAccount, Profitable.NetLS, Profitable.TTLSales, Profitable.TotalAssets, Profitable.CurrentAssets, Profitable.FixedAssets, Profitable.InvOtherOrgan, Profitable.OtherAssets, Profitable.CurrentLiabilities, Profitable.LongTermDebt, Profitable.ServiceRev, Profitable.[Income/LossfromLLC], Profitable.[-/+SaleAssetorInv], Profitable.Patronage, Profitable.IncomeTaxes, Profitable.Sales, Profitable.Year, Profitable.RetainedSavings, Profitable.GrainBushels, Profitable.[Salary/BenefitExp], Profitable.Depreciation, Profitable.CCMAmoritization, Profitable.EBITDA, Profitable.InterestExp, Nz([memberequity],0)-Nz([invotherorgan],0) AS LocalEquity, [Forms]![frmState]![txtQueryCriteria] AS Expr1, Profitable.OtherLTL, Profitable.EBITDARevised, Profitable.EBITRevised, Profitable.NetAssets
FROM Profitable
WHERE (((Profitable.CHSAcctNo) Like [forms]![frmState]![select Account] & "*" And (Profitable.CHSAcctNo) Like [forms]![frmState]![select Account] & "*") AND ((Profitable.[Grain/FS]) Like [Forms]![frmState]![Select Type] & "*") AND ((Profitable.BSMId) Like [Forms]![frmState]![BDMList] & "*") AND ((Profitable.TopAccount) Like [Forms]![frmState]![TopAccount] & "*") AND ((Profitable.Year) Between [forms]![frmState]![lstYear] And [forms]![frmState]![2ndYear]) AND (([Forms]![frmState]![txtQueryCriteria]) Like "*;" & [State] & ";*"));

 

[Brianwarnock]

FWIW. I think PBaldy's solution is the only viable one, you cannot have the Between And in the Iif , and I don't see how GSS tackles the whole problem.

Brian

 

[jderrig]

OK, thanks for that suggestion. I am unclear though on how the NZ function would work in this situation. I am trying to filter via a sales range and not date for this parameter.

 

[pbaldy]

Like

Between Nz([forms]![frmState]![lstYear], 1901) And Nz([forms]![frmState]![2ndYear], 2029)

 

[jderrig]

Maybe I am confused here...or I'm not understanding. I have the years set for the parameter fine. It's that I want to filter sales ranges. The user will enter a sales range to pull the accounts between those ranges.

Beginning Sales: 100,000,000
Ending Sales: 500,000,000

The results would be all those that have sales between 100M and 500M- which I have no issues with but I would like to have the option of leaving those two fields blank so that the results will show all accounts regardless of sales.

Like

Between Nz([forms]![frmState]![lstYear], 1901) And Nz([forms]![frmState]![2ndYear], 2029)

 

[pbaldy]

I'm simply demonstrating the concept I had in mind. You can apply it as desired to the appropriate field, with the appropriate form references and the appropriate values.

 

[jderrig]

That worked great Paul. Thanks much!!!!!

 

[pbaldy]

Happy to help!