Case statement Problem

[tony Jacobs]

Crystal;

IF I do not use the " access complains about it as an error.

Hi Tony,

this is not right:
Case "Like'B*'"

firstly, you would need a space after the word Like; second, you would
not include Like in the quotes (it is, essentially, the operator) -- and
Access does not interpret what is in quotes as part of code syntax ...
but anyway, you cannot use 'Like' in VBA code (works fine in SQL -- ie:
queries). If you want to test the first character for "B", you need to
do what Doug suggested:

Select Case Left(Me.Combo28.Column(1) & "", 1)
Case "B"



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

I tried the code without the left function, and my debugging message box
returns what is actually in the combobox column(1) which is any of the below
criteria. here is the code:
.............................................................................
.............................................................................
.............

Debug.Print Me.Combo28.Column(1)

' Select Case Left(Me.Combo28 & "", 1)
' Select Case Left(Me![Combo28].Column(1), 1)

Dim myrpt As Variant
myrpt = Me.Combo28.Column(1)

Select Case myrpt

Case "Like'B*'"

stDocName = "Productsreport1"

Case "Like'C*'"

' Case Left(Me![Combo28].Column(1), 1) = "C"

stDocName = "Productsreport2"

Case Else

stDocName = "Productsreport"

End Select

MsgBox ("The ComboBox Value is " & Me.Combo28.Column(1)), vbInformation,
" OOps "
DoCmd.OpenReport stDocName, acViewPreview, , "ProductID IN " &
strSelected

EndIT:

End Sub
.............................................................................
.............................................................................
...................

the immediate window returns the following or has returned the following ( I
figured out how to work it now)
(below using the left statement)
L
L
L

using only the (Me.Combo28.Column(1))

Like 'C*'
Like 'C*'
Like 'C*'
Like 'P*'
Like 'B*'
.............................................................................
.............................................................................
....................

Still the report printed is the last one which is in the case else
condition.

Okay a quick update: I put a message box that printed the value of the Combo
box, and the left statement returns the letter "L" only

that's why the report selection is bypassed to the Case Else Statement ..

Please note that the coulmn(1) in the combobox is as follows

Column(1) is:
Like 'C*'
Like 'P*'
Like 'B*'
Like '*'
Like 'Z*'
Like"*"
Like "[BC]*"


I am thinking of trying the combobox without the Left function.


Compile Error
Expected Expression if I put ? Debug.Print

Left(Me.Combo28.Column(1)
&

"", 1) in thr immediate window

Also I get an error that the Value of the Combo Box is not set yet.or not
calculated. I used to be able to use the immediate window very
successfully.
Please tell me if I am doing something wrong.

Thanks



Before the Select Case statement, put:

debug.print Left(Me.Combo28.column(1) & "",1)

if the correct answer appears in the immediate window, then you might
try:
dim myrpt as variant
myrpt= Left(Me.Combo28.Column(1) & "",1)
Select Case myrpt
Case "B"
...........
Case "C"
etc.

There is no way the correct report name would not be assigned to
stDocName
in this case.

Damon



No Damon;

The problem is that the same report opens in all cases. Which means
that
the
case statement is not picking up the value of the combo box.
The open report works perfectly and it tells me which records in the
list
box it is about to print.

Now what I am trying to do is limit the list box to all items that
start
with a C, then print the C report

Limit items to all that start with a B then print all items that Start
with
a B , then Print the B report (I called it report2 for example).

The "ProductID IN " & strSelected statement.is flawless. I worked it
out.
Happy 4th of July. Proud to be an American.

I would suggest you put a breakpoint at the beginning of your case
stmt
and
step thru the code, checking values-- if the code picks the correct
report
name, then the problem is in your DoCmd.OpenReport stDocName,
acViewPreview,
, "ProductID IN " & strSelected statement.

Damon


Doug;

No luck. No Errors but no results.

Thanks for your time


