Combine (2) Case Selects (Access 97)

Topic

New Reply

What I am attempting to do is allow the user to choose they type of items from Me.Criteria (Case 1 = “Active”, Case 2 = “Deleted”, Case 3 = “All”) as well as how they want to sort the data from Me.GrpSortBy. As it is written right now, the Me.GrpSortBy works just fine. However, no matter which Criteria is choosen, it always shows all data. Therefore, I need to know how to combine both cases for the report.

Private Sub command3_Click()
Dim PlantSort As String
Dim strWhere As String
Dim strWhere1 As String

DoCmd.RunMacro “PlantCombo”
gstrTitle = “Composition / Status”
Select Case Me.Criteria
Case 1
strWhere1 = “[Deleted] = False”
Case 2
strWhere1 = “[Deleted] = True”
Case 3
strWhere1 = “[Deleted] <=0" ' nothing needed to get all records"
End Select

Select Case Me.grpSortBy
Case 1
strWhere = "[SortBy] = Y1Sum"
PlantSort = "[Plant] = 'Altima Trim & Chassis'"
DoCmd.OpenReport "General Info", acViewPreview, , PlantSort
Case 2
strWhere = "[SortBy] = Project Classification"
PlantSort = "[Plant] = 'Altima Trim & Chassis'"
DoCmd.OpenReport "General Info", acViewPreview, , PlantSort
Case 3
PlantSort = "[Plant]='Altima Trim & Chassis'"
DoCmd.OpenReport "General Info By Code", acViewPreview, , PlantSort
End Select
End Sub

The reason I wrote the Me.GrpSortBy like I did (including the running report, ect. at that time) was because if Case 1 or Case 2 is choosen, (1) report is ran and it is just sorted differently. However it Case 3 is choosen a different report runs.

Any ideas on how to get this to work?

Reply | Quote

Replies

I would suggest that you declare another variable StrReportName to hold the name of the report, and set its value in the second Select statement, then move the docmd.openReport line outside the second select statement.

Also declare another variable to hold the final where condition.

Before you open the report however, build strWhereFinal out of the the selections made in the two select statements.

Something like this: strWhereFinal = strwhere & ” and ” & strwhere1 & ” and ” strPlantsort

DoCmd.OpenReport strReportname, acViewPreview, , strWhereFinal

Reply | Quote

Here is what I have done.
If Me.grpSortBy Case 3 is choosen with any Case from Me.Criteria, the report runs perfectly. However if Case 1 or 2 from Me.grpSortBy is choosen, the syntax error (missing operator) in query expression ‘([Deleted] = False and [SortBy] = Project Classification and [Plant] = ‘Altima Trim & Chassis’)’

Private Sub command3_Click()
Dim intCount As Integer
Dim strWhere As String
Dim strWhereS As String
Dim strWhereT As String
Dim PlantSort As String

Dim isgm As String

intCount = 1
engall.Visible = False
engalll.Visible = False
bfs.Visible = False
bfsl.Visible = False
pfs.Visible = False
pfsl.Visible = False
tc.Visible = False
tcl.Visible = False
powertrain.Visible = False
powertrainl.Visible = False
EngineeringL.Visible = False

gstrTitle = “Composition / Status”
gstrTitleSt = Choose(Criteria, “Active Items”, “Deleted Items”, “All Items”)
DoCmd.RunMacro “PlantCombo”

Do While intCount <= 2

'DoCmd.Close
Select Case Me.Criteria
Case 1
strWhere = "[Deleted] = False"
Case 2
strWhere = "[Deleted] = True"
Case 3
strWhere = "[Deleted] <=0" ' nothing needed to get all records"
End Select

Select Case Me.grpSortBy
Case 1
strWhereS = "[SortBy] = Y1Sum"

Case 2
strWhereS = "[SortBy] = Project Classification"

Case 3

End Select

strWhereT = strWhere & " and " & strWhereS

