update check box fields in the table using array based on certain value

One of my table has _Ct values and I want to update other relevant fields in
the same table ( basically check box) with ‘yes’ or ‘no’ depending on the _ct
values.  Can some one show me how I can do this in Access and correct the
following logic?

Public Const ct = "H1_Ct H3_Ct H5a_Ct H5b_Ct RP_Ct swInfA_Ct swH1_Ct"
Public Const test = "H1_Tested  H3_Tested  H5a_Tested  H5b_Tested  RP_Tested”

Public Const result = "H1_Result H3_Result  H5a_Result  H5b_Result
RP_Result"

Dim objDB As DAO.Database
Dim mytbl As DAO.Recordset

Set objDB = CurrentDb()
Set mytbl = objDB.OpenRecordset("ABI_db")

Dim array_ct() As String
Dim array_t() As String
Dim array_r() As String
Dim i As Integer

array_ct = Split(ct, " ")
array_t = Split(Test, " ")
array_r = Split(result, " ")

For i = 1 To UBound(array_ct)
  If array_ct(i) > 5 And array_ct(i) < 37.44 Then Do
    array_t(i) = "1"
    array_r(i) = "1"
  End
  
  Else: If array_ct(i) = 0 Or array_ct(i) > 37.44 Then Do
    array_t(i) = "1"
    array_r(i) = "0"
  End
  
End
         
Next

Thanks

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

0
mls
2/4/2010 7:09:37 PM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
829 Views

Similar Articles

[PageSpeed] 30

Public Const ct = "H1_Ct H3_Ct H5a_Ct H5b_Ct RP_Ct swInfA_Ct swH1_Ct"
Public Const test = "H1_Tested  H3_Tested  H5a_Tested  H5b_Tested  RP_Tested”

Public Const result = "H1_Result H3_Result  H5a_Result  H5b_Result
RP_Result"

Dim objDB As DAO.Database
Dim mytbl As DAO.Recordset

Set objDB = CurrentDb()
Set mytbl = objDB.OpenRecordset("ABI_db")

Dim array_ct() As String
Dim array_t() As String
Dim array_r() As String
Dim i As Integer

array_ct = Split(ct, " ")
array_t = Split(Test, " ")
array_r = Split(result, " ")

For i = LBound(array_ct) To UBound(array_ct)          '  LINE CHANGED
 If array_ct(i) > 5 And array_ct(i) < 37.44 Then          ' No "Do"... it's
not a Do/Loop
   array_t(i) = "1"                                                    ' are
you really storing numbers as text?  
   array_r(i) = "1"
 ElseIf array_ct(i) = 0 Or array_ct(i) > 37.44 Then     'No Do here either.
   array_t(i) = "1"
   array_r(i) = "0"
 End    
Next i

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

0
PieterLinden
2/7/2010 4:23:28 AM
Good question. All my array variable field values are Integers but field
names are character so how can I split them?  When I run the following code
it shows Type mis-match error message.. I tried to declare them as variant
but it says user defined type not defined.

Dim array_ct() As Integer
Dim array_t() As Integer
Dim array_r() As Integer
Dim i As Integer

array_ct = Split(ct, " ")
array_t = Split(test, " ")
array_r = Split(result, " ")

For i = LBound(array_ct) To UBound(array_ct)
If array_ct(i) > 0 And array_ct(i) < 37.44 Then
  array_t(i) = -1
  array_r(i) = -1
ElseIf array_ct(i) = 0 Or array_ct(i) > 37.44 Then
  array_t(i) = -1
  array_r(i) = 0
  End If
