Solved Show all records if combobox empty but report shows blank first page (1 Viewer)

[foshizzle]

I have the following code which will return all Airline Companies if combobox "cboAirlineCompany" on a form is left blank.
The results of the query returns the expected results however, if I run this in a report, the first page is blank as if it was using NULL from the blank combobox. Then, all Airline Companies appear on the following pages.

any thoughts?

PARAMETERS [TempVars]![tmpEndDate] DateTime;
SELECT tblBalance.TransactionDate, tblBalance.AirlineCompanyLookup, tblBalance.Balance AS PrvBalance, (Select Sum(a.Balance)                    
From  tblBalance as a
    Where  a.TransactionDate =  [TempVars]![tmpEndDate]-1) AS RunPrvBalance
    FROM tblBalance
    INNER JOIN tblAirlines ON tblBalance.AirlineCompanyLookup = tblAirlines.AirlineID
WHERE (((tblBalance.TransactionDate)=TempVars!tmpStartDate-1)
       And ((tblBalance.AirlineCompanyLookup)=Nz(Forms!frmReportBuildFuelTrans!cboAirlineCompany,[AirlineCompanyLookup])));

Here is the code from the combobox

SELECT tblAirlines.AirlineID,  tblAirlines.AirlineCompany 
    FROM tblProviders 
    INNER JOIN tblAirlines 
    ON tblProviders.ProviderID = tblAirlines.ProviderNameLookup 
    WHERE (((tblAirlines.AirlineStatus)="Active")) 
    ORDER BY tblAirlines.AirlineCompany 
UNION SELECT Null,"(All)" FROM tblProviders;

 

[Pat Hartman]

Anything compared to null = null. You need to change the Where to:

tblBalance.AirlinesCompanyLookup = (Forms!frmReportBuildFuelTrans!cboAirlineCompany OR Forms!frmReportBuildFuelTrans!cboAirlineCompany Is Null)

 

[foshizzle]

Hi Pat,

Here is the modified query. When I run it this way, it takes an hour or so, then spits out the same result. what did i miss?

PARAMETERS [TempVars]![tmpEndDate] DateTime;
SELECT tblBalance.TransactionDate, tblBalance.AirlineCompanyLookup, tblBalance.Balance AS PrvBalance, (Select Sum(a.Balance)     
    From  tblBalance as a     
    Where  a.TransactionDate = [TempVars]![tmpEndDate]-1) AS RunPrvBalance
FROM tblBalance INNER JOIN tblAirlines ON tblBalance.AirlineCompanyLookup = tblAirlines.AirlineID
WHERE (((tblBalance.TransactionDate)=[TempVars]![tmpStartDate]-1)
    AND (tblBalance.AirlineCompanyLookup=[Forms]![frmReportBuildFuelTrans]![cboAirlineCompany])) OR ((([Forms]![frmReportBuildFuelTrans]![cboAirlineCompany]) Is Null));

 

[foshizzle]

Hmm.. I also tried this, which also returns the expected results in the query itself, but the report hangs.. Let me check that report out again

PARAMETERS [TempVars]![tmpEndDate] DateTime;
SELECT tblBalance.TransactionDate, tblBalance.AirlineCompanyLookup, tblBalance.Balance AS PrvBalance, (Select Sum(a.Balance)                      
    From  tblBalance as a                      
        Where  a.TransactionDate =  [TempVars]![tmpEndDate]-1) AS RunPrvBalance
FROM tblBalance INNER JOIN tblAirlines ON tblBalance.AirlineCompanyLookup = tblAirlines.AirlineID
WHERE (((IIf(IsNull([Forms]![frmReportBuildFuelTrans]![cboAirlineCompany]),True,[AirlineCompanyLookup]=[Forms]![frmReportBuildFuelTrans]![cboAirlineCompany]))<>False));

 

[Pat Hartman]

Access doesn't optimize subselects very well. Try using a join to a saved querydef instead of the subselect.

 

[SHANEMAC51]

(Select Sum(a.Balance) From tblBalance as a Where a.TransactionDate = [TempVars]![tmpEndDate]-1) AS RunPrvBalance

I don't understand this built-in query - it just counts the balance for the day before the report, i.e. it is a fixed value

why not take it out of the SELECT in FROM

 

[foshizzle]

Access doesn't optimize subselects very well. Try using a join to a saved querydef instead of the subselect.

Pat - I think you were on to something here. I re-built the query for this and it worked without adding the additional blanks. thanks for the suggestion!

 

[Pat Hartman]

You're welcome