If Me.grpSortBy = 1 Or Me.grpSortBy = 2 Then
strWhereT = strWhere & " and " & strWhereS & " and [Plant] = 'Altima Trim & Chassis'"
DoCmd.OpenReport "General Info", acViewPreview, , strWhereT
Else
strWhere = strWhere & " and [Plant] = 'Altima Trim & Chassis'"
DoCmd.OpenReport "General Info By Code", acViewPreview, , strWhere
End If

[Criteria] = 1

Exit Sub

Loop
End Sub

I have looked on MS Knowledge Base, my Access 97 Bible and other "help" sites but have found nothing on combining Select Cases'.
Please, any other ideas or possibly an example would be greatly appriciated.

Reply | Quote

I’m a little confused, but I believe that your problem is that you need single quotes around the string values for the sortby…

strWhereS = “[SortBy] = ‘Y1Sum'”
strWhereS = “[SortBy] = ‘Project Classification'”

HTH

Reply | Quote

Trudi,
I believe you have got me on the right track. The Report did pull up, but had “errors” in every field. Now, I am truely lost, because the simple ‘ ‘ at the [SortBy] caused the report to accept the syntax but read it incorrectly……

Reply | Quote

Okay… Let’s try get this working…

To relieve my confusion, I need you to confirm a bit of information before I start writing this code… Me.Criteria and Me.grpSortBy… Criteria and grpSortBy are the names of controls on the form?? or?? … Could you possibly give me a few sample records from the data so that I can see what the field names are and the kind of data?

Reply | Quote

It would probably be best if I break it down via the following:
(2) Option Groups
Group1: Me.Criteria
(3) Options reading from one field [Deleted]. Option 1 = Deleted = False…. Option 2 = Deleted = True….. Option 3 = Deleted = True or False (to read all records)
Group2: Me.SortBy (This is how to tell the report how to Sort the data)
First, in the query the following expression was created:
SortBy: Choose([Forms]![InternalReports]![grpSortBy],[Project Classification])
***Side Note**** I have tried changing the query expression to:
Choose([Forms]![InternalReports]![grpSortBy],[Project Classification] or [Y1Sum]) then tried:
Choose([Forms]![InternalReports]![grpSortBy],’Project Classification’) then tried:
Choose([Forms]![InternalReports]![grpSortBy],’Project Classification’ or ‘Y1Sum’)
****End Note
Then, in the report, [SortBy] is used in the sorting and grouping
(3) Options on form for Me.grpSortBy. Option 1 = [SortBy] = Project Classification ….. Option 2 = [SortBy] = Y1Sum
*****Note**** Both Project Classification and Y1Sum are fields in the query. Also, Option 1 or Option 2 is to run report “General Info” **** End Note*****
Option 3 = No Sorting, but instead it runs a different report named “General Info By Code”

Also, PlantSort = “[Plant] = ‘Altima Trim & Chassis'” . In this line I am wanting to tell the report to only pull the records where the field [Plant] = Altima Trim & Chassis.

Well there is the entire section concerning the Select Cases…… As for giving some actual data,
Fields: [Deleted] is a (Yes/No) check box
The rest I believe are explained above.

Reply | Quote

Well that solves the first mystery… You’re getting errors because I told you to put single quotes around “Project Classification” and “Y1Sum”… If these are field names then you need to put square brackets around them… The report’s recordsource is looking for those two Values in a field called SortBy…

Let me look through this and see if I can help… Can I have the names of the recordsources for both reports?

Reply | Quote

General Info RecordSource is General Info
General Info By Code RecordSource is General Info By Code

I know I have gone astray from using rpt and qry.

All Fields in one query are in the other query.

Reply | Quote

General Info RecordSource is General Info
General Info By Code RecordSource is General Info By Code

I know I have gone astray from using rpt and qry.

All Fields in one query are in the other query.

Reply | Quote

I deleted all the reports except the (2) Generals, all the forms except the 1, all the queries except the (2) generals and all the “user tables”, and other unneeded tables for the database and it only comes down to 21.1MB in size. Then even tried to delete all but only 1 table and still the sam result. Zipping it only brings it down to 5.71MB. Therefore, I do not think a sample is possible.

