Select Case

[pbrown2]

Currently there are (2) option groups:
Dim strWhere1 as string
Dim strWhere2 as string

1) Me.grpSortBy
Case1
strwhere1 = ......
Case2
strwhere1 = ......
Case3
strwhere1 = ......
2) Me.Criteria
Case1
strwhere2 = ......
Case2
Strwhere2 = ......
Case3
strwhere2 = ......

If there was only on case I know I could use
strWhere1 = strWhere1 & " and [Plant] = 'Area1'"

However, how do you combined 2 different select cases?
I have tried something like:
StrWhereT = strwhere1 & strwhere2
and tried
strwhereT = strwhere1 + strwhere2
then replaced strwhere1 in the codes end by strwhereT. However that did not work.

That is why I would like to know how to combined (2) Select cases....
Any type of example would be appreicated.

Thank you for any and all help,

PBrown

 

[Eupher]

Have you tried:

StrWhereT = strwhere1 & " AND " & strwhere2

Ken S.

 

[pbrown2]

Thanks, at least I am getting closer..... It is now giving me syntax errors, instead of just running the report and skipping the Me.Criteria and running the report.

The error is syntax error in the expression:

[Deleted] = trueAND[sortby]=project classification and [Plant] = Area1

Perhaps if you see the "lower" portion you may see something.

The 'Else' part of the IF statement at the bottom is working. I believe it is simply because that part really only uses Me.Criteria and the Me.grpSortBy is not needed or wanted.

It seems it is all in the joining of Me.Criteria and Me.grpSortBy

Select Case Me.Criteria
Case 1
strWhere = "[Deleted] = False"
Case 2
strWhere = "[Deleted] = True"
Case 3
strWhere = &quot;[Deleted] <=0&quot; ' nothing needed to get all records&quot;
End Select
Select Case Me.grpSortBy
Case 1
strWhereS = &quot;[SortBy] = Y1Sum&quot;

Case 2
strWhereS = &quot;[SortBy] = Project Classification&quot;

Case 3

End Select
strWhereT = strWhere & &quot;AND&quot; & strWhereS

If Me.grpSortBy = 1 Or Me.grpSortBy = 2 Then
strWhereT = strWhereT & &quot; and [Plant] = 'Area1'&quot;
DoCmd.OpenReport &quot;General Info&quot;, acViewPreview, PlantSort, strWhereT
Else
strWhere = strWhere & &quot; and [Plant] = 'Area1'&quot;
DoCmd.OpenReport &quot;General Info By Code&quot;, acViewPreview, , strWhere
End If

[Criteria] = 1

Exit Sub

Loop
End Sub


Thank you for any and all help,

PBrown

 

[pbrown2]

Found (1) thing...
If I move the &quot;End Select&quot; for Me.Criteria to after the &quot;End Select&quot; for Me.grpSortBy, almost everything works.
The one thing that does not work is if I choose Case 3 for Me.Criteria, which is Deleted (True or False)
I have tried changing Case 3 to:
1) strwhere = &quot;[Deleted] = True&quot; or &quot;[Deleted] = False&quot;
2) strwhere = &quot;[Deleted] = ' '&quot;
3) strwhere = &quot;[Deleted] = &quot;

However, nothing seems to work. I get the same syntax error that was there before I move the &quot;End Select&quot;


Thank you for any and all help,

PBrown

 

[Eupher]

The End Select should not be moved; keep your Select Case statements separate or the whole 2nd Select Case will be evaluated only if the 3rd option is selected under the first Select Case - that is, you'll end up with nested Select Case statements. This can be useful, but not in this context.

The AND in the concatenation must be surrounded by spaces as in my first example: &quot; AND &quot;

Ken S.

 

[pbrown2]

Here is what I now have:
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 = &quot;Composition / Status&quot;
gstrTitleSt = Choose(Criteria, &quot;Active Items&quot;, &quot;Deleted Items&quot;, &quot;All Items&quot
DoCmd.RunMacro &quot;PlantCombo&quot;


Do While intCount <= 2




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

Select Case Me.grpSortBy
Case 1
strWhereS = &quot;[SortBy] = Y1Sum&quot;

Case 2
strWhereS = &quot;[SortBy] = Project Classification&quot;

Case 3

End Select

strWhereT = strWhere & &quot; and &quot; & strWhereS


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

[Criteria] = 1

Exit Sub

Loop
End Sub

However, Now neither Case 1 or 2 from Me.grpSortBy will work with any Case from Me.Criteria. (Me.grpSortBy Case 3 still works with Case 1, 2 or 3 from Me.Criteria).

Any other ideas?

Thank you for any and all help,

PBrown

 

[CajunCenturion]

Do you need to surround your strWhereS values with single quotes:

Case 1
   strWhereS = "[SortBy] =

'

Y1Sum

'

"
Case 2
   strWhereS = "[SortBy] =

'

Project Classification

'

"

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[pbrown2]

Now the reports run for Me.grpSortBy Case 1 & 2 for all cases for me.criteria. However, the results are &quot;Errors&quot; in every field.
In the query, SortBy =
SortBy: Choose([Forms]![InternalReports]![grpSortBy],'Project Classification' Or 'Y1sum')
Also Tried:
SortBy: Choose([Forms]![InternalReports]![grpSortBy],[Project Classification] Or [Y1sum])

Since Project Classification and Y1Sum are actual fields in the report.

Is it getting closer?

Thank you for any and all help,

PBrown

 

[CajunCenturion]

The syntax for the Choose function is not correct. I suggest that you look up the Choose function in the Help files and check out the examples.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein