• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1568
  • Last Modified:

Access 2003 - VBA code for date criteria

Hi Experts
I am busy creating a Access 2003 DB. With a lot of help from The Experts off this site. We have manage to create a report section in my DB. With that said, we also manage to setup a date criteria for my reports using VBA. Which by the way, I dont know anything about, still in the process of teaching myself.
SO this is where you guys come in.
Just a bit of background:
With the help from a expert, we manage to setup one report with a date criteria, but I need to add more reports with date criterias. So this is where I am at.
Once again with the help from an expert, he has manage to to set it up, if one at to click on the report with the date criteria , a Report Period box appears and then one can put ones dates in that is needed for the report.  This is all done with VBA.
To my question:
Directly below is the code I am having problems with, as you can see (or may be not as I am not sure at all what I am doing in VBA) I am trying to add the second report, which is called:Const cReportTotalInstall = "Total Installations with Installation Date Report". ( The below code is not the original code , I have been "playing" with to be able to add the second report )
When I go in the live platform of my DB and go to the reports, the error that comes up for my new report is a pop up box stating the following:
Enter parameter value, and below that it says Salesdate.
Now if I make out the one line that says; sWhere = "SalesDate between #" & Format(Me.cboStartDate, "dd/mmm/yyyy") & "# AND #" & Format(Me.cboEndDate, "dd/mmm/yyyy") & "#"
, the report works fine, it is visa verse for the other report. I just cant seem to get them to work together.
I just think that I just have not added the new report properly to the below code.
If you guys could please just have a look at it and see where I am going wrong with it, I would appreciate it very much.
Below this code, I added all the code for that object, I dont know if it is needed or not.
Any help would be greatly appreciated.
Kind regards
mustekkzn
___________________________________________________________________________________________________________________________________________________________________

Function fnBuildWhere() As Boolean
 
    If Me.optGp = -1 Then
        sWhere = "InstallDate between '" & Format(Me.cboStartDate, "dd/mmm/yyyy") & "' AND '" & Format(Me.cboEndDate, "dd/mmm/yyyy") & "'"
        'you may need to change this to # depending on your pc settings
        sWhere = "SalesDate between #" & Format(Me.cboStartDate, "dd/mmm/yyyy") & "# AND #" & Format(Me.cboEndDate, "dd/mmm/yyyy") & "#"
        'sWhere = "SalesDate between #" & Me.cboStartDate & "# AND #" & Me.cboEndDate & "#"
    Else
        sWhere = ""
    End If
    Debug.Print "swhere: " & sWhere


End Function
____________________________________________________________________________________________________________________________________________

Option Compare Database
Option Explicit
Dim sReport As String 'module level
Dim sWhere As String
Const cReportTotalInstall = "Total Installations with Installation Date Report"
Const cReportTotalSales = "Total Sales with Sales Date Report"
_____________________________________________________________________________

Function fnOpenReports() As Boolean

   
    'Purpose: function to open reports replacing original macro
On Error GoTo errH
    sReport = Me.lstReports
    'add [ ] to name
    'sReport = "[" & sReport & "]"
    'check if report has a date range
    'Total Sales with Sales Date Report
    Select Case sReport
        Case cReportTotalSales
            If fnInvalidDate = True Then
                Exit Function
            End If
            fnBuildWhere
            If sWhere <> "" Then
                DoCmd.OpenReport sReport, acViewPreview, , sWhere
            Else
                DoCmd.OpenReport sReport, acViewPreview
            End If
        Case cReportTotalInstall    'your new report
          'assuming fnInvalidDate is appropriate for this report (or modify it accordingly)
            If fnInvalidDate = True Then
                Exit Function
            End If
            'assuming fnBuildWhere is appropriate for this report (or modify it accordingly)
            fnBuildWhere
            If sWhere <> "" Then
               DoCmd.OpenReport sReport, acViewPreview, , sWhere
            Else
                DoCmd.OpenReport sReport, acViewPreview
            End If
        Case Else
            DoCmd.OpenReport sReport, acViewPreview
    End Select

    Exit Function

errH:
    Beep
    MsgBox Err & " " & Err.Description
   

End Function
_______________________________________________________________________________

