Select Case question - Comparing strings

I am passing variables into a subroutine, and comparing these to some
parameters in a array.  If all 4 of the variables passed in are equal to 4
values in the array, I run a a loop adding values to the array.

I can make a series of If..Then statements work but cannot get the equivalent
Select case statements to work.

I am attaching the entire subroutine;

Any suggestions appreciated. 

Thanks

JDB

' --------------------------------------

Sub Add_ClnLoins(idxdate As Variant, _
                    idxSpecies As String, _
                    idxclnshift As String, _
                    dat_start_time As Variant, _
                    hrs As Single, _
                    dat_Clned_Loins As Single, _
                    arr_Hourly_Demand As Variant)

Dim testDate As Date
Dim testTime As Date

Dim i As Integer
Dim iHr As Integer
Dim LoinsPerHr As Single


i = 1
 Do While arr_Hourly_Demand(i, 1) <> ""

testDate = FormatDateTime(arr_Hourly_Demand(i, 1), vbShortDate)
testTime = FormatDateTime(arr_Hourly_Demand(i, 2), vbShortTime)


'*********  This series of statements does NOT work

Select Case Trim(CStr(idxSpecies))
  Case Trim(CStr(idxSpecies)) = CStr(arr_Hourly_Demand(i, 5))
    Select Case Trim(CStr(idxdate))
      Case CStr(idxdate) = CStr(testDate)
        Select Case Trim(CStr(idxclnshift))
          Case CStr(idxclnshift) = CStr(arr_Hourly_Demand(i, 4))
            Select Case Trim(CStr(dat_start_time))
               Case CStr(dat_start_time) = CStr(testTime)
                  For iHr = 1 To hrs

                    arr_Hourly_Demand(i - 1 + iHr, 6) = dat_Clned_Loins / hrs

                  Next iHr

                  End Select
                  End Select
                  End Select
                  End Select

'  ******* The following series of statements work ***************

If idxSpecies = CStr(arr_Hourly_Demand(i, 5)) Then
    If CStr(idxdate) = CStr(testDate) Then
       If CStr(idxclnshift) = CStr(arr_Hourly_Demand(i, 4)) Then
          If CStr(dat_start_time) = CStr(testTime) Then
                  For iHr = 1 To hrs

                  arr_Hourly_Demand(i - 1 + iHr, 6) = dat_Clned_Loins / hrs

                  Next iHr

                  End If
                  End If
                  End If
                  End If
                


i = i + 1

Loop

End Sub

0
JDB
6/5/2010 8:11:36 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

1 Replies
1039 Views

Similar Articles

[PageSpeed] 53

Sounds like you misunderstand when to use a Select Case statement.  Use it
when the variable you are examining could contain a wide range of values.  If
you can do what you want with a couple of Ifs, you probably don't need a case
statement.  Case statements really shine when you have lists or ranges of
values that you need to treat together.  This just doesn't sound like one of
those cases.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201006/1

0
PieterLinden
6/5/2010 9:35:11 PM
Reply:

Similar Artilces:

[O2007] -> How to search for a string in the body and get the line number?
Hi, I'm trying to get a reply to a mail message and look for a specific substring. Then I need to know the position of the line. For example: search "Sincerely yours," get the line number insert 3 lines BEFORE the "Sincerly Yours" I can't find a way. I know how to use Replace to change subtrings, but I can't get a way to just locate one and get the line number. How can I do this? Then, how can I insert text at that linePosition-3? Thanks for any help. Sincerely, Steve JORDI (Remove the K_I_L_LSPAM from my email address) ------...

ext. hard drive question
Hi all, I have copied files from three pc's to an ext. harddrive. I didn't label them as I should and now I am getting them mixed up. How can I tell which pc they came from? two are XP and one is Vista. Thanks From: "JS" <sivright@nospamultravision.net> | Hi all, | I have copied files from three pc's to an ext. harddrive. I didn't label | them as I should and now I am getting them mixed up. | How can I tell which pc they came from? two are XP and one is Vista. | Thanks You can't. You need to practice better data storage management skil...