End
Next i
PieterLinden wrote:
>Public Const ct = "H1_Ct H3_Ct H5a_Ct H5b_Ct RP_Ct swInfA_Ct swH1_Ct"
>Public Const test = "H1_Tested  H3_Tested  H5a_Tested  H5b_Tested  RP_Tested”
>
>Public Const result = "H1_Result H3_Result  H5a_Result  H5b_Result
>RP_Result"
>
>Dim objDB As DAO.Database
>Dim mytbl As DAO.Recordset
>
>Set objDB = CurrentDb()
>Set mytbl = objDB.OpenRecordset("ABI_db")
>
>Dim array_ct() As String
>Dim array_t() As String
>Dim array_r() As String
>Dim i As Integer
>
>array_ct = Split(ct, " ")
>array_t = Split(Test, " ")
>array_r = Split(result, " ")
>
>For i = LBound(array_ct) To UBound(array_ct)          '  LINE CHANGED
> If array_ct(i) > 5 And array_ct(i) < 37.44 Then          ' No "Do"... it's
>not a Do/Loop
>   array_t(i) = "1"                                                    ' are
>you really storing numbers as text?  
>   array_r(i) = "1"
> ElseIf array_ct(i) = 0 Or array_ct(i) > 37.44 Then     'No Do here either.
>   array_t(i) = "1"
>   array_r(i) = "0"
> End    
>Next i

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

0
mls
2/8/2010 3:34:43 PM
Reply:

Similar Artilces:

Using expression builder object
Hi, I'm developing a wizzard in Access which builds import templates for various data sources to a fixed set of tables. In step 3 the users must be able to build an expression ; for instance Left([Fieldx],20) . Now I would like to have a command button on my form which calls the Access expression builder to allow the users to use this to build the expression. This expression will then be stored in a text box linked to the templates table. Anybody know how to call and use this object from VBA code? -- Kind regards Noëlla DoCmd.RunCommand acCmdInvokeBuilder I th...

Before Update on record select
I am getting no where fast. I have a main form and sub form and what i need is a message to appear when user moves on to the next record using the record selector on the bottom of the form. I have tried the beforeupdate on the form but no results. Can form design prevent a message prompt? One problem is that if no changes to the current record have been changed, the Before Update event will not fire. If you do make a change, the code as is should work. I have gotten into the habbit of not using the standard record navigators, but write my own so I can easily deal with this. -- Dave Ha...

function to check for entry
I'll try and make this as clear as I can... I am looking for a function that will check column b,c,d,e for an entr if there is an entry then check row n in the same row for it's value. that's the first bit, then I need to be able to check the date valu (month) in column a and total it for the month. perhaps I could run the first function only looking for month=1 o something and just run it 12 times? not sure how to go about this -- Message posted from http://www.ExcelForum.com well I've got this far but it still doesn't work and gives an error: =IF(MONTH(A3:A167)=4,...

Lookup based on criteria in 2 columns
Hi, I am trying to use a vlookup or other function to return the value in the amount column based on the location and date. Here is a sample of my data: Location Date Amount 101 9/15/8 10 101 9/16/8 20 101 9/17/8 15 102 9/15/8 50 102 9/16/8 75 102 9/17/8 67 For example if I wanted to return the amount for location 102 on 9/15/8, what formula would I use? I tried using variations of vlookups but had no luck. Thanks, =SUMPRODUCT(--(A2:A50=102),--(B2:B50=DATE(2008,9,15)),C2:C50) -- Regards, Peo Sjo...

Address book updating...
Hello all, I have noticed since we moved to Exchange 2003 that it takes a long time (several hours) before I see newly created accounts in the address book. Is there somewhere I can adjust the update time? TIA, Gary Check the update interval in the RUS (Recipient Update Service). Teo "GaryH" wrote: > Hello all, > I have noticed since we moved to Exchange 2003 that it takes a long time > (several hours) before I see newly created accounts in the address book. > Is there somewhere I can adjust the update time? > TIA, > Gary > > > Thank you...I will ...

Inserting form values into a table
We have a form with values taken from an sql query that comes from two different tables. We would like to enter the information into a third table. Can some one direct me to code that will do the following: 1. Provide the Insert sql that shows us how to add the form values to the table 2. Show us how to loop while inserting the information into the table (there could be several lines on the form, each must be inserted one at a time). I have worked with Access before and have never had a problem inserting information. However, I cannot quite figure out how to insert informtion through an ...

how do I automatically update a text box in a chart?
Hi, You can link the textbox to a cell. When you update the cell the textbox should reflect this. Select the textbox border and then in the formula bar enter the complete cell reference, for example, =Sheet1!A1 Cheers Andy Kath wrote: -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

data input in text box
We have a form which the operator enters data in a text box. Currently we have a 'done' button on the form that the operator clicks to send the text box info to a vba program. How can we send the text box info to the vba program when the operator hits the enter key @ the end of the data entry for the text box? TIA -- _______________________________ In Christ's matchless name ted & colleen n6trf kc6rue Use the control's AfterUpdate event. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ted" <n6trf@arr...

