If statement help

[lghovden06]

I am doing a monthly report for a company in access. I am fairly new to access so I don't know a whole lot. The report was orginaly created in excel. one of the columns, accounts receivable, equals the previous days accounts receivalbe + time sales - Rec on Account. At the beginning of th month the Accounts Receivable equals the final day in the last month's. I am trying to create an if statement to check if it is null.
Here is what I kind of came up with
Accounts_Receivable: If(IsNull( [Accounts_Receivable]),[last_month]+[time_sales] - [rec_on_acc], [Accounts_Recievable] + [time_sales] - [rec_on_acc]

I don't even think this is close but PLEASE HELP!

[hjozinovic]

I am doing a monthly report for a company in access. I am fairly new to access so I don't know a whole lot. The report was orginaly created in excel. one of the columns, accounts receivable, equals the previous days accounts receivalbe + time sales - Rec on Account. At the beginning of th month the Accounts Receivable equals the final day in the last month's. I am trying to create an if statement to check if it is null.
Here is what I kind of came up with
Accounts_Receivable: If(IsNull( [Accounts_Receivable]),[last_month]+[time_sales] - [rec_on_acc], [Accounts_Recievable] + [time_sales] - [rec_on_acc]

I don't even think this is close but PLEASE HELP!

hey Ighovden,

I believe the best solution would be to have a running sum.
I'm talking about Reports here. If you're creating a report you can have a running sum field that would always have the latest 'sum'.
It would not matter wether the sum is from previous month or not.

You can read more about creating a running sum in reports if you search MsAccess help for 'running sum'.

regards,
H.

[salimudheen]

Hey,
I think u create the report using Ms Access. In ms access report field, use the IIF statement instead of if statement u declared. Others in ur statements are correct.

[lghovden06]

hey Ighovden,

I believe the best solution would be to have a running sum.
I'm talking about Reports here. If you're creating a report you can have a running sum field that would always have the latest 'sum'.
It would not matter wether the sum is from previous month or not.

You can read more about creating a running sum in reports if you search MsAccess help for 'running sum'.

regards,
H.

Hi,
Thank you for the reply. My only proublem with a running sum is one of the equations for a field "Accounts_Receivable" incolvel more than one row. For example here is what it looked like in excel =(Y1 + V2) - W2 next cell down would look like this =(Y2+V3)-W3 Y in the Accounts receivalbe column V is Time_Sales and W is Rec_on_Acc. In Access the Accounts_Receivable field will start with the end of last months Accounts_Receivalbe, but if I type the equation Accounts_Receivable: Accounts_Recievable + Time_Sales - Rec_on_Acc it uses the Accounts_Receivalble on that row which is zero. it needs to use Accounts_Receivable in the above row and then use Time_Sales and Rec_on_Acc in the current row.
I hope this makes some sense. I had a hard time describing it in words. If this doesn't make sense I can try to reword it.
Thanks

[ADezii]

hey Ighovden,

I believe the best solution would be to have a running sum.
I'm talking about Reports here. If you're creating a report you can have a running sum field that would always have the latest 'sum'.
It would not matter wether the sum is from previous month or not.

You can read more about creating a running sum in reports if you search MsAccess help for 'running sum'.

regards,
H.

The general Logic would be:

IIf(IsNull([Accounts_Receivable]), 0, ([last_month] + [time_sales]) - [rec_on_acc])

[Stewart Ross]

Hi. There are things that spreadsheets are much better at than databases - and one of them is the ability to generate row-by-row running sums. Databases can't easily mimic that kind of functionality, because the relational database model has no concept of record position.

If you look at how a spreadsheet does it, the running sum is adding the current cell to the previous value for that cell, all down a particular column. In spreadsheets, this relies on every row having a specific row and column position (or address). So, to produce a running total we add a new column and populate it with references to the previous cells (D2 = A2 + A1 for example, where A1 is a reference to the previous row's value). The key concept here is that every row of a spreadsheet has a reference number - which is not the case in a database.

Without the row referencing of the spreadsheet it is very difficult to describe the logic of the calculation in such a way that it can take place at all. The running sum for reports is a good example of how something like it can be achieved, but not easily within a general query.

Usually, to do a running sum in SQL involves self-joining a copy of a table to itself using some field (such as a date) to achieve a join of the current records to the previous one. It can be done, but is neither easy nor fast. It cannot be done using a simple lookup or built-in formula.

I would recommend exporting the data to Excel and using its flexibility to do such running SUMs - just as they were done originally. If you have access to the current total, as in your first posts and in subsequent responses, then you can obtain a total for your report - but it is an intermediate total of some kind rather than a running sum.

-Stewart

[hjozinovic]

hey Ighovden,

I made a small base trying to get the result you are looking for.
I hope you will find it usefull.

H.