Creating Filter based on at least two criterias (1 Viewer)

[Steven.Ashby82]

I have a report which is needs to be sent off to management once a month it needs to contain all [Status] = "open" records regardless of age and then the Recrods from the previous month with status's [Status] = "Pending" & "Closed"

Is there a simple way of doing this?

Also is it possible to create a sort of menu that the user can select the criteria via drop-down menus and calendars?

Thank you

 

[pr2-eugin]

Hello Steven,

I have a report which is needs to be sent off to management once a month it needs to contain all [Status] = "open" records regardless of age and then the Recrods from the previous month with status's [Status] = "Pending" & "Closed"

Is there a simple way of doing this?

You need to fiddle with the Report Source, I mean change the RecordSource SQL query to achieve this..

Also is it possible to create a sort of menu that the user can select the criteria via drop-down menus and calendars?

Thank you

This by normal methods is not possible.. but you can design a Form that will have the Drop down box and few text fields.. and Create the Query that your Report would be based upon.

 

[Steven.Ashby82]

Regarding the first issue, I managed to achieve showing all status's for the previous month but I struggled to find the code to write at simply in SQL view so that I could specify to always show "Open" calls

The second question I can't get my head around how to achieve this to start with, do you know of any demos out there that I can start with and then expand into my own style?

Thank you

 

[pr2-eugin]

It is really simple.. Design a Form, that will have a ComboBox lets name it statusCmbBox, based on your information the items in the ComboBox would be, "Open", "Pending", "Closed".. Then you will have two other TextBoxes lets say startDate and endDate, that will obtain date.. Then a button call it genQuery.. So on the click of the button you have to see what the field values are and create a Query..

Private Sub genQuery_Click()
    Dim strSQL As String
    
    If Me.statusCmbBox.ListIndex = -1 Then
        Call MsgBox("You have to select a Status to run the Report !", vbInformation, "Missing Information")
        Exit Sub
    End If
    
    If Len(Me.startDate & vbNullString) = 0 Then
        Call MsgBox("You have to set a Start date to run the Report !", vbInformation, "Missing Information")
        Exit Sub
    End If
    
    If Len(Me.endDate & vbNullString) = 0 Then
        Call MsgBox("You have to set a End date to run the Report !", vbInformation, "Missing Information")
        Exit Sub
    End If
    
    strSQL = "SELECT[COLOR=Blue] theFields[/COLOR] FROM [COLOR=Blue]theTable[/COLOR] WHERE (([COLOR=Blue]stausFieldName[/COLOR] = '" & Me.statusCmbBox & "') AND ([COLOR=Blue]dateFieldName[/COLOR] BETWEEN #"& Me.startDate &"# AND #"& Me.endDate &"#))"
    
    DoCmd.OpenReport "[COLOR=Blue]ReportName[/COLOR]", OpenArgs:=strSQL 
End Sub

Then in the Report OnOpen you can see if you have any open args, as..

Private Sub Report_Open(Cancel As Integer)
    If Len(Me.OpenArgs & vbNullString) > 0 Then
        Me.RecordSource = Me.OpenArgs
    End If
End Sub

 

[Steven.Ashby82]

Not sure what I've done wrong but it prints the report each time I click GenQuery and then it still prints it with records outside the parameters set

 

[pr2-eugin]

Try a Debug.Print, before opening the Report and see if the Query is right, if you feel it is right, open the Query wizard and paste the Query from the Immediate window and see if you have any errors.. Also if you could paste the SQL Query generated here it might help to identify some problem..

To avoid print use acViewReport.. As the default is acNormal..

DoCmd.OpenReport "ReportName", acViewReport, OpenArgs:= strSQL

 

[Steven.Ashby82]

I can't get that to work either but I have managed to stop it printing,

I keep getting a pop up box that reads "Enter Parameter Value"

 

[pr2-eugin]

As mentioned in my previous post..

Also if you could paste the SQL Query generated here it might help to identify some problem..

Enter Parameter value occurs when you have misspelled the Control Names or if there is a control on the report named as the same one that already exist in the Form.. Make sure all controls are uniquely named, and properly spelled..

 

[Steven.Ashby82]

Sorry Paul, I'm struggling to even get that to work tonight. I followed the instructions but still couldn't achieve success

 

[Steven.Ashby82]

SELECT Status FROM FaultTable WHERE ((Status = 'Open/Reopened') AND (DateRaised BETWEEN #01/01/2013# AND #31/01/2013#))

The source table is called FaultTable


Ahhh where you said "theFields" should that contain every field in the report? there's about ten, if so how do i list them all i.e. do I just use &?

 

[pr2-eugin]

Yes.. something like..

SELECT FaultTable.* 
FROM FaultTable 
WHERE ((Status = 'Open/Reopened') AND (DateRaised BETWEEN #01/01/2013# AND #31/01/2013#))

 

[Steven.Ashby82]

That's nearly got it but it's still not liking the "DateRaised"

strSQL = "SELECT FaultTable.* FROM FaultTable WHERE ((Status = '" & StatusCmbBox & "') AND (DateRaised BETWEEN #" & txtDateFrom & "# AND #" & txtDateUntil & "#))"

I can't for the life of me figure it out :banghead::banghead::banghead:

 

[Steven.Ashby82]

Ok so I've been up all night trying to sort this and I only have 3hrs to rectify it but I have now managed to lose the "Enter Parameter Value" popup for "DateReceived" field but it still won't display any records within the report. I'm really desperate now and will even consider trying a completely different method.

Please can someone help?

All format types are set to medium date.

 

[pr2-eugin]

Did you try copying the Query into the Query design and see if it actually returns any data? Worst case, could you upload a stripped down version of your DB?

 

[Steven.Ashby82]

I did and didn't find any error

Here is the file Thank you

 

[pr2-eugin]

Steven, based on the Data you have in the table the report returns only ID 39, as that is the only data that matches the Criteria.. i.e. Status is Opened/Reopened and between the two dates.. And it works just fine..

 

[Steven.Ashby82]

Ahhhh Just seen the issue! If I select between 01/01/2013 & 23/01/2013 It will return empty but I start from the previous month i.e 31/12/2012 until 23/01/2013 it will return the correct data.

Do you know why this is happening?

 

[pr2-eugin]

I am not sure, what is that you are referring to.. This is what I get..

SELECT FaultTable.* FROM FaultTable WHERE ((Status = 'Open/ReOpened') AND (DateRaised BETWEEN #01/01/2013# AND #23/01/2013#))
[COLOR=Green]' Gives me ID 39[/COLOR]

SELECT FaultTable.* FROM FaultTable WHERE ((Status = 'Open/ReOpened') AND (DateRaised BETWEEN #31/12/2012# AND #23/01/2013#))
[COLOR=Green]' Still gives me only 1, which is ID 39[/COLOR]

 

[Steven.Ashby82]

relooked at this and if you start with 02/01/2013 that wouldn't work

Sorry Paul I was naughty and assumed 01/01/2013 was the same

 

[pr2-eugin]

Okay now I understand what is going on here.. Well the problem is because of the Format.. So try to establish a Format as..

AND (DateRaised BETWEEN " & Format(txtDateFrom, "\#mm\/dd\/yyyy\#") & " AND " & Format(txtDateUntil, "\#mm\/dd\/yyyy\#") & "))

For more information on Dates, look into International Dates..