Record does not exist yet

[Drand]

Hi All

I have a budget application which includes two tables, Budget and Actual Data. There are various expense categories which are captured by month.

The issue I have is on the form that displays Actual expenses V's Budget Expenses per month where the actual expenses have not been incurred yet, hence no record exists yet in the table. As a result the form will not display until the data actually exists.

If possible, I would like the form to display zero values for these records until such time as the expense becomes a record in the table.

Is this possible?

Many thanks

 

[GPGeorge]

You could try NZ([ActualExpense],0) as the Default control source. You can't use it as the actual control source because that would prevent entering the actual ActualExpense. If you only need to display something, that might do.

 

[bastanu]

Hard to say without seeing your form, especially its record source but I would say you should try to change the join between tables from an equijoin to an outer join (with the arrow pointing towards the actuals).

Cheers,

 

[arnelgp]

you use Query and either use Left Join or Right Join to connect the two tables, like:

SELECT budget.AccountNo, budget.Budget, Nz(Data.Actual,0) AS [Actual Data]
FROM budget LEFT JOIN (SELECT [Actual Data].AccountNo, Sum([Actual Data].Amount) As Actual
FROM [Actual Data] GROUP BY [Actual Data].AccountNo)  AS Data
ON budget.AccountNo = Data.AccountNo
GROUP BY budget.AccountNo, budget.Budget, Data.Actual;