Reply | Quote

Did you compact the database after deleting everything that wasn’t needed? …Delete most of the records in the tables involved??

Reply | Quote

Right click on the file and “compact”?
If so, I have tried that before, but when I attach it anywhere, it wants to go as the full size. I “compacted” that way once, when a test FE was going out, but it was too large for our e-mail and we can send up to 9MB internally.

Is there a trick I am missing?

Reply | Quote

While the database is open… Select the Tools menu and go to Database Utilities -> Compact Database… Try that…

Reply | Quote

Compacted, removed all moduels and tables, then zipped. Now only 237KB, however still too large to attach with the limit showing 100KB. Also, according to the note below the attach file space, I do not see MDB.

Reply | Quote

You can’t attach an .mdb directly, that’s why you have to zip it. The .zip extension is allowed.

You should do the Compact and Repair after deleting objects, immediately before zipping the database.

Reply | Quote

You can’t attach an .mdb directly, that’s why you have to zip it. The .zip extension is allowed.

You should do the Compact and Repair after deleting objects, immediately before zipping the database.

Reply | Quote

Still, with the zip. It is over the 100. I even tried saving the tables as excel then compacting, but the file is still too large.

Reply | Quote

It is almost impossible that a zipped compacted Access 97 database with all non-essential content removed should be over 100 KB.

One other “trick” (substitute the path and file name of your database):
Select Start | Run…
Type “C:Program FilesMicrosoft OfficeOfficeMSACCESS.EXE” /Decompile “C:AccessMyDatabase.mdb” and press Enter.
Close the database.
Select Start | Run…
Type “C:Program FilesMicrosoft OfficeOfficeMSACCESS.EXE” “C:AccessMyDatabase.mdb” /Compact and press Enter.
Zip the database.

Reply | Quote

It is almost impossible that a zipped compacted Access 97 database with all non-essential content removed should be over 100 KB.

One other “trick” (substitute the path and file name of your database):
Select Start | Run…
Type “C:Program FilesMicrosoft OfficeOfficeMSACCESS.EXE” /Decompile “C:AccessMyDatabase.mdb” and press Enter.
Close the database.
Select Start | Run…
Type “C:Program FilesMicrosoft OfficeOfficeMSACCESS.EXE” “C:AccessMyDatabase.mdb” /Compact and press Enter.
Zip the database.

Reply | Quote

Okee dokee… Unfortunately it doesn’t look like I’m going to get to see this thing, so let’s try this one piece at a time…

First of all… Let’s talk about the report Sort…

1) I don’t know why you would have put that SortBy field in the query… If the recordsource for the “General Info” Report contain the fields, [Project Classification] and [Y1Sum], and those are the fields you want to sort on that is all you need…

2) DoCmd.OpenReport does not have a Sorting argument so I would just use the Report object itself and the OrderBy property… For example… The following code will open the report needed and set the OrderBy property as well as turn OrderByOn property to True… This is assuming I’m understanding correctly and you have a control option group called “grpSortBy” on the form with three different Options… The user having selected 1 meaning “sort the report by Y1Sum”, 2 meaning “sort the report by Project Classification” and 3 meaning

Reply | Quote

I am to just delete my current code (all of it), right?

Reply | Quote

