432,257 Members | 925 Online
Need help? Post your question and get tips & solutions from a community of 432,257 IT Pros & Developers. It's quick & easy.

# Best way to tell if X is between two values? A97

 P: n/a 225 If 1 <= MyCount <= 4 Then GString = "a few days ago. " 230 If 5 <= MyCount <= 10 Then GString = "a week or so ago. " 235 If 11 <= MyCount <= 17 Then GString = "about 2 weeks ago. " 240 If 18 <= MyCount <= 24 Then GString = "about 3 weeks ago. " 245 If 25 <= MyCount <= 30 Then GString = "about a month ago. " The above lines do not do what I expected. What's the best way to do what I'm trying to do. If you're confused by the above syntax, then don't bother - its old stuff. Nov 21 '05 #1
46 Replies

 P: n/a 225 If 1 <= MyCount And MyCount <= 4 Then GString = "blan" 230 If 5 <= MyCount And MyCount <= 10 Then GString = "blah " 235 If 11 <= MyCount And MyCount <= 17 Then GString = "blah" 240 If 18 <= MyCount And MyCount <= 24 Then GString = "blah" I know about the above syntax, OK. I just wondered if there was another way similar to the old-style shorthand approach in the OP? Nov 21 '05 #2

 P: n/a Might be a bit long winded to type in all the cases but - Select Case MyCount Case ..... End Select or perhaps with the between operator Select Case MyCount Case MyCount Between 1 and 4 GString = "a few days ago" Case MyCount Between 5 And 10 GString = "a week or so ago" ..... End Select -- Nick Coe (UK) http://www.alphacos.co.uk/ AccHelp + pAnimal http://www.pjandcoe.co.uk/ Online Store http://www.mrcomputersltd.com/ Repairs Upgrades In news:09********************************@4ax.com, MLH typed: 225 If 1 <= MyCount <= 4 Then GString = "a few days ago. " 230 If 5 <= MyCount <= 10 Then GString = "a week or so ago. " 235 If 11 <= MyCount <= 17 Then GString = "about 2 weeks ago. " 240 If 18 <= MyCount <= 24 Then GString = "about 3 weeks ago. " 245 If 25 <= MyCount <= 30 Then GString = "about a month ago. " The above lines do not do what I expected. What's the best way to do what I'm trying to do. If you're confused by the above syntax, then don't bother - its old stuff. Nov 21 '05 #3

 P: n/a "Nick Coe (UK)" wrote in message news:43**********************@ptn-nntp-reader02.plus.net... Might be a bit long winded to type in all the cases but - Select Case MyCount Case .... End Select or perhaps with the between operator Select Case MyCount Case MyCount Between 1 and 4 GString = "a few days ago" Case MyCount Between 5 And 10 GString = "a week or so ago" .... End Select -- Nick Coe (UK) Select Case is the ideal solution for this, but use the To operator. Select Case MyCount Case 1 To 4 : GS.... Case 5 To 10: GS... etc End Select One reason for choosing Case over the original posted code is efficiency. It will only evaluate until a match is found then exit the Case statement. With the earlier posted code, the series of IF...Then statements, all would get evaluated, even if the first one hit. Randy Nov 21 '05 #4

 P: n/a The Switch function would be another way of doing this. The Select Case method is probably easier to write but it's worthwhile looking at the Switch function in the Help file and being aware of what it does. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications re******@pcdatasheet.com www.pcdatasheet.com If you can't get the help you need in the newsgroup, I can help you for a very reasonable fee. Over 1000 Access users have come to me for help. Need a month calendar or 7 day calendar? Need appointment scheduling? Need room reservations scheduling? Need employee work scheduling? Contact me! "MLH" wrote in message news:09********************************@4ax.com... 225 If 1 <= MyCount <= 4 Then GString = "a few days ago. " 230 If 5 <= MyCount <= 10 Then GString = "a week or so ago. " 235 If 11 <= MyCount <= 17 Then GString = "about 2 weeks ago. " 240 If 18 <= MyCount <= 24 Then GString = "about 3 weeks ago. " 245 If 25 <= MyCount <= 30 Then GString = "about a month ago. " The above lines do not do what I expected. What's the best way to do what I'm trying to do. If you're confused by the above syntax, then don't bother - its old stuff. Nov 21 '05 #5

 P: n/a steve, time to go back to school. The OP request dealt with ranges of values, Switch deals with discrete values. As Nick and Randy have mentioned, the solution is Select Case... John... Visio MVP "PC Datasheet" wrote in message news:NQ****************@newsread2.news.atl.earthli nk.net... The Switch function would be another way of doing this. The Select Case method is probably easier to write but it's worthwhile looking at the Switch function in the Help file and being aware of what it does. PC Datasheet "MLH" wrote in message news:09********************************@4ax.com... 225 If 1 <= MyCount <= 4 Then GString = "a few days ago. " 230 If 5 <= MyCount <= 10 Then GString = "a week or so ago. " 235 If 11 <= MyCount <= 17 Then GString = "about 2 weeks ago. " 240 If 18 <= MyCount <= 24 Then GString = "about 3 weeks ago. " 245 If 25 <= MyCount <= 30 Then GString = "about a month ago. " Nov 21 '05 #6

 P: n/a MLH wrote: 225 If 1 <= MyCount <= 4 Then GString = "a few days ago. " 230 If 5 <= MyCount <= 10 Then GString = "a week or so ago. " 235 If 11 <= MyCount <= 17 Then GString = "about 2 weeks ago. " 240 If 18 <= MyCount <= 24 Then GString = "about 3 weeks ago. " 245 If 25 <= MyCount <= 30 Then GString = "about a month ago. " The above lines do not do what I expected. What's the best way to do what I'm trying to do. If you're confused by the above syntax, then don't bother - its old stuff. With your syntax the confusting thing is the <= and <=. Usually one expects >= and <= to get a range. Nov 21 '05 #7

 P: n/a "PC Datasheet" wrote in message news:NQ****************@newsread2.news.atl.earthli nk.net... The Switch function would be another way of doing this. The Select Case method is probably easier to write but it's worthwhile looking at the Switch function in the Help file and being aware of what it does. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications re******@pcdatasheet.com www.pcdatasheet.com A quick note to anyone reading this message thread. This newsgroup is an excellent resource for getting help with your Access projects. If you feel the need, however, to seek professional help, I strongly advise that you not consider hiring this PC Datasheet character. Steve is a bright kid but is totally unprofessional. There are literally hundreds of competent Access consultants available. Many post regularly in this newsgroup, others can easily be located by googling or visiting guru.com. Nov 21 '05 #8

 P: n/a "PC Datasheet" schreef in bericht news:NQ****************@newsread2.news.atl.earthli nk.net... If you can't get the help you need in the newsgroup, I can help you for a very reasonable fee. Over 1000 Access users have come to me for help. But NONE of these 1000 users come to your aid here ... ??? Need a month calendar or 7 day calendar? Need appointment scheduling? Need room reservations scheduling? Need employee work scheduling? Contact me! Need wrong answers? Contact Steve! Need to hire a lame coward? Contact Steve! Need a person with absolutely NO ethics? Contact Steve! Arno R Nov 21 '05 #9

 P: n/a PC Datasheet wrote: MsgBox MyVariant Run this code and see what you get!!! Moreover, Switch() evaluates all of its arguments - a great feature in more advanced apps!! -- Paul Nov 21 '05 #12

 P: n/a "PC Datasheet" wrote in message news:Jy***************@newsread3.news.atl.earthlin k.net... -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications re******@pcdatasheet.com www.pcdatasheet.com A quick note to anyone reading this message thread. This newsgroup is an excellent resource for getting help with your Access projects. If you feel the need, however, to seek professional help, I strongly advise that you not consider hiring this PC Datasheet character. Steve is a bright kid but is totally unprofessional. There are literally hundreds of competent Access consultants available. Many post regularly in this newsgroup, others can easily be located by googling or visiting guru.com. Nov 21 '05 #13

 P: n/a "PC Datasheet" schreef in bericht news:Jy***************@newsread3.news.atl.earthlin k.net... Yes, Salad is correct. If what is posted is just meant to illustrate the question then fine. Otherwise the If statements need to be written like: If MyCount >= 1 And MyCount <= 4 Then GString = "a few days ago. " Thanks, we did *not* know that... Great contribution, thanks! But IMO Select Case is still the preferred method here as Randy and Nick wrote hours ago ... You don't show a Dim statement but presumably MyCount is dimmed as an Integer. However, if MyCount is dimmed as Single and can have a fractional part such as 4.5, then your code is flawed because you don't account for the this possibility. Your code does not account for values between 4 and 5. Who asked for this lecture? You are only talking bullshit again, bla bla bla bla bla bla bla bla The Code does not account for *various* values ... Probably this code does not need to, because MLH is still 'counting the days' I guess? Again a pseudo answer *only* posted to 'drop' your advertising here. Arno R Nov 21 '05 #14

 P: n/a I'll partially concede that to you, but the Select Case evalutes what is necessary, Switch evaluates all. Unlike the Select Case which has an ELSE clause, all conditions in a Switch must be addressed. For coding readabilty, the Select is easier to read. Select Case MyCount Case 1 To 4: GString = "a few days ago. " Case 5 To 10: GString = "a week or so ago. " Case 11 To 17: GString = "about 2 weeks ago. " Case 18 To 24: GString = "about 3 weeks ago. " Case 25 To 30: GString = "about month ago. " Case Else: GString = " " End Select GString = Switch(MyCount >= 1 And MyCount <= 4, "a few days ago. ", _ MyCount >= 5 And MyCount <= 10, "a week or so ago. ", _ MyCount >= 11 And MyCount <= 17, "about 2 weeks ago. ", _ MyCount >= 18 And MyCount <= 24, "about 3 weeks ago. ", _ MyCount >= 25 And MyCount <= 30, "about month ago. ", _ MyCount > 30, " ", MyCount < 1, " ") So when should we expect the flood of apologies and answers that you owe this newsgroup? John... Visio MVP Nov 21 '05 #15

 P: n/a "PC Datasheet" schreef in bericht news:EX***************@newsread3.news.atl.earthlin k.net... What's left ?? Arno R Nov 23 '05 #17

 P: n/a Simple, those are webpages that you have to go to see the that. The webpage also gives more insight into who you are dealing with. Yes, it is pure advertising, but it is NOT in this newsgroup. He only adds three short lines, yours is eight long winded lines. Of course, the big reason is that he actually has useful information BEFORE his signature. So there is no reason to harrass him. John... Visio MVP "PC Datasheet" wrote in message news:e7*****************@newsread1.news.atl.earthl ink.net... By the way ---- Nick Coe (UK) http://www.alphacos.co.uk/ AccHelp + pAnimal http://www.pjandcoe.co.uk/ Online Store http://www.mrcomputersltd.com/ Repairs Upgrades Go to the first URL and it says "I am available for hire short term, long term or one off fixed price job to develop, design, support ......" He does not work for free!! Why aren't you harassing him??? Go to the second URL and it's about selling T-Shirts with pictures of dogs on them. That has nothing to do with Access!!! Why aren't you harassing him??? Go to the third URL and it says "Call or EMail Today For A Competitive Quotation" (for PCs, Laptops, etc) That's pure advertising!!! Why aren't you harassing him??? Nov 23 '05 #20

 P: n/a StopThisAdvertising wrote: "PC Datasheet" schreef in bericht news:Jy***************@newsread3.news.atl.earthlin k.net...Yes, Salad is correct. If what is posted is just meant to illustrate thequestion then fine. Otherwise the If statements need to be written like:If MyCount >= 1 And MyCount <= 4 Then GString = "a few days ago. " Thanks, we did *not* know that... Great contribution, thanks! But IMO Select Case is still the preferred method here as Randy and Nick wrote hours ago ... Yes. I would use Select Case. I was simply noting the < < stuff. But your sarcasm meter is so high I'd almost think I'm in a girly-boy catfight. The only person I have an issue with is/has been the moron with the hard-on for David F. His posts have made this place a pisshole in the past. Your catfight with Datasheet just adds to the BS in this place. If you have anything to contribute to the group, contribute. Your war with Datasheet is simply pissing and moaning in the wind for the sake of nothing. Grow up and get a life. Nov 23 '05 #24

 P: n/a "PC Datasheet" wrote in message news:Zv***************@newsread3.news.atl.earthlin k.net... You're the dimmest of the dimmest dimwits. It's hard to believe that anyone could be as stupid as you. First you say it works and now you say it doesn't work. It's just like everything you have been posting - it's all a big fabrication (oops, sorry! you will have to get someone to explain what that big-ggg word means!). Over and over you show your ignorance and stupidity!!! Excellent professional discussion. Definitely someone I should hire. If you look at the code you will see that MyCount has to be an integer and it has to be between 1 and 10. Sure it will crash outside those parameters!!! The original poster used 1 to 30, your code did not handle that. Any good code should handle exceptions or is this an example of the "quality" of work you get paid for? You're so stupid that you tried it but then again I forgot that you (and your mama) can't read. The constraints of those parameters is exactly what my other post is about where your dimwitted protoge condemned me for posting. And now his dimwitted underling stumbles over it. Why don't you go to Keith W the third member of the dimwiteers and have him explain it to you. It's just unbelievable how stupid you are !!!!!!!!!!!!!!!!!!!!!! More exquisite prose. PC Datasheet Strange way of spelling steve. John... Visio MVP Nov 23 '05 #26

 P: n/a "PC Datasheet" wrote in message news:lN***************@newsread3.news.atl.earthlin k.net... Once again your stupidity shines like a bright star!!! Where do you think I got his URL for selling T-Shirts with dogs pictures on them. Read my lips -- right out of CDMA!! You admit that it is pure advertising and yet you say it is okay. I'm offering fee-based help with Access to anyone who needs it. So why it is okay for him to sell T-shirts with dog pictures in them in the newsgroup and you have been condemning me for offering help with Access? The advertising is not in CDMA, but on his webpage, yours is in CDMA. It was your choice to click on the URL. His message also contained content that was of value to the newsgroup, your "Contact me" did not. -- more childish rants snipped. PC Datasheet Nov 23 '05 #27

 P: n/a "PC Datasheet" wrote in message news:lj*****************@newsread2.news.atl.earthl ink.net... I am now approaching 1050 Access users who have requested fee-based help from me. I am seeing more and more of these people by-passing the newsgroups and coming straight to me because of the unprofessional atmosphere that has been created by Arno R and his band of dimwiteers. Interesting how being contacted by 1000 has now changed to contacted by 1050 who have requested fee-based help. Are you playing with the numbers? If you do have 1050 paying customers, why do you need to solicit? No repeat business? Why are they not flooding these newsgroups with testimonials? As for unprofessional atmosphere, the childish insults and slurs have been authored by you. When you actually do help, I have not against your message, unless you were passing incorrect information (i.e. Adding I = I +1 to an automatic loop). So I can not see my behaviour as being unprofessional. John... Visio MVP Nov 23 '05 #28

 P: n/a "PC Datasheet" wrote in message news:xZ***************@newsread3.news.atl.earthlin k.net... Here's an example from another newsgroup of what Arno R and his band of dimwiteers are causing --- Of course, if you had not been advertising below your sig line, then Arno would not have responded. Simple cause and effect. John... Visio MVP Nov 23 '05 #30

 P: n/a Another dimwitted response!!! I suggested to the poster something to try to solve her problem and Arno R's worthless garbage made her afraid her original inquiry was lost so she reposted. You certainly are an embarassment to good MVPs!!! -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications re******@pcdatasheet.com www.pcdatasheet.com If you can't get the help you need in the newsgroup, I can help you for a very reasonable fee. Over 1000 Access users have come to me for help. Need a month calendar or 7 day calendar? Need appointment scheduling? Need room reservations scheduling? Need employee work scheduling? Contact me! "John Marshall, MVP" wrote in message news:Qo********************@magma.ca... "PC Datasheet" wrote in message news:xZ***************@newsread3.news.atl.earthlin k.net... Here's an example from another newsgroup of what Arno R and his band of dimwiteers are causing --- Of course, if you had not been advertising below your sig line, then Arno would not have responded. Simple cause and effect. John... Visio MVP Nov 23 '05 #33

 P: n/a "PC Datasheet" wrote in message news:i7****************@newsread2.news.atl.earthli nk.net...A - Ha!!! So you are now saying it is okay to include URLs in the newsgroupthat advertise any kind of merchandiise or service. If this is the generalconsensus of all newsgroup members, bring it on marketers!!! Headline: John Marshall dwt okays placing your URL in the newsgroups to advertise merchandise and services Still not able to read? I did not "approve" of using URLs or say it was okay. I just pointed out that the ads were not in his post, but on a webpage mentioned in his sigline. John... Visio MVP Nov 23 '05 #34

 P: n/a "PC Datasheet" wrote in message news:lj*****************@newsread2.news.atl.earthl ink.net... Finally, as far as my business, people in these newsgroups see through all the worthless garbage Arno R, John Marshall, Keith Wilby and now you are polluting the newsgroups with. They're disgusted with the likes of all of you. If you don't want a libel suit on your hands Steve I suggest you retract that statement, you're way out of line. You don't know what I think and you most certainly do not speak for me. I am *deadly* serious about this, time to stop the name-calling and libellous statements. Nov 23 '05 #36

 P: n/a Although the use of the Select Case is perfectly OK and efficient there is one potential problem. The original question seemed to be asking how to split a range of numbers into non-overlapping but fully exhaustive sub-ranges. By writing: Select Case MyCount Case 1 To 4 : GS.... Case 5 To 10: GS... Case 11 To 17: GS ... Case 18 To 24: GS ... etc End Select You must ensure that you do not leave gap or have overlaps. If you decide that the 10 boundary, for example, should be 9, you MUST remember to change it in two places. An alternative is to use a properly constructed If statement: If MyCount <= 4 then GString = ... ElseIf MyCount <= 7 then GString = ... ElseIf MyCount <= 11 Then GString = ... ElseIf MyCount <= 24 Then GString = ... ..... Else GString = ... End If Now, if you want to change the breaks points you only have the change one value and you are assured that there are no gaps. By the way the messages from PD Datasheet do not irritate me. I did not even notice that there was advertising until the other offensive objections started to dominate the discussions. I find these far more irritating; the abuse and language used is indefensible. Nov 23 '05 #37

 P: n/a "Jim Devenish" wrote in message news:11**********************@g49g2000cwa.googlegr oups.com... By the way the messages from PD Datasheet do not irritate me. I did not even notice that there was advertising until the other offensive objections started to dominate the discussions. I find these far more irritating; the abuse and language used is indefensible. Hello Jim. The advertising isn't an irritant to me per se but if everyone adopted the same stance then this group would become unusable owing to spam overload. If we all abide by the rules then everyone is happy. The majority of the indefensibly abusive language comes from the perpetrator of the spamming. I really don't think there's much point in trying to persuade him any longer to give up so he'll be going into my KF. Once he's had enough rope he will hang himself. Regards, Keith. Nov 23 '05 #38

 P: n/a Salad, are you sure? Does't your syntax say that whatever is in the middle is less than something on the left AND less than something on the right? How does one determine a range from that? xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx With your syntax the confusting thing is the <= and <=. Usually oneexpects >= and <= to get a range. Nov 23 '05 #39

 P: n/a Thx, Jim. I'll go with the Select - Case X To Y architecture. I like it. Its easy to read - won't slow me down when debugging (which I do a lot of, believe me). Select Case MyCount Case 1 To 4 : GS.... Case 5 To 10: GS... Case 11 To 17: GS ... Case 18 To 24: GS ... etc End Select Nov 23 '05 #40

 P: n/a To be clearer, I'll put in actual values: Take the case of X=5... 3 < 5 < 7 ==> I see no way to convey 5 being between 3 and 7 using any form of W > X < Y. You see what I mean. I was talking in the OP (which I see has now turned into an advertising debate) about a shorthand approach to defining a range. What I gather is that there is no SYNTAXwise shorthand approach to defining a range similar to the MATHwise correct 3 < X < 7. I can accept that. Salad, are you sure? Does't your syntax say that whatever is in themiddle is less than something on the left AND less than somethingon the right? How does one determine a range from that?xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxWith your syntax the confusting thing is the <= and <=. Usually oneexpects >= and <= to get a range. Nov 23 '05 #41

 P: n/a MLH wrote: To be clearer, I'll put in actual values: Take the case of X=5... 3 < 5 < 7 ==> I see no way to convey 5 being between 3 and 7 using any form of W > X < Y. You see what I mean. I was talking in the OP (which I see has now turned into an advertising debate) about a shorthand approach to defining a range. What I gather is that there is no SYNTAXwise shorthand approach to defining a range similar to the MATHwise correct 3 < X < 7. I can accept that. I understand what you mean. However, I'd change W > X < Y to W >= X =< Y IF's are handy for when you have MULTIPLE comparisons. SELECT CASE's are handy when you have SINGLE comparisons. Back in FoxPro I could have done a CASE ID > 10 And Height > 5 And Width > 4 ...process CASE ID > 20 And Height = 5 And Width < 4 ...process but that was OK because FoxPro allowed multiple comparisons of different values. Not so in VBA. Yeah, "turned into an advertising debate" is actually "degenerated into an advertising debate". What a pile of poop. But it's kind of like a conversation...you talk about sending a man to the moon and at the end of the conversation you are talking about fishing in a lake...not related but very rambling. Nov 23 '05 #42

 P: n/a John Marshall, MVP wrote: Unlike the Select Case which has an ELSE clause, all conditions in a Switch must be addressed. For coding readabilty, the Select is easier to read. Select Case MyCount Case 1 To 4: GString = "a few days ago. " Case 5 To 10: GString = "a week or so ago. " Case 11 To 17: GString = "about 2 weeks ago. " Case 18 To 24: GString = "about 3 weeks ago. " Case 25 To 30: GString = "about month ago. " Case Else: GString = " " End Select GString = Switch(MyCount >= 1 And MyCount <= 4, "a few days ago. ", _ MyCount >= 5 And MyCount <= 10, "a week or so ago. ", _ MyCount >= 11 And MyCount <= 17, "about 2 weeks ago. ", _ MyCount >= 18 And MyCount <= 24, "about 3 weeks ago. ", _ MyCount >= 25 And MyCount <= 30, "about month ago. ", _ MyCount > 30, " ", MyCount < 1, " ") There is only one place where I can see an advantage to the Switch function and that is if you need to have a calculated field within an SQL statement. -- Bri Nov 23 '05 #43

 P: n/a "PC Datasheet" schreef in bericht news:xZ***************@newsread3.news.atl.earthlin k.net... Randy, Here's an example from another newsgroup of what Arno R and his band of dimwiteers are causing --- Or this example dated 20-11-2005: "yah go away pcdatabase, i dont need YOUR help. You greedy idiot, this forum is to help people not to make money." Or this example dated 21-11-2005: "Good Morning, Gentlemen, OK, so I'm still plugging away at this database, and while I'm a big fan of the 1st Amendment, could you please find somewhere else to argue? Not that I don't thoroughly enjoy the disappointment of logging on after receiving an e-mail that someone has posted a reply and finding out that it's from some schmuck who has nothing better to do that plug his own business. . . Steve, if I wanted to (or even could. . .) pay someone for this, wouldn't it make more sense for me to explore that option up-front instead of wasting my time trying to learn how to do this for myself? I haven't written to you eagerly expressing my rapacious desire for your oh-so-unselfish services, so please find someone else's post to fill with nonsensical garbage. That will be all." Need more examples of this? Contact me! Arrno R Nov 23 '05 #44