Still having trouble running reports with multi select list boxes, and Happy New Year all!

[Gina Maylone]

Good morning!

When I debug the criteria is correct, yet I'm getting an Expected Array error on the last line "getcriteria()" I have no idea how to fix this, yes I am in over my head. I thank you in advance for any insight you can give me!!

Private Sub Command45_Click() Dim VarItm As Variant
 Dim varitm2 As Variant
 Dim stDocCriteria As String
 Dim stdoccriteria2 As String
 Dim getcriteria As String
 Dim bidstatus As Variant
 Dim mfg As Variant
 
  
  For Each VarItm In bidstatus.ItemsSelected
   stDocCriteria = stDocCriteria & "[bidstatus] = " & bidstatus.Column(0, VarItm) & " OR "


   Next
   For Each varitm2 In mfg.ItemsSelected
   stdoccriteria2 = stdoccriteria2 & "[mfg] = " & mfg.Column(0, VarItm) & " OR "


   Next
   If stDocCriteria <> "" Then
   stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
    If stdoccriteria2 <> "" Then
   stdoccriteria2 = Left(stdoccriteria2, Len(stdoccriteria2) - 4)


   End If
   getcriteria = stDocCriteria & " And " & stdoccriteria2


        Debug.Print "Get Criteria " & getcriteria
   
   'DoCmd.Openreport "BidReportbyMfrSummary", acPreview, , getcriteria()


   DoCmd.Openreport "secondBidReportbyMfrSummary", acPreview, , getcriteria()


End If


End Sub

Here's from the immediate window: Get Criteria [bidstatus] = Open/Quote And [mfg] = AAB

[orange]

Why do you have the () after getcriteria?
You defined getcriteria as a string not a string array.

Dim getcriteria As String

- so no brackets required.

It seems you are missing some important quotes in your rendered criteria.

[Gina Maylone]

Hi Orange! How are you doing? How do I define as string array? It isn't available when I start typing. I will recheck my quotes. Thank you so much for the direction!!

[orange]

I'm doing well --in isolation. I don't think you need an array, but I don't know the details of your post.
You can remove the brackets from getcriteria and see what/if error occurs.

Do you have some examples of your Report's where criteria that you can post?

[Gina Maylone]

My report criteria looks like this: [bidstatus] = OpenQuote And [mfg] = Greenheck, when I run a detailed report based on the same criteria I get ALL mfgs. UGH. My debug gives me: Get Criteria [bidstatus] = OpenQuote And [mfg] = Greenheck as well, no quotes? Oh, and I removed the parens and that seemed to help.

Glad you are well. Hopefully soon we can get out!

[orange]

I tried to get a string that "looked " like your requirement with this little test

Sub test4()
Dim x As String
x = """[bidstatus] = 'Open/Quote' And [mfg] = 'AAB'"""
Debug.Print "OpenReport reportname,acNormal,," & x
End Sub


which gives result

OpenReport reportname,acNormal,,"[bidstatus] = 'Open/Quote' And [mfg] = 'AAB'"

**As a test you could try opening your report and use the following, to see/confirm if the syntax is correct.


DoCmd.Openreport "BidReportbyMfrSummary", acPreview, "[bidstatus] = 'Open/Quote' And [mfg] = 'Greenheck'",,"[bidstatus] = 'Open/Quote' And [mfg] = 'Greenheck'"

[moke123]

Since your using a Multi-select listbox have you considered an "In" clause?

  For Each VarItm In bidstatus.ItemsSelected
   stDocCriteria = stDocCriteria & "'" & bidstatus.Column(0, VarItm) & "'," 
   Next

'code here to strip off trailing ,

X= "[bidstatus] In(" & stDocCriteria  & ")"

I use a public function for such things.
Attached is an example file.
See the third listbox down where you can experiment with the optional arguments.
Select what column to use and what delimiter you want to use.

your code could simply look something like

X= "[bidstatus] In(" & getLBX(Me.YourListBoxName,1,,"'")  & ")"

The result will look like "[bidstatus] In( 'Open/Quote','Close/Quote')"

[Gina Maylone]

Hi Moke! I have tried an in clause, but have simply had no luck getting correct numbers. The point of the report and subreport is to compare sales during a certain time period by MFR and Bid Status. I started out with a main report and subreport, but realized I can't pass the criteria to the subreport via the selection form. My head hurts thinking about this issue. Thank you for the sample db, very impressive. I have tried so many things! Adding the multi-selections to a text box, separated by commas or "or", adding "in" to the front of the string. Heads spinning, may just scrap it and start over, thank you so much for your time. Happy 2021!

[orange]

Did you try running the statement I suggested in post #6 as a test? You could make it a 1 line routine--just to ensure the syntax is correct.

**As a test you could try opening your report and use the following, to see/confirm if the syntax is correct.


DoCmd.Openreport "BidReportbyMfrSummary", acPreview, "[bidstatus] = 'Open/Quote' And [mfg] = 'Greenheck'",,"[bidstatus] = 'Open/Quote' And [mfg] = 'Greenheck'"

Also do you have some examples of your Report's where criteria that you can post? People can help if they knew more of your situation. Some complex criteria or even a copy of the db with some clear instructions would be useful.

[Gina Maylone]

Hi Orange, yes I did try it. It gave me all mfrs and all bidstatus.

 DoCmd.Openreport "BidReportbyMfrSummary", acPreview, "[bidstatus] = 'OpenQuote' And [mfg] = 'Greenheck'"

If I put the criteria directly in the query, I get the correct information. So confused! Thanks again for your time!

SELECT DISTINCTROW ProjectItems.ItemBid, MFGs.MFG, Projects.BidDate, Projects.ProjectName, BidStatus.BidStatusFROM MFGs INNER JOIN (BidStatus INNER JOIN (Projects INNER JOIN ProjectItems ON Projects.ProjectID = ProjectItems.ProjectID) ON BidStatus.BidStatusID = ProjectItems.BidStatusID) ON MFGs.MFGID = ProjectItems.MFGID
GROUP BY ProjectItems.ItemBid, MFGs.MFG, Projects.BidDate, Projects.ProjectName, BidStatus.BidStatus, ProjectItems.MFGID
HAVING (((MFGs.MFG)="greenheck") AND ((Projects.BidDate) Between [Forms]![CompareSales]![firstbegindate_beginr] And [Forms]![CompareSales]![firstbegindate_endr]) AND ((BidStatus.BidStatus)="openquote"));

[orange]

If it opens and creates a report with all mfrs and all bidstatus, then it seems the "where criteria" are not working.

Where does your query fit into this?

[Gina Maylone]

The report itself is built on the query CompareMFG

SELECT DISTINCTROW ProjectItems.ItemBid, MFGs.MFG, Projects.BidDate, Projects.ProjectName, BidStatus.BidStatusFROM MFGs INNER JOIN (BidStatus INNER JOIN (Projects INNER JOIN ProjectItems ON Projects.ProjectID = ProjectItems.ProjectID) ON BidStatus.BidStatusID = ProjectItems.BidStatusID) ON MFGs.MFGID = ProjectItems.MFGID
GROUP BY ProjectItems.ItemBid, MFGs.MFG, Projects.BidDate, Projects.ProjectName, BidStatus.BidStatus, ProjectItems.MFGID
HAVING (((Projects.BidDate) Between [Forms]![CompareSales]![firstbegindate_beginr] And [Forms]![CompareSales]![firstbegindate_endr]));

[orange]

Try this standalone--I think there is a comma missing. I think the where clause should be the 4th parameter.

DoCmd.Openreport "BidReportbyMfrSummary", acPreview,, "[bidstatus] = 'OpenQuote' And [mfg] = 'Greenheck'"

[Gina Maylone]

That was perfect! Correct amount and everything! Thanks Orange!!

Now to figure out how to get that to work if they select all in one or both list boxes.

[orange]

OK Great!

Now we need some details of the listboxes and their rowsources and what the process of filling the form involves.
Better if we could have a copy of a test database with instructions of filling the form.
Would also be helpful if you could show/mock up some of the criteria you will be using.

Another factor to consider is that REPORTs have their own Sorting and Grouping, so you may not need these in your query -in fact they may be ignored by the report mechanism of Access.