I have a database where I would like to change a fieldname to something different.
I know I can use edit replace in the code and that works ok, but is there a way to change the fieldname in the code, the Table, the queries, and sql all in 1 foul swoop or do I have to do them all seperately by hand.
I have got a query which gives me the following output;
Nr ----------- area ------- area 2 ---------- holler
14-1096-------1------------1-----------------5.9
14-1097-------2------------2-----------------7.8
14-1100-------1------------1-----------------13.4
14-1101-------2------------2-----------------7.8
What i would like to do is to calculate the sum of holler when they are in the same area.So the sum of nr 14-1096 + 14-1100 and 14-1097 + 14-1101. Ive tried to do the following;I tried to do the following just to check it would even work;
Code:
test: (SELECT Sum([holler]) FROM querytoetsn2hr_gemiddelde_filter WHERE ((querytoetsn2hr_gemiddelde_filter.area)=("1")))
Which worked perfectly, it gave me 19.3..
Code:
test: (SELECT Sum([holler]) FROM querytoetsn2hr_gemiddelde_filter WHERE ((querytoetsn2hr_gemiddelde_filter.area)=(querytoetsn2hr_gemiddelde_filter.area2)))
That gave me the sum of all 4 the Nrs. Which makes sense, because you basically say that as long as area and area are the same calculate the sum of holler.if there is a way to say "sum of holler when area has the same value".
I want to use a variable as FieldName in the DLookup function.
Normal DLookup:
Code:
DLookup("FieldName" , "TableName" , "Criteria= 'string'")
I want to use:
Code:
DLookup(variable, "TableName", "Criteria= 'string'")
Where variable is an integer, which represents the column number in the table.
Is this possible?
I ahve in error typed the above - however it compiles and compacts and repairs without throwing an error. Should this be spotted before I actually run the line of code??
Ta
I am trying to create a query that can be customised by a combobox.
I have a combobox that lists the fieldnames from table (rather than records)
I want to be able to run a query that updates the field selected from combobox with the vaule from another txtBox
What I want to be able to write in SQL is;
UPDATE Products
SET [Forms]![Master_Form]![Combobox] = [Forms]![Master_Form]![Qty]
WHERE (((Products.ProductName)=[Forms]![Master_Form]![ProductName]));
But of course this does not work.
Any help would be appreciated in SQL or VBA?
I have a form with numerous fields and would like to be able to move the focus to a selected field by typing the field name into a control box. How can I do this? TIA
View 4 Replies View RelatedI have a form with 48 unbound text fields name txt1 to txt48.
These are to represent 24 hrs in half hours, to visually represent the hours capacity of my workshop. Now if I choose a date, which is say Monday, I check my settings, and see that the workshop has a capacity of 15 hrs on a monday. Now I would like to in a loop set the backcolour of the remaining fields to red.
Something like for I = 15 (workshop capacity) to 48
me."txt" & I.backcolour=1111
next I
I have a report named rpt100 with two subreports srpt100a and srpt100b. The subreports are based on query qry100a and qry100b. Both queries are based on tbl100. I removed a field named 'Comment' from tbl100, as it wasn't useful; Also removed the fieldname from both qry100a and qry100b. When opening rpt100 a parameter dialog opens asking for data on the deleted fieldname 'Comment'. The field 'Comment' was never used in the report or subreports.
Inspection of the subreport fieldlist shows field 'Comment' still present.
How, other than remaking the rpt100 and both srpt100a and srpt100b, do I remove the field 'Comment'?
Gunner...:confused:
As example, I have a table with an Item number, introduction year and a number of historical and future Sales periods set per year, these sales columns are listed Y1990, Y1991, Y1992....... Y2015.
Based on each items introduction year, I want to list the first 5 years of sales.
I wanted to create a dimensional fieldname eg: FirstYear: "Y"&[introduction year] to get the value of that respective year. (I currently just get a text saying "Y1995", and not the content )
Any help is appreciated!:)
(Note: I can't transpose the data in the tables for other reason)
Access 2010 report. I am trying to use DCount but have a string for the fieldname and cannot figure out the correct syntax. This is what I have tried:
txtMiscYes1 = DCount("[" & str1 & "]", "tblTemp", "[" & str1 & "]" = '1')
I want to count any answer that has a 1 and skip a 2 or 3 answer.
Hello all :)
I've read some really funny stuff while searching for an answer to this. You're a bunch of really witty types :)
So I'm sure someone can help me - why would this entered data not make it into the table?
I have a form that has a subform. After the subform I have a button to continue. If this is clicked I change the visible property for another field to true. This field appears to work fine, but the data doesn't make it to the underlying table, in fact it sometimes seems to be included in the next record.
Any ideas?
Thanks
A.
Hi,
Self learning trying to modify a query fieldname and criteria thru code.
Have a small form with a button making a copy of a query/s (eventually making about 50 copies).
Once these have been made, would like to open the query up, which I can do, then modify both
the fieldname and the field criteria to suit my needs from parameters set in the form.
How do I do this if it can be done ?
Thanks in advance
Ian T
Im running a query and normally there is only a field-name in heading. I have
multiple tables with equal field names. Now I want to get table names in
heading too (Tablename.Fieldname) so I can make difference between fields on each tables when previewing query. Is this possible in access? I don't want to change all field-names manually, I know it's possible and done easily, but there are almost hundred fieldnames...
Im finding a solution. Help me please. Thanks :eek:
- Roger
--
Have a nice day.
Hey guys, I am trying to input data into a form, the form consist of mulitiple tables. Once I set it up I get a "#Name?" error in the text box, and when I try to input data into the text box, at the bottom of the form it keeps saying "control can't be edited; it's bound to unknown field 'FieldName'."
Can anybody help me with this problem?
Thanks
Hello friends,
I am having trouble displaying or updating data through all my subforms.
So im just trying to isolate the issue,
I went into my table definition and changed fields named with seemingly reserved keywords as follows:
name = personName
userId = personId
Now, i have hundreds of report and queries based on these tables (3+) with these fields. What do i need to do to automatically update my reports and queries without going into each one to manually update :eek:
Is that what the autocorrect option does? I have been advised to always have it turned off as it causes database corruption.
So how do i update my all things with these field name change.
Any suggestions, comments would be greatly appreciated :)
I'm trying to change database name, and not the one in database properties and changing the title. I've done that already.
View 8 Replies View Related is it possible to change a name in tables, forms, queries etc in one input
for example I have a spelling mistake in my table , forms etc , can I do a change it in one go throughout the database
I finally found and eliminated all duplicate entries in my tblStoreProducts table, so that I could create a 2-field Index called UniqueProduct that would prevent duplicate entries. Without it, uploading information from Excel (not the preferred method) would result in duplicated entries in this table which would ultimately reflect in the Hazardous Materials reports that must be submitted to the fire marshal.
Today I was going through data entries in the Product table and noticed some that are STILL coming up with the UPC (a text field) in Scientific Notation.
I know that I have to change the information in the linked table tblStoreProducts first. The most simple way would be to just change the UPC of the product in that table to the correct UPC. It shouldn't be causing a problem, because it doesn't create a duplicate record. I'm getting the error "The text you entered isn't an item in the list" now that I've created that index.
I changed the index to Unique = No and still get the same error.
I removed the index all together...still get the same error.
I'm making the changes directly in the BE database right now. If I can't enter information directly, the forms aren't going to work either.
Is it possible to show an alert/reminder any time data in a particular field is entered or changed? It would have to show up if the data was changed in table view or query view.
Until I can write something that will store historical data for these fields, I want to make sure anyone entering information will record the old information in a separate table. Yes, I know this is not the ideal way to store the type of data; but this database has been running for 10+years and is tied in to so many aspects of the company re-writing it is just not an option ...
I have a form with a sub, and that sub has a sub. I'm on the first sub trying to change the rowsource of a combo box on the second sub, and none of the following want to work.
Code:
'Me!frmRentalsSubDetails.Form!cmbEquipmentID.RowSource = EquipFilter & Me.ToLocation & "));"
'Forms![frmrentalcontainer]![frmRentals].Form![frmRentalsSubDetails].Form![cmbEquipmentID].RowSource = EquipFilter & Me.ToLocation & "));"
'Me.frmRentalsSubDetails!Form.cmbEquipmentID.RowSource = EquipFilter & Me.ToLocation & "));"
Me![frmRentalsSubDetails].Form![cmbEquipmentID].RowSource = EquipFilter & Me.ToLocation & "));"
VBA syntax for changing various "Field Properties" on an EXISTING table. Specifically, the field properties I'm trying to change are:
Field Size (I want to make this 'Double')
Format (I want this to be 'Standard') and
Decimal (I want this to be '2')
The Data Type is already set as 'Number'.
I have copied our company access database onto my lap top and linked the front end to the back end. My problem now is that when I open the switchboard it is looking for the data and tables on the G drive, this was where it was on our server. How do you change the path for the switchboard.?
View 10 Replies View Relatedbefore ACCESS Excel was used to create orders etc,we are up to order number 16223 now I have access and the auto number will start at 1.how can I get the auto number to start at 16223.
View 11 Replies View RelatedI have a database with two tables: taskTable and volumeTable. On the task table I have the list of task performed in my team and the time it takes to perform the task related to a category, and on the volume tasks I have the volumes of workload based on the category moth after month. Then I have a query that multiplies the time it takes to perform a task with the volume in the category and returns me results for each month.
Now, if a task changes on march and the time to perform it its reduced or increase I change it on the table, howerver, ALL results for all months. I would like to know if there is a way for the change to be applied from the month it is changed and not retroactively.
Example Task Table:
Task Time to Perform Task Category
Buy ticket 5 min Business Visitor
Create profile 10 min Business Visitor
Example Volumes Table:
Month Year Category Volume
March 2014 Business Visitor 39
In this query I have setup I need to get my Dates field into short date format and another field needs to be with 0 decimal points. I take it there is code I have to enter but I cannot find it anywhere.
View 4 Replies View RelatedI've got an Access 2010 application that currently links to a SQL Server 2000 database. I need to change the link to a SQL Server 2008 database on a different server. Where do I go to change the link? When I click on External Data, ODBC Database, I only get an option to import source data. I don't want to import any data. I just want the application to link to a different external database. If I go ahead and click OK, I choose my ODBC connection and log in to the 2008 database. Now I've got a list of "import objects" to choose from. I choose one table (just for testing; I actually want to link with all tables), it says it imported successfully, but when I view the data in that table from Access, it's the same old 2000 database data.
View 5 Replies View Related