message
Given you were trying to use Column(1) in your first example, try
Select Case Left(Me.Combo28.Column(1) & "", 1)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Sorry John; I did not work ! Any other ideas?


Select Case Left(Me.Combo28 & "",1)

Case "B"
stDocName = "Productsreport1"

Case "C"

stDocName = "Productsreport2"

Case Else

stDocName = "Productsreport"

End Select

DoCmd.OpenReport stDocName, acViewPreview, , "ProductID IN
"
&
strSelected


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


strive4peace wrote:
hi Tony

"Me![Combo28].column(1) = Like'B*'"
--> left(Me![Combo28].column(1),1) = "B"

"Me![Combo28].column(1) = 'Like'C*''"
--> left(Me![Combo28].column(1),1) = "C"


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




tony Jacobs wrote:
I am having trouble with this code can anyone help.

I have the follwing case statement that checks the value of
the
combo
box,
and based on that it will select which report to print, but
I
can
not
get it
to work

Select Case ReportPrint

Case "Me![Combo28].column(1) = Like'B*'"
stDocName = "Productsreport1"

Case "Me![Combo28].column(1) = 'Like'C*''"

stDocName = "Productsreport2"

Case Else

stDocName = "Productsreport"

End Select
DoCmd.OpenReport stDocName, acViewPreview, , "ProductID
IN
"
&
strSelected


Thanks in advance

 

[tony Jacobs]

Doug;
The Value of the Me.Combo28.Column(1) is Like 'B*' , and any other
variations.(Like 'C*', etc.....). You helped me with this earlier on. The
lstbox gets poulated with items that start with a B when I select a combo
box value of Like 'B*', and so on.

No all I want to do is print the items in the lstbox , using the
corresponding report. i.e: print
the B* ( items that start with a B) on the B report (Report2) , C Items ..
By the way; I put a message box that returns the contents of
Me.Combo28.Column(1), and it returns the correct value.

I am thinking of using If statements. This is my first time using Case
statements. and the spaces between the Like and the first ' is correct,
only one space.

I am Stumped now.

The problem would appear to be that the value in Me.Combo28.Column(1) is not
what you're expecting it to be. In other words, whatever value is there
never starts with either a B or a C.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Damon;
I tried your code and I added 3 message boxes at the end of the code to
see
the value of the stDocName and it is always the report name in the Case
else
statement.

It seems that we are close, but the conditional reports are not being
picked
up .

Before the Select Case statement, put:

debug.print Left(Me.Combo28.column(1) & "",1)

if the correct answer appears in the immediate window, then you might
try:
dim myrpt as variant
myrpt= Left(Me.Combo28.Column(1) & "",1)
Select Case myrpt
Case "B"
...........
Case "C"
etc.

There is no way the correct report name would not be assigned to
stDocName
in this case.

Damon



No Damon;

The problem is that the same report opens in all cases. Which means
that
the
case statement is not picking up the value of the combo box.
The open report works perfectly and it tells me which records in the list
box it is about to print.

Now what I am trying to do is limit the list box to all items that
start
with a C, then print the C report

Limit items to all that start with a B then print all items that Start
with
a B , then Print the B report (I called it report2 for example).

The "ProductID IN " & strSelected statement.is flawless. I worked it out.

Happy 4th of July. Proud to be an American.

I would suggest you put a breakpoint at the beginning of your case
stmt
and
step thru the code, checking values-- if the code picks the correct
report
name, then the problem is in your DoCmd.OpenReport stDocName,
acViewPreview,
, "ProductID IN " & strSelected statement.

Damon


Doug;

No luck. No Errors but no results.

Thanks for your time


message
Given you were trying to use Column(1) in your first example, try

Select Case Left(Me.Combo28.Column(1) & "", 1)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Sorry John; I did not work ! Any other ideas?


Select Case Left(Me.Combo28 & "",1)

Case "B"
stDocName = "Productsreport1"