Exchange update problem
I have tried to upgrade exch2k3 sp1 to sp2, but the update fails with "the file pcproxy.dll is in use, and setup cannot identify the app or srvc. setup cannot continue" Any clues/ideas/suggestions? Please. -- ----------------------------------------------------------------------------------------------------------------------- This message has been checked for all known viruses. The information contained in this e-mail and any attachments is confidential and may be the subject of legal, professional or other privilege. It is intended for the named addressee only and may not ...

Outlook 2003 and "Check Names"
Hi, We are testing Outlook 2003 at our office now and there is one very frustrating "feature" that is bugging all of us. When we create a new e-mail message and type in the first name of a person from the global address book and hit ctrl- k to check the name...we get a response that Outlook doesn't recognize the name and there are "no suggestions". The only way Check Name will successfully resolve the name is if we type it last name first (ie, Gates, Bill). This was not the case in Office XP or Outlook 2000. You could search by first name (or partial first name)...

Using part of a cell in a chart title
I have a chart which should get a title. However, this should be partly be used from a cell e.g. "counted with 5%" 5% should be taken from the cell and used in the title. Is this possible? Hi, Yes it's possible but all of the chart title needs to be in the cell. So you may need to use a helper cell and concatenate text and value. http://www.andypope.info/tips/tip001.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Nicole" <Nicole@discussions.microsoft.com> wrote in message news:5CB7A971-AA7F-4C34-BB42-7DC283AA2958@micro...

Size of dynamically allocated array
What's the easiest way to determine the size (number of objects) of a dynamically allocated array? Chip While it's probably not reliable, I did find that the address you get back from the 'new' operator is not the actual starting address of the allocated block. If you subtract sizeof(DWORD)*4 from that address and then read the value at that address, you'll have the requested size, but not the actual size that the memory manager allocated. For example: char *p = new char[1234]; strcpy(p, "Hello World!"); // not necessary but does help to locate stuff in mem...

formula based on format
Is there a way to have a formula that is based on a cell colour or based on the way a cell is formated? I need it to count the number of occurrences that this happens. -- Thank you for your time. Windows NT Office 97 Hi you'll need VBA. See for an example: http://cpearson.com/excel/colors.htm "James Kendall" wrote: > Is there a way to have a formula that is based on a cell colour or based on > the way a cell is formated? I need it to count the number of occurrences > that this happens. > -- > Thank you for your time. > Windows NT > Office 97 This ...

adding name /creating field/query?
Hello, I can create an invoice_number field in a query using the primary field ID from the main table as invoice_number: ID but if ID say is 100, I cannot work out how to create renewal_invoice_100 Cheers Geoff Geoff We aren't there. We can't see what you're looking at. Where did "renewal_invoice_100" come from and what does it mean? Please post the SQL statement of the query you are trying to use. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://micro...

Filter recordset using query results
Hi all I have a form based on a query called [qry Quarterly Planning], it lists all Itineraries on the system. On this form you can filter records by specifying a Start and End Date for the [ReviewDate] and/or [Specialist]. It is a subform on a main unbound form, lets call this Subform1. Along side this I have another subform (Subform2) which displays ReviewDates that exist against an Itinerary. In other words Subform1 has a start date of an activity and if the activity lasts longer than 1 day, then the additional dates are stored in Subform2 (ItineraryDates). Currently when I...

make subreport2 visible based on condition
Help, I am running Access 2003 and have trouble getting this work right. I have an unbound subreport that contains 3 additional subreports. I want only one of the 3 to be visible based on the value in a combo box on a selection form. I can't seem to get the reference right and have tried several variations. If Forms!frmWeeklyGLrpt!cboCC = 1 Then Me!subrptGLWeekly.Report!subrptGLWeeklyCMRDA.Visible = True Me!subrptGLWeekly.Report!subrptGLWeeklyCMRDB.Visible = False Me!subrptGLWeekly.Report!subrptGLWeeklyCMRDC.Visible = False End If If Forms!frmWeeklyGL...

How do I use traffic lights in excel
I am wanting to use traffic lights in excel that change colour based on the result of a variance cell, ie if the result of the cell is 10 make the traffic light green, if it is 20 make the traffic light amber, if the result is 30 make the traffic light red. How do I do this? Shorty Format>Conditional Formatting>Cell Value is: Note: you can add up to 3 conditions(4 if you count default) Gord Dibben Excel MVP On Wed, 22 Dec 2004 16:35:03 -0800, Shorty <Shorty@discussions.microsoft.com> wrote: >I am wanting to use traffic lights in excel that change colour based on the &g...

Grammar check not working
Hello, I am using Word 2007 and have a problem with grammar and punctuation errors. I deliberately put two spaces between words, do not put space after a comma, write long sentences and finish a sentence without a verb but the green underline never appears. The spell check is functioning properly, no problem with that. In Word Options > Proofing, "Mark grammar errors as you type" is selected. I changed that selection and tried again but it still did not work. I used different languages as default language but no change. I would be grateful if someone could come...

Using Visio HTML output within frames
Hi, I want to include visio HTML output in a frame of another html file. Unfortunately it is not working. I understood the problem is in vml_*.htm files. It is due to, the target arrtibute(pointing to _parent) in v:shapetype tag and href attribute (pointing to #) in v:shape tag. These attributes should point to "_top" and "<target-html-file>#" respectively inorder to work. I want to change these options while saving .vsd as web page? I would appreciate if you can help me in this regard. <v:shapetype id="VISSHAPE" target="_parent" coor...

outputting values to a range from one formula
I wish to generate a table automatically by means of a single formula that applies an iteration on a starting given value with a given step, and the computed values are posted/entered automtically into cells from a given cell onwards, say below it, until the computed value reaches a certain given limt. This is somehow the inverse of INDIRECT or of OFFSET. These can pull values from a variable addresses of cells. What I need is to push values into a variable addresses of cells. Can anyone help me on that?? Thanks. :confused -- Shafe ---------------------------------------------------------...

How do I merge cells with multiple data values?
I've tried highlighting the two cells which are in the same row. It suggested that I format and align. Both of which I've tried. I keep getting the same error message, "The selection contains multiple data values. Merging into one cell will keep the upper-most data only." I need to make the cells one with all my information. Is this possible? Not knowing what you want to do, let me make a suggestion. Put all your data into the first cell and leave the second cell empty. Select both cells. Click on Format - Cells - Alignment tab. In the "Horizontal" b...

Can you only merge up to a certain number of cells
I am working on microsoft excel 2003, I have a sheet that I merged cells starting with line 8 through 43...when I type my information in the merged area I can see all that I am typing...say it goes up to line 30 once I hit the enter key I can only see up to line 20. Even when I print it out it only prints up to line 20...I have checked to make sure there are not locked cells etc. I cannot figure out at all why this is happening...is there only up to a certain number of cells you can merge? From "Excel Specifications and Limits" Length of cell contents (text) ...

Newbie Question: Using Web Services
Hello All. I've been trying to implement Infopath with CRM with no success. I've tried the example, http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmbscrm1_2/html/mbs_crminfopathcrmintegration.asp, to no avail. I have followed the example to the letter, but find the information about publishing the Web Service to the server to be somewhat lacking. Admittedly, I am not a programmer, and I am continually running into an error in line 54: xmlDoc.LoadXml(objQuery.ExecuteQuery(objBizUser.WhoAmI(), strAllAccountsFetchXML)); Has anyone else ...

Calculating age in a label or text box on a form
Hello All, I have a text box for birthdate for a user to enter the birthdate and I want to the age for the user to be automatically calcuated and displayed in another label called "Age." What formula can I use? Missy ...

Purchase Resolution Tables
PRW tables in Great Plains - are these tables temporary tables populated only when the purchase resolution is run? Im asking because i want to extract data in crystal from the PRW40034 table but cant seen any PRW tables via crystal. Thanks Theo :) I reckon these PRW tables are names of once before existing tables in earlier versions of GP - i believe the tables I want are the MPO tables If this is 100% (im at about 95% with this) its a shame MBS havent cleared up the table list to NOT show tables that are no longer used. Theo :) "Theo" wrote: > PRW tables in Great Plains -...