View Certain Records or All

[CCIDBMNG]

I have a form that the user enters the start and end dates they would like to view. There they also select the office they would like to view. The date parameters work just fine but and I so does the office criteria. What if I want to show either a specific office or I just want to view all? How can I do this without creating seperate queries.

 

[llkhoutx]

Create multiple recordsources depending on your criteria, esp. use VBA code to generate the SQL string for your form recordsource depending on the criteria.

 

[CCIDBMNG]

Where would I place the code? In the form? In the report? And what event?

 

[jaydwest]

View certain Records or all

With some of these complex report selections, we create the SQL in code. This gives you complete flexibility, but there are some tradeoffs. It slower than using a compiled parameter query and ties up more system resources. But in cases like yours, we have found it is worth the tradeoffs. Here's same code

Dim mySQL as String
Dim myW as String

set qryReport = dbs.querydefs("Your Report Query Name")
mySQL = "SELECT .... FROM .... "
myW = "WHERE ("
if Not IsNull(me![OfficeName]) Then
myW = myW & "OfficeName = '" & Me![OfficeName] & "') AND "
End If
If Not Isnull(Me![StartDate]) and Not IsNull(Me![EndDate]) then
myW = myW & " Date Between #" & Me![StartDate] & "
# AND #" & Me![EndDate] & "#) AND "
End If

if MyW > "WHERE (" THEN
myW = Mid$(myW, 1 , len(myW) - 5
Else
myW = ""
End If

qryReport.SQL = mySQL & myW & ";"

--------------------------------------
This will generate the exact query you want as a recordsource for your report. You could also use it with a mutiliple select list box to select multiple office and date ranges.

Note the if MyW > .. is used to strip off the " AND " at the end of myW.

If the Report query is very complex, you can copy the initial SQL from the query window. We also have a function that will automatically strip myW from an existing query and return the SELECT.. FROM SQL. Then you can add the myW Clause without having to type in the SQL. If you would like it, let me know.

Hope this helps. If you have any questions, let me know.

 

[CCIDBMNG]

I know how to code it I'm just not sure where to place the code to set the reports recordsource .

 

[jaydwest]

Try putting the code on the [Print] Button onClick event. Because the code references a named Query, the Query's SQL is persistent. So if you have a question about the report's recordsource, you can view it in MA Access' Query Window.

 

[nuke_girl]

Hi guys,

First, Jay, thanks for posting this code (eventhough it was quite a while ago!). I'm pretty sure it's exactly what I'm looking for.

The report I've built pulls a "Group Code" from a combo-box in a form and creates the report based on that single Group. I've been asked to change it so that you can select one, multiple, or all Group codes. The report is already set up grouping on Group code (this could get confusing!), so that's no big deal. I just don't know how to set up my query to pull multiple codes from a list box. I do know how to set up the list box, just not how to use it for more than one value to be entered into a parameter query.

As it's set up now, the report opens in print-preview mode from the switchboard. The OnOpen event first opens the form with the combo-box, and when you hit "ok" it hides the form and creates the report, which contains 2 subreports, and thus 3 queries, only one of which has the parameters entered, and the others are linked by master/child fields. Pretty simple.

I'm looking at your code here, and I'm not sure how to put it into my report. My VB skills are almost nil. I can sorta understand what you've written, but I have no idea how to use it. Where do I put it? Which parts do I have to change? I don't need the Start and End dates, but in my form, I select "Activity Code" which controls the "Group Code" combo, and then both parameters are needed in the query. Activity Code will never be Null, because it is essential in the query, so do I even need to put it into the code?

As you can see, I have no idea what I'm doing!!

Any help you can give me would be great.

Thanks,
~ Nuke ~

 

[Mile-O]

You mean like this?

 

[nuke_girl]

That looks like it'll probably help... when I can make the error messages go away! But I'm off now for the weekend, so I'll take a look at it again on Monday and let you know if it's what I need.

Thanks, Mile.

~ N ~

 

[nuke_girl]

Almost there... I think?

Hi again Mile,

I've got the code working for the first part of the If...Else (when no selection is made), and it's FANTASTIC!

However, when I try to run the query with one or more selections, I get the error: "This expression is typed incorrectly, or it is too complex to be evaluated...[blah blah blah]...(Error 3071)". It appears it's because the leading zeros from the criteria from the list box are being dropped, and my guess is that there is some way to change the lines,

For Each var In Me.Group.ItemsSelected
strSQL = strSQL & Me.Group.Column(0, var) & ","​

to fix this.

If I am indeed correct that this is the problem, do you know how I could fix it?

Thanks again,
~ N ~

EDIT: I just tried manually entering the critera manually as In("002", "004", "005") as opposed to In(2,4,5) as the code was creating and it worked just fine, so I think the leading zeros are the problem.

 

[nuke_girl]

It appears that my problem is in the datatype needed for the "For Each" control variable is either Variant or Object, but the list box data is text, and is being used as criteria in a text field of a query. So when the code runs, it places, for example, In(2, 4, 7300) where I need it to be In("002", "004", "7300"), as the data is selected from the list box. It doesn't work to change the datatype of var to anything different, but I don't know what else to do.

Any suggestions?
~ N ~