Case "C"

stDocName = "Productsreport2"

Case Else

stDocName = "Productsreport"

End Select

DoCmd.OpenReport stDocName, acViewPreview, , "ProductID IN

"
&

strSelected


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


strive4peace wrote:
hi Tony

"Me![Combo28].column(1) = Like'B*'"
--> left(Me![Combo28].column(1),1) = "B"

"Me![Combo28].column(1) = 'Like'C*''"
--> left(Me![Combo28].column(1),1) = "C"


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




tony Jacobs wrote:
I am having trouble with this code can anyone help.

I have the follwing case statement that checks the value of the
combo
box,
and based on that it will select which report to print, but I
can
not
get it
to work

Select Case ReportPrint

Case "Me![Combo28].column(1) = Like'B*'"
stDocName = "Productsreport1"

Case "Me![Combo28].column(1) = 'Like'C*''"

stDocName = "Productsreport2"

Case Else

stDocName = "Productsreport"

End Select
DoCmd.OpenReport stDocName, acViewPreview, , "ProductID

IN
"

&
strSelected


Thanks in advance

 

[tony Jacobs]

I got it Brutus........LoL

All code was good, problem was one space between the like and the 'C*' in
the case statement with the many iterations I may have Phat fingered it.
Thank you all for your time. A light bulb lit when crystal mentioned the
space. After I am done with this project, I'll try an If statement scenerio.

Thanks All. It works perfectly now. Thanks for getting me to use the
immediate window again. it's been 3 years.

Have a great week end. Now Me Go Get Some Brewskie !!!!

 

[Marshall Barton]

I am having trouble with this code can anyone help.

I have the follwing case statement that checks the value of the combo box,
and based on that it will select which report to print, but I can not get it
to work

Select Case ReportPrint

Case "Me![Combo28].column(1) = Like'B*'"
stDocName = "Productsreport1"

Case "Me![Combo28].column(1) = 'Like'C*''"

stDocName = "Productsreport2"

Case Else

stDocName = "Productsreport"

End Select
DoCmd.OpenReport stDocName, acViewPreview, , "ProductID IN " &
strSelected

I think you are going about this the wrong way. The idea of
using Select Case for the Where clause from your table is
hard coding way too much.

I suggest that you add another field to the table. The
field would indicate which report should be used and can be
included in the combo box's RowSource as Column(2). This
way, the code would have no dependency on the where
condition.

If the field contains 1 for the Productsreport1 case, 2 for
Productsreport2 and nothing for when you want to use
Productsreport, then the code could simply be:

stDocName = "Productsreport" & Me.combo28.Column(2)
DoCmd.OpenReport stDocName, . . .

 

[Douglas J. Steele]

If the combo box is returning Like 'B*', you need code like:

Select Case Nz(Me.Combo28.Column(1), "")
Case "Like 'B*'"
stDocName = "Productsreport1"
Case "Like 'C*'"
stDocName = "Productsreport2"
Case Else
stDocName = "Productsreport"
End Select


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug;
The Value of the Me.Combo28.Column(1) is Like 'B*' , and any other
variations.(Like 'C*', etc.....). You helped me with this earlier on. The
lstbox gets poulated with items that start with a B when I select a combo
box value of Like 'B*', and so on.

No all I want to do is print the items in the lstbox , using the
corresponding report. i.e: print
the B* ( items that start with a B) on the B report (Report2) , C Items ..
By the way; I put a message box that returns the contents of
Me.Combo28.Column(1), and it returns the correct value.

I am thinking of using If statements. This is my first time using Case
statements. and the spaces between the Like and the first ' is correct,
only one space.

I am Stumped now.

The problem would appear to be that the value in Me.Combo28.Column(1) is not
what you're expecting it to be. In other words, whatever value is there
never starts with either a B or a C.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Damon;
I tried your code and I added 3 message boxes at the end of the code to
see
the value of the stDocName and it is always the report name in the Case
else
statement.

