Modifying a query to return all info if select criteria is left blank

[KelMcc]

I have a form that is called by a switchboard. In this from, "callreport1", you enter criteria which in turn, a query uses to build a form.

The criteria is:
to date
from date
employee name

As it works now if you enter your date range (to/from date) and an employee name it generates a report of info pertaining just to that employee.

I'd like to modify this query so that if the employee field is left blank that they report will return info on all employees for the selected date range.

WARNING: I've got only the most basic grasp of SQL. Anything beyond SELECT, FROM, and WHERE will take a little explaining. Sorry.

 

[David R]

Since you're doing this from a form

The easiest way is probably going to have your popup form check to see if the [Employee] criteria field is empty. If it is, have it set the field to "*", and that should give you all results.

 

[KelMcc]

Sorry David, I need a little more help than that. I understand that by converting the blank field to a "*" (wildcard) that should return all employees.

Where I'm not following too well is where to do this. From what you wrote, I'm inferring that I go to the Properties of the form that's used to "build" the report? If so, do I do this function "After Upate", "On close", etc..... which one? And then, do I use "Expression Builder" or do I use "Event Procedure" and add some code? What code do I add?

 

[David R]

Maybe we're miscommunicating a bit

Here's my assumptions thus far:

You've got a form, "callreport1", where you can enter criteria for the fields you want to show up on your report.

You've got a query, underneath "report1", which references "callreport1"'s fields to get the criteria.

Presumably on "callreport1" you've got a button that says "Run the report" or something similar.


Did you make this button's Click event run a macro, are you using VBA code, or what?
If it's a macro, I'm not sure you can put this sort of detailed "if" instruction into it. I can give you code that will run it though.

If you're familiar with VBA code and used that (or used the Command Button Wizard to build the code), then you want to add a line:

If IsNull(Forms!callreport1.EmployeeName) Then
  Forms!callreport1.EmployeeName = "*"
End If
  
  DoCmd.OpenReport "report1",....

Hope that helps. Post back if I've thoroughly confused you.

 

[KelMcc]

The button has an "event procedure" (this is what you mean by VBA Code?). When clicked, yes, it runs the query that produces the report.

Here's the code for it:
_________________________
Private Sub RunReport_Click()
On Error GoTo Err_RunReport_Click

Dim stDocName As String

stDocName = "ISIF Report 2a"
DoCmd.OpenReport stDocName, acPreview

Exit_RunReport_Click:
Exit Sub

Err_RunReport_Click:
MsgBox Err.Description
Resume Exit_RunReport_Click
_________________________

So, I add you suggested code before the "DoCmd" instruction?

I'll fiddle with it and get let you know.

 

[David R]

Got it in one

Now, the question is, is it working as you intended?

However you may have taken me a bit too literally. You wrote:

stDocName = "ISIF Report 2a"
DoCmd.OpenReport stDocName, acPreview

in your original code. If that's your report name, by all means that's what you should use. You mentioned something about "callreport1" so I thought maybe the report name was "report1".

My If statement will go somewhere before the DoCmd.OpenReport, yes. You're catching on quick!

 

[KelMcc]

Yeah, the form that calls the query is actually called "ISIF Report Criteria", I called it "callreport1" here to give it a more generic/intuitive name for whoever was might be reading.
And "ISIF Report 2a" is the query.

I'll give it a shot after lunch.

 

[KelMcc]

ok this is what I changed it to:
----------Private Sub RunReport_Click()
On Error GoTo Err_RunReport_Click

Dim stDocName As String

stDocName = "ISIF Report 2a"

If IsNull(Forms![ISIF Report Criteria].BA) Then
Forms![ISIF Report Criteria].BA = "*"
End If

DoCmd.OpenReport stDocName, acPreview

Exit_RunReport_Click:
Exit Sub

Err_RunReport_Click:
MsgBox Err.Description
Resume Exit_RunReport_Click

End Sub
--------------

And I get this error:
The object doesn't contain the automation object "If.".

 

[KelMcc]

ok, the "if" error was due to some garbage in the properties of the form itself and is fixed.

So, the query does run and doesn't bomb, but it doesn't return anything but a blank report. Now, if I close the report, and I see the form again, the "*" is populated in the employee field...

 

[David R]

My apologies

I gave you a solution that didn't work, because I didn't test it. I must have been off my keel yesterday (too much time spent floating down rivers last weekend, apparently).

Let's try this again (I'll underline my changes)

Private Sub RunReport_Click() 
On Error GoTo Err_RunReport_Click 

Dim stDocName As String[u], stDocWhere As String[/u]

stDocName = "ISIF Report 2a" 

If [u]Not[/u] IsNull(Forms![ISIF Report Criteria].BA) Then 
[u]stDocWhere = "[EmployeeName] = '" & Me.BA & "'"[/u]
End If 

DoCmd.OpenReport stDocName, acPreview[u], , stDocWhere[/u]

Exit_RunReport_Click: 
Exit Sub 

Err_RunReport_Click: 
MsgBox Err.Description 
Resume Exit_RunReport_Click 

End Sub

The other thing you'll have to do is take OUT the Forms![ISIF Report Criteria].BA reference in the query. For this one criteria, since it can be null, we're doing it in the WHERE clause of opening the report itself, not in the underlying query.

Again, I apologize. This time I did test it out and it should work, provided I got the names right (I wasn't sure if .BA was the name of the field on your popup form, but I guessed so).

 

[KelMcc]

1 steps forward 1 step back...

OK, I made the changes you suggested, including deleting the line in my query.

And, yeah, when I leave the employee blank it returns all of them. But now when I enter an employee name I get nothing. I think the problem lies w/ deleting that lne from my query.... Once you delete the "Forms![ISIF Report Criteria].BA reference" from the WHERE statement I'm not sure how I'd get data by employee now.

Plus, if I deleted that from the WHERE statement, why would I need to do that code anyway? Make sense?

 

[David R]

Ok, we've introduced a new concept, try not to let your head explode..

We're combining two methods of limiting data. In one, you're using the Query to look at the Popup form [ISIF Report Criteria] to provide criteria lines for your query. Presumably one will always have a begin and end date to search by, or this won't work.

We're also using the WHERE clause of the OpenReport action to further limit the results that the Query gives us. This might not be the simplest method but it is flexible enough. This occurs not within the query but when the report is opened, and is calculated once again from [ISIF Report Criteria].

I assumed .BA was the field containing the EmployeeName you want to look for (or a blank). That's where this line comes from:

If Not IsNull(Forms![ISIF Report Criteria].BA) Then 
stDocWhere = "[EmployeeName] = '" & Me.BA & "'"
End If

"If .BA isn't empty, then assign the Where clause to look for records where [EmployeeName] matches the current contents of .BA"

Adding , , StDocWhere into the DoCmd.OpenReport statement is what tells the report the contents of our new WHERE clause. Otherwise StDocWhere is empty and the report opens as normal (to no specific employee).

Did that help?

 

[KelMcc]

Ah, ok, yes, you have the correct assumption for what BA is.

And, just to clarify, the pop-up form, ISIF Report Criteria, calls the query, ISIFReport2, which creates the report, ISIF Report 2.

I think the problem come down to this... What are you assuming [Employeename] is? I realize I used that term generically in my first post, it is a criteria called BA in my pop-up form.

Inferring from your post, I think you're saying that [Employeename] is the field in the report that corresponds to "BA" in the pop? Yes? If so, that field (in the report) is also called BA. Does that mean I need to change it to [BA] or ([ISIF Report 2].BA) or something? Here's what I have:

-----
Private Sub RunReport_Click()
On Error GoTo Err_RunReport_Click

Dim stDocName As String, stDocWhere As String

stDocName = "ISIF Report 2a"

If Not IsNull(Forms![ISIF Report Criteria].BA) Then
stDocWhere = "[BA] = ' " & Me.BA & " ' "
End If

DoCmd.OpenReport stDocName, acPreview, , stDocWhere

Exit_RunReport_Click:
Exit Sub

Err_RunReport_Click:
MsgBox Err.Description
Resume Exit_RunReport_Click

End Sub
-----

Also, does the spacing on the ' and " matter? They have single spaces between them.

 

[KelMcc]

OK, I just tried it w/ [Employeename] and it gave me a prompt to put in an employee name. I then entered an employee name, the report did come up, but had an "error".... grrr.. !

Man, its like sooo close! I can taste it which is refreshing, but since its not working, all I taste is the sand of bitterness! Ok, melodrama aside, we're pretty close.

Thanks for putting up w/ many questions.

 

[David R]

My comments are once again underlined for clarity.

And, just to clarify, the pop-up form, ISIF Report Criteria, calls the query, ISIFReport2, which creates the report, ISIF Report 2.

Ahh yes, that does clarify somewhat. See below.

I think the problem come down to this... What are you assuming [Employeename] is? I realize I used that term generically in my first post, it is a criteria called BA in my pop-up form.

Inferring from your post, I think you're saying that [Employeename] is the field in the report that corresponds to "BA" in the pop? Yes? If so, that field (in the report) is also called BA. Does that mean I need to change it to [BA] or ([ISIF Report 2].BA) or something? Here's what I have:

Now we're getting somewhere. Yes, there is a field somewhere that .BA has to match. I was calling it [EmployeeName] - I don't know what it's called in your table/query.

Are you saying the criteria field on the popup is named [BA] and the printable control on your report (that shows an Employee's Name) is called [BA]? While I'm not sure this will cause problems, even a simple renaming of the report field to [txtBA] can't hurt. Sometimes Access is touchy.

Private Sub RunReport_Click()
On Error GoTo Err_RunReport_Click
             
    Dim stDocName As String, stDocWhere As String

    stDocName = "ISIF Report 2a"
    
    If Not IsNull(Forms![ISIF Report Criteria].BA) Then
    stDocWhere = "[BA] = ' " & Me.BA & " ' "
    [u]'This is the touchy line. The first part, "[BA] = '", is referring to[/u]
    [u]'the field in your table or query which [BA] on the popup form has[/u]
    [u]'to match. They are not the same thing, or you'd be saying[/u]
    [u]'match yourself' The Me.BA part is correct.[/u]
    End If
        
    DoCmd.OpenReport stDocName, acPreview, , stDocWhere

Exit_RunReport_Click:
    Exit Sub

Err_RunReport_Click:
    MsgBox Err.Description
    Resume Exit_RunReport_Click
    
End Sub

Also, does the spacing on the ' and " matter? They have single spaces between them.
Take out the extra spaces. They can't be helping, and they might make your data not match when it should. I realize the close spacing makes it hard to read at times.

 

[KelMcc]

SUCCESS SWEET SUCCESS!!

OK, the BA thing was right as I did it and as you explained it. One thing I've learned (as this is my first db I'm doing from scratch) but can't fix now (as I'm too far in) is to NOT give things the same name. "BA" is the name of that field in the query, on the report and the form.

*gets ready for scolding*

So, as I put in my last example, that was correct. Guess what the culprit was! The damned spaces. I took those out and WHAMMY it worked.

I am now thoroughly gruntled.

Thanks Dave, if I lived in KC, I'd take you to a Royals game.

 

[David R]

And here I thought you liked my advice...

The Royals have lost their last 7 games, including 3 against the soon-to-be-deceased Montreal Expos.

I'd much rather see an Anaheim game - they at least beat the team I saw them play (NY, years ago, when they were still the California Angels).