Dynamic Chart Question #3
Hi All, I'm trying to set up line charts for more than 40 different production locations. But I would like to give the user the option of selecting one or two (at most) locations then have Excel display the corresponding chart/s instead of creating 40+ charts. Each location will have its own data table (pounds/dollars). How do I go about doing this task? Thanks. -- when u change the way u look @ things, the things u look at change. Hi, For information on dynamic charts and controlling which series are displayed have a look at Jon's page. http://peltiertech.com/Excel/Charts/Dy...

.pst question
Outlook/Exchange novice here.... I have a user that has archived files to different areas on his laptop. He wants them all to appear in his folder list under Archive Folders. If I copy and paste them into docs and settings\%username%\local settings\application data\microsoft\outlook do i need to rename the files so as not to overwrite the one archived file that is in there? In other words, what is the proper procedure to restore these archived files that are not where Outlook 2k sees them so that they can be viewed in his Archive Folders list? Thank you Hey, What I would do is put...

Convert Date string to date format
I have some date strings I need to convert to date format. For example im trying to convert strings like "200910" to Oct-2009. I tried using something like this to do it but I get an error: Cell A4: "200910" this is the date string to be converted Cell B4: =left(a4,4)&","&right(a4,2)&","&"01" changes to "2009,10,01" Cell C4: =date(b4) After converting to a date I would use the custom cell format "mmmm-yyyy" in C4 to give the result of Oct-2009. I get the error "You've entered too few a...

Excel Number Question
I should know this one but I'm rusty. I'm using Excel 97. I received a spreadsheet with a filed of Zip Codes that has two different appearances. Most of the numbers are '85226 and are left aligned. A few of them are 85226 (no ' ) and are right aligned. Is there an easy way to edit the Zip Codes to remove the ' and have them all line up the same way? TIA, Carl -- If you can read this, thank a teacher.... If you are reading it in English, thank a Veteran..... Hi Carl, With any empty cell selected, try: Edit | Copy | Select zip code cells | Edit | Paste Special...

OT: But should be easy question for experienced MSVC6 users
I can't compile my MSVC6 project. It's very frustrating. 2 errors: (1) C:\Proj1\File1.cpp(938) : fatal error C1010: unexpected end of file while looking for precompiled header directive Note, I had this error. Have commented out the entire contents, am still getting this error. (2) BSCMAKE: error BK1506 : cannot open file '.\Debug\File1.sbr': No such file or directory I have know idea what this is on about. On Tue, 2 Aug 2005 23:51:32 UTC, "Hamish" <h.dean@xtra.co.nz> wrote: > I can't compile my MSVC6 project. It's very frustrating. ...

Office 2K XL question please
I want to do an FFT on some data so in the HELP system I find I must install the Analysis ToolPak. It even tells me how to install it: "To install the Analysis ToolPak On the Tools menu, click Add-Ins. " Only problem is that there's no TOOLS on my menu. My top menu items are FILE EDIT VIEW INSERT FORMAT DATA WINDOW HELP. I looked everywhere for some other menu with TOOLS in it with no luck. I did find a "Toolbar" but I saw nothing about any Analysis or Add-Ins. If I ever get it installed, I'll need the TOOLS menu again for the HELP tells how to use it: &q...

help.. multiple select in listctrl
I'm looking for the code to select multiple row in clistctrl by holding the leftbutton. however i can select multiple item IF i drag the empty space at FIRST row, and there's a rectangle when selecting the items.. i want to select multiple items whether its in second column or in another column. Thanks before.. Regard, Riki Risnandar >I'm looking for the code to select multiple row in clistctrl by holding the >leftbutton. >however i can select multiple item IF i drag the empty space at FIRST row, >and there's a rectangle when selecting the items.. i want ...