Function fnInvalidDate() As Boolean
    'Frank Teng
    '4/10/07
    'Purpose: check enddate after startdate
    If Me.optGp = 0 Then
        Exit Function
    End If
    If Nz(Me.cboStartDate, Now() + 1) > Nz(Me.cboEndDate, Now) Then
        Beep
        MsgBox "Your start date is after your end date or you have a blank date! Must enter again", vbExclamation
        fnInvalidDate = True
    Else
        fnInvalidDate = False
    End If

End Function
_______________________________________________________________________________

Function fnShowDateControls() As Boolean
   
    'currently only valid for one report, Total Sales with Sales Date Report
    'expand this list if you create more reports with date fields you want as filters
    Dim blnX As Boolean
    sReport = Nz(Me.lstReports, "")
    Select Case sReport
        Case cReportTotalSales
            blnX = True
        Case cReportTotalInstall
            blnX = True
        Case Else
            blnX = False
    End Select
   
    With Me.optGp
        .Visible = blnX
        If .Value = -1 And blnX Then
            Me.cboStartDate.Visible = True
            Me.cboEndDate.Visible = True
        Else
            Me.cboStartDate.Visible = False
            Me.cboEndDate.Visible = False
        End If
    End With
    fnShowDateControls = blnX
       
End Function
_______________________________________________________________________________

Function fnBuildWhere() As Boolean
   
   
    If Me.optGp = -1 Then
        sWhere = "InstallDate between '" & Format(Me.cboStartDate, "dd/mmm/yyyy") & "' AND '" & Format(Me.cboEndDate, "dd/mmm/yyyy") & "'"
        'you may need to change this to # depending on your pc settings
        sWhere = "SalesDate between #" & Format(Me.cboStartDate, "dd/mmm/yyyy") & "# AND #" & Format(Me.cboEndDate, "dd/mmm/yyyy") & "#"
        'sWhere = "SalesDate between #" & Me.cboStartDate & "# AND #" & Me.cboEndDate & "#"
    Else
        sWhere = ""
    End If
    Debug.Print "swhere: " & sWhere


End Function
_______________________________________________________________________________

Private Sub cboEndDate_BeforeUpdate(Cancel As Integer)
    'Cancel = fnInvalidDate
    'fnBuildWhere
End Sub
_______________________________________________________________________________

Private Sub cboStartDate_BeforeUpdate(Cancel As Integer)
    'Cancel = fnInvalidDate
    'fnBuildWhere
End Sub
_______________________________________________________________________________

Private Sub cmdOpenReport_Click()
    fnOpenReports
End Sub
_______________________________________________________________________________

Private Sub EditReportList_Click()

End Sub
_______________________________________________________________________________

Private Sub Form_Load()
    fnShowDateControls
End Sub
_______________________________________________________________________________

Private Sub lstReports_Click()
    fnShowDateControls
End Sub
_______________________________________________________________________________

Private Sub optGp_Click()
    Me.cboEndDate.Visible = Me.optGp
    Me.cboStartDate.Visible = Me.optGp
   
End Sub


0
mustekkzn
Asked:
mustekkzn
  • 12
  • 8
  • 4
2 Solutions
 
MikeTooleCommented:
<       sWhere = "InstallDate between '" & Format(Me.cboStartDate, "dd/mmm/yyyy") & "' AND '" & Format(Me.cboEndDate, "dd/mmm/yyyy") & "'"
        'you may need to change this to # depending on your pc settings
        sWhere = "SalesDate between #" & Format(Me.cboStartDate, "dd/mmm/yyyy") & "# AND #" & Format(Me.cboEndDate, "dd/mmm/yyyy") & "#">

This will always result in the same string in sWhere - the second assignment statement overwrites the string.
You need to add logic to choose the where clause based on the selected report.
0
 
mustekkznAuthor Commented:
Hi MikeToole
As to your above comment stating "You need to add logic to choose the where clause based on the selected report." where would I find this and what do I need to do to fix this?
Also, I just thought I would let you know that I am very new to VBA and dont know much.
Thanks so much for your post, its much appreciated.
mustekkzn
0
 