strRptName = IIf(Me!grpSortBy > 2,

Reply | Quote

Why don’t you email your cutdow zipped database to Trudi’s email address?

Reply | Quote

Trudi,
If that is alright with you I can send the zipped cut down sample to your e-mail.

Reply | Quote

Reply to one of Trudi’s posts so she will be prompted.

Reply | Quote

Reply to one of Trudi’s posts so she will be prompted.

Reply | Quote

Trudi,
If that is alright with you I can send the zipped cut down sample to your e-mail.

Reply | Quote

Why don’t you email your cutdow zipped database to Trudi’s email address?

Reply | Quote

strRptName = IIf(Me!grpSortBy > 2,

Reply | Quote

I am to just delete my current code (all of it), right?

Reply | Quote

Would you mind if I sent you the cut down zip database to your e-mail?

Reply | Quote

Sorry it took so long for me to answer… Duty called…

Sure… I sent you a private message with my work email address…
I don’t know if I’m allowed to post it here… and while I’m at work I can’t get at my online email account anyway…
I’ll continue posting my advice and questions on here though…

Thanks for suggesting it Pat! I’m dying to get a look at this thing…

Reply | Quote

Sorry it took so long for me to answer… Duty called…

Sure… I sent you a private message with my work email address…
I don’t know if I’m allowed to post it here… and while I’m at work I can’t get at my online email account anyway…
I’ll continue posting my advice and questions on here though…

Thanks for suggesting it Pat! I’m dying to get a look at this thing…

Reply | Quote

Would you mind if I sent you the cut down zip database to your e-mail?

Reply | Quote

Okee dokee… Unfortunately it doesn’t look like I’m going to get to see this thing, so let’s try this one piece at a time…

First of all… Let’s talk about the report Sort…

1) I don’t know why you would have put that SortBy field in the query… If the recordsource for the “General Info” Report contain the fields, [Project Classification] and [Y1Sum], and those are the fields you want to sort on that is all you need…

2) DoCmd.OpenReport does not have a Sorting argument so I would just use the Report object itself and the OrderBy property… For example… The following code will open the report needed and set the OrderBy property as well as turn OrderByOn property to True… This is assuming I’m understanding correctly and you have a control option group called “grpSortBy” on the form with three different Options… The user having selected 1 meaning “sort the report by Y1Sum”, 2 meaning “sort the report by Project Classification” and 3 meaning

Reply | Quote

Still, with the zip. It is over the 100. I even tried saving the tables as excel then compacting, but the file is still too large.

Reply | Quote

Compacted, removed all moduels and tables, then zipped. Now only 237KB, however still too large to attach with the limit showing 100KB. Also, according to the note below the attach file space, I do not see MDB.

Reply | Quote

While the database is open… Select the Tools menu and go to Database Utilities -> Compact Database… Try that…

Reply | Quote

Right click on the file and “compact”?
If so, I have tried that before, but when I attach it anywhere, it wants to go as the full size. I “compacted” that way once, when a test FE was going out, but it was too large for our e-mail and we can send up to 9MB internally.

Is there a trick I am missing?

Reply | Quote

Did you compact the database after deleting everything that wasn’t needed? …Delete most of the records in the tables involved??

Reply | Quote

I deleted all the reports except the (2) Generals, all the forms except the 1, all the queries except the (2) generals and all the “user tables”, and other unneeded tables for the database and it only comes down to 21.1MB in size. Then even tried to delete all but only 1 table and still the sam result. Zipping it only brings it down to 5.71MB. Therefore, I do not think a sample is possible.

Reply | Quote

Well that solves the first mystery… You’re getting errors because I told you to put single quotes around “Project Classification” and “Y1Sum”… If these are field names then you need to put square brackets around them… The report’s recordsource is looking for those two Values in a field called SortBy…

Let me look through this and see if I can help… Can I have the names of the recordsources for both reports?

Reply | Quote

It would probably be best if I break it down via the following:
(2) Option Groups
Group1: Me.Criteria
(3) Options reading from one field [Deleted]. Option 1 = Deleted = False…. Option 2 = Deleted = True….. Option 3 = Deleted = True or False (to read all records)
Group2: Me.SortBy (This is how to tell the report how to Sort the data)
First, in the query the following expression was created:
SortBy: Choose([Forms]![InternalReports]![grpSortBy],[Project Classification])
***Side Note**** I have tried changing the query expression to:
Choose([Forms]![InternalReports]![grpSortBy],[Project Classification] or [Y1Sum]) then tried:
Choose([Forms]![InternalReports]![grpSortBy],’Project Classification’) then tried:
Choose([Forms]![InternalReports]![grpSortBy],’Project Classification’ or ‘Y1Sum’)
****End Note
Then, in the report, [SortBy] is used in the sorting and grouping
(3) Options on form for Me.grpSortBy. Option 1 = [SortBy] = Project Classification ….. Option 2 = [SortBy] = Y1Sum
*****Note**** Both Project Classification and Y1Sum are fields in the query. Also, Option 1 or Option 2 is to run report “General Info” **** End Note*****
Option 3 = No Sorting, but instead it runs a different report named “General Info By Code”