It seems that we are close, but the conditional reports are not being
picked
up .


Before the Select Case statement, put:

debug.print Left(Me.Combo28.column(1) & "",1)

if the correct answer appears in the immediate window, then you might
try:
dim myrpt as variant
myrpt= Left(Me.Combo28.Column(1) & "",1)
Select Case myrpt
Case "B"
...........
Case "C"
etc.

There is no way the correct report name would not be assigned to
stDocName
in this case.

Damon



No Damon;

The problem is that the same report opens in all cases. Which means
that
the
case statement is not picking up the value of the combo box.
The open report works perfectly and it tells me which records in the
list
box it is about to print.

Now what I am trying to do is limit the list box to all items that
start
with a C, then print the C report

Limit items to all that start with a B then print all items that Start
with
a B , then Print the B report (I called it report2 for example).

The "ProductID IN " & strSelected statement.is flawless. I worked it
out.

Happy 4th of July. Proud to be an American.

I would suggest you put a breakpoint at the beginning of your case
stmt
and
step thru the code, checking values-- if the code picks the correct
report
name, then the problem is in your DoCmd.OpenReport stDocName,
acViewPreview,
, "ProductID IN " & strSelected statement.

Damon


Doug;

No luck. No Errors but no results.

Thanks for your time


message
Given you were trying to use Column(1) in your first example,
try

Select Case Left(Me.Combo28.Column(1) & "", 1)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Sorry John; I did not work ! Any other ideas?


Select Case Left(Me.Combo28 & "",1)

Case "B"
stDocName = "Productsreport1"

Case "C"

stDocName = "Productsreport2"

Case Else

stDocName = "Productsreport"

End Select

DoCmd.OpenReport stDocName, acViewPreview, , "ProductID
IN "
&
strSelected


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


strive4peace wrote:
hi Tony

"Me![Combo28].column(1) = Like'B*'"
--> left(Me![Combo28].column(1),1) = "B"

"Me![Combo28].column(1) = 'Like'C*''"
--> left(Me![Combo28].column(1),1) = "C"


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




tony Jacobs wrote:
I am having trouble with this code can anyone help.

I have the follwing case statement that checks the value
of
the
combo
box,
and based on that it will select which report to print,
but I
can
not
get it
to work

Select Case ReportPrint

Case "Me![Combo28].column(1) = Like'B*'"
stDocName = "Productsreport1"

Case "Me![Combo28].column(1) = 'Like'C*''"

stDocName = "Productsreport2"

Case Else

stDocName = "Productsreport"

End Select
DoCmd.OpenReport stDocName, acViewPreview, , "ProductID IN
"
&
strSelected


Thanks in advance

 

[tony Jacobs]

Excellent Idea. I love it. I will change it to that.

Thanks Marsh !!!

I am having trouble with this code can anyone help.

I have the follwing case statement that checks the value of the combo box,
and based on that it will select which report to print, but I can not get it
to work

Select Case ReportPrint

Case "Me![Combo28].column(1) = Like'B*'"
stDocName = "Productsreport1"

Case "Me![Combo28].column(1) = 'Like'C*''"

stDocName = "Productsreport2"

Case Else

stDocName = "Productsreport"

End Select
DoCmd.OpenReport stDocName, acViewPreview, , "ProductID IN " &
strSelected

I think you are going about this the wrong way. The idea of
using Select Case for the Where clause from your table is
hard coding way too much.

I suggest that you add another field to the table. The
field would indicate which report should be used and can be
included in the combo box's RowSource as Column(2). This
way, the code would have no dependency on the where
condition.

If the field contains 1 for the Productsreport1 case, 2 for
Productsreport2 and nothing for when you want to use
Productsreport, then the code could simply be:

stDocName = "Productsreport" & Me.combo28.Column(2)
DoCmd.OpenReport stDocName, . . .