Total by date 1

[mru]

Hi everyone

Can someone give me some idea of the best way to make the following form work.

What I intend to do is make a (continuous) form that will list all product suppliers. Thats easy enough but the data I would like to show for the suppliers comes in 3 columns which are: "sum balance of purchases for the year up to 2 months ago" " sum balance of previous months purchases " and "sum of the current months purchases ". Obviously the columns would need to be updated automatically at the start of every new month.

I have some knowledge (although not extensive) of access and have created a few continuous forms but I cant get my head round which will be the best way to set this one up.

Thanks

mru

 

[JoanneM]

after noon


I presume you are sourcing this form from a query can you confirm that's correct


regards
jo

 

[mru]

Hello JoanneM

The only way I could think of sourcing the data was with a query, I didnt know if this was the best way or how I could write a query that would do it properly

Thanks
mru

 

[JoanneM]

Hello there

I've just written a snippet of code for you.
it does the date calculations great
But
do you have a sample tbl that I can run it against I have a date field covering your timescale populated courtesy of excel but the summing bit I'll have to check?

Thanks

Jo
email2jo@btinternet.com

address for tbl sample thanks

 

[JoanneM]

In all cases Fldname should be replace with the name of your date field

For this month
date parameters in your query grid goes as follows

top line where the field name goes copy
ThisMonth:Fldname

& in the criteria line
Between dtFilter(1) And Date()

****************
For Last month
date parameters in your query grid goes as follows

top line where the field name goes copy
Lastmonth:Fldname

& in the criteria line
Between dtFilter(2) And Date(3)


***************

For Year

date parameters in your query grid goes as follows

top line where the field name goes copy
LastYear:Fldname

& in the criteria line
Between dtFilter(3) And Date(4)

**********************
Put the function below into a module

Function dtFilter(FltrType As Integer) As Date
Dim dtstr As String
Dim currday As Integer
Dim currmonth As Integer
Dim currYr As Integer
Dim startYr As Integer
Dim StartMonth As Integer
Dim Endmonth As Integer
currday = Day(Date)
currmonth = Month(Date)
currYr = Year(Date)


StartMonth = currmonth ' 12 months ago is the same month



'balance of purchases for the year up to 2 months ago" "
'sum balance of 'previous months purchases '
'" and "sum of the current months purchases ".


Select Case FltrType
Case 1
'current Month
dtstr = "01/" & currmonth & "/" & currYr
dtFilter = Format(CDate(dtstr), "dd/mm/yy&quot


Case 2
'previous month start
Select Case currmonth
Case Is <= 2
currYr = currYr - 1

Case Else
currYr = currYr
End Select

If currmonth = 1 Then
currmonth = 12
Else
currmonth = currmonth - 1
End If

dtstr = &quot;01/&quot; & currmonth & &quot;/&quot; & currYr
dtFilter = Format(CDate(dtstr), &quot;dd/mm/yy&quot

Case 3
'previous month end
'working on the assumption that the system date on m/c is correct
dtFilter = Format(Date - (currday + 1), &quot;dd/mm/yy&quot

Case 4
'year start date

currmonth = Month(Date)
If currmonth = 12 Then
currYr = currYr
Else
currYr = currYr - 1
End If
dtstr = &quot;01/&quot; & currmonth & &quot;/&quot; & currYr
dtFilter = Format(CDate(dtstr), &quot;dd/mm/yy&quot

Case 5
' year end date

Select Case currmonth
Case Is <= 3
currYr = currYr - 1
Case Else
currYr = currYr
End Select

currmonth = currmonth + 9

dtstr = &quot;01/&quot; & currmonth & &quot;/&quot; & currYr
dtFilter = Format(CDate(dtstr), &quot;dd/mm/yy&quot

Case Else
dtFilter = Date

End Select


End Function



You now have the ability to filter the dates - this could be done using access functions but this way at least you can see and troubleshoot a little easier

As for the summing mail me a sample and then I will know what I'm dealing with otherwise in about 10 mins I'm off to see the new Star Trek flick so I'll contact you later

regards
jo

 

[JoanneM]

To make things right and Dandy I have to put a ryder on this one and hold my hands up for a slap

I copied the first instruction but I didn't check that I had copied correctly

Last month & lastyr Had
Between dtFilter(2) And Date(3)

which should have been dtFilter(2) And dtFilter(3)
&
Between dtFilter(3) And Date(4)
which should have been dtFilter(3) And dtFilter(4)

I have corrected it below so my apologies.

For this month
date parameters in your query grid goes as follows

top line where the field name goes copy
ThisMonth:Fldname

& in the criteria line
Between dtFilter(1) And Date()

****************
For Last month
date parameters in your query grid goes as follows

top line where the field name goes copy
Lastmonth:Fldname

& in the criteria line
dtFilter(2) And dtFilter(3)



***************

For Year

date parameters in your query grid goes as follows

top line where the field name goes copy
LastYear:Fldname

& in the criteria line
Between dtFilter(3) And dtFilter(4)

 

[mru]

Thanks Jo

The help and advice I've recieved on this board has always been very helpful but never as thorough as this.

mru