query

Hudson40 · March 11th, 2005, 06:19 AM

Hi All

I need to run a query that calculates the summing of figures within a certain month. The query uses a table that has data for several hundread cost centres, the data being divided over a max of 12 months.I just need the present month. If I prompt the user with the question 'which month do you want' is there a quick way to use one query for the 12 possible months rather than firing off 12 different queries?

eg table

cost centre month1 month2 month3 month4 month5
00001 Â£300 Â£200 Â£150
00002 Â£259 Â£600 Â£160

etc

Cheers
Tony

mmcdonal · March 11th, 2005, 08:37 AM

Hi,

   Create one query that does all 12 months, and then use the user input to select only the month the user selects. I do this in one of my main forms where I have them select a month from one combo box, and then a year from another, and then click a button that calls a report based on a query that selects all data. I use this code on the button's On Click event:

'=====
    Dim stDocName As String
    Dim stMonth As String
    Dim stYear As String
    Dim stLinkCriteria As String

    stMonth = Me.cboMonth
    stYear = Me.cboYear

    stLinkCriteria = "[Month] = " & "'" & stMonth & "'" & " And " & "[Year] = " & "'" & stYear & "'"

    stDocName = "rptMyMonthYearReport"
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

'=====

   When I built the combo box, I typed in the month values rather than look them up so speed things along. I had to put a On No Data event on the form just in case there was no data for the month.

HTH

mmcdonal

mmcdonal · March 11th, 2005, 08:39 AM

BTW, the data in the table you have there is not normalized, but it should speed this process up iof this is just OLAP.

mmcdonal

Hudson40 · March 11th, 2005, 10:17 AM

thanks a mill

Cheers
Tony

BrianWren · March 11th, 2005, 11:14 AM

OLAP? What does this mean? (One Lap Around the Park?)

Iâve always wondered what Microsoft was thinking when they set up the wizards to create

Code:

    Dim stDocName      As String
    Dim stLinkCriteria As String

    stLinkCriteria = "..."
    stDocName = "rptMyMonthYearReport"
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

It is almost always the case that you use stDocName just one time, so I always change this to

Code:

    Dim stLinkCriteria As String

Code:

    stLinkCriteria = "..."
    DoCmd.OpenReport "rptMyMonthYearReport", acPreview, , stLinkCriteria

I often replace the LinkCriteria string with a literal as well, although in cases like this one, where the string is pretty long, I often retain the model of setting a string, then using it as an argument.

rjweers · March 12th, 2005, 12:32 PM

I agree with Brian about the excess code. OTOH, if they would just bother to use the excess code in the error trap...

Instead of:

    MsgBox Err.Description
    Resume Exit_Command7_Click

Why not:

    MsgBox "Open form : " & stDocName & " reported error: " & vbCrLf & Err.Description
    Resume Exit_Command7_Click

I suppose they left that out so if we don't want the extra code there isn't so much to clean up. But then again when you're debugging, you can change the value of stDocName without changing the code. Of course this makes more sense for the Link Criteria. So in that sense, the wizard helps.

OLAP, in case you really don't know, On Line Analytical Processing.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org