Cashflow query to return previous valid field if date has null val

T

Tiago

Hello guys!

Hope someone can help as usual!
I have designed a simple query to control my cashflow, pulling data from a
topay_query and toreceive_query. I have managed to make all the calculations,
no problem.
The thing is that I have an "Accumulated payed" and "Accumulated received".
When there's no moviment in a day these fields come out blank. How can I do
that when there's no movement, the query would consider always the value of
the previous valid field?

THanks mate!
 
L

Lance

How are your queries set up? Are you linking by day? Is one of your
criteria to only show the current day?
 
T

Tiago

I have one table called DateMAIN with a date field that relates to:

datetopay at "topay_query";
datetoreceive at "toreceive_query".

And I have no criterias, so I have one line for each date that's why
sometimes they are blank.

Tiago.
 
L

Lance

OK.. more questions.

What output are you looking for? The latest entries in both tables?
And what are you using the datemain table for?
 
T

Tiago

Hi Lance,

I'm looking for something like this:

date Revenue Cost Balance
01/nov - 50.0 20.00 30.00
02/nov - 00.0 00.00 30.00 NO MOVEMENT
03/nov - 10.0 20.00 20.00
04/nov - 00.0 30.00 -10.00
05/nov - 00.0 00.00 -10.00 NO MOVEMENT

Note that the 02/nov and 05/nov there wasn't any revenue or cost, so the
balance was equal to the one of the previous date.

The datemain table is used just to give me all dates of the year, just a
reference to link the other 2.

Thanks in advance for your help.

Cheers, tiago.
 
L

Lance

OK, first we address the null data from missing days. We do this by
appending a 0 to the front of your revenue field and then getting the value
of that. Like this: Val("0" & [value_pay]). Null field become 0, and fields
with values in them remain the same since leading 0's in a numeric field are
ignored.

Next we address your running sum. To use a running sum in a query requires
a function. Such as:

Function running_sum(inPay As Long, inRec As Long)
Static vPay As Long
Static vRec As Long

vPay = vPay + inPay
vRec = vRec + inRec
running_sum = vRec - vPay
End Function

Now put together your query. Link both $$ tables to datemain and include
all records from datemain and only records that match from the other ( AKA
arrow pointing from datemain to each of your other two tables ). Include the
date from datemain, the adjusted pay from step #1, the adjusted received from
step #1, and a function call ( being passed the adjusted pay and received )
to running_sum.

You should have what you need.
 
L

Lance

One issue I just remembered.. the value in the function continues running
through multiple queries. You might want something like the following
instead.

Function running_sum(inPay As Long, inRec As Long, Optional reset As Boolean)
Static vPay As Long
Static vRec As Long

If reset Then
vPay = 0
vRec = 0
End If

vPay = vPay + inPay
vRec = vRec + inRec
running_sum = vRec - vPay
End Function


Do something like this whenever you need to reset the values.
Sub reset_sum()
Call running_sum(0, 0, True)
End Sub


Lance said:
OK, first we address the null data from missing days. We do this by
appending a 0 to the front of your revenue field and then getting the value
of that. Like this: Val("0" & [value_pay]). Null field become 0, and fields
with values in them remain the same since leading 0's in a numeric field are
ignored.

Next we address your running sum. To use a running sum in a query requires
a function. Such as:

Function running_sum(inPay As Long, inRec As Long)
Static vPay As Long
Static vRec As Long

vPay = vPay + inPay
vRec = vRec + inRec
running_sum = vRec - vPay
End Function

Now put together your query. Link both $$ tables to datemain and include
all records from datemain and only records that match from the other ( AKA
arrow pointing from datemain to each of your other two tables ). Include the
date from datemain, the adjusted pay from step #1, the adjusted received from
step #1, and a function call ( being passed the adjusted pay and received )
to running_sum.

You should have what you need.

Tiago said:
Hi Lance,

I'm looking for something like this:

date Revenue Cost Balance
01/nov - 50.0 20.00 30.00
02/nov - 00.0 00.00 30.00 NO MOVEMENT
03/nov - 10.0 20.00 20.00
04/nov - 00.0 30.00 -10.00
05/nov - 00.0 00.00 -10.00 NO MOVEMENT

Note that the 02/nov and 05/nov there wasn't any revenue or cost, so the
balance was equal to the one of the previous date.

The datemain table is used just to give me all dates of the year, just a
reference to link the other 2.

Thanks in advance for your help.

Cheers, tiago.
Click to expand...
Click to expand...
 
T

Tiago

Thanks Lance, let's see how it goes!

Lance said:
One issue I just remembered.. the value in the function continues running
through multiple queries. You might want something like the following
instead.

Function running_sum(inPay As Long, inRec As Long, Optional reset As Boolean)
Static vPay As Long
Static vRec As Long

If reset Then
vPay = 0
vRec = 0
End If

vPay = vPay + inPay
vRec = vRec + inRec
running_sum = vRec - vPay
End Function


Do something like this whenever you need to reset the values.
Sub reset_sum()
Call running_sum(0, 0, True)
End Sub


Lance said:
OK, first we address the null data from missing days. We do this by
appending a 0 to the front of your revenue field and then getting the value
of that. Like this: Val("0" & [value_pay]). Null field become 0, and fields
with values in them remain the same since leading 0's in a numeric field are
ignored.

Next we address your running sum. To use a running sum in a query requires
a function. Such as:

Function running_sum(inPay As Long, inRec As Long)
Static vPay As Long
Static vRec As Long

vPay = vPay + inPay
vRec = vRec + inRec
running_sum = vRec - vPay
End Function

Now put together your query. Link both $$ tables to datemain and include
all records from datemain and only records that match from the other ( AKA
arrow pointing from datemain to each of your other two tables ). Include the
date from datemain, the adjusted pay from step #1, the adjusted received from
step #1, and a function call ( being passed the adjusted pay and received )
to running_sum.

You should have what you need.

Tiago said:
Hi Lance,

I'm looking for something like this:

date Revenue Cost Balance
01/nov - 50.0 20.00 30.00
02/nov - 00.0 00.00 30.00 NO MOVEMENT
03/nov - 10.0 20.00 20.00
04/nov - 00.0 30.00 -10.00
05/nov - 00.0 00.00 -10.00 NO MOVEMENT

Note that the 02/nov and 05/nov there wasn't any revenue or cost, so the
balance was equal to the one of the previous date.

The datemain table is used just to give me all dates of the year, just a
reference to link the other 2.

Thanks in advance for your help.

Cheers, tiago.


:

OK.. more questions.

What output are you looking for? The latest entries in both tables?
And what are you using the datemain table for?

:

I have one table called DateMAIN with a date field that relates to:

datetopay at "topay_query";
datetoreceive at "toreceive_query".

And I have no criterias, so I have one line for each date that's why
sometimes they are blank.

Tiago.

:

How are your queries set up? Are you linking by day? Is one of your
criteria to only show the current day?

:

Hello guys!

Hope someone can help as usual!
I have designed a simple query to control my cashflow, pulling data from a
topay_query and toreceive_query. I have managed to make all the calculations,
no problem.
The thing is that I have an "Accumulated payed" and "Accumulated received".
When there's no moviment in a day these fields come out blank. How can I do
that when there's no movement, the query would consider always the value of
the previous valid field?

THanks mate!
Click to expand...
Click to expand...
Click to expand...
 
Top