MikeTooleCommented:
You need the same logic to choose which where statement as you use to choose which OpenReport statement. It would be tedious to do with the current structure of your code, so I would recommend you restructure as below. This uses the ListIndex property of your listbox which tells you which row is selected (0 for the first row, 1 for the second, etc.). That means that the the listbox entries don't need to exactly match your report names - you can make them anything you like. I've assumed that the Total installations report is the first enty in the listbox and Total Sales is the second one. Change it round if I have that wrong. The 'Where' prameter in the OpenReport command will sometimes be blank but, as far as I can remember, that is just the same as leaving it out of the command altogether.

Const cReportTotalInstall = "[Total Installations with Installation Date Report]"
Const cReportTotalSales = "[Total Sales with Sales Date Report]"


Function fnOpenReports() As Boolean
Dim intReportID as integer
On Error GoTo errH
     If fnInvalidDate = True Then
         Exit Function
     End If
     intReportID = Me.lstReports.ListIndex
     DoCmd.OpenReport fnReportName(intReportID), acViewPreview, , fnWhere(intReportID)
    Exit Function
errH:
    Beep
    MsgBox Err & " " & Err.Description
End Function

Function fnReportName(ReportID as Integer) As String
    Select Case ReportID
        Case 0
            fnReportName = cReportTotalInstall    
        Case 1
            fnReportName = cReportTotalSales
    End Select
End Function

Function fnWhere(ReportID as Integer) As String
    If Me.optGp = -1 Then Exit Function

    Select Case ReportID
        Case 0
             fnWhere = "InstallDate between '" & Format(Me.cboStartDate, "dd/mmm/yyyy") & "' AND '" & Format(Me.cboEndDate, "dd/mmm/yyyy") & "'"
        Case 1
             fnWhere = "SalesDate between #" & Format(Me.cboStartDate, "dd/mmm/yyyy") & "# AND #" & Format(Me.cboEndDate, "dd/mmm/yyyy") & "#"
    End Select
End Function



0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
mustekkznAuthor Commented:
Hi MikeToole
Thanks for the above code. I am not sure what it actually is suppose to do, but it has given me a few other problems now.
1. The Report Period Date field appears on all my reports now. Even reports where I dont even need date criterias.
2. Open Report button I have to click on to open the report seems to not be working. But I can double click on the report and then it opens.
3. Also, the date criteria fields does not seem to line up with report that is given.
Just a few small problems I have picked up to start with.

I am not sure if I handled the above correctly. All I did was copy and paste the code into the VB code screen, replacing the old code. Is this correct?

I will attach a print screen of the actual report field, may be that will help you a bit?
Thanks once again for all your help.
Kind regards
mustekkzn
0
 
mustekkznAuthor Commented:
Below is the link to the print screen I made.
Thanks
mustekkzn
https://filedb.experts-exchange.com/incoming/ee-stuff/5082-IQuoteDB--1-.zip 
0
 
MikeTooleCommented:
mustekkzn
I'll have a look at it this weekend.
0
 
mustekkznAuthor Commented:
Hi MikeToole
Thanks so much, I'll appreciate it very much.
Not to sure where in the world you are, but enjoy the world cup rugby final.
Have a good one.
regards
mustekkzn
0
 
frankyteeCommented:
mustekkkzn, mike has the right idea. when i wrote the original code it was meant for that single report which needed the date criteria. now that you want to add more reports, you can either add cases for the new reports in the various functions or restructure it as mike said to make it more elegant.
(bear in mind sometimes when i code in EE its with a kebab in one hand and a coke in the other so with time on your side there can be ways of improving it etc)
with the "where" criteria, you can simply add a dummy criteria (that will not filter out any records) for your other reports where there is no filter , that way all records will show for that report

Function fnWhere(ReportID as Integer) As String
    If Me.optGp = -1 Then
            fnWhere = "1 = 1"   'always true so in affect no records filtered out
       Exit Function
    end if
    Select Case ReportID
        Case 0
             fnWhere = "InstallDate between '" & Format(Me.cboStartDate, "dd/mmm/yyyy") & "' AND '" & Format(Me.cboEndDate, "dd/mmm/yyyy") & "'"
        Case 1
             fnWhere = "SalesDate between #" & Format(Me.cboStartDate, "dd/mmm/yyyy") & "# AND #" & Format(Me.cboEndDate, "dd/mmm/yyyy") & "#"

        case else 'no other report with criteria
             fnWhere = "1 = 1"
    End Select

however as you add more reports you need to expand fnWhere and also you may need to change the rowsources of the drop boxes etc

also bear in mind that since you are new to VBA, what you are trying to do (create a report menu with dynamic controls to cater for multiple scenarios and filters etc) is beyond most beginners, and over the years i've been contracting and redeveloping apps that other contractors wrote, it takes experienced programmers to do it well. so dont be too frustated if you hit obstacles along the way.
0
 
mustekkznAuthor Commented:
Hi Experts
I must be totally honest with you guys. I REALLY like the idea of when I only select the report with the
date criteria, the Report Period appears.
Before I pursue MikeTooles way where the Report Period is visible for all reports, I just would like to confirm
with you if it is in anyway possible to edit or change the first code I received, from Frankytee, to be able to add more reports with date criterias?
Is it such a big problem to get around error?
If it is more of a challenging, I am up for it. Just with the code I have received from both of you, I have sort of manage to work out what the code is actually doing,
which I think is a good start for me, but I have to be honest, it is going to take me a long to time to write my own code.

Thanks once again for your time with this.

Regards
mustekkzn
0
 
frankyteeCommented:
>I must be totally honest with you guys. I REALLY like the idea of when I only select the report with the
date criteria, the Report Period appears.
yes, you just need to expand the code that makes th report period controls appear (check the click or after update event of your list control which calls fnShowDateControls)
something like:
.....
 Select Case sReport
        Case cReportTotalSales
            blnX = True
        Case cReportTotalInstall
            blnX = True
        Case cWhateverReport3ThatHasDateCriteria   'whatever report defined by that constant
            blnX = True
        Case cWhateverReport4ThatHasDateCriteria   'whatever report defined by that constant
            blnX = True
...etc
        Case Else
            blnX = False
    End Select
....

>with you if it is in anyway possible to edit or change the first code I received, from Frankytee, to be able to add more reports with date criterias?
yes, you need to update functions fnBuildWhere or fnWhere (whichever you are using) to handle the new report by adding another CASE statement for that report.

if you expand the "where" function to handle the where clause depending on each report, then you can simplify the fnOpenReports function as Mike's eg shows.
you wouldn't need to handle each report seperately like in my sample code in fnOpenReports as I did not know if you had modified the "where" function so I purposely added redundancy to be conservative in the ongoing discussions of your previous question.


0
 
mustekkznAuthor Commented:
Hi Frankytee
Thanks for above post.
I am just a bit confused at the moment to which code to use. So I thought to give an update to where I am at the moment regarding the code. Which you will find below.

With the code I got from you and MikeToole's code,  I have manage to resolve the following problem I had:" I REALLY like the idea of when I only select the report with the
date criteria, the Report Period appears. " 
Thanks so much for that.

I am having a few small problems with the code I current have:
1. The date criteria is not work like it should, I put in a date criteria and it juat keeps on showing all the information. And it is on both reports with date criteria's
2. For the life of me I just cant get the Open Report button to work like it should. At the moment it is say after selecting a report and click on Open Report: 2497 The action or method required a Report Name argument.

Thanks so much.
Regards
mustekkzn

___________________________________________________________________________________
Option Compare Database
Option Explicit
Dim sReport As String 'module level
Dim sWhere As String
Const cReportTotalInstall = "Total Installations with Installation Date Report"
Const cReportTotalSales = "Total Sales with Sales Date Report"
___________________________________________________________________________________
Function fnOpenReports() As Boolean
Dim intReportID As Integer
On Error GoTo errH
     If fnInvalidDate = True Then
         Exit Function
     End If
     intReportID = Me.lstReports.ListIndex
     DoCmd.OpenReport fnReportName(intReportID), acViewPreview, , fnWhere(intReportID)
    Exit Function
errH:
    Beep
    MsgBox Err & " " & Err.Description

End Function
___________________________________________________________________________________
Function fnInvalidDate() As Boolean
    'Frank Teng
    '4/10/07
    'Purpose: check enddate after startdate
    If Me.optGp = 0 Then
        Exit Function
    End If
    If Nz(Me.cboStartDate, Now() + 1) > Nz(Me.cboEndDate, Now) Then
        Beep
        MsgBox "Your start date is after your end date or you have a blank date! Must enter again", vbExclamation
        fnInvalidDate = True
    Else
        fnInvalidDate = False
    End If

End Function
___________________________________________________________________________________
Function fnShowDateControls() As Boolean
    Dim blnX As Boolean
    sReport = Nz(Me.lstReports, "")
    Select Case sReport
        Case cReportTotalSales
            blnX = True
        Case cReportTotalInstall
            blnX = True
        Case Else
            blnX = False
    End Select
   
    With Me.optGp
        .Visible = blnX
        If .Value = -1 And blnX Then
            Me.cboStartDate.Visible = True
            Me.cboEndDate.Visible = True
        Else
            Me.cboStartDate.Visible = False
            Me.cboEndDate.Visible = False
        End If
    End With
    fnShowDateControls = blnX
       
End Function
___________________________________________________________________________________
Function fnReportName(ReportID As Integer) As String
    Select Case ReportID
        Case 0
            fnReportName = cReportTotalInstall
        Case 1
            fnReportName = cReportTotalSales
    End Select
End Function
___________________________________________________________________________________
Function fnWhere(ReportID As Integer) As String
    If Me.optGp = -1 Then Exit Function

    Select Case ReportID
        Case 0
             fnWhere = "InstallDate between '" & Format(Me.cboStartDate, "dd/mmm/yyyy") & "' AND '" & Format(Me.cboEndDate, "dd/mmm/yyyy") & "'"
        Case 1
             fnWhere = "SalesDate between #" & Format(Me.cboStartDate, "dd/mmm/yyyy") & "# AND #" & Format(Me.cboEndDate, "dd/mmm/yyyy") & "#"
    End Select
End Function
___________________________________________________________________________________
Private Sub cboEndDate_BeforeUpdate(Cancel As Integer)
    'Cancel = fnInvalidDate
    'fnBuildWhere
End Sub
___________________________________________________________________________________
Private Sub cboStartDate_BeforeUpdate(Cancel As Integer)
    'Cancel = fnInvalidDate
    'fnBuildWhere
End Sub
___________________________________________________________________________________
Private Sub cmdOpenReport_Click()
    fnOpenReports
End Sub
___________________________________________________________________________________
Private Sub EditReportList_Click()

End Sub
___________________________________________________________________________________
Private Sub Form_Load()
    fnShowDateControls
End Sub
___________________________________________________________________________________
Private Sub lstReports_Click()
    fnShowDateControls
End Sub
___________________________________________________________________________________
Private Sub optGp_Click()
    Me.cboEndDate.Visible = Me.optGp
    Me.cboStartDate.Visible = Me.optGp
   
End Sub



0
 
frankyteeCommented:
its a bit hard to explain in more detail. compact, zip and upload your file to EE and post the link and i'll recode it for you. if the EE upload is still not working then email to my yahoo.
0
 
mustekkznAuthor Commented:
Hi Frankytee
Below is the link to the DB.
Thanks so much. I am sure if you had to fix it and send it back, I will work out what is plotting?
regards
mustekkzn

https://filedb.experts-exchange.com/incoming/ee-stuff/5116-IQuoteDB_20071018-including-aut.zip 
0
 
frankyteeCommented:
updated db at
https://filedb.experts-exchange.com/incoming/ee-stuff/5118-IQuoteDB_20071021.zip

i had to remove references to dlls
controls 1.0 type library
outlook view control
as i dont have them on my pc. you need to add them back in
i'm logging off now so let me know you how go.
0
 
MikeTooleCommented:
mustekkzn
Pity about the Rugby. :-(

My first look at this was to address the immediate problem of the wrong Where clause. The simplification I proposed was necessary, but wouldn't work with a reports list box that could change under user control. Here's another rework of the code in your last post. It aims to simplify by reducing the number of subroutines used. The logic to set the visibility of the option group is put in the list box click event and the logic controling the display of the start/end combos is put in the option group click event. Square brackets, [],  are ignored altogether since they're not needed in the DoCmd.OpenReport. And, as far as I can see, a blank Where parameter in the OpenReport is the same as leaving it out altogether, so for the reports that aren't qualified by date, the code just supplies a blank string for the Where.

I can't guarantee that the code will work perfectly - we have to make assumptions when working from a distance - but please try it and, if there's a problem, try to analyze the reason for yourself, it's a learning process! The best help in this may be to put a Break Point on the line
     Private Sub cmdOpenReport_Click()
and use the F8 key to step through the code line by line. You can see then exacly what's happening - and it would have answered your original query in this question about why the where clause went wrong.

Hope this helps,
Mike


Option Compare Database
Option Explicit
Const cReportTotalInstall = "Total Installations with Installation Date Report"
Const cReportTotalSales = "Total Sales with Sales Date Report"

Function fnInvalidDate() As Boolean
    'Frank Teng
    '4/10/07
    'Purpose: check enddate after startdate
    If Me.optGp = 0 Then
        Exit Function
    End If
    If Nz(Me.cboStartdate, Now() + 1) > Nz(Me.cboEndDate, Now) Then
        Beep
        MsgBox "Your start date is after your end date or you have a blank date! Must enter again", vbExclamation
        fnInvalidDate = True
    Else
        fnInvalidDate = False
    End If

End Function

Function fnWhere() As String
    If Me.optGp.Visible = False Then Exit Function
    Dim strFieldName As String
    Select Case Nz(Me.lstReports)
        Case cReportTotalInstall
             strFieldName = "InstallDate"
        Case cReportTotalSales
             strFieldName = "SalesDate"
        Case Else
           Exit Function
    End Select
    fnWhere = strFieldName & " between '" & Format(Me.cboStartdate, "dd/mmm/yyyy") & "' AND '" & Format(Me.cboEndDate, "dd/mmm/yyyy") & "'"
End Function

Private Sub cmdOpenReport_Click()
On Error GoTo errH
     If fnInvalidDate Then Exit Sub
     If Nz(Me.lstReports) = "" Then Exit Sub
     DoCmd.OpenReport Me.lstReports, acViewPreview, , fnWhere()
    Exit Sub
errH:
    Beep
    MsgBox Err & " " & Err.Description
End Sub
Private Sub EditReportList_Click()
'
End Sub
Private Sub Form_Load()
    lstReports_Click
End Sub
Private Sub lstReports_Click()
    Select Case Nz(Me.lstReports)
        Case cReportTotalSales, cReportTotalInstall
            optGp.Visible = True
        Case Else
            optGp.Visible = False
    End Select
    optGp_Click
End Sub
Private Sub optGp_Click()
    Me.cboEndDate.Visible = Me.optGp And Me.optGp.Visible
    Me.cboStartdate.Visible = Me.optGp And Me.optGp.Visible
End Sub
0
 
frankyteeCommented:
mustekkzn, so its all working fine now?
did the code changes make sense to you?
0
 
mustekkznAuthor Commented:
Hi guys
Thanks so much for both your above posts. They have helped me so much.
I will most definitly go and sit and work through the code. After just looking at above code and Frankytee's code, I could already see a few places where I went wrong. For example;
like below, I never added the other reports for the fnOpenReports function to work 100%. Which reading through old posts, now I see what you guys were saying about the Case statement.
________________________________________________________________________________
Function fnReportName(ReportID As Integer) As String
   
    Select Case ReportID
        Case 0   'Case 0 is wrong
            fnReportName = cReportClientDetails
        Case 1   'Case 0 is wrong
            fnReportName = cReportFranchiseSalesAgent
        Case 2   'case 1 is wrong
            fnReportName = cReportClientsAddress
        Case 3   'Case 0 is wrong
            fnReportName = cReportTotalInstall
        Case 4   'case 1 is wrong
            fnReportName = cReportTotalSales
    End Select
End Function
________________________________________________________________________________

I would like to close off this question, if you guys dont mind, but I will be posting more question on the above code, as I am working through it. Just to make sure I do understand now.
Regarding the points, I would like to spilt them up, as I think that both of you guys have helped me tremendously.
Please, I could ask that you guys have a look out for any future posts that I will be making.
Thanks once again
Regards
mustekkzn.

Below is the code I am currently using, I thought I would be handy for someone that would need it.
 
____________________________________________________________________________
Option Compare Database
Option Explicit
Dim sReport As String 'module level
Dim sWhere As String
Const cReportClientDetails = "Clients Contact Details Report"
Const cReportFranchiseSalesAgent = "Franchise and Sales Agent Report"
Const cReportClientsAddress = "Clients Address Details Report"
Const cReportTotalInstall = "Total Installations with Installation Date Report"
Const cReportTotalSales = "Total Sales with Sales Date Report"

_________________________________________________________________________________
Function fnOpenReports() As Boolean
Dim intReportID As Integer
On Error GoTo errH
     If fnInvalidDate = True Then
         Exit Function
     End If
     intReportID = Me.lstReports.ListIndex
         
     DoCmd.OpenReport fnReportName(intReportID), acViewPreview, , fnWhere(intReportID)
    Exit Function
errH:
    Beep
    MsgBox Err & " " & Err.Description
   

End Function

_________________________________________________________________________________

Function fnInvalidDate() As Boolean
   
    If Me.optGp = 0 Then
        Exit Function
    End If
    If Nz(Me.cboStartDate, Now() + 1) > Nz(Me.cboEndDate, Now) Then
        Beep
        MsgBox "Your start date is after your end date or you have a blank date! Must enter again", vbExclamation
        fnInvalidDate = True
    Else
        fnInvalidDate = False
    End If

End Function

_________________________________________________________________________________

Function fnShowDateControls() As Boolean
       
    Dim blnX As Boolean
   
   
    sReport = Nz(Me.lstReports, "")
    Select Case sReport
        Case cReportTotalSales
            blnX = True
            Me.cboStartDate.RowSource = "qcboSalesDate"
            Me.cboEndDate.RowSource = "qcboSalesDate"
        Case cReportTotalInstall
            blnX = True
            Me.cboStartDate.RowSource = "qcboInstallDate"
            Me.cboEndDate.RowSource = "qcboInstallDate"
        Case Else
            blnX = False
    End Select
   
   
   
    With Me.optGp
        .Visible = blnX
        If .Value = -1 And blnX Then
            Me.cboStartDate.Visible = True
            Me.cboEndDate.Visible = True
        Else
            Me.cboStartDate.Visible = False
            Me.cboEndDate.Visible = False
        End If
    End With
    fnShowDateControls = blnX
       
End Function

_________________________________________________________________________________

Function fnReportName(ReportID As Integer) As String
    '21/10/07 FT: see comments below
    Select Case ReportID
        Case 0   'Case 0 is wrong
            fnReportName = cReportClientDetails
        Case 1   'Case 0 is wrong
            fnReportName = cReportFranchiseSalesAgent
        Case 2   'case 1 is wrong
            fnReportName = cReportClientsAddress
        Case 3   'Case 0 is wrong
            fnReportName = cReportTotalInstall
        Case 4   'case 1 is wrong
            fnReportName = cReportTotalSales
    End Select
End Function

_________________________________________________________________________________

Function fnWhere(ReportID As Integer) As String
    '21/10/07 FT: mustek, to "comment out" code, add a single quote ' at the start of the line
    '---------------------------------------------------------------------
    'If Me.optGp = -1 Then Exit Function
    '---------------------------------------------------------------------
    'FT: above is wrong, -1 means date criteria was selected
    If Me.optGp = 0 Then
        fnWhere = "1 = 1"   'dummy criteria to ensure all records selected
        Exit Function
    End If
   
    'reportID 0 and 1 are incorrect, it should be below
    Select Case ReportID
        Case 3  'case 0 was wrong
             'fnWhere = "InstallDate between '" & Format(Me.cboStartDate, "dd/mmm/yyyy") & "' AND '" & Format(Me.cboEndDate, "dd/mmm/yyyy") & "'"
             'FT: replace date wrappers ' with # for my pc, you may need to review this on your pc
             fnWhere = "InstallDate between #" & Format(Me.cboStartDate, "dd/mmm/yyyy") & "# AND #" & Format(Me.cboEndDate, "dd/mmm/yyyy") & "#"
     
        Case 4 'case 1 was wrong
             fnWhere = "SalesDate between #" & Format(Me.cboStartDate, "dd/mmm/yyyy") & "# AND #" & Format(Me.cboEndDate, "dd/mmm/yyyy") & "#"
       
        Case Else
            fnWhere = "1 = 1"
    End Select
   
    Debug.Print "fnwhere for reportid " & ReportID & ": " & fnWhere
End Function

_________________________________________________________________________________

Private Sub cmdOpenReport_Click()
    fnOpenReports
End Sub

_________________________________________________________________________________

Private Sub Form_Load()
    fnShowDateControls
End Sub

_________________________________________________________________________________

Private Sub lstReports_BeforeUpdate(Cancel As Integer)

End Sub

_________________________________________________________________________________

Private Sub lstReports_Click()
    fnShowDateControls
End Sub

Private Sub optGp_Click()
    Me.cboEndDate.Visible = Me.optGp
    Me.cboStartDate.Visible = Me.optGp
   
End Sub

_________________________________________________________________________________


Private Sub Command22_Click()
On Error GoTo Err_Command22_Click

    Dim stDocName As String

    stDocName = "Total Installations with Installation Date Report"
    DoCmd.OpenReport stDocName, acPreview

Exit_Command22_Click:
    Exit Sub

Err_Command22_Click:
    MsgBox Err.Description
    Resume Exit_Command22_Click
   
End Sub
0
 
mustekkznAuthor Commented:
Hi frankytee.
It is all working 100%. I wound say that the code is making 100% sense as yet, but I can most definitly see where I have been going wrong.
I need to sit down with code and understand it fully.
Please would you look out for any future posts I will be making?
Thanks once again.
regards
mustekkzn
0
 
frankyteeCommented:
note that in the modified code i uploaded, i kept the intReportID that mike introduced since you used it in the version you uploaded, ie:
intReportID = Me.lstReports.ListIndex

just remember as mike pointed out, this refers to the order of the reports that your current listbox of reports appears. if you add more reports, and the query that drives your listbox changes the order of your existing reports, then you would need to change your code as report 3 may no longer be cReportTotalInstall, report 4 may no longer be cReportTotalSales etc. they could be 5 and 6 etc
moving forward, you will be better off to modify the code to pass the report name itself (which wont change unless you actually rename the report) rather than the reportID. that way you wont need to change the code for existing reports when you add new reports.
0
 
frankyteeCommented:
>Please would you look out for any future posts I will be making?

yep, not a problem but its always best to post them to EE that way you can get another perspective from someone else like mike. dont restrict your learning to just my coding or habits.
cheers and congrats on the rugby (i don't really follow it though but i was cheering for france)
0
 
mustekkznAuthor Commented:
Hi Frankytee
I will post them to EE, but I must say I like your way of thinking. Very much. Like with the date criteria Report Periods. I was thinking the same way, but before I could even start posting, you jumped a head of me. Thanks for that!
With that said, I will never disregard anyone else's posts, cause they can just as well bring in other ideas, like MikeToole did.

Regarding the rugby; GO BOKKE!!! (Sorry, got to get that out of my system first)
Yip my team won!! LOL.
Thanks once again
Regards
Mustekkzn
0
 
frankyteeCommented:
thanks, you're welcome.
i'm more of a tennis fan, and the best player in the world is also south african (or at least half SA) so you guys are having a good run.
0
 
mustekkznAuthor Commented:
O, okay, I use to follow tennis when Pete Sampras was number one.
I must say I did not know that Roger Federer was half South African, well what do you know. I am a BIG Roger Federer fan too now. LOL
Ye, we are having a good run at the mo, until politics gets in the way again. Which is very sad.
Anyway, chat soon again and take care
mustekkzn
0
 
mustekkznAuthor Commented:
Hi Frankytee
I just posted a new question, I just thougt to drop you a line.
Thanks
mustekkzn

http://www.experts-exchange.com/?qid=22911086
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 12
  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now