Modules & VBA :: Counting Value And Assigning Numerical Value?
Aug 15, 2013
I am working on an app, that has a field named "Name". I have everything set up with a module, which, for example, the name "John", it assigns 1. when it sees "John" again it assigns 2. Then "Jane" It assigns 1 again. I want the module to see every instance of "John" to assign the same number, 1, then all instances of "Jane" 2, etc. Here is the code in the module I am using, but it is assigning the values wrong. My final plan is to use the numbers for conditional formatting, so all johns one color, all janes, a different color. I can't use the conditional formatting wizard because these names pop up at random, and the names populate at random.
Option Compare Database
Global GBL_Category As String
Global GBL_Icount As Long
Public Function Increment(ivalue As String) As Long
If Nz(GBL_Category, "zzzzzzzz") = ivalue Then
GBL_Icount = GBL_Icount + 1
' MsgBox icount
Else
GBL_Category = ivalue
GBL_Icount = 1
End If
Increment = GBL_Icount
End Function
What is the best solution to this problem? How can I automatically assign a numerical value to a field based on the information currently contained as test in another field? For example, if I have a field that ranks a film as “Poor, Fair, Good, Great, Superior”, how can I have a set value appear for statistical purposes that assigns a number to each of these values (Something like: If rating of current record = “Poor”, then ranking = “1”; If rating of current record = “Fair”, then ranking = “2”; If rating of current record = “good”, then ranking = “3”, etc. I also want to be able to have the values recalculate if someone changes their original opinion.
I've got a rapidly expanding database that I designed to do my quotes for work and now I'm trying to expand it to add up the invoice amounts when the jobs come to fruition. I've got 99% of it working well but as I'm not the only one entering data I'm trying to make it as foolproof as possible and here is my problem.
I have a multi-line textbox that receives 'vehicles' in an abbreviated format and I need to get them in order based on a sort column number in the appropriate table. This i can do but the code I have found removes the duplicates which I don't want as I can can two identical vehicles on the same job.
I have two tables. The first is a list of users. The second is a list of projects. I need to loop through the user list and assign each one to a project. At the end of the user list it will just start over at the top until all the projects have someone assigned to it. What is the best way to do this?
Table1 Users
User1 User2 User3
Assignments
ProjectAssigned To Project1 Project2 Project3 Project4 Project5 Project6
After processing.
ProjectAssigned To Project1User1 Project2User2 Project3User3 Project4User1 Project5User2 Project6User3
I got one months table containing a reporting_month ,timeperiod and an Index column ID with data type Autonumber.Basically I want to search through the table whenever the User types in a new reporting month or timeperiod over the dialogue.Now I want to realize the following options:
1. The user types in a new reporting month, when a record in the months table exists with a timeperiod and a blank reporting month field, it should be assigned there. For example the User types in reporting month = 032014 it should be assigned to the Time_Period Value = 042014-032015
2. Vice versa, the user types in a time period. This value shuold be assigned to the blank field beneath the existing reporting month.
My scenario is that I am using a one time table that is a copy of my real transaction table (called GLAcTranLine) to enter general ledger transactions. Both my form and my sub-form are unbound. The form is called GLFI50 and the sub form is called GLFI50TranSub on disk and inside the main form. That is, both the sub form and its description inside the form (in the label that appears when I build the sub form with the wizard) bear that name.
So in the load for the main form I carry out these job steps.
1. I take a "No Data" copy of the transaction table GLAcTranLine, to get the structure. That works fine. The name I give to it is prefaced with GLFI50 and ends with a random number. That works fine too.
2. I insert a blank record into the new table. That also works fine.
3. Assign the control source to the Sub form and to its individual objects. That just dies in a crumpled heap. Neither the form not the individual columns code works.
The error I get (in both cases) is the Run-time error '2465' Application-defined or object-defined error.
I believe this means that it just cannot work out what idiotic error I have made. The code all passes the compiler test.
Here is the SUB.
Private Sub Form_Load()
'STEP 1. I take a "no data" copy of Create the Work Table and assign it as a control source to the sub form '================================================= ======================
'(Copies the structure of the source table to a new target one. It isn't a temporary table so I need to 'find a way of assigning a temporary name and then using that name on this prog. 'The True on the end says "Do not copy the data)"
'GLFI50Work is the Global variable that holds the temporary table name. Dim RandomInt As Single
I'm trying to put together a DB for creating notifications to customers. The source file is pulled out from another system as an Excel file, which is then uploaded to Access via VBA. The user runs a query, and then sends the results (customer information) to an Excel file where we have set up a mail merge document (the notification itself).
A customer may appear in multiple records (having multiple accounts), but will only receive one notification.
What we're trying to do is give each notification an unique ID that will become its document reference number once the document is created. The number will need to be the same for all the records in which the customer appears, and they will need to be consecutive, as the documents themselves will be stored in PDF form later on in the process.
So I would like to assign an unique ID to each customer, each time it shows up when querying the uploaded source file (the query is done in a form, that has a sub-form for showing the results). Then, when exporting the query results, they will be also copied and stored to another table, along with the assigned reference number, for monitoring purposes.
The Access file itself will be split and used by multiple users (up to 9 users at a time). In theory, each user will see their own customers (each customer account is assigned to a certain user), but the number assignation will need to also depend on when the query has been run. For example, if user 1 queries the DB at 9:30AM they will get records numbered 1000 to 1050; user 2 (who queries the DB at 9:31AM) gets numbers 1051 to 2100 etc.
I built a query "WplatyKlienci" which sum all of payments "Sumawplat" from my clients "klient".
Code:
SELECT SUM(tblWycenaZakonczone.cenanetto) AS Sumawplat, tblKlienci.nazwaSkrocona AS klient FROM tblWycenaZakonczone INNER JOIN (tblZleceniaZakonczone INNER JOIN TblKlienci ON tblZleceniaZakonczone.id_k = tblKlienci.id_k) ON tblWycenaZakonczone.id_zlecenia = tblZleceniaZakonczone.id_zlecenia WHERE tblWycenaZakonczone.cenanetto is not null GROUP BY tblKlienci.nazwaskrocona;
Now I need to know 2 things and I have problem with counting in sql:/
1) what percentage of the total amount represents a particular client. 2) I want to create a graph, so I need to do another query, which shows values form no. 1 only for 10 clients with the largest payments + one position named "others" which represents rest of clients
In my database when an item is currently being loaned the item's loaned field will have a 1 to indicate this. When the item is returned the value is 0. Only so many items are available and when I use the DCount function it is counting the 0s as 1s.
1. How many employees received more than one award (answer: 2). 2. How many employees who received the Nobel prize also received any second prize (Answer: 2). 3. How many employees who received the Nobel prize also received the Fulbright (answer: 1).
Suppose I have a table with a variety of fields; one autonumber, some text fields and some date fields.
I need code to calculate the number of fields that are NOT date fields and then code to count how many of those fields (the ones that are not date fields) have data in them.
Following is the code to count how many fields in a table had data and this works well, but now I need to find a way to ignore the date fields. How can this code be modified to do the same but ignore the date fields?
Code: Public Function PercentCompleted(Table As String, IDProject As Long, TotalFields As Single) As Double
Dim sampO As DAO.Recordset, iCtr As Long, strSQL As String, Percent As Double 'Takes the fields based on the projectID strSQL = "SELECT *" & _ "FROM " & Table & " WHERE IDProject = " & IDProject Set sampO = CurrentDb.OpenRecordset(strSQL)
i have a query that returns data per week number. To obtain the week number i used this : Format([Date],"ww"). This gives me the week numbers great but when the query is sorted the week numbers are not sorted numerically, ie i get this
1 10 11 12 2 3 4 5 6 7 8 9
is there a way to sort my data to be numerical. Here is the query:
SELECT Format([Date],"ww") AS [Week Number], Sum(Conformance.Scrap_Quantity) AS Scrap, Sum(Conformance.Rework_Quantity) AS Rework FROM Conformance WHERE (((Conformance.Procedure_Followed)=False)) GROUP BY Format([Date],"ww");
Lets say I have table with one of the column header as notes. The notes is of type 'text'
In the notes column information like the following is typed out
MAX O.D 3.456" Min ID 1.2" and OAL 3.4"
or
MAX O/D 5.456" Min I.D 1.2" and Min Length 4.4 inches
I want to pull out the first numerical value (3.456 - first example, 5.456 -second example) and the last numerical value (3.4 -first example and 4.4 in second example). How would I be able to do this.
So the end result should be 2 more columns with max od as one and min length as the other. Regards George
I have to make changes to an existing Dbase. Problem is that in this Dbase it is on one form possible to put text in a numerical field. I need to do this again, however it does not work, Access gives the message that Text will not go in a numerical field. I am talking about a combobox.
My problem is the amount is showing on the screen as 3.15654E+11 I need it to show the actual value which is 315,654,000,000 I already have the field set as double.
When I tried to import the field from a csv file, it created errors when I had the field set as Long Integer. When I changed it to double, I didn't get the errors but got the value of these larger amounts as 3.15654E+11
I tried to update query just those records to the value 315,654,000,000 but got another error.
I have an ASP form that saves numerical data to an access database via an insert statement in the asp page.
The main problem i have is that i have some code that adds all the stored values together and provides a total on the screen for the user to print off.
I would like to save this 'total' value to a field in the table but cannot seem to get this to work.
So i thought of running a query in access that would add the sum total of a number of fields and store it to the total field.
I have a table which tracks material quotes. I have two fields for the quantity range called PriceBreakBegin and PriceBreakEnd. If I have a part that has contract pricing (no limit on the PriceBreakEnd), can I use a wildcard or something in the PriceBreakEnd field to represent any number, and not just the highest integer I can put in there like 99999?
I have a report due the first of each week in which I need the cases open and cases closed for the previous week, the week two weeks prior and the 2007 and 2006 year to date on two different types of cases. I have a case management table with a field for Type of Case, date assigned and date closed that I uses in my queries. Presently I have two query, one that generates only Type 1 cases from the Case Management Table and another for Type 2. I then use the Type 1 Query in another query that limits the results for Type 1 cases to those opened last week, one for those open two weeks ago, one for 2006 YTD and one for 2007 YTD. In these 4 queries I have one field [Type of Cases] and I have the query count. I then do this for Type 2 cases and then go through the whole process to do Closed Cases. All my queries have criteria to automatically filter the dates to the time periods mentioned above. I then have one report query that I put all the number in for my report. This query has 16 fields with the numbers for each period, last week open and closed, 2 weeks open and closed, etc. I then generated a report that takes these numbers from my report query and puts it in a report format automatically. As you can imagine this takes some time to go through each query to generate these numbers, so I was wondering how I may do this differently. Also, I have experienced a problem when a field produces no records I get a blank sceen with nothing under the Count of column and get the same thing for my report. How can I fix this.
I'm new to building databases and I wouldn't think that this is difficult but it has stumped me and everyone that I have approached to elegantly perform this calculation.
Essentially I have a table of meters (the best real world example is the watermeter that your community uses to measure how much water you use each month.) Each meter in the table has a "child table" of the date/time that the meter was read and the reading on the meter (stored as a number). I need to calculate the difference between the last two readings in the child table for each meter(essentially how much water each meter has used since the last reading.)
I have been able to sum, average, etc. the meter readings, but I can't seem to find a way to get a difference.
is there any way I can make a field with a data type that calculates numbers in the field but also allows text to be entered into the field (e.g. N/A or No Score) The non-numerical data certainly wouldn't have to be calculated and could be filtered out when calculating averages and other numerical operations.
