Modules & VBA :: Sorting TextBox Values In Numerical Order
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 Replies
ADVERTISEMENT
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
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
Apr 2, 2014
I have a problem with a cascading form, which writes back to another table. The scenario is:
There are three tables. Users, Departments & SubDepartments.
Each table has an Autonumber set as the primary key .
The form is to write back to the user table, and within the user table there is a Department & SubDepartment field. These are linked to the relevant tables and all that works.
For the form I have two combo boxes for Department and SubDepartment. Department simply pulls from the Departments table and displays/writes back to User table correctly.
The SubDepartment is the one I am having problems with. I have got it to cascade correctly but here is the exact issue:
On the existing records the subdepartment is being displayed as a number (the primary key autonumber). The drop down list displays the text of what the subdepartment is (which is what I require) but then when that is selected it throws up the error that the value entered isn't correct for that field. I guess it's because it wants to write back the numerical record ID rather than the text from the other field.
The row source query is SELECT [qrySubDepartment].[SubDepartmentName] FROM qrySubDepartment ORDER BY [SubDepartmentName];
The table fields for the subdepartment table are SubDepartmentID, SubDepartmentName, DepartmentID and SDID.
SDID can be ignored for the purpose of this question and DepartmentID links back to the main department. Just to confirm visually the cascading form does work in terms of when you pick a department it only displays the subdepartments associated to it.
View 2 Replies
View Related
Sep 27, 2012
I have a list of 22 soccer players with their weights sorted from heaviest to lightest in numerical order. I want to add further players and have the list reflect their standing each time it changes i.e. Col 1 Player ID, Col2 FirstName, Col 2 SecondName, Col 3 Mass:90 Kgs Col 4 Position In group: 1
A new player registers and is 95Kg.....he becomes #1 and the rest go down etc. Can this be done?
View 6 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
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
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 2 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
Jun 5, 2014
I have a form in which the user has the privilege to download some data from the table.the user will paste some of the numbers to the textbox from the excel in order to download the specific data.i need to write a query to parse the textbox using the in condition
View 12 Replies
View Related
Sep 22, 2013
Trying to figure out during the key down event -
a.) how to also stop a leading zero (e.g. don't allow 04, but allow 4)
b.) Don't allow any combination of numbers to be outside 1 to 36
Yes, I can do this on a validate event after the fact. Just wondering if there is anything that could put this all into the KeyDown event to prevent errors in the first place.
This code works great to prevent any key entry except 0..9, Tab, and backspace
Code:
Private Sub txtSectionNumber_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode ' only accept a number 0 to 9 Backspace or Tab
Case vbKey0, vbKey1, vbKey2, vbKey3, vbKey4, vbKey5, vbKey6, vbKey7, vbKey8, vbKey9, vbKeyBack, vbKeyTab
' do nothing and accept the value
Case Else
KeyCode = 0
End Select
End Sub
In the US Western States, a survey township is simply a geographic reference used to define property location for deeds and grants as surveyed and platted by the General Land Office (GLO). A survey township is nominally six by six miles square. They are assigned numbers of 1 to 36.
View 2 Replies
View Related
Feb 9, 2005
I have created a report from the information submitted on a form. When I pull up the report, I would like it to sort differently than it is. I am not sure what it is using to sort from but I want it to sort by a designated # I have assigned it.
Is there a way to make it so the access's record # will always match our companies record #?
Today I entered information in a form that should have gone in as record 96 but when I went back to look at the record in the report, it was record # 72. ??????? Now what do I do?
View 2 Replies
View Related
Sep 2, 2013
I am creating a database for cases. I want to set autonumber into sequence, Let say if there are data numbering 1,2,3,4,5. If I delete no.2, The data will rearrange in oreder from 1,2,3,4. While If I add a new value it would be the no.5.
Instead of data value autonumber 1,2,3,4,5 as I deleted no.2 and add new records. The data has become 1,3,4,5,6.
View 1 Replies
View Related
Sep 27, 2013
I would like to get the Min + Max values of the data currently in the form, but without changing the sorting currently on the form.
So I was hoping for this, but it is not working. The data in the recordset are not sorted.
Code:
Set R = Me.RecordsetClone
R.Sort = "SendOn ASC"
R.MoveFirst
MinDate = R!SendOn
R.MoveLast
MaxDate = R!SendOn
Any other method except iterating through the entire recordset?
View 3 Replies
View Related
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
Sep 18, 2013
I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN)
SET TableA.HCC = TableB.HCC
WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL
ORDER BY TableB.HCC, TableA.CDN;
View 2 Replies
View Related
Jun 21, 2013
My table have orderid along with other columns, i want to create a form where i am having a textbox for order id input by user. Firstly when user sees the form its only with the textbox and table fields, when i put the order id in the textbox the listing should be made.
View 1 Replies
View Related
Sep 15, 2005
There is a text box in a form that user can enter the value.
How can I check if the value entered by user is already existed in the record of the linked table or not?
If the value (or record) do not exist, that new value/record will be added to
the table.
If the value do exist, just show the msgbox to indicate.
How can I do that ? :confused:
Thanks
View 1 Replies
View Related
Jan 4, 2006
I have a database with a code field which contains alfanumerical values like:
IB1, IB2, IB3...IB180, and so on.
When I sort the usual way A-->Z, I obtain:
IB1, IB10, IB100, IB101...IB109, IB11, IB110, IB111
How can I sort nicely to obtain IB1, IB2, IB3...
To complicate things, there are alfanumerical values with a different number of letters before the figures: like F1, F2... and SER1, SER2 and so on.
Is there any plugin to nicely sort those codes?
thanks in advance
Romain
View 3 Replies
View Related
Jan 25, 2005
:confused: :confused: :confused:
Please bear with me. I'm new to using access and plodding along little by little and I had little luck finding relavent past posts. I have a table with the column headings and a couple of records listed below. Each of those records has a subdatasheet pertaining to data for the commands. For every command, my dept does a survey or inspection every so many years. How often we do these surveys depends on the HAZ CAT. If the command has a HAZ CAT of I, they have a survey done every year. (HAZ CAT= II, every 2 years; HAZ CAT = III, every 4 years). On a form, I want to be able to generate the next serveral survey years (let's say for the next 10 years) based on the command's most recent survey year and its HAZ CAT. So for each command, there should be a listing of all its survey years. Then I want to be able to sort by year so I'll know what commands will need to be surveyed each year. I'll eventually have a form which will be my control panel. On the control panel will be command buttons labeled with years for the next 10 years. The button should open up a form with the records for the respective command information.
[CommandID] [Command] [HAZ CAT] [Last Recent Survey]
1 AIMD I 2004
16 DODDS III 2002
View 2 Replies
View Related
Nov 23, 2006
Hi All hope some one can help with this.
I have a sub for which is set to sort by ascending order for a given field which happens to be a text field with vales set something like abc1.1,abc1.2 ect. The problem is, is that when you get to abc1.10, and more these are then sorted as so
abc1.1
abc1.10
abc1.11
abc1.2
abc1.3
ect
How can I stop this and sort it:
abc1.1
abc1.2
abc1.3
.......
abc1.10
abc1.11
Thanks for any help with this
Paul
View 2 Replies
View Related
Jun 19, 2013
Im trying to sort a form on a date. it only has dates put in once a confirmed date is known so when i sort all the blanks come to the top is there a way of sorting excluding null values
View 9 Replies
View Related
Dec 30, 2013
I currently have records that end with a letter and 2 numbers. For example, A1, A2, ... , A10, A11. When I try to sort my table/query by these values, A10 & A11 come before A2. It seems that it is sorting by the first digit shown. Is there any way to fix this quickly within table/query properties so that this can be displayed in proper numeric order?
View 7 Replies
View Related
Jul 1, 2013
I'm trying to make a report that a untrained user can use to review the relevant data from the database I'm creating. All of the work I've done so far has been directly in the tables . Here's a quick outline of my general data organization:
Department:
DepartmentID(PK)
DepartmentName
Employee:
EmployeeID(PK)
EmployeeName
DepartmentID(FK)
Needs_Work (check box)
[code]....
Basically, I want the user to select a department to view using a combo box or option group (which I'm not sure of how to do in a report). Then, I want to view the employees within that department sorted first by whether or not the Needs_Work check box is selected, and then by how many of their competencies are unchecked.
View 2 Replies
View Related
Jul 22, 2005
Code:SELECT * FROM mytable ORDER BY type, value
hi i am trying to order a select statement by two columns, so it first orders by type, then by value. the above code would work in php/mysql (i think!) but not with asp/access. any ideas?
cheers
View 5 Replies
View Related
Aug 22, 2012
I have a Form which presents the results from a query. Upon clicking the header of each column of data the records are sorted A-Z and Z-A using the code below. This works fine when all records have data in the relevant field but not when several records have a value of null. It appears that when null values are present the "If Me.OrderBy = "property" & " DESC"" always evaluates as not true, even though all the null values are on top and all the filled in values are in appropriate order.
A second issue is one of my fields is not sorting properly at all. I have two date fields, one sorts the other does not. The one that sorts is a user entered date (8/22/2012) which defaults to "9/9/9999" if the user does not enter a different value. The other date field which does not sort properly at all is a calculated date field which chooses the earlier of two possible dates (both associated with the same record) using
IIf([Senior Mortgage Maturity]>[Mezz Maturity],[Mezz Maturity],[Senior Mortgage Maturity])
This code is repeated for each of the six fields with the relevant field name in place of 'property'.
Private Sub Property_Label_DblClick(Cancel As Integer)
Me.OrderByOn = True
If Me.OrderBy Like "Property" Then
Me.OrderBy = "property" & " DESC"
Else
Me.OrderBy = "property"
End If
End Sub
View 14 Replies
View Related
