Different Critera to Open Report

[DanG]

Hello,

I have always been fine taking existing code and adapting it, but not at writing my own from scratch

I have a report (rpt_MSR_Detail_byAdvisor) that I want to open from a form (called ReportCentral) and on that form I have 2 combo boxes (DistrictPicker & AdvisorPicker). I would like to use the value of either field (not both at the same time like a cascade) to open the report or if left blank open to all records.

Below is the code I have thus far. I tried implementing the "or" on the criteria, but it doesn't work. I understand why, but it does illustrate pretty much what I want to do.

What I think the answer is, is to establish a variable that contains either fields value (or null) and insert the variable into the report criteria., But I not quite sure how to do it.

Private Sub MSRDetail_Click()
On Error GoTo Err_MSRDetail_Click

    Dim stDocName As String

    stDocName = ReportPicker.Value

    DoCmd.OpenReport stDocName, acPreview, , "IIf([Forms]![frmReportCentral]![DistrictPicker] Is Null,"""",[txtDistNum]=[Forms]![frmReportCentral]![DistrictPicker])or IIf([Forms]![frmReportCentral]![AdvisorPicker] Is Null,"""",[txtEmpNum]=[Forms]![frmReportCentral]![AdvisorPicker])"


Exit_MSRDetail_Click:
    Exit Sub

Err_MSRDetail_Click:
    MsgBox Err.Description
    Resume Exit_MSRDetail_Click
    
End Sub

Any pointer would help a lot!
Thank you

 

[pbaldy]

Rather than trying to test within the wherecondition, I'd do something like this:

Dim strWhere as String

If whatever Then
  strWhere = "whatever"
ElseIf blah Then
  strWhere = "blah"
Else
  strWhere = ""
End If

DoCmd.OpenReport stDocName, acPreview, , strWhere

 

[DanG]

That's exactly what I was looking for, but when I test on just the district field on the report form I get an error..."MS Access can't find the field "|" referred to in your expression".

Private Sub MSRDetail_Click()
On Error GoTo Err_MSRDetail_Click

Dim stDocName As String
Dim strWhere As String

If IsNull([Forms]![frmReportCentral]![DistrictPicker].Value) = False Then
  strWhere = [txtDistNum] = [Forms]![frmReportCentral]![DistrictPicker]
Else
  strWhere = ""
End If
  
  


    stDocName = ReportPicker.Value

    DoCmd.OpenReport stDocName, acPreview, , strWhere


Exit_MSRDetail_Click:
    Exit Sub

Err_MSRDetail_Click:
    MsgBox Err.Description
    Resume Exit_MSRDetail_Click
    
End Sub

 

[Scooterbug]

put in debug.print strWhere and see what comes up in the Immediate Window

If [txtDistNum] is actually a text field, then you will have to add quotes to the Where portion

 

[DanG]

Got it!
Thank you very much for your help!!!!

I didn't use quotes...

strWhere = [COLOR="Red"]"[/COLOR][txtDistNum] = [Forms]![frmReportCentral]![DistrictPicker][COLOR="red"]"[/COLOR]

 

[pbaldy]

Here's a reference on the syntax:

http://www.baldyweb.com/wherecondition.htm

 

[AFKillbait]

I was attempting to do the same thing on one of my forms, but I'm still pretty new to VBA programming (haven't even touched VB code in almost 12 years until recently). What does the "ReportPicker" represent?

Also, I can't look at the syntax reference because that site is blocked from my current location for some reason.

 

[pbaldy]

ReportPicker is a control (probably a combo or listbox) that contains the names of reports. Not sure why my site is blocked; it's a simple site hosted by GoDaddy. Here's the text of that page:

Often you might have a form or control that displays summary information, and you want to select one of those items and open a second form that displays the detailed information. The technique I use most often is the wherecondition argument of DoCmd.OpenForm (or DoCmd.OpenReport). It is sort of like saying "open this form but only show the record(s) that meets this criteria". Using this technique, the second form can be based on the table itself or a query that returns all records. It looks like this for a numeric value:

DoCmd.OpenForm "SecondFormName", , , "FieldName = " & Me.ControlName

Where SecondFormName is the name of the form being opened, FieldName is the field in that form's recordsource the restriction is based on, and ControlName is the name of the control on the current form that contains the value to be shown on the second form. As you will find throughout VBA, text and date values are treated differently. For text:

