Case statement Problem few more tries
in [Forms]
|
From: tony Jacobs on 4 Jul 2008 21:22 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. "tony Jacobs" <tony(a)nospam.net> wrote in message news:u6qq#gj3IHA.1192(a)TK2MSFTNGP05.phx.gbl... > 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"[B]*" > Like "[BC]*" > > > I am thinking of trying the combobox without the Left function. > > > "tony Jacobs" <tony(a)nospam.net> wrote in message > news:uN16#Mj3IHA.1204(a)TK2MSFTNGP04.phx.gbl... > > 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 > > > > > > > > "Damon Heron" <damon_327(a)hotmail.com> wrote in message > > news:-YidnckdMbU5N_PVnZ2dnUVZ_hqdnZ2d(a)comcast.com... > > > 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 > > > > > > > > > > > > "tony Jacobs" <tony(a)nospam.net> wrote in message > > > news:eBGBGrf3IHA.2580(a)TK2MSFTNGP06.phx.gbl... > > > > 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. > > > > > > > > "Damon Heron" <damon_327(a)hotmail.com> wrote in message > > > > news:9dmdnXMuvpjXy_PVnZ2dnUVZ_rbinZ2d(a)comcast.com... > > > >> 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 > > > >> > > > >> > > > >> "tony Jacobs" <tony(a)nospam.net> wrote in message > > > >> news:uw4i46e3IHA.5060(a)TK2MSFTNGP02.phx.gbl... > > > >> > Doug; > > > >> > > > > >> > No luck. No Errors but no results. > > > >> > > > > >> > Thanks for your time > > > >> > > > > >> > > > > >> > "Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_canada.com> wrote in > > > >> > message > > > >> > news:u$XS#Re3IHA.1808(a)TK2MSFTNGP04.phx.gbl... > > > >> >> 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 > > > >> >> http://I.Am/DougSteele > > > >> >> (no e-mails, please!) > > > >> >> > > > >> >> > > > >> >> "tony Jacobs" <tony(a)nospam.net> wrote in message > > > >> >> news:uvAAKLe3IHA.3348(a)TK2MSFTNGP03.phx.gbl... > > > >> >> > Sorry John; I did not work ! Any other ideas? > > > >> >> > > > > >> >> > > > > >> >> > "John Spencer" <spencer(a)chpdm.umbc> wrote in message > > > >> >> > news:uB2eF3d3IHA.4272(a)TK2MSFTNGP03.phx.gbl... > > > >> >> >> 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 > > > >> >> >> >> > > > >> >> >> >> > > > >> >> > > > > >> >> > > > > >> >> > > > >> >> > > > >> > > > > >> > > > > >> > > > >> > > > > > > > > > > > > > > > > > > > >
From: strive4peace on 5 Jul 2008 01:41 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 :) * tony Jacobs wrote: > 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. > > > > > > > > > "tony Jacobs" <tony(a)nospam.net> wrote in message > news:u6qq#gj3IHA.1192(a)TK2MSFTNGP05.phx.gbl... >> 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"[B]*" >> Like "[BC]*" >> >> >> I am thinking of trying the combobox without the Left function. >> >> >> "tony Jacobs" <tony(a)nospam.net> wrote in message >> news:uN16#Mj3IHA.1204(a)TK2MSFTNGP04.phx.gbl... >>> 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 >>> >>> >>> >>> "Damon Heron" <damon_327(a)hotmail.com> wrote in message >>> news:-YidnckdMbU5N_PVnZ2dnUVZ_hqdnZ2d(a)comcast.com... >>>> 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 >>>> >>>> >>>> >>>> "tony Jacobs" <tony(a)nospam.net> wrote in message >>>> news:eBGBGrf3IHA.2580(a)TK2MSFTNGP06.phx.gbl... >>>>> 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. >>>>> >>>>> "Damon Heron" <damon_327(a)hotmail.com> wrote in message >>>>> news:9dmdnXMuvpjXy_PVnZ2dnUVZ_rbinZ2d(a)comcast.com... >>>>>> 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 >>>>>> >>>>>> >>>>>> "tony Jacobs" <tony(a)nospam.net> wrote in message >>>>>> news:uw4i46e3IHA.5060(a)TK2MSFTNGP02.phx.gbl... >>>>>>> Doug; >>>>>>> >>>>>>> No luck. No Errors but no results. >>>>>>> >>>>>>> Thanks for your time >>>>>>> >>>>>>> >>>>>>> "Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_canada.com> wrote in >>>>>>> message >>>>>>> news:u$XS#Re3IHA.1808(a)TK2MSFTNGP04.phx.gbl... >>>>>>>> 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 >>>>>>>> http://I.Am/DougSteele >>>>>>>> (no e-mails, please!) >>>>>>>> >>>>>>>> >>>>>>>> "tony Jacobs" <tony(a)nospam.net> wrote in message >>>>>>>> news:uvAAKLe3IHA.3348(a)TK2MSFTNGP03.phx.gbl... >>>>>>>>> Sorry John; I did not work ! Any other ideas? >>>>>>>>> >>>>>>>>> >>>>>>>>> "John Spencer" <spencer(a)chpdm.umbc> wrote in message >>>>>>>>> news:uB2eF3d3IHA.4272(a)TK2MSFTNGP03.phx.gbl... >>>>>>>>>> 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 >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>> >> > >
From: John Spencer on 5 Jul 2008 06:27 Crystal, I believe you can use Like in VBA. I do so on occasion. Perhaps you meant that you can't use Like in Case statements - which I believe is correct. '==================================================== John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County '==================================================== strive4peace wrote: > 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 :) > * > > > > > tony Jacobs wrote: >> 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. >> >> >> >> >> >> >> >> >> "tony Jacobs" <tony(a)nospam.net> wrote in message >> news:u6qq#gj3IHA.1192(a)TK2MSFTNGP05.phx.gbl... >>> 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"[B]*" >>> Like "[BC]*" >>> >>> >>> I am thinking of trying the combobox without the Left function. >>> >>> >>> "tony Jacobs" <tony(a)nospam.net> wrote in message >>> news:uN16#Mj3IHA.1204(a)TK2MSFTNGP04.phx.gbl... >>>> 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 >>>> >>>> >>>> >>>> "Damon Heron" <damon_327(a)hotmail.com> wrote in message >>>> news:-YidnckdMbU5N_PVnZ2dnUVZ_hqdnZ2d(a)comcast.com... >>>>> 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 >>>>> >>>>> >>>>> >>>>> "tony Jacobs" <tony(a)nospam.net> wrote in message >>>>> news:eBGBGrf3IHA.2580(a)TK2MSFTNGP06.phx.gbl... >>>>>> 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. >>>>>> >>>>>> "Damon Heron" <damon_327(a)hotmail.com> wrote in message >>>>>> news:9dmdnXMuvpjXy_PVnZ2dnUVZ_rbinZ2d(a)comcast.com... >>>>>>> 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 >>>>>>> >>>>>>> >>>>>>> "tony Jacobs" <tony(a)nospam.net> wrote in message >>>>>>> news:uw4i46e3IHA.5060(a)TK2MSFTNGP02.phx.gbl... >>>>>>>> Doug; >>>>>>>> >>>>>>>> No luck. No Errors but no results. >>>>>>>> >>>>>>>> Thanks for your time >>>>>>>> >>>>>>>> >>>>>>>> "Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_canada.com> wrote in >>>>>>>> message >>>>>>>> news:u$XS#Re3IHA.1808(a)TK2MSFTNGP04.phx.gbl... >>>>>>>>> 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 >>>>>>>>> http://I.Am/DougSteele >>>>>>>>> (no e-mails, please!) >>>>>>>>> >>>>>>>>> >>>>>>>>> "tony Jacobs" <tony(a)nospam.net> wrote in message >>>>>>>>> news:uvAAKLe3IHA.3348(a)TK2MSFTNGP03.phx.gbl... >>>>>>>>>> Sorry John; I did not work ! Any other ideas? >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> "John Spencer" <spencer(a)chpdm.umbc> wrote in message >>>>>>>>>> news:uB2eF3d3IHA.4272(a)TK2MSFTNGP03.phx.gbl... >>>>>>>>>>> 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 >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>> >> >>
From: tony Jacobs on 5 Jul 2008 09:33 Crystal; IF I do not use the " access complains about it as an error. "strive4peace" <strive4peace2006(a)yahoo.com> wrote in message news:Oga4AHm3IHA.3500(a)TK2MSFTNGP05.phx.gbl... > 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 :) > * > > > > > tony Jacobs wrote: > > 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. > > > > > > > > > > > > > > > > > > "tony Jacobs" <tony(a)nospam.net> wrote in message > > news:u6qq#gj3IHA.1192(a)TK2MSFTNGP05.phx.gbl... > >> 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"[B]*" > >> Like "[BC]*" > >> > >> > >> I am thinking of trying the combobox without the Left function. > >> > >> > >> "tony Jacobs" <tony(a)nospam.net> wrote in message > >> news:uN16#Mj3IHA.1204(a)TK2MSFTNGP04.phx.gbl... > >>> 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 > >>> > >>> > >>> > >>> "Damon Heron" <damon_327(a)hotmail.com> wrote in message > >>> news:-YidnckdMbU5N_PVnZ2dnUVZ_hqdnZ2d(a)comcast.com... > >>>> 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 > >>>> > >>>> > >>>> > >>>> "tony Jacobs" <tony(a)nospam.net> wrote in message > >>>> news:eBGBGrf3IHA.2580(a)TK2MSFTNGP06.phx.gbl... > >>>>> 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. > >>>>> > >>>>> "Damon Heron" <damon_327(a)hotmail.com> wrote in message > >>>>> news:9dmdnXMuvpjXy_PVnZ2dnUVZ_rbinZ2d(a)comcast.com... > >>>>>> 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 > >>>>>> > >>>>>> > >>>>>> "tony Jacobs" <tony(a)nospam.net> wrote in message > >>>>>> news:uw4i46e3IHA.5060(a)TK2MSFTNGP02.phx.gbl... > >>>>>>> Doug; > >>>>>>> > >>>>>>> No luck. No Errors but no results. > >>>>>>> > >>>>>>> Thanks for your time > >>>>>>> > >>>>>>> > >>>>>>> "Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_canada.com> wrote in > >>>>>>> message > >>>>>>> news:u$XS#Re3IHA.1808(a)TK2MSFTNGP04.phx.gbl... > >>>>>>>> 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 > >>>>>>>> http://I.Am/DougSteele > >>>>>>>> (no e-mails, please!) > >>>>>>>> > >>>>>>>> > >>>>>>>> "tony Jacobs" <tony(a)nospam.net> wrote in message > >>>>>>>> news:uvAAKLe3IHA.3348(a)TK2MSFTNGP03.phx.gbl... > >>>>>>>>> Sorry John; I did not work ! Any other ideas? > >>>>>>>>> > >>>>>>>>> > >>>>>>>>> "John Spencer" <spencer(a)chpdm.umbc> wrote in message > >>>>>>>>> news:uB2eF3d3IHA.4272(a)TK2MSFTNGP03.phx.gbl... > >>>>>>>>>> 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 > >>>>>>>>>>>> > >>>>>>>>>>>> > >>>>>>>>> > >>>>>>>> > >>>>>>> > >>>>>> > >>>>> > >>>> > >>> > >> > > > >
|
Pages: 1 Prev: Load Access From Default Drive? Next: Case statement Problem not resolved |