Also, PlantSort = “[Plant] = ‘Altima Trim & Chassis'” . In this line I am wanting to tell the report to only pull the records where the field [Plant] = Altima Trim & Chassis.

Well there is the entire section concerning the Select Cases…… As for giving some actual data,
Fields: [Deleted] is a (Yes/No) check box
The rest I believe are explained above.

Reply | Quote

Okay… Let’s try get this working…

To relieve my confusion, I need you to confirm a bit of information before I start writing this code… Me.Criteria and Me.grpSortBy… Criteria and grpSortBy are the names of controls on the form?? or?? … Could you possibly give me a few sample records from the data so that I can see what the field names are and the kind of data?

Reply | Quote

I keep looking at the code you have there and it’s really making no sense to me… Why are you doing a Do While Loop through it?… and you’re not incrementing intCount anywhere that I can see… Aren’t you going into an endless loop there?? …

Could you post a small sample of the database?… I think maybe we should start from scratch… …This should not be so difficult…
What do you want to do… in every day terms… ?

Reply | Quote

I created a similar form for the same database and had a select case that involved Me.Criteria. So, I simply copied the code to this button. Therefore, there maybe some “needless” information, and I am more than open to suggestions……
As for starting over, I hope you mean only this form………… This database is almost complete and has taken months to develop, therefore, starting the entire thing over is not a possibility.
Post a small sample? I can not, “Confidential Data”, and it would take some time to delete the tables and create new tables with dummie data.
I would like to save that as a last resort.

Reply | Quote

Noooo, I didn’t mean start the whole form or database over!
I meant this particular procedure… The command button code… That’s all…

Okay… but I’ll probably have more questions… Hang in there…

Reply | Quote

Noooo, I didn’t mean start the whole form or database over!
I meant this particular procedure… The command button code… That’s all…

Okay… but I’ll probably have more questions… Hang in there…

Reply | Quote

I created a similar form for the same database and had a select case that involved Me.Criteria. So, I simply copied the code to this button. Therefore, there maybe some “needless” information, and I am more than open to suggestions……
As for starting over, I hope you mean only this form………… This database is almost complete and has taken months to develop, therefore, starting the entire thing over is not a possibility.
Post a small sample? I can not, “Confidential Data”, and it would take some time to delete the tables and create new tables with dummie data.
I would like to save that as a last resort.

Reply | Quote

I keep looking at the code you have there and it’s really making no sense to me… Why are you doing a Do While Loop through it?… and you’re not incrementing intCount anywhere that I can see… Aren’t you going into an endless loop there?? …

Could you post a small sample of the database?… I think maybe we should start from scratch… …This should not be so difficult…
What do you want to do… in every day terms… ?

Reply | Quote

Trudi,
I believe you have got me on the right track. The Report did pull up, but had “errors” in every field. Now, I am truely lost, because the simple ‘ ‘ at the [SortBy] caused the report to accept the syntax but read it incorrectly……

Reply | Quote

I’m a little confused, but I believe that your problem is that you need single quotes around the string values for the sortby…

strWhereS = “[SortBy] = ‘Y1Sum'”
strWhereS = “[SortBy] = ‘Project Classification'”

HTH

Reply | Quote

I attach a little demo of this type of thing working.

One option group specifies sort order and one specifies a filter.

The sort order option group is dealt with in the onopen event for the report and sets the orderby property of the report.
The other one is dealt with in the command button and sets the filter. You could also move all this code to the onopen event as an alternative.

Reply | Quote

Hi John… Great example! … Thanks…