DoCmd.OpenForm "SecondFormName", , , "FieldName = '" & Me.ControlName & "'" '(note, that's a double quote, single quote and double quote at the end)

For a date value, use # instead of the single quote:

DoCmd.OpenForm "SecondFormName", , , "FieldName = #" & Me.ControlName & "#"

As noted above, this technique can also be used when opening reports.

 

[AFKillbait]

Much appreciated. I was actually trying two different sets of code to find which one was the simplest to use to get the result I needed, one was very similar to the original poster (couldn't figure out what object to use, and then wasn't sure what the ReportPicker represented in his code) and the second was similar to yours, but I was missing the single quotes, and couldn't figure out why that one wasn't working.

I'm guessing the coding would be similar if I wanted to use multiple filters, correct?

 

[pbaldy]

Yes, you'd just need to include AND or OR between the conditions, as appropriate. Basically the wherecondition is an SQL WHERE clause without the word "WHERE".

 

[AFKillbait]

I am able to view your site without any issues Bob.

 

[AFKillbait]

I was told of the evils of Lookup Wizards and searches, and went to remove them (changed them all to text fields and used a combo box on a form to input the information), however now my search function just brings up the report with none of the fields filled in rather than actually displaying the unfiltered report. I'm not sure what is different between using a text field that has its information input via a combo box and using a Lookup Wizard that has its information input by the same combo box. This is the code I'm currently using:

Private Sub btnORE_Click()
    If Me.cboFilter = "" Then
        DoCmd.OpenReport "ReportName", acViewReport
    Else
        DoCmd.OpenReport "ReportName", acViewReport, , "UTC = '" & me.cboFilter & "'"
    End If
End Sub

It worked with the Lookup Wizards yesterday after the suggestions you gave, and when I open the report directly it displays all of the information without any problems.

 

[pbaldy]

If it's bringing up a key field now instead of the text, try

"UTC = " & me.cboFilter

You might set a breakpoint and make sure the value in the combo is the one expected for UTC.

 

[AFKillbait]

The field is still text, and it doesn't give me an error. The report just opens up without any information.

If there is actually information in the combo box (ie. the value is not Null) then the report opens correctly, filtering the information to only display the ones with the correct UTC value. That is what is confusing me.

I'm reasonably certain it's something to do with the null value part of the code, just not sure what to do to fix it.

 

[pbaldy]

Oh, I misunderstood. Try this:

If Len(Me.cboFilter & vbNullString) = 0 Then

which will test for both Null and ZLS ("").

 

[AFKillbait]

That did it, thanks. What does the;

Len(Me.cboFilter & vbNullString) = 0

part of the code do exactly? Been long enough I may as well be considered a total novice with code, so trying to understand what does what as I go along.

 

[pbaldy]

It can be hard to tell if a control is Null vs containing a ZLS, since they look the same. You could test for each separately, like:

If IsNull(Whatever) OR Whatever = "" Then

but the test I posted checks for both by combining whatever the control contains with a ZLS. If the control is Null or already contains a ZLS, then the combined length will be zero. If the control contains something, then the length will be greater than zero. You might find this interesting:

http://www.mdbmakers.com/forums/showthread.php?s=&threadid=2602

 

[AFKillbait]

I don't think my network admins like your links or something Paul, that's the second one you've posted that has been access denied.

 

[pbaldy]

Funny, as that's another forum just like this one. The relevant parts:

ZLS is an abbreviation for zero length string, some people like to call it an empty string. You often see ZLS string in code as "" (although as we talked about above vbNullString is a better choice).

The most common source of errors here is confusing ZLS with Null, usually when reading a field, expecting to get a string, but the field has no data and thus gives Null. This raises the all-to-familiar run-time error "Invalid use of Null".

By default, leaving a field blank in a table stores Null. You can change this behavior by changing the AllowZeroLength property of the field.

Null, like Empty, is both a keyword and a special value Variants can contain. It indicates the variable contains no valid data. This usually causes people problems when reading a field from a table that contains no data. Null is not equal to anything, not even itself! VBA has the IsNull function to tell you if a variable contains Null, and the Nz function to return an expected value (like zero or "") if a variable contains Null. Perhaps the best way to think about Null is as meaning "Unknown".

 

[adios]

Thank you very much