Inventory questions- multiple parts???
Hi i have a question on inventory contol. I make a product out of several parts, How can i add these to the finished part, so when i sell it it deducts the other parts? In other words i make a product, and in the product i use Stainless springs and Stainless mounts. I want to have it set up that when i sell my product, it deducts the springs and mounts i have left, so i know when i am running low. Right now i have the springs and mounts as "free products" and i include it in the invoice, but i was hoping there is an easier way. Please email me, i don't check this board ...

Merge cases / items into cases
Hi, With Dynamics CRM 3.0, is it possible to either merge cases in their entirety, or merge additional items into a case? By additional items I mean, for example, I have an ongoing case with a customer but then they send me a new email (that doesn't have the CRM tags) - can I merge that email into the existing case? Cheers. You can append the email to the case (providing the case is still open). To do this, set the Regarding field of the email -- David Jennaway - Microsoft Dynamics CRM MVP Web: http://www.excitation.co.uk Blog: http://mscrmuk.blogspot.com/ "Wayno" wrote: ...

Comparing two versions of a worksheet
What is the best way to compare two versions of a work sheet to determine changes that have been made? Have any rows been inserted or deleted? Same for columns? If none have--just data has changed, then Myrna Larson and Bill Manville have written a comparison program that does a cell by cell comparison (a1 with a1, x99 with x99). You can find it at: http://www.cpearson.com/excel/download.htm Look for Compare. Billy Washam wrote: > > What is the best way to compare two versions of a work > sheet to determine changes that have been made? -- Dave Peterson ec35720@msn.com ...

Fuzzy string comparison / detecting "similar" strings
When dealing with real live data, you often have some variation of minor errors in your data. E.g. I have two lists (databases) in which Names sligthly differ. Examples: "Clark Kent" vs "Clark Kent" "John P. Smith" vs "John Paul Smith" "Miller Limited" vs "Miller Ltd." "Peter Hammer" vs "Petre Hammer" I am looking for a way to handle this (semi-) automatic. My idea is to have a function f, that takes two strings and delivers a measure on how much the are alike. E.g. f should be 1, if both arguments are identica...

Database Runtime Question
I am designing a database in Access 2003. It is almost complete other then the fact I need to create a runtime for it to enable PCs without Access to view the database. My question is, when the runtime is created and installed on the PCs that need it, if I need to add or make changes to the database will the users with the runtime program be able to see these changes or would I need to create a new runtime for them to see the changes? Thanks in advance for your responses, they are appreciated:) -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/...

Validating a Date String
Is there a function(s) for validating a Date stored in a CString? FYI, the CString is set by an external program. The input string is in the format of 2 digits for Day, 2 digits for Month, 2 digits for Year. For example, how could I test "290204" is valid, "290205" is invalid. You might check into COleDateTime::ParseDateTime to see if it will detect this, but it requires you canonicalize the date into one of its acceptable formats. Otherwise, just check the fields for consistency with leap-year status. joe On Thu, 20 Apr 2006 22:43:01 -0700, Charles Tam <Cha...

New here -- weird question
Hello all. I'm new here. I have a weird question. Is there any way to import into Outlook 97 my settings, mails, address book, etc., from Outlook Express 6? I'm on a WinXP system. Thanks! Unlikely. Versions are too disparate. Did you try the Import Wizard's option to import from the concurrent versions of OE? -- Russ Valentine [MVP-Outlook] "Jeff Needle" <jeff.needle@general-net.com> wrote in message news:11ajt6a9acu7k2a@corp.supernews.com... > Hello all. I'm new here. I have a weird question. > > Is there any way to import into Outlook 97...

How do I select which labels to print on the Avery sheet.
In Publisher 2003 can I select which lables to print on rather than produce a whole sheet of labels the same as you can in Word. Templates here. http://www.publishermvps.com/General/Templates/tabid/38/Default.aspx -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Dennis the Menace" <Dennis the Menace@discussions.microsoft.com> wrote in message news:C13CE06A-2975-45DD-9BAF-7968860AB706@microsoft.com... > In Publisher 2003 can I select which lables to print on rather than produce a > whole sheet of labels the same...

