Assigning Numerical Values To Text Entries In Another Field
Sep 23, 2004
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.
View Replies
ADVERTISEMENT
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
View 1 Replies
View Related
Jun 2, 2005
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.
What appreciate some tips how to get this done.
Thanks,
Dirk
View 5 Replies
View Related
Nov 29, 2004
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.
Thanks
View 2 Replies
View Related
Aug 6, 2014
I have a report in which a textbox generates numerical values and letter values. I want to...On report load - if textbox = numbers then hide otherwise show if it contains letter values.
View 9 Replies
View Related
Aug 2, 2006
Hi,
Could you help me with the following.
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
View 3 Replies
View Related
Dec 6, 2012
I have a field named Supplies Expense. As an analyst inputs a business's expenses, it may require multiple entries into this one field. What is the best way to accomplish this?
For example:
Supplies Expense (Field Name) [$0.00]
The analyst has the following data:
2009 Financial Statement
Paper $50
Depreciation Expense N/A
Ink $100
So, going down the list, the analyst goes into access and enters $50 for paper then later needs to enter $100 for ink.
What is the best way to combine these multiple entries into a single Supplies Expense field and allow for a quality check of the individual numbers at the end of the project?
View 8 Replies
View Related
Jun 19, 2006
Hi there
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.
Is this possible and how would i go about it?
Cheers
View 3 Replies
View Related
Apr 5, 2013
I have a subformClientOffers on my frmAllProperties. On the subformClientOffers is a field called Outside Agents. This field allows for multiple values to be selected.
The subfrmClientOffers is in datasheet view.
While the field Outside Agents DOES allow me to select more than one, they post in the field one after another:
Ken Shaw, John Doe, Jane Doe, Harlan Bestlyn
Therefore, the datasheet view of the subformClientOffers causes the field Outside Agents to get very long, much too long to view without scrolling. My client wishes for everything on the frmAllProperties to be viewable without scrolling.
Is there anyway, to cause the field Outside Agents to place a return between each selected name (sort of a wrap text kind of thing)?
One thing I might mention is that the field is a drop down field from tblOutsideAgents. The table itself has First Name and Last Name, but I concatenated that into Fullname on the form, so that when the user opens the drop down they see the entire name. I don't know if that would have any effect on making the 'return' thing work or not.
I can't use this field as a subdatasheet because I already have one subdatasheet attached to the subform to show multiple notes on each Client Offer.
View 4 Replies
View Related
Sep 20, 2014
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.
e.g
Vehicle Sort Order
UNT/FL 500
FL4/45 400
E7/F5 600
UNT/FL 500
I want as
FL4/45 400
UNT/FL 500
UNT/FL 500
E7/F5 600
View 4 Replies
View Related
Nov 15, 2011
How do I suppress the numerical values from being displayed on the "yes/no" check boxes when a user clicks directly in the field, instead of clicking the arrowhead as they should?
View 3 Replies
View Related
Jul 24, 2013
I've got a table tblPatienten, a table tblRechnung and a form frmRechnung.
The primary key in tblPatienten is KundenNummer, the primary key in tblRechnung is RechnungsNummer. The relationship between tblPatienten and tblRechnung is one to many.
Now, every patient (stored in tblPatienten) is allowed to make multiple orders (stored in tblRechnung).
How can I assign each new order entry in frmRechnung to an existing patient in tblPatienten?
View 10 Replies
View Related
Mar 19, 2015
I am having some problem regarding the way the options in a lookup field is displayed. Currently the options are displayed numerically instead of displaying the actual text.
I have three tables which are ContactPersonnelT, OrganizationT and TrainingDonorT.
One of the fields in the ContactPersonnelT table is, Affiliation. This field is a lookup value (look up wizard) which comes from the OrganizationT table field, OrganizationName.
In the TrainingDonorT table I have a field called, ContactPersonnel. This field is a lookup value which comes from the ContactPersonnelT table field, ContactPersonName. In the drop down list of the ContactPersonnel, besides the ContactPersonName. I also want to display the Affiliation field. However, instead of displaying the text in the OrganizationName field, a numerical value is showing.
How to change the display to text instead of the numerical value.
View 5 Replies
View Related
Dec 19, 2013
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.
View 14 Replies
View Related
Apr 17, 2006
hello guys
i have values in two unbound textboxes and now i would like to assign the sum to a bound textbox. Also need AfterUpdate event too.
Can any expert help?
Thank you
View 4 Replies
View Related
Aug 30, 2005
Hi,
I am looking for Idea's of how to create a report.
Basically the report is a league table showing Month and YTD, this is the easy bit.
The part I need suggestions and guidance on:
The report also needs to show peoples movements in the league table i.e: whether they have moved up, down or not moved at all from the previous months position.
I was thinking of creating 2 tables: Current Month + Previous Month, in each table the persons position is indicated (an ascending record number is created throughout the table - not sure how to do this yet)
Then using code, lookup each persons position in previous month and write that position number against the persons UID and current position in current months table.
Am I on the right tracks or does someone have an easier solution?
Thanks in advance.
Darrell....
View 7 Replies
View Related
Aug 30, 2014
Have two tables: Assignment and StudentHeader - they are related by AssignmentGUID
Have the SQL:
SELECT StudentHeader.[Student ID], StudentHeader.GUID
FROM Assignment INNER JOIN StudentHeader ON Assignment.GUID = StudentHeader.[Assignment GUID]
WHERE (((Assignment.[Assignment Type])="Q") AND ((Assignment.[Assignment Number])=2))
GROUP BY StudentHeader.[Student ID], StudentHeader.GUID
ORDER BY StudentHeader.[Student ID], StudentHeader.GUID;
This returns:
Student IDGUID
al003072274
al154636303
al154636463
al595048272
al941626429
am180614426
am750033300
am750033462
I want a third field - Sequence - based on the Student ID and GUID, therefore
Student IDGUIDSequence
al0030722741
al1546363031
al1546364632
al5950482721
al9416264291
am1806144261
am7500333001
am7500334622
View 14 Replies
View Related
May 23, 2013
I've been building a small app for some friends. At present, they enter "records" line by line into a word processor, then use (archaic, IMHO) macros to produce the desired printouts. They wanted the new data entry UI to mimic that process.
Ok, so I've adopted the datasheet form as the principal entry vehicle. Based on the initial info they gave me, there were two convenient fields to sort on, so I used them in the OrderBy clause of the query which serves as the form's recordsource. I also developed the code to maintain the sort order after they inserted or deleted a record.
Now it turns out they don't require an entry into those fields, and in fact there's no logical field at all on which to order by.
So the question is: how to approach this? From my reading, I can't use the primary key or an autonumbered field, since the former won't necessarily reflect the desired incrementation and the latter only triggers for a new record, not an insertion (is this correct?).
My thought is to add a simple numerical field (which will be hidden from the user) with sequential values. I can do the OrderBy on it, and add code to adjust those values whenever a record is deleted or inserted. (That will require looping through the recordset from the point where the deletion or insertion occurred.)
For instance, is there some way to add a calculated field to the recordsource for this purpose?
View 11 Replies
View Related
Jul 13, 2005
Hi guys,
This is my first post of hopefully many, and I hope to be able to keep visiting and helping others in the future.
I'm building a database at the moment which I've done basing forms on Queries as I'm very familiar with doing so. However, one form in the database if a bit different.
The form in question is for shipping off refurbished units at the warehouse, by adding a SHIP ID to the record for each unit scanned in. The form before creates a recird ubdexed by a SHIP ID in the SHIPPING table, along with some other info such as delivery address and ship date. Each unit will be assigned this SHIP ID by the child form I wish to create, by scanning the Serial Number against it.
Two actions are done on the child form: Read in Serial Number of unit, find record in MAIN table and display key fields such as the units status (i.e. "WIP" or "REPAIRED" into text boxes on the form Allow unit to be shipped (by assigning the SHIP ID to the unit's record in the main table, only if the STATUS field shows as "REPAIRED"
Now, because I want to scan units into the form's Serial Number textbox and do the STATUS field check, then assign the SHIP ID; I need a recordset, right? I've inherited development of datdbases which used recordsets in their forms before, but have never had to create one from scratch. Also I wasn't able to take a copy of the source code of those databases before I left my last company.
Can anyone give me some info on what the bare minimum is to create a recordset, and to use it to save the record once I've made changes.
For your info I would consider myself an Access intermediate, and I've worked with it in various roles and versions for around 5 years.
Thanks,
Gareth
View 6 Replies
View Related
Jan 25, 2005
I have a table field which long ago was merged from several other fields. When the data was merged into the field it was delimited by "1." then "2." up to "5."
Example: MergedField = "1.Animal 2.Large 3.African 4.Grey 5.Long Nose"
I now want to split it appart in a query where "1.Animal" goes into expression1, "2.Large" goes into expression2, etc.
I need to base the text on where the one number begins and grab everything until the next number in the mergefield is detected.
Can someone show me the syntax for this.
Thanks!
View 1 Replies
View Related
Feb 5, 2014
In a form is there a way to change sort order based on which field is active?
I'm using a Split Form which gives me the ability to sort by clicking on the header name ans selecting sort. This slowing things done since my workload is heavy.
View 3 Replies
View Related
May 10, 2013
I'm trying to set the control source for a control on my report that describes the number of portions that goes into a box. Sometimes there is a specific number of portions per box and sometimes it's a range. When the number of units is specific, then it is entered into the MasterCaseMinUnits Field. If there is a range of units possible than the min is entered into MasterCaseMinUnits and the max is entered into the MasterCaseMaxUnits.
I want my report to look at the mastercasemaxunits, if it is blank it will only display what is in the MasterCaseMinUnits field. If there is a value in the masterCasemaxUnits field, then it will display the masterCaseMinUnits & " - " & MasterCaseMaxUnits. this is the code I'm Using:
Code:
=IIf(Nz([MasterCasemaxUnits],[MasterCaseMinUnits])=[MasterCaseMinUnits],[MasterCaseMinUnits],[MasterCaseMinUnits] & " - " & [MasterCaseMaxUnits])
When I run my report, the control displays: #Type!
View 2 Replies
View Related
Jul 14, 2014
I am trying to create a database for a company I work with which creates a food product which needs to be traceable from creation to finish. I have created a table with all of the necessary information for the creation of a batch.
1. During this batch creation, we add water multiple times which we record each time, i.e., 2cups, 2 cups, 2cups, etc. I have a field for "water", and wish to record each instance and amount of water added, but only want one record for the complete batch. Also I only need the total water amount in the batch record, but want to be able to expand to see the individual amounts as needed.
2. Also, we need to record various readings (temp, etc.) throughout batch creation. Again, same problem as before.
What is the best way to do this? I want to be able to reference this info anytime I want to check creation specs for each individual batch.
I am using forms for data entry.
View 6 Replies
View Related
Sep 25, 2015
I have developed a database but have had difficulty with sorting data within subform of a main form. The subform displays the related tasks that correspond with main form that has been selected. Within the main form I have also created a text field that defines a particular sorting sequence of the tasks found within the subform which is titled Task Sequence. What I would like to do, is use the Task Sequence field to sort the order of tasks within subform. I have tried: IIf([ID] Is Null, 0, Val([Task Sequence])) within the Advanced filter/sort but either it shows only the first task defined in the Task Sequence or it wants to filter the main form and not the subform.
View 14 Replies
View Related
Sep 19, 2013
I was looking to run a query using one text box with multiple entries. For example : EP342, EP423, EP234. Is there a way to do this in VB?
View 5 Replies
View Related
Sep 17, 2013
In Access 2010 I have a Data Entry Form on which I have an unbound textbox in the header that the user can put a default date in. In the body of the form is a bound textbox that records the date and the default value is set as =defaultdatestat (obviously the name of the box in the header).
Problem: The default date shows up perfectly until a value is put in any of the other text boxes.
For further info : If you put values in text boxes default value disappears; if you then push escape the default value reappears when the values in the text boxes disappear.
View 4 Replies
View Related
