Two Case Statements

[Afrosheen via AccessMonster.com]

Hello and thanks for reading this.

I have two case statements that are basically the same. I would like to
consolidate them in to one if I can.

Select Case flg
Case "Work Plans"
strWhere = "qryTaps.WorkPlan=""" & strMon & _
""" AND qryTaps.Archive=False"
End Select

The second case statement is just about the same.

Select Case flg
Case "Work Plans"
strWhere = "qryTaps.WorkPlan=""" & strMon & _
""" AND qryTaps.Archive=False" & _
" AND qryTaps.Supervisor=""" & Me.cboloc & """"

End Select

The only difference between the two is after the Archive = False etc.

I tried using the if then statement like below, but when I followed the code
it jumped right over it.
head1 = "True"
Select Case flg
Case "Work Plans"
If head1 = "True" Then
strWhere = "qryTaps.WorkPlan=""" & strMon & _
""" AND qryTaps.Archive=False"
Else
strWhere = "qryTaps.WorkPlan=""" & strMon & _
""" AND qryTaps.Archive=False" & _
" AND qryTaps.Supervisor=""" & Me.cboloc & """"
End If

I can use the two case statements if I have to but I'd like to combine them
if possible to save space and have the prg run more efficiently.

Any suggestions would be appreciated.

 

[Ray Milhon]

Not sure what you mean by it jumped right over it but the first thing I would
do is change head1 from a string to boolean. Just seems simpler to me.

head1 = true

If head1 then....

 

[Stefan Hoffmann]

hi,

Any suggestions would be appreciated.

Can you please post the entire code of your original two Select-Case
statements? I don't like to guess right now....


mfG
--> stefan <--

 

[Afrosheen via AccessMonster.com]

Thanks for the quick reply. Here are the two complete case statements. As you
see they are quite long.

Private Sub qbuild()
On Error GoTo qbuild_Error

Select Case flg
Case "Work Plans"
strWhere = "qryTaps.WorkPlan=""" & strMon & _
""" AND qryTaps.Archive=False" & _
" AND qryTaps.Supervisor=""" & Me.cboloc & """"

Case "Late Work Plans"
strWhere = "(((qryTaps.WorkRec) Is Null))" & _
" and qrytaps.WorkPlan=""" & strMon & _
""" and qryTaps.archive=false" & _
" AND qryTaps.Supervisor=""" & Me.cboloc & """"


Case "Interims"
strWhere = "qryTaps.intdue=""" & strMon & _
""" AND qryTaps.Archive=False" & _
" AND qryTaps.Supervisor=""" & Me.cboloc & """"

Case "Late Interims"
strWhere = "(((qryTaps.IntRec)Is Null))" & _
" and qrytaps.IntDue=""" & strMon & _
""" and qryTaps.archive=false" & _
" AND qryTaps.Supervisor=""" & Me.cboloc & """"

Case "Finals Due"
strWhere = "qryTaps.finaldue=""" & strMon & _
""" AND qryTaps.Archive=False" & _
" AND qrytaps.Supervisor=""" & Me.cboloc & """"

Case "Late Finals"
strWhere = "(((qryTaps.FinalRec) Is Null))" & _
" and qrytaps.finaldue=""" & strMon & _
""" and qryTaps.archive=false" & _
" AND qrytaps.Supervisor=""" & Me.cboloc & """"
head1 = "False"
End Select
'Debug.Print strWhere

On Error GoTo 0
Exit Sub

qbuild_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
qbuild of VBA Document Form_Copy of frmtemp"
Err.Description = Err.Description & " In Procedure " & "qbuild of VBA
Document Form_Copy of frmtemp"
Call LogError(Err.Number, Err.Description, "qbuild")


End Sub

'-----------------------------------------------------------------------------
----------
' Procedure : cmdAll1_Click
' Author : Tom
' Date : 5/7/2009
' Purpose :
' Notes :
'-----------------------------------------------------------------------------
----------
'
Private Sub cmdAll1_Click()
On Error GoTo cmdAll1_Click_Error

If IsNull(List55) Or IsNull(grpMonth) Then
MsgBox "You must select from Type of Report List and the Month first.."
List55.SetFocus
Exit Sub
End If

strDocName = "rptTapsCover"

Select Case flg
Case "Work Plans"
strWhere = "qryTaps.WorkPlan=""" & strMon & _
""" AND qryTaps.Archive=False"

Case "Late Work Plans"
strWhere = "(((qryTaps.WorkRec) Is Null))" & _
" and qrytaps.WorkPlan=""" & strMon & _
""" and qryTaps.Archive = False"

Case "Interims"
strWhere = "qryTaps.Intdue=""" & strMon & _
""" AND qryTaps.Archive=False"

Case "Late Interims"
strWhere = "(((qryTaps.IntRec)Is Null))" & _
" and qrytaps.IntDue=""" & strMon & _
""" and qryTaps.Archive = False"

Case "Finals Due"
strWhere = "qryTaps.finaldue=""" & strMon & _
""" AND qryTaps.Archive = False"

Case "Late Finals"
strWhere = "(((qryTaps.FinalRec) Is Null))" & _
" and qrytaps.finaldue=""" & strMon & _
""" and qryTaps.Archive = False"

End Select
'Debug.Print strWhere
head1 = "True"
DoCmd.OpenReport strDocName, acPreview, , strWhere, , List55

head1 = "False"
strWhere = ""
List55 = Null
strMon = ""
Me.grpMonth = Null

On Error GoTo 0
Exit Sub

cmdAll1_Click_Error:

If Err.Number <> 2501 Then
Err.Description = Err.Description & " In Procedure " & "cmdAll1_Click
of VBA Document Form_Copy of frmtemp"
Call LogError(Err.Number, Err.Description, "cmdAll1_Click")
End If
End Sub

 

[Stefan Hoffmann]

Thanks for the quick reply. Here are the two complete case statements. As you
see they are quite long.

Okay. First of all, rewrite your conditions like this using this utility
function in a standard module:

Public Function SQLQuote(AString As String, _
Optional ADelimiter As String = "'" _
) As String

SQLQuote = ADelimiter & _
Replace(AString, ADelimiter, ADelimiter & ADelimiter) & _
ADelimiter

End Function

Your first statment:

Select Case flg
Case "Work Plans"
strWhere = "qryTaps.WorkPlan = " & SQLQuote(strMon)
Case "Late Work Plans"
strWhere = "IsNull(qryTaps.WorkRec) " & _
"AND qrytaps.WorkPlan = " & SQLQuote(strMon)
Case "Interims"
strWhere = "qryTaps.intdue = " & SQLQuote(strMon)
Case "Late Interims"
strWhere = "IsNull(qryTaps.IntRec) " & _
"AND qrytaps.IntDue = " & SQLQuote(strMon)
Case "Finals Due"
strWhere = "qryTaps.finaldue = " & SQLQuote(strMon)
Case "Late Finals"
strWhere = "IsNull(qryTaps.FinalRec) " & _
"AND qrytaps.finaldue = " & SQLQuote(strMon)
head1 = "False"
End Select
strWhere = strWhere & " " & _
"AND NOT qryTaps.Archive " & _
"AND qryTaps.Supervisor = " & SQLQuote(Me.cboloc)

So you can do it.

Private Sub BuildStatement1()

BuildStatement1
strWhere = strWhere & " " & _
"AND qryTaps.Supervisor = " & SQLQuote(Me.cboloc)

End Sub

Private Sub BuildStatement2()

Select Case flg
Case "Work Plans"
strWhere = "qryTaps.WorkPlan = " & SQLQuote(strMon)
Case "Late Work Plans"
strWhere = "IsNull(qryTaps.WorkRec) " & _
"AND qrytaps.WorkPlan = " & SQLQuote(strMon)
Case "Interims"
strWhere = "qryTaps.intdue = " & SQLQuote(strMon)
Case "Late Interims"
strWhere = "IsNull(qryTaps.IntRec) " & _
"AND qrytaps.IntDue = " & SQLQuote(strMon)
Case "Finals Due"
strWhere = "qryTaps.finaldue = " & SQLQuote(strMon)
Case "Late Finals"
strWhere = "IsNull(qryTaps.FinalRec) " & _
"AND qrytaps.finaldue = " & SQLQuote(strMon)
head1 = "False"
End Select
strWhere = strWhere & " " & _
"AND NOT qryTaps.Archive"

End sub



mfG
--> stefan <--

 

[Afrosheen via AccessMonster.com]

Thanks Stefan. I'll give it a shot. I don't understand it quite yet so it
will probably take me the weekend. It looks like I'll do some cut/paste.

Thanks for getting back to me. I hope you're going to be around this weekend.
LOL

 

[Afrosheen via AccessMonster.com]

I'm sorry, this is probably pretty dumb. I understand about the function.
I take it the case statement1 goes in the main program or does it stay in the
module. Then if it stays in the module I call it with the:

Private Sub BuildStatement1()
BuildStatement1
strWhere = strWhere & " " & _
"AND qryTaps.Supervisor = " & SQLQuote(Me.cboloc)
End Sub

Yes?
Then the BuildStatement 2 goes in the main program?

I'm sorry for asking these dumb questions. It's almost like spoon feeding
this one.

Sorry

Thanks Stefan. I'll give it a shot. I don't understand it quite yet so it
will probably take me the weekend. It looks like I'll do some cut/paste.

Thanks for getting back to me. I hope you're going to be around this weekend.
LOL
[quoted text clipped - 74 lines]

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

sorry for the delay.

I'm sorry, this is probably pretty dumb. I understand about the function.
I take it the case statement1 goes in the main program or does it stay in the
module. Then if it stays in the module I call it with the:

Both ways are possible.

Private Sub BuildStatement1()
BuildStatement1
strWhere = strWhere & " " & _
"AND qryTaps.Supervisor = " & SQLQuote(Me.cboloc)
End Sub

Then the BuildStatement 2 goes in the main program?

But you should not distribute these two methods over different modules.
Due to the fact, that you are using global variables and have control
references, you should store it in the module of your form.


mfG
--> stefan <--

 

[Afrosheen via AccessMonster.com]

Thanks for getting back to me. A quick question. I don't know if this can be
done or not, but since this statements main part is the month [strMon] and
archive= false, couldn't I put a If then statement in for the rest?

For example:
select case flg
Case "Work Plans"
strWhere = "qryTaps.WorkPlan=""" & strMon & _
""" AND qryTaps.Archive=False" & ""
if flg2 = true then
strHere = " AND qryTaps.Supervisor=""" & Me.cboloc & """"
endif

shtWhere = stWhere & strHere
end select

I don't know. I know the code is not right because I tried building the
strWhere and adding the strHere. Did a debug.print and it only showed up with
the first part of the strWhere.

Thanks for your help.

hi,

sorry for the delay.

I'm sorry, this is probably pretty dumb. I understand about the function.
I take it the case statement1 goes in the main program or does it stay in the
module. Then if it stays in the module I call it with the:

Both ways are possible.

Private Sub BuildStatement1()
BuildStatement1

[quoted text clipped - 3 lines]

Then the BuildStatement 2 goes in the main program?

But you should not distribute these two methods over different modules.
Due to the fact, that you are using global variables and have control
references, you should store it in the module of your form.

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

Thanks for getting back to me. A quick question. I don't know if this can be
done or not, but since this statements main part is the month [strMon] and
archive= false, couldn't I put a If then statement in for the rest?

You can do it, but if I remember the structure of your code correctly,
then this value is part of every case branch. So paste it after the
Select-Case statement.

I don't know. I know the code is not right because I tried building the
strWhere and adding the strHere. Did a debug.print and it only showed up with
the first part of the strWhere.

The problem is the value of flg2. Where did you set it?

btw, take a look at the top of your module. Does it include a line

Option Explicit

? If not, append it and run Debug/Compile from the menu.


mfG
--> stefan <--

 

[Afrosheen via AccessMonster.com]

I do have Option Explicit enabled at the top.

The flg2 would be set when I press a command button called cmdAll.

Could you please check the code to make sure I've got it correct because it
doesn't seem to be working.


Thanks again for your help

hi,

Thanks for getting back to me. A quick question. I don't know if this can be
done or not, but since this statements main part is the month [strMon] and
archive= false, couldn't I put a If then statement in for the rest?

You can do it, but if I remember the structure of your code correctly,
then this value is part of every case branch. So paste it after the
Select-Case statement.

I don't know. I know the code is not right because I tried building the
strWhere and adding the strHere. Did a debug.print and it only showed up with
the first part of the strWhere.

The problem is the value of flg2. Where did you set it?

btw, take a look at the top of your module. Does it include a line

Option Explicit

? If not, append it and run Debug/Compile from the menu.

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

The flg2 would be set when I press a command button called cmdAll.

Could you please check the code to make sure I've got it correct because it
doesn't seem to be working.

Can you post the entire code of your form?


mfG
--> stefan <--

 

[Afrosheen via AccessMonster.com]

Thanks again Stefan. Please don't get me wrong about what you've already done
for me. I appreciate it. I just thought this way may work also.

'-----------------------------------------------------------------------------
----------
' Procedure : qbuild
' Author : Tom
' Date : 5/7/2009
' Purpose :
' Notes :
'-----------------------------------------------------------------------------
----------
'
Private Sub qbuild()
On Error GoTo qbuild_Error

Select Case flg
Case "Work Plans"
strWhere = "qryTaps.WorkPlan=""" & strMon & _
""" AND qryTaps.Archive=False"

Case "Late Work Plans"
strWhere = "(((qryTaps.WorkRec) Is Null))" & _
" and qrytaps.WorkPlan=""" & strMon & _
""" and qryTaps.archive=false"


Case "Interims"
strWhere = "qryTaps.intdue=""" & strMon & _
""" AND qryTaps.Archive=False"

Case "Late Interims"
strWhere = "(((qryTaps.IntRec)Is Null))" & _
" and qrytaps.IntDue=""" & strMon & _
""" and qryTaps.archive=false"

Case "Finals Due"
strWhere = "qryTaps.finaldue=""" & strMon & _
""" AND qryTaps.Archive=False"

Case "Late Finals"
strWhere = "(((qryTaps.FinalRec) Is Null))" & _
" and qrytaps.finaldue=""" & strMon & _
""" and qryTaps.archive=false"

End Select

If flg2 = true then
strWhere = strWhere & " AND qrytaps.Supervisor=""" & Me.cboloc & """"
endif

On Error GoTo 0
Exit Sub

qbuild_Error:
Err.Description = Err.Description & " In Procedure " & "qbuild of VBA
Document Form_Copy of frmtemp"
Call LogError(Err.Number, Err.Description, "qbuild")


Thanks again.