Beginners Question
I know how to add two cells but I would like the cells to have a title within the cell. Something like 8.5 Hrs, or 32 steaks. Something like that. -- Thank you in Advance Hi Format>Cells>Number>Custom> #.0 "Hrs" or General "Steaks The part before the text can be formatted in any of the standard number formats. It will appear as 32 Steaks, but all that will be held in the cell is 32, which can then be used in further calculations if required. -- Regards Roger Govier "GordyB" <GordyB@discussions.microsoft.com> wrote in mess...

Column chart question
How can I group data in column chart? _Week_ _Day_ _Qty_ 1- Fri - 200 2- Fri- 350 3- Fri- 150 1- Sat- 225 2 - Sat -200 3- Sat -100 How do I make column chart with all Fri together and all Sat togethe and label the weeks -- rajgopa ----------------------------------------------------------------------- rajgopal's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=728 View this thread: http://www.excelforum.com/showthread.php?threadid=31952 You could create a pivot table with Weeks & Days as your axis. So you will get a table like: ... ...

question #5
I have normal excel document consisted of 2 columns A and B. In each columns there are 4000 rows. In each row is url adress in form: http://www.studiotatjana.com I want new list of urls in column C which is sum of values in column A and B. So there will be 8000 urls in column C. But there needs to be only one rule. If value in one row of 4000 row in column A is identical to any value in any row in column B then that value do not go to colum C. I want that if one url adress can be fund in column A and B that that url is deleted and do not go to column C. All values in colum A are difere...

select and paste
I want to copy past a formula in an adjacent cell and drag it down for all records in the adjacent cell. There are thousands of records Is there a way of selecting and pasting only the cells that has data in it.. rather than selecting the whole column and pasting it? Yes, to select your column and then pull-down: Edit > Goto... > Special and then check what you want - constants, formulae, etc. This will end up selecting only certain cells. The do the paste. -- Gary's Student "flow23" wrote: > I want to copy past a formula in an adjacent cell > and drag it...

Select Thing in Report
How can you set up when you run a report to only pick a certain information. I have a table that has a drop down menu of locations, I would like to run a report and when you go to run it it asks you what location you would like to pick. If you could please reply when you get a chance. Thanks. On Wed, 12 Mar 2008 17:42:00 -0700, Anthony wrote: > How can you set up when you run a report to only pick a certain information. > I have a table that has a drop down menu of locations, I would like to run a > report and when you go to run it it asks you what location you would like t...

Automatic print layout selection
Can you set up an option so that when you print a report, it automatically selects landscape or portrait and fits the report to the page rather than going to a 2nd line You can modify the report with Report Writer and on the Report Definition window change the Printer Orientation to Landscape. Don't forget to change security to use the modified report. You can also use Named Printers to change the printer properties to landscape if the report is one controlled by Named Printers. David Musgrave [MSFT] Original Developer of Named Printers Senior Development Consultant Escalation Eng...

Can combobox selection place another value in spreadsheet?
I have a combobox where the user selects a city. The cities come from a spreadsheet where all the cities are listed in one column, and all of their zip codes are in the column next to it. How can I allow the user to pick a city, while putting that city's zip code into the spreadsheet? Astello If this is a combobox from the control toolbox (ActiveX) then set the ListFillRange property to the address of the list (e.g Sheet3!$A$1:$A$100), then set the LinkedCell property to a cell that will return the value of the selected item in the combobox. I usually set this to the cell 'unde...

WMP 11 WIN-XP_MCE = Rip Location question
I want to set my RIP location to a virtual (remote) drive ... not on my computer, but visible and available. WMP 11 does not seem to be accepting the UNC location; \\EXTERNAL\M-Drive\Music but WMP 11 keeps defaulting to C:\Music ... is it possible to specify a UNC path or not ??? TIA In WMP,click on tools,options,rip music tab,change the location.If its not listed,go to hardware tab & edit for remote drive. "PC Guy" wrote: > I want to set my RIP location to a virtual (remote) drive ... not on my > computer, but visible and available. WMP 11 does not...