Modules & VBA :: Splitting Dataset Sorted On Two Fields Based On Change In Value In One Field
Feb 6, 2014
I am new to MS Access, and am not sure if what I want to do is even possible. It is my understanding that comparing subsequent cells within a field in a database cannot be done- so I thought I'd see if there is a way to go around it.
I have a dataset for pedestrian activity, with over 3 million rows and 40 columns - too big for excel to handle.
I need to sort the entire dataset by 2 fields, following which I need to search down the field containing my pedestrian ids (numbered 1, 2, 3... till approx 10000), and when my ped id changes from one to the next, I need to check the value in a field showing the ped location, and if that matches with the ped's previous location, I need to copy out a cell corresponding to the previous cell's time stamp. If it doesn't match, I need to copy out another time stamp from another field.
View Replies
ADVERTISEMENT
Jun 8, 2006
Hi All
Been handed a contacts database, one field of the DB includes all the industry sectors associated with the customer, with multiple industry references separated only by a comma within the field (e.g. Insurance-Life,Insurance-Broking,Insurance-Motor,Insurance-Pensions,Insurance-Home,Insurance-Health) – I want to split this one field into multiple fields using the comma as the break point (this is something that’s relative easy to do in Excel with the ‘Text to Columns’ tool – but unfortunately it can’t take the 130,000 odd records of the databasel)
Can any one recommend an expression or method that will see the 6 industry references split into 6 separate fields ?
Thanks
Jim
View 1 Replies
View Related
Jun 5, 2013
I'm making up a stock system for the independent jewellers I work for. Each stock pattern needs an individual code which can quickly be recognised read as tickets are often taken off the jewellery and can be mixed up easily. To this end we categorise based on two criteria:
1. Material
2. Type
At the moment these are as follows:
MATERIAL
IDMaterial
0Base and Miscellaneous
1Silver
2Gold
3Palladium
4Platinum
5Pewter
TYPE
IDStock Type
0Previously Owned
1Ring
2Pendant
3Chain
4Necklace
5Bracelet or Bangle
6Earrings
7Brooch
8Gents
9Gift, Clock or Miscellaneous
So if I had three pairs of silver earrings I want the first to be No. 161, the next 162, the next 163. If I had three Gold Bracelets I'd want them No. 251, 252, 253 and so on...Material and type are both selected form separate Comboboxes on a form.
View 2 Replies
View Related
Aug 4, 2015
I'm trying to lock certain fields on my form based on the value in a particular field. It seems to work the first time but applies itself to all other records in the database from then on!
The starting point is a command button that sends an email and makes Me.DPLLock = 1. The corresponding table entry is formatted as a number.
The code is...
Private Sub Form_Current()
If Me.DPLLock = 1 Then
Me.OR_Name.Locked = True
Me.OR_Sales_Order.Locked = True
Me.OR_WO_No.Locked = True
Me.OR_Qty.Locked = True
[Code] ....
Ive tried Me!DPLLock with the same result.
View 7 Replies
View Related
Nov 28, 2014
I have one field called FULL_ADDRESS. From that field I am going to populate two other fields. One called ADD_1 and the other, ADD_2.
In the FULL_ADDRESS field I have the following text :
"BOX 2747 ROCKY MOUNTAIN HOUSE AB T4T 1L7"
I want ADD_1 updated to:
"BOX 2747 ROCKY MOUNTAIN HOUSE AB"
and ADD_2 updated to:
"T4T 1L7"
While I understand how to do an update if I was using a space as a reference, i.e.
Trim(Left([FULL_ADDRESS],Instrrev([FULL_ADDRESS]," ")-1))
Trim(Mid([FULL_ADDRESS],Instrrev([A]," ")+1))
I can't figure out how to skip the first space from the right, and reference to the second occurring space, so that I get "T4T 1L7" to populate the ADD_2 field, and everything to the left of "T4T 1L7" placed in the ADD_1 field.
View 6 Replies
View Related
Dec 19, 2006
I am trying to come up with a simple way for users to query the database and pull back entries relating to the (potentially vague) data they input.
I know how to run a query based upon data input in a form ("=[Forms]![FormName]![FieldName]"), and I know I can do this several times in one query.
However, if one of the fields is blank, the query doesn't return anything.
Is there a way to use only one query, but still allow the user to leave search criteria blank? The only thing I can think of is by using a very convoluted VB script and creating a query for each possible combination of criteria.
It's not an error-checking issue, either, as entering info into all of the fields isn't required.
For reference, the searchable fields are:
Agent Name, Beginning Date, Ending Date (intended for a date range), Request Type, and District Name.
A user could then search for any requests made by an Agent; any requests by an agent within a date range; any requests made by anyone in a date range; ad nauseum.
There are quite a few combinations, there, and I'd rather not create a query for each one (or code the VB conditionals to choose the right one).
I hope I'm clear in what I'm trying to accomplish :o
Thanks in advance!
View 6 Replies
View Related
Aug 9, 2013
I have a form with a dataset based on a table. (there are a number of buttons on the form with code so it's important I don't delete the form and start again with a new one)
The data displays correctly but the order is wrong. It displays the last record added as the first record when viewed in form view.
I want to reverse this order, how do i go about it? It seems very simple but I've yet to find a solution.
I also don't want to base it on a query because of the code mentioned earlier.
View 4 Replies
View Related
Dec 15, 2006
I would like to change the property of a text box to Visible and Required when a user chooses a specific value in a drop down box of a different text box. Is this possible using a macro or some other way?
Thank you,
Deana
View 1 Replies
View Related
Sep 5, 2013
I have a report that is looking up min and max quality specifications for a product. The table with the specifications has a Yes/No check box field for when the specs are defined as "Internal". So the table looks like Product/MinValue/MaxValue/Internal
I want the report to display "TEST AND RECORD" when the Internal box is checked where the MinValue and MaxValue (that are stored in the table) would be displayed otherwise.
View 3 Replies
View Related
Aug 4, 2013
I have an edit button in my form which activates VBA, collects all data from the fields and edit the dataset. Everything work fine except the case that next to the edit access adds a new record as well with exactly the same data.
This is my code :
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("select * from tbllocations where locationid = " & cdkey)
[code]...
I checked every parameter and it seems everything is OK which surely is the base for the correct edit of the dataset. locationid is the key of the table.
View 3 Replies
View Related
Jun 9, 2006
Hi guys, I need a little help on next record stuff...:eek:
Can someone please explain how I can make my database actually go to the "Next" record after I update the "LName" field on my form? My database is sorted on "LName". After I update the "LName" field and save the record, the sort order is messed up. If I requery the form in the sub routine, the database goes to the first record. I need it to go to the "Next" record (the one that would have actually come next before I changed the LName). For example, if my database contains these names:
Baker
Doe
Franklin
Goodwin
Johnson
Jones
Smith
Taylor
and I change the current record's LName from Franklin to Phranklin, I expect the database to go to Goodwin (the record that would have followed Franklin) after pressing my next record command button. Likewise, if I changed Phranklin to Franklin, I expect the database to go to Smith (the record that would have followed Phranklin) after pressing my next record command button.
I've tried different versions of FINDFIRST on this site, but can't get it to work. I would like to find the next record based on my key field (autonumber) named "rec_id".
Thanks, and luv ya in advance!
-carol
http://profiles.yahoo.com/c_coop2005;)
View 14 Replies
View Related
Jul 31, 2014
So how would I go about changing the width of a combo box to the length of the value in vba?
View 14 Replies
View Related
Jun 18, 2014
I have a form I would like to validate input on. The field UpdatedResponsibilityCode can be updated but it can only have certain values based on the PaymentResponsibilityCode. Is there a way to force this validation?
If UpdatedResponsibilityCode = 4 then PaymentResponsibilityID can not equal 1
View 3 Replies
View Related
May 30, 2014
I have a form where customer data is entered. one field serves as a criteria for a query. If, for example, A is selected in the form the query uses the criteria "A" in the X field f query. However, if B is selected in the form, the query uses the criteria "B" in Y field of query.
I can easily do this by setting up two queries and having criteria A run in X field of query and similarly for B. However, it seems as if there should be a way for me to have one query and simply use the criteria in a different field.So, is there VBA that will update a given query's SQL to use one criteria in one case and another critiera in another case?
View 1 Replies
View Related
Jun 9, 2015
Can you have a form that will make a cosmetic change base on the value of a field?
So I have a Member's Detail form and I would like the form to look a little different (say header background a different color) based on the member type. Is this possible?
View 3 Replies
View Related
May 24, 2012
I want to display the ID of a dataset in a form but I don't want the user to be able to edit it. Therefore I want to display the id in a label and not a textbox or combo etc.
I can't see a way of how to do this though. Is this possible, or am I going about it the wrong way?
View 3 Replies
View Related
May 29, 2014
So I have a combobox that pulls values from a table. I am being requested to do an update to the database which means that I have to filter some values on that combobox based on a field on a previous form.For example:The main form is called "frmcontacts" and has a table field called "txttypeofcustomer" which can have two values: "customer" or "dealer".
The form that contains the combobox is called "frmnewissue" the combobox is called "cmbissuetype". "frmNewIssue" is opened through a button on "frmContacts". This combobox pulls the values from a table "tblissuetype". The values in that combobox are then: "Repair" "Return" "Question" "Replace".
If the value on txtTypeofCustomer is "Dealer" then all the values for the combobox remain visible. If the value of txtTypeOfCustomer is "Customer" then "Replace" cannot be visible or has to be greyed out.
View 6 Replies
View Related
Oct 29, 2013
I need a query's field to change to what is selected in a combo box.
Example,
I select product 1 in the combo box on a continuous form, after the update there is a requery. The form now displays the data from product 1.
I have tried putting [forms]![form1]![combo1] into the field box of the query. But this only makes the form display "product 1" in every row, it does not take any data from the table.
View 3 Replies
View Related
Jun 24, 2014
I would like to change the background color of a field that is the result of a Unique Values query. I am trying to get a list of invoices where all the line items are approved. I can't seem to get it to work the way I want because if even one invoice line item is approved it will show up as approved.
Is there a way to change the background color of the invoice field to red if ANY of the Approved line items are = False
View 7 Replies
View Related
May 5, 2013
I need to come up with a way to derive field X (see below) in a query.
For each change in field A, Set X=1
For each change in field B, X=X+1
The below table shows what the query results should look like.
A
X
B
[code]....
View 2 Replies
View Related
Apr 11, 2014
I have a subform that displays doubles in a textbox as fixed with 4 decimal places as default. I have been trying to change the number of decimal places based on the value in a textbox on the main form. I have tried this:
Code:
Forms![Main Form]![Holder_subform].Form![Final PCT].[DecimalPlaces] = CByte(Forms![Main Form].Numdecs)
where [Final PCT] is the textbox on the subform containing the decimal number and Numdecs is the textbox on the main form containing the number of decimals I want to use. I want to implement this Form_Current sub.
View 2 Replies
View Related
Jun 28, 2013
In access report, I'm trying to change color of text in specific records based on the true/false value in another record. Works in forms using conditional formatting, but won't seem to work in a report.
Here's what works in forms : IIf([2009 Symposium]=true, forecolor=255 ....this changes the records to red.
But using the same expression in a report doesn't change the text color.
View 2 Replies
View Related
Nov 12, 2014
I have a little "y2k issue".
My DB has many (!) forms that are based on Crosstabs queries. The headers are years, and therefore my formfields has controlsources like 2014, 2015 and so on.
Example:
The table it comes from looks something like this:
tblProject
Project: Myproject
Value: 1000
EndYear: 2014
The crosstab then takes the EndYear as a coloumn header. Then my forms use these fields, now named 2014, 2015, 2016 .... and give me a nice looking table showing the accumulated "end" for multiple projects.
The problem is when the year changes after 31/12, all fields must manually be changed. This means I have to manually change 2014 to 2015, 2015 to 2016 and so on in all of my formsfields controllsource.
I have played around to find a way to change the source of the field, and hoped I could say something like =year(now()) and for the next year say =year(now())+1 but this only returns that year as a value, and not as a controlsource...
The query functions nicely (ive changed the system time, and it has no issues with this structure), but the forms source is still in the previous year, and returns and error (of course).
When the year changes past new years eve, so will the headers (fieldname) change... but how to make the form change fieldsource?
View 3 Replies
View Related
Nov 5, 2014
I have an on click event to mail a report which works. I want to change the text to include data from a table.
I changed the code to include the field 'office' from the table 'Checks' but get an error saying 'Object required'.
Code is :
Private Sub cmd_mailreport_Click()
Dim office As Object
Set office = Checks.office
DoCmd.SendObject acReport, "checks", "PDFFormat(*.pdf)", _
"info@company.com", "", "", office & " Daily Check - " & Date, "Attached is the report for the office", _
True, ""
End Sub
View 1 Replies
View Related
Dec 14, 2007
Hello all
I have a table containing the following fields:
Customer ID
Customer Name
Countries
The ID is numeric, the name is a string and the countries are the tricky bit since it contains a string of concatenated 2 digit ISO codes: "USCAGB".
What I would like to do is convert the concatenated representation to a normalized one. Take this example table:
ID Name Countries
2 John SEDK
3 Maria USCAGB
From the above table I would like to create a new table that links Customer IDs to single countries as
ID Name Countries
2 John SE
2 John DK
3 Maria US
3 Maria CA
3 Maria GB
How would I go about solving this?
View 4 Replies
View Related
Oct 5, 2012
I want to split a table into multiple sets based on rowcount. Suppose I have a table having 10,000 records. I want different sets which should have values based on rowcount. Suppose if I select set 1 then the table should populate records from 1-2500. If I select set 2 then the table should automatically give the records from 2501-5000. If i select set3 then the table should have values from 5001-7500 and so on.
View 3 Replies
View Related