For those who are following the thread, I got a sample of the database and fixed it up with pbrown…
We got the control and field names figured out and the code looks very similar to John’s… He did have one other problem though… The “[Parts] = Altima…” part was a mistake… The data in the Parts field was nothing like that… which meant that he got no records returned… Hence the Errors all over the report…

Reply | Quote

Just now had a chance to plug in the code and everything is working as planned/needed.

Thanks again!!!

PBrown

Reply | Quote

Just now had a chance to plug in the code and everything is working as planned/needed.

Thanks again!!!

PBrown

Reply | Quote

Hi John… Great example! … Thanks…

For those who are following the thread, I got a sample of the database and fixed it up with pbrown…
We got the control and field names figured out and the code looks very similar to John’s… He did have one other problem though… The “[Parts] = Altima…” part was a mistake… The data in the Parts field was nothing like that… which meant that he got no records returned… Hence the Errors all over the report…

Reply | Quote

I attach a little demo of this type of thing working.

One option group specifies sort order and one specifies a filter.

The sort order option group is dealt with in the onopen event for the report and sets the orderby property of the report.
The other one is dealt with in the command button and sets the filter. You could also move all this code to the onopen event as an alternative.

Reply | Quote

Here is what I have done.
If Me.grpSortBy Case 3 is choosen with any Case from Me.Criteria, the report runs perfectly. However if Case 1 or 2 from Me.grpSortBy is choosen, the syntax error (missing operator) in query expression ‘([Deleted] = False and [SortBy] = Project Classification and [Plant] = ‘Altima Trim & Chassis’)’

Private Sub command3_Click()
Dim intCount As Integer
Dim strWhere As String
Dim strWhereS As String
Dim strWhereT As String
Dim PlantSort As String

Dim isgm As String

intCount = 1
engall.Visible = False
engalll.Visible = False
bfs.Visible = False
bfsl.Visible = False
pfs.Visible = False
pfsl.Visible = False
tc.Visible = False
tcl.Visible = False
powertrain.Visible = False
powertrainl.Visible = False
EngineeringL.Visible = False

gstrTitle = “Composition / Status”
gstrTitleSt = Choose(Criteria, “Active Items”, “Deleted Items”, “All Items”)
DoCmd.RunMacro “PlantCombo”

Do While intCount <= 2

'DoCmd.Close
Select Case Me.Criteria
Case 1
strWhere = "[Deleted] = False"
Case 2
strWhere = "[Deleted] = True"
Case 3
strWhere = "[Deleted] <=0" ' nothing needed to get all records"
End Select

Select Case Me.grpSortBy
Case 1
strWhereS = "[SortBy] = Y1Sum"

Case 2
strWhereS = "[SortBy] = Project Classification"

Case 3

End Select

strWhereT = strWhere & " and " & strWhereS

If Me.grpSortBy = 1 Or Me.grpSortBy = 2 Then
strWhereT = strWhere & " and " & strWhereS & " and [Plant] = 'Altima Trim & Chassis'"
DoCmd.OpenReport "General Info", acViewPreview, , strWhereT
Else
strWhere = strWhere & " and [Plant] = 'Altima Trim & Chassis'"
DoCmd.OpenReport "General Info By Code", acViewPreview, , strWhere
End If

[Criteria] = 1

Exit Sub

Loop
End Sub

I have looked on MS Knowledge Base, my Access 97 Bible and other "help" sites but have found nothing on combining Select Cases'.
Please, any other ideas or possibly an example would be greatly appriciated.

Reply | Quote

I would suggest that you declare another variable StrReportName to hold the name of the report, and set its value in the second Select statement, then move the docmd.openReport line outside the second select statement.

Also declare another variable to hold the final where condition.

Before you open the report however, build strWhereFinal out of the the selections made in the two select statements.

Something like this: strWhereFinal = strwhere & ” and ” & strwhere1 & ” and ” strPlantsort

DoCmd.OpenReport strReportname, acViewPreview, , strWhereFinal

Reply | Quote