Calculate Numbers From A Text Field?
Dec 27, 2006
Ok, this one has me stumped.
I have a several fields on my form that are list boxes that must have text as the properties so I can populate the lookup data with things like" Severe rating = 7 points" and "Moderate rating = 3 points". I have another similar field where the choices are "High probability = 9 points" and "Low probability = 2 points" etc. A third field needs to show the total points (product) of the choices from the first two fields, i.e. 9 points x 2 points = 18 points. Then,
a fourth field needs to display where the answer falls in a grid, i.e. 1-14 points = Low, 15-29 points = medium, 30-49 points = high, etc.
How do I apply the calculations on the field choices if the field properties have to be text instead of number?
Thanks,
bugleboy:confused:
View Replies
ADVERTISEMENT
Mar 26, 2014
We have a database that we use to track the tablets in our organization, a combination of Apple, Android, and Windows.
We label them with a hostname depending on their branding. Example below.:
APPLE01
APPLE02
APPLE03
ANDROID01
ANDROID02
ANDROID03
WINDOWS01
WINDOWS02
In the database we use the field name HOSTNAME for these entries. When sorting the field, due to it being a text field, we get the traditional sort of
APPLE01
APPLE02
APPLE10
APPLE100
APPLE101
APPLE11
APPLE110
ANDROID01
ANDROID02
ANDROID10
ANDROID100
ANDROID101
ANDROID11
ANDROID110
We would like to have them show up as
APPLE01
APPLE02
APPLE10
APPLE11
APPLE100
APPLE101
APPLE110
ANDROID01
ANDROID02
ANDROID10
ANDROID11
ANDROID100
ANDROID101
ANDROID110
What is the best way to tackle this?
View 1 Replies
View Related
May 15, 2012
i have 1 table name "table1"and i have in this table 4 fields
1) id
2)pay
3) tax
4) total
The id is primary. I have form in this form i have 3 text box "pay" "tax" "total". i want to insert some number to pay and number to tax and make some button to make calculation of the 2 text box and it will show me the result in the total and insert them to the fields.
View 3 Replies
View Related
Feb 17, 2005
Is it possible to have text and numbers in one field as access asks you specify the data type of that field?
View 1 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 3, 2006
--------------------------------------------------------------------------------
Hi,
Could you help me with the following.
Lets say I have table with one of the field header as 'notes'.
The notes is of type 'text'
In the notes column information like the following is typed out
Record 1 - MAX O.D 3.456" Min ID 1.2 and OAL 3.4"
or
Record 2 - MAX O/D 5.456" Min I.D 1.2" and Min Length 4.4 inches
Please note that as shown in the example there is no standard way of entering data. The only order that is followed is that the first numerical value corresponds to OD, 2nd corresponds to ID and 3rd corresponds to overall length.
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 1 Replies
View Related
Nov 10, 2005
I have a field in a query that contains numbers and text (text field). The numbers displayed come from a percent calculation and display with many decimals ie, .99898745987245. Is there a way to eliminate the decimals with code in the query field? For example .99898745987245 to equal 99%? I can’t format the field as a number or percent because it has both text and numbers. HELP!!
Thanks
View 5 Replies
View Related
Oct 24, 2013
I have a field that is text format. However many of the records in that field contain numbers.I want to delete all the records that are numbers in this text field, leaving only records with textual data.
View 4 Replies
View Related
Jan 11, 2013
I know you can't store text in a numeric field but I always thought you could store numbers in a text field - provided you didn't need to do any calculations on them. My problem is as follows:
I receive an Excel 2003 spreadsheet once a month, which I save to a specific filename/location overwriting the previous file. My Access 2003 database uses this as a linked table and (among other things) runs an append query to add the new data onto an existing table.
We have now added a new column called Reference in the spreadsheet. Often, this will be empty, but it could contain numbers or text. This is the first month I have received it and most entries are blank (including the first row) but further down there are some numeric values.
So I added a new Reference field to my main table and set it to text. Then I amended the append query to include the new field. But when I run it I get the error "Numeric field overflow". If I take that column back out of the query, it runs fine, so that's definitely the offending data. And when I open the linked table in Excel and scroll down to where I should see the reference numbers, I see #Num! So it looks to me like it doesn't recognise numbers as text.
Things I've already tried
In Excel, I formatted all the Reference cells as text.
That didn't work, so next I added a dummy record at the top of the Excel file (just under the headings), with zeroes in the numeric columns and 'X's in the text columns including Reference.
But that doesn't work either. Given the above circumstances, what's the best way to proceed with this?
View 3 Replies
View Related
May 5, 2013
I am trying to create a query to find missing sequential numbers in a text field. I am using this specific field as a case number which is designated as two letters, the # sign, two digits indicating the year, a dash, and then a four digit number; For Example: AB#13-1234.
The reason for this query is to tell the user of this database that a specific case number has yet to be entered and needs to be. The case numbers are unique and will never be referenced more than once.
My table name is "MainDataTbl" and the field i'm trying to find the missing case numbers is titled "CaseNumber".
View 9 Replies
View Related
Mar 1, 2015
I am looking to calculate numbers in query like this if the field name in query is Unit and the second field name is amount and i want that the amount field to calculate the unit field on this criteria..Suppose if total unit is 393 so the calculation should be like this;
First hundred should be calculated with 100.00 X 5.79 = 579.00
Second 200.00 X 8.11 = 1622.00
Third 93.00 X 12.33 = 1146.69
Total should be displayed in the amount field = 3347.69
View 14 Replies
View Related
Jan 10, 2007
Ok so in excel I have some numbers that are stored as text. The reason being that they are zip codes and some begin with 0 and excel doesn't want numbers to start with 0....so when I import these into an access field that has an input mask for zip codes...will it convert these correctly since the field is a text with input mask?
View 1 Replies
View Related
Apr 7, 2014
I have a form that have textbox that calculate total and then i want to convert numbers to words. Iv got the code with function currencytotext but when i set the code source of another textbox to:
=currencytotext([inv_total])
I have an error. I saw this on internet but i think bcz I am using access 2013 may be the syntax is different a bit.
View 5 Replies
View Related
May 6, 2013
I am creating a Make Table Query and calculating new fields based on current and prior year-end numbers. If the prior year-end number does not exist (Is Null), I want the use the current rate or calculate the change in rate. I have typed the below in the Field Box:
CHG_IN_PGM_RATE: IIf(([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE]) Is Null,([MASTER LIST CURRENT]![PTD_PM_PGM_RATE]),(([MASTER LIST CURRENT]![PTD_PM_PGM_RATE])-([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE])))
However, when running the query, I get the attached error message.
The screenshot will also show how the two tables are joined.
View 2 Replies
View Related
Jan 13, 2006
I was wondering if there is a way of only allowing text in a text field - we need to do this for validation purposes when inporting a text file into a database. I have tried input mask but this only checks when entering new data - I have tried to use validation rule like"???????" however this means that there has to be 7 letters in each field and this is not the case. - the field is for town and a couple of the records in this field have numbers at the end which are incorrect that we want the database to reject :eek:
View 1 Replies
View Related
Mar 22, 2014
I have 4 fields that are unbound on a form. img1 img2 img3 img4..When these are entered they are all combined and autofill another unbound textbox = imagename.. what i would like to do is from this unbound textbox 'imagename' ..i would like to populate a textbox that IS bound called FileName
[Event Procedure]
Private Sub imagename_Click()
Me.imagename = Me.FileName
End Sub
View 4 Replies
View Related
Jun 14, 2006
I wondered if someone could help.
I am using the Val () Function to convert a text field to number within a query which still gives me a text output.
P.S I do not have permissions to change within table as using Access as a front end to SQL via link Tables.
View 1 Replies
View Related
Mar 24, 2007
Hello, I have a Field in a Table called Fund Codes and they are entered as text: 1, 2, 3, 4 all the way to 1000. Now i need to find away to change 1 to 001, 2 to 002, 3 to 003, 10 to 010, but leave 100 as 100 and 222 as 222, so only add 0s to numbers from 1 to 99. Is there a way to do it? I know i can change the text to numbers and use format to do that, but i need to leave it as text. Please help. Many thanks!
View 11 Replies
View Related
Oct 12, 2006
HI,
I have a field that contains mainly numbers, but the data type is set to Text because there are a few records where the numbers have a letter suffix.
Eg. Invoice numbers 100, 101, 102, 102A, 103 etc.
These suffixes are a result of poor data entry when the data was stored in Excel.
What I was to be able to do is sort this field and treat all entities as numbers. Currently when I sort it, it shows:
1
10
11
2
20
21
3
...etc
Is this possible, even though the data type is Text?
View 2 Replies
View Related
Apr 16, 2014
i want to use the AfterUpdate to calculate the age using two text fields
Private Sub Birthdate_AfterUpdate()
Me.AGE = DateDiff("yyyy", [Birthdate], Now()) + Int(Format(Now(), "mmdd") < Format([Birthdate], "mmdd"))
End Sub
but it wont work
View 4 Replies
View Related
May 31, 2007
Hi *,OK, still new to Access so I apologize in advance if that is a dumb question ;)I have a table "survey" where I wanna store number values (0,25,50,75,100 respectively). The user is supposed to use a form to input the data into the table. For this purpose, he/she is supposed to use a drop down box.So, is it possible that the drop down box does not show the values but instead a verbal description of these values (very important, important, average, etc.)? What I have tried was to create a table with the number values and use this table as data source for the drop down box... it shows the values but for the end user, verbal descriptions would be better...Thanks!Steve
View 2 Replies
View Related
Oct 13, 2005
I am trying to import a field into a data, which is either a number, a number and text or just text. Within Excel the format has been set to text. The data type for the field in Access has been set to text and yet for those records which are only numbers when imported the record is shown as a number in scientific format. Does anyone have any ideas how I can get the number to be a number in its full format?
View 2 Replies
View Related
Mar 8, 2006
Hi,
I have a strange problem, I have created this query:
INSERT INTO WEEKONE ( Assigned_To )
SELECT QPTActions.Assigned_To
FROM QPTActions
WHERE QPTActions.Entry_Date<=DateValue('1/2/2006') And QPTActions.Close_Date>DateValue('1/2/2006');
It works, but it inserts Numbers into the Assigned_To column in the table WEEKONE. When I looked in the QPTActions database the values are text but the type of column was originally set to Number. I changed this to Text but its still inserting Number values into the destination table. How can I fix this?
View 1 Replies
View Related
Dec 14, 2005
Hi,
I'm trying to search a field which has both names and numbers in it.
For purely text entries the wildcard search works just fine.... like this:
Like "*" & [What is Last Name?] & "*"
I can type in "Smi" and get all of the Smith, Smiths, etc.
However, when I try to use this for entries which include numbers (123), which should return "Smi123" I don't get any results at all.
Any suggestions? I can put in the SMI123 as straight text in the criteria box, and will get the appropriate record.... so I know it is there!
Thanks .....
Gordon
View 4 Replies
View Related
May 23, 2006
Hi,
I am using group options and they store a number instead of the vaules you typed in, is there anyway of making a group option that will store values and not numbers?
View 9 Replies
View Related
Aug 19, 2004
I created a database that has a field called "Corrective Actions." I want the user to be able to type in a series of numbers, in the following format: 001, 002, 051, 123. Each number has a certain meaning. For example, 001 means "Upgrade to current revision," 002 means "Replace Unit," and so on. Here is what I would like to happen: The user will type in the numbers in the appropriate format. When the user presses the tab key to go to the next field, I want the numbers to be replaced with the actual meaning. My problem is that I can not figure out how to accomplish this. Can anyone help me?
View 5 Replies
View Related
