Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query help needed (2000)

    I have a report based on a query, based on a main table that holds data.
    In the query I have the following:
    DateSerial(Nz(Year([DateReceived]),1999),Nz(Month([DateReceived]),1),1) with the criteria DateSerial(Year(Date()),Month(Date())-1,1)
    The report pulls of alot of data and statistics relating to the previous month based on DateReceived.
    I've been asked to produce stats for months previous to the previous month (i.e. June) . As the report has alot of calculations in it (Around 50!) I figured it would be easier to change the query.
    I'm not too sure how to specify months in the about formula...........I basically need to pull of stats for March through to May 2004, seperately.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query help needed (2000)

    Do you want to create separate queries for (1) last month (2) next to last month (3) the month before that? Or do you want one query, with a prompt for "how many months ago"?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query help needed (2000)

    Hi Hans,
    I have literally just been informed that the long term plan for the database is that the user can produce reports for previous months. I'm not too sure of the best way to do this. Would the user have something similar to a menu with what you've suggested, or something like a list of dates displaying months and years?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query help needed (2000)

    If the user must be able to produce a report for any month in the past, I would create a form with two combo boxes (or list boxes) and a command button. One of the combo boxes would list the available years, the other the months of the year (this one would have two columns, the first column - hidden - containing the month numbers 1-12, the second column - displayed - containing the names January-December). The command button would open the report, after checking that something has been selected in both combo boxes.

    Say that the form is named frmSelectMonth, and that the combo boxes are named cboYear and cboMonth. The query that acts as record source for the report would not have the expression for the previous month in the criteria (since that is not needed now), but:

    DateSerial([Forms]![frmSelectMonth]![cboYear],[Forms]![frmSelectMonth]![cboMonth],1)

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query help needed (2000)

    Coding again...................oops (no surprise there). I've put the following coding behind the control.
    Comes up with Compile Error:Block If Without End If ,with highlighting the last line..........

    Private Sub CmdReport_Click()
    On Error GoTo ErrHandler

    If IsNull(Me.cboMonth) Then
    Me.cboMonth.SetFocus
    MsgBox "Please select a month.", vbInformation
    Exit Sub

    If IsNull(Me.cboYear) Then
    Me.cboYear.SetFocus
    MsgBox "Please select a year.", vbInformation
    Exit Sub

    End If

    DoCmd.OpenReport "ReportStats", acViewPreview
    Exit Sub

    ErrHandler:
    If Not (Err = 2501) Then ' Ignore Error 2501 = report canceled
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query help needed (2000)

    You're missing an End If after

    If IsNull(Me.cboMonth) Then
    Me.cboMonth.SetFocus
    MsgBox "Please select a month.", vbInformation
    Exit Sub

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query help needed (2000)

    Things never run smoothly.................ho hum.
    The coding is now fine, but it is producing the same stats regardless of month....................
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query help needed (2000)

    You didn't modify the query that is used in the calculations on the report. I have added the column and condition needed. (I also added a Default Value for the combo boxes on frmReport, so that the previous month is automatically filled in.)
    Attached Files Attached Files

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query help needed (2000)

    Thanks Hans,
    I see where I went wrong now..................
    Thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •