Selecting Report Criteria in Access 2000

[jamey84]

I have a database that houses project information. Each project has equipment that is associated with the project. My main goal is that I want to be able to do some reporting on the quantities of equipment, for certain time spans depending upon in service dates. (ie. I want to show the quantity of widgets going into service between 6/1/2005 and 6/1/2006).

This reporting is going to be used by people not familiar w/ Access, so I was thinking that I could create a form that would display the different types of equipment in a drop down box, and then give text boxes where you could put in the date range. The user would then select the type of equipment, and date range.

The problem that I am running into is that I am unsure how I can use this information that I select/input as part of a query to extract the info from the database, and in the end, display that data selected in a report. Any ideas, as my SQL isn't what it once was? Maybe there is a better way to do this that I am not thinking of? Thanks much in advance.

jamey84

 

[opvk]

Add 2seperate columns in your table where the equipment datas are stored,(for entering the dates).For example,from your example,(ie. I want to show the quantity of widgets going into service between 6/1/2005 and 6/1/2006).

1st column can contain from date 6/1/2005 and 2nd column can contain 6/1/2006.
write a selct query with the dates as parameters. create a report based on this select query.

Hope this works

 

[OnTheFly]

I am assuming you have a field in the table that had the service date and an EquipID for the Equipment type. Here is how I would do this.

Create the report to display the information you want using the table or a querie that pulls all the information including service date and Equipment Type (even if you do not display these fields in the report they must be part of the data set.

Then in the OnClick even of a print button on your form where they make their equip type choice and enter the dates you would put code like this:

Dim strFilter as String
strFilter="EquipID=" & Me.cboEquipType & " AND ServiceDate Between #" & NZ(Me.txtStartDate,Date()) & "# AND #" & NZ(Me.txtEndDate,Date()) & "#"

Docmd.OpenReport "rptViewEquipment",acViewPreview,,strFilter


...where cboEquipType is the combo box they choose their equipment type from and it is bound to the EquipID, txtStartDate is where they enter the Start Date, txtEndDate is where they enter the End Date and rptViewEquipment is the name of the report your created to display the information.

This will open the report using the information entered in the form to filter it to show only those record.

Hope this helps.

OnTheFly

 

[ptpaton]

Jamey84,

The above two suggestions are great, but the best way to perform this is in a query.
1. Start a new select query
2. Add your equipment table
3. Bring down each field that you want displayed in your report. I'll include equipment and service date.
4. Enter the following in the criteria for each respective field.
Equipment: Forms!YourForm!DropDownBoxName
ServiceDate: Between [Enter Starting Date (mm/dd/yy)] AND [Enter Ending Date (mm/dd/yy)]

The criteria for the service date is called a parameter. At run time, the user will be prompted to enter values for the starting date and ending dates. Whatever is typed between the brackets above will be displayed to the user. Whatever is entered by the user at run time will be used as criteria for the query.

Using this criteria will limit the records returned to:
1. Only the equipment selected in the drop down box and
2. Only service dates between the two ranges.

Base the end report on this query, and set up a command box that the user clicks after selecting an equipment type. Or, open the report from the AfterUpdate property of the dropddown box.

Either way, the report will open with only the records you need. This really is the simplest and fastest way to do this.

Let me know if you need more help.



-Patrick

Nine times out of ten, the simplest solution is the best one.

 

[jamey84]

Thanks for all of the help....your ideas worked well. I appreciate it very much!

 

[jamey84]

Thanks again for the help with my first question.

Now I am trying to augment my in-service date. I am trying to predict when I need to purchase the equipment, by adding on a 30 day buffer, as well as a specific lead time(in days) that differs for each piece of equipment. I've been trying to use the DateAdd function, which easily adds the 30 days. Now I'm getting stuck on trying to add the Lead Time values. The values are stored in a table in my database, but it seems not to like it when I am referencing the table for the number to add. There is probably something that I am just overlooking, but if anyone can lend some expertise, I'd appreciate it. Thanks much!

jamey84

 

[OnTheFly]

How are you referencing the number from the table? From a Query? Can you give an example of the formula you are trying to use that is not working and the specifics of what Access does not like about it (the error or what it is doing).

Let us know and we will see if we can help.

Hope this helps.

OnTheFly

 

[jamey84]

Sure,

I also started a new thread on this question yesterday, because I thought it may be lost at the end of this topic. It's located at:

http://www.tek-tips.com/viewthread.cfm?qid=900811&page=1

Currently, I use a combo box to display the different types of equipment. I then have two text boxes, where you would enter the dates that I want to grab the quantity of equipment from(ie. in Q2, 2005, I will need to buy 35 boxes). For my calculations right now, I have another text box, where I am adding 30 days using the AddDate function. The name of the text box is txtBeginningDate+30, and the function I'm using is =DateAdd("d",-30,[Forms]![frm_Report_Criteria]![txtBeginningDate]).

I want to add the specific lead time value for each piece of equipment. The lead time values are stored in the same table as the type of equipment, called tbl_Item.

Right now, I have another text box where I am trying to add the lead times in. I've tried using this function into the box called txtBeginningDate+30+Lead, =DateAdd("d",[fld_Technical_Spec_Lead_Time],[Forms]![frm_Report_Criteria]![txtBeginningDate+30])

When I do this, the number that is being grabbed from my table is the first record in the table, in the fld_Technical_Spec_Lead_Time.

If there is anything else that I need to clarify, just let me know. I appreciate all the help. Thanks!

jamey84

 

[FancyPrairie]

Here's another way to do it and the code can be resused in other databases you may create in the future. The tag property and name of the controls is the key to making it work. To test it, create a new module, copy and paste the code below in the new module, create a form with 2 text boxes to represent your date range and 1 multi-select listbox that contains a list of your equipment. Then open the report like this:

Docmd.OpenReport "rptViewEquipment",acViewPreview,,BuildWhereClause(Me)


Note that the code doesn't care whether you have 0 pairs of textboxes (date ranges) or many. Nor does it care whether you have 0 multi-select listboxes or many. It will handle it all. Again, the key to making it work is setting the tag property correctly and naming the controls correctly.

For example, suppose you have 2 text boxes (named ServiceDate_BeginR and ServiceDate_EndR) and 1 multi-select list box (list of equipment).

Now suppose the user enters a 6/1/2005 for the begin date range and 6/1/2006 for the end date range. And also suppose the user selects EqupID 1 and EquipID 5 from the list box.
The following function will return your where clause as:

(Betweeen #6/1/2005# and #6/1/2006#) and (EquipID In(1,5))

The key to how this works is using a naming convention and using the Tag Property. The function below assumes that the text boxes that represent a pair of date ranges has a naming convention where the Base Names are the same but the suffix is either _BeginR (ServiceDate_BeginR) or _EndR (ServiceDate_EndR). The tag property of the textbox that represents the 1st date in the range (ServiceDate_BeginR) has the following format:
Where=YourTableName.YourDateFieldName,Date;

The only things you need to note in the multi-select listbox is the Tag property and the bound column. The format of the tag property should look like this:

Where=YourTableName.YourEquipmentIDFieldName,Number;

Where EquipID represents the bound column of the list box.

Option Compare Database
Option Explicit

    Dim mstrAnd As String
    Dim mstrFilter As String
    
Function BuildWhereClause(frm As Form) As String

'********************************
'*  Declaration Specifications  *
'********************************

    On Error GoTo ErrHandler

'****************
'*  Initialize  *
'****************

    mstrFilter = vbNullString
    mstrAnd = vbNullString
    
    BuildWhereClause_DateRange frm
    BuildWhereClause_ListBox frm
    
    If (InStr(1, mstrFilter, "!!!Error!!!") > 0) Then
        MsgBox "error"
        BuildWhereClause = "!!!ERROR!!!"
        Exit Function
    End If
    
    BuildWhereClause = mstrFilter
    
'********************
'*  Exit Procedure  *
'********************
        
ExitProcedure:

    Exit Function

'****************************
'*  Error Recovery Section  *
'****************************
        
ErrHandler:
        
    MsgBox Err.Number & vbCrLf & Err.Description, vbExclamation
    
    BuildWhereClause = "!!!ERROR!!!"
    
    Resume ExitProcedure

End Function


Function BuildWhereClause_DateRange(frm As Form)

'********************************
'*  Declaration Specifications  *
'********************************

    Dim ctl As Control
    Dim ctlEndR As Control
    
    Dim strField As String
    Dim strType As String
    
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    
    On Error GoTo ErrHandler

'*********************************************************
'*  Loop thru all controls on form to find list box(es)  *
'*********************************************************

    For Each ctl In frm.Controls
                
        If (ctl.ControlType = acTextBox) And (Right$(ctl.Name, 7) = "_BeginR") Then
                
        '*************************************************************************************************
        '*  Should this list box be processed?                                                           *
        '*  If so, then tag property contains the name of the table and field and the type of the field  *
        '*      (Structure of tag property:  Where=TableName.FieldName,DataType,    )                    *
        '*      NOTE that the code assumes the tag property is structured properly                       *
        '*************************************************************************************************
            
            If ((ctl.Enabled) And (Not ctl.Locked) And (InStr(ctl.Tag, "Where=") > 0)) Then
                
                If (IsNull(ctl)) Then GoTo 7000
                On Error Resume Next
                Set ctlEndR = frm(Left$(ctl.Name, Len(ctl.Name) - 7) & "_EndR")
                If (Err.Number = 2465) Then
                    Err.Clear
                    GoTo 7000
                End If
                If (IsNull(ctlEndR)) Then GoTo 7000
    
                On Error GoTo ErrHandler
                
                j = InStr(ctl.Tag, "Where=")
                k = InStr(j, ctl.Tag, ",")
                strField = Mid(ctl.Tag, j + 6, k - (j + 6))
                
                j = InStr(k + 1, ctl.Tag, ";")
                strType = Mid(ctl.Tag, k + 1, j - k - 1)
                
                mstrFilter = mstrFilter & mstrAnd & " (" & strField & " Between #" & ctl.Value & "# AND #" & ctlEndR.Value & "#) "
                mstrAnd = " AND "
                
            End If
        End If
7000:
    Next
    
'********************
'*  Exit Procedure  *
'********************
        
ExitProcedure:

    Exit Function

'****************************
'*  Error Recovery Section  *
'****************************
        
ErrHandler:
        
    MsgBox Err.Number & vbCrLf & Err.Description, vbExclamation
    
    mstrFilter = "!!!ERROR!!!"
    
    Resume ExitProcedure

End Function


Function BuildWhereClause_ListBox(frm)

'********************************
'*  Declaration Specifications  *
'********************************

    Dim ctl As Control
    
    Dim varItem As Variant
    
    Dim strField As String
    Dim strType As String
    
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    
    On Error GoTo ErrHandler

'*********************************************************
'*  Loop thru all controls on form to find list box(es)  *
'*********************************************************

    For Each ctl In frm.Controls
                
        If (ctl.ControlType = acListBox) Then
                
        '*************************************************************************************************
        '*  Should this list box be processed?                                                           *
        '*  If so, then tag property contains the name of the table and field and the type of the field  *
        '*      (Structure of tag property:  Where=TableName.FieldName,DataType;    )                    *
        '*      NOTE that the code assumes the tag property is structured properly                       *
        '*************************************************************************************************
            
            If ((ctl.Enabled) And (Not ctl.Locked) And (ctl.ItemsSelected.Count > 0) And (InStr(ctl.Tag, "Where=") > 0)) Then
                
                j = InStr(ctl.Tag, "Where=")
                k = InStr(j, ctl.Tag, ",")
                strField = Mid(ctl.Tag, j + 6, k - (j + 6))
                
                j = InStr(k + 1, ctl.Tag, ";")
                strType = Mid(ctl.Tag, k + 1, j - k - 1)
                
                mstrFilter = mstrFilter & mstrAnd & " (" & strField & " In ("
                
        '******************************************
        '*  Loop thru items selected in list box  *
        '******************************************
        
                For Each varItem In ctl.ItemsSelected
                    
                    If (strType = "String") Then
                        mstrFilter = mstrFilter & "'" & ctl.Column(ctl.BoundColumn - 1, varItem) & "', "
                    ElseIf (strType = "Number") Then
                        mstrFilter = mstrFilter & ctl.Column(ctl.BoundColumn - 1, varItem) & ", "
                    End If
                    
                Next varItem
    
                mstrFilter = Mid(mstrFilter, 1, Len(mstrFilter) - 2) & ")) "
                mstrAnd = " AND "
                
            End If
        End If
    
    Next
    
'********************
'*  Exit Procedure  *
'********************
        
ExitProcedure:

    Exit Function

'****************************
'*  Error Recovery Section  *
'****************************
        
ErrHandler:
        
    MsgBox Err.Number & vbCrLf & Err.Description, vbExclamation
    
    mstrFilter = "!!!ERROR!!!"
    
    Resume ExitProcedure

End Function

 

[ptpaton]

Jamey,

You don't need to use DateAdd to increment dates. All you have to do is add a number to your field, and Access will know to add that many days to it.

So:

[txtBeginningDate] + 30

Will add 30 days to your beginning date, as long as BeginningDate is a date field. Do this as an expression in a query.

Also, you can add two fields together in the same manner. Take two fields: 1. BeginningDate and 2. LeadTime
Let's say LeadTime is the frequency of time that you usually have to order something. If it's every 30 days, make it 30. Store this value in a table for later.

Put this in the expression of a query:
[BegnningDate] + [LeadTime].

This will add 30 days (or however many days in LeadTime) to the beginningdate.

That's it!

lemme know if it works.



-Patrick

Nine times out of ten, the simplest solution is the best one.