I've made a simple form to Login/Logout with radio buttons but the buttons only allow me to push a number as a value, in my case 1 or 2 for Login or Logout.
How would I make an update query to change those numbers to the equivalent text? Or is that not possible in the same field because that is 2 different data types?
Is it possible for an update query to simply replace all the data in a table's field with the results from a query?
For example, I have Table A with the field Years. This table performs various calculations within based on the field Years.
I would like an update query that simply replaces the Years field data in Table A with a new set of years based on a query's search criteria.
When I run the below query, I get an error saying the query must be updateable- but Im not trying to update the query, rather the Table
I've attached a photo ......
I have this working query:
Code:
INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA )
SELECT Left([dbo_BACKUP_ACESSOS.LOGIN],255) AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA
FROM dbo_BACKUP_ACESSOS
WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>"ACTIVE DIRECTORY") AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));
But Iwant to be able to use a set of data to be used in the Replace Statement, so I create a table to add each string I would like to have replaced by "nothing", and trying to make the replace query to look there in order to find what to replace.I also created a table where I will list the systems that I dont want in the select, so I removed the "ACTIVE DIRECTORY" and replaced by the colum that have the list of system I dont want listed.This is the result:
Code:
INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA )
SELECT Replace((Left([dbo_BACKUP_ACESSOS.LOGIN],255)),[PREFIXOS_E_SUFIXOS]![Valor],"") AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA
FROM dbo_BACKUP_ACESSOS
WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>[SISTEMAS_EXCLUIDOS]![Sistema]) AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));
The thin is that this keeps asking me to enter the parameter value for "PREFIXOS_E_SUFIXOS!Valor" and for "SISTEMAS_EXCLUIDOS!Sistema"
Right now, I'm working with an SQL code of
Code:
"UPDATE Individuals SET [ShareholderOf] = " & Me.CompanyNo & " WHERE [Name] = '" & PerName & "';"
However, doing so will obviously change the "Shareholder of" field into what the user inputs (Me.CompanyNo). What should I use if I want it to ADD the user input rather than REPLACING the old [shareholderof] value?
The company I work for is consolidating regional server space onto a single server. I have relinked all of my tables, but my ~180 queries are still pointing to the old server.
My question: Is there a script or other process that can be executed that will search through all my queries within the database to find the string "dbo_tbl" and replace with "dbo_vwtbl"? I would very much like to avoid taking each individual query to a notepad...
I have a field that contains 12 numbers. I need to replace the middle four numbers with a character so that the entire number is not readable. How do I do that?
View 14 Replies View RelatedI have a linked table to Access 2010 which contains tracking numbers for shipments.When I link the spreadsheet to the Table, the FedEx tracking numbers are changed to for example - 9.813842152e+014 instead of 981384215196229. Is there anyway to change these back to its original format? I was thinking of creating a query where I could use Conversion Function, but it kept giving me error. Please note that both the UPS Tracking and FedEx tracking are in the same field.
View 8 Replies View RelatedI have a list of consumables;
Syringe 50ml
Syringe 20ml
Syringe 5ml
Syringe Cap
White Needle
I want to remove only the number and the ml part from the list, so I would end up with;
Syringe
Syringe
Syringe
Syringe Cap
White Needle
If I use
PHP Code:
Replace([DrugNameVial],"50ml","")Â
I get the desired result for the 50ml syringe size.
I have tried every possible combination of "**ml", "##ml", "Like [0-9]ml all with no success.
How this can be resolved without having to individual enter each syringe size "5ml", "20ml" etc
I can't even just take the text from the right till the first space as this would lead to problems with other consumables in the list.
I'm having problems with an assignment in an online course I am taking.
The assignment is to create one Update Query to find & remove typos in a field. Specifically, the typos are multiple f's & g's embedded in a field.
I created an Update Query that finds the typos & removes just the f's. I'm having trouble with the syntax to also remove the g's.
This is what I have so far that works great.
Update: Replace([Field2],"fff","")
Criteria: Like “*fff*”
or: Like “*ggg*”
The Query finds the records that contain both the "fff" & the "ggg" typos but I'm having trouble with Syntax for removing both.
Does anyone know what I have to change in the "Update:" line to include removing the g's?
I know I could easily create 2 Querys to remove the f's then the g's but the assignment requires only 1 Query to remove both.
Thanks,
Yaani-Mai
We have a field of 7m records of varying length, some of which are numbers, some just letters, some alphanumeric, and most which include a dash, space or some sort of punctuation mark.
We need to replace all letters with "L", then replace all digits with "@".
We can replace characters individually using this update query:
Replace([Ref Digit Or Letter],"A","L")
But would like to use wildcard searches to resolve this, something like (these don't work!)...
Replace([Ref Digit Or Letter],"A-Z","L")
Replace([Ref Digit Or Letter],"LIKE [*A-Z*]","L")
I am running a query for an apparel manufacturing facility. In my query I have a table called 'OrderForm' which is where the orders are put in. There are more than one type of fabric that can be a part of an apparel item, so as a result, there are multiple fields pulling from the 'FabricType' table. In order to get this to work in my query I created 'SubTables' for the different fabric fields. For example, I have tblFrontfab, tblbackfab, tblsleevefab, and tblcollarfab which are just extra copies of the 'FabricType' table. The actual question is that when I want to replace characters like ,./& in the fabric field but I can't use the replace function. It says that it is too complex to calculate.
I used this notation
Frontfab: Replace([tblFrontfab].[fabric],".","")
and the error was that it is to complex to evaluate.
I currently import data into a table and it it has a text field which looks like this: 12,345.67 GBP...I need to use a query to make this a number field so that I can sum it's contents. I've managed to remove the 'GBP' part but can't seem to get rid of the comma?
View 4 Replies View RelatedIs it possible to use find and replace to modify text in report design - or else is there something else I can do to get the same effect? I have a really useful report and I want to modify it for use with a different dataset. To do that I will need to change the text in over 150 text boxes. I have seven different fields which each occur over 20 times in the control source formulas in the text boxes, because they are used in different ways in different calculations. If this was excel I could do a simple find and replace to change e.g. every reference to fieldname OLD to fieldname NEW, but I cannot see how to do that within access report so am haveing to click on each text box in turn, go to properties and edit the text box .
I am trying to switch to a more general naming system in the modified report so then I will be able to assign data with the required fieldnames for the report within a query. But the report I am starting with has field names based on years 2013, 2014, 2015 etc.I want a quick way to change each reference to those field names to my more generic new field names.
I have a Table1 served by Form1..It is a list of: UnqID, process, quantity, totaltime(in seconds).I want to click on a record to bring up a filtered Form2 with the chosen record on it.What I want to be able to do is to now split the quantity (and the time) and put these new records back into Table1 and delete the original record
EG
ID1,10,write a report,2400
I want to delete this and replace it with two (or three/four etc) replacements, but still adding up to 10 quantity and 2400 seconds so that the new data could be:
ID2,5,write a report,1200
ID3,5,write a report,1200
My initial thoughts are to create a holding table to:Append filtered data on Form2 to a holding Table1hld (i don't know how to do this) delete data in Table1.then enter the new quantities into a holding Table2 (that I will input myself) and then append (through a series of queries back into Table1).The first problem is how to append (and subsequently delete) the filtered record from Form2 to Table1hld.
791335.12pack. This is the object that is in one of my access fields. I need to extract the 12 and place that in another column called qty.
View 1 Replies View RelatedHow do you update a table by reducing a number by 10?
My assignment question is:
10 students have left GY101. Write an SQL UPDATE statement to reduce the class size by 10 for all modules taken by GY101 students.
I can display the students who take GY101 with the following code
SELECT moduleCode, classSize
FROM ROOM_BOOKING1
WHERE moduleCode IN (SELECT modCode
FROM STUDENT_REG1
WHERE sID IN (SELECT id
FROM STUDENT
WHERE courseCode = "GY101"));
But how to reduce the number by 10.
I want to create an update query to update 20 record with the first item in combolist then the next 20 record updated with the second item in list...
View 2 Replies View RelatedI have 4 tables with a field that holds a "batch number" there can be many batch numbers in these fields.I want if possible to replace a batch number from all my tables that contains that data and replace it with another batch number in one go. is a macro the only way to do this.I would like a macro that asks me the batch number to replace and then asks me the number to replace it.
View 14 Replies View RelatedI have a table with a field name compliance level which is filled with drop down list and having like this;
poor
good
excellent
IR
Now what I want is if i select poor from list then in query it generate -10 in query field named analysis, same if select good then generate 10 and so on means i will assign numbers for each field...
One can select only one item like, poor at one record entry...
My database tracks all of the staff development sessions provided for the past ten years, thus we have thousands of records. Our school district wants all departments to move away from using an employee's Social Security number to a number assigned by the district. Each employee has been assigned a unique six digit number.
In my staff development database, the employees data (Social Security number, campus, job description, etc.) are in a table, "EmployeesTbl", with the primary key being the Social Security Number. Classes data are in a table, "ClassesTbl" with some of the fields being ClassNum (an autonumber), class name, class description, etc., with the ClassNum being the primary key
in this table.
A third table, "AttendTbl", links the two and shows each class (staff development session) each employee has taken. This table has a a foreign key, "SocNum", that links it to the EmployeesTbl, and another foreign key, "ClassNum", that links it to the ClassesTbl.
I have added the EmpNum field to the EmployeesTbl, and all of the the district assigned employees' numbers have been entered. I have also added this field to the AttendTbl. Is there a means of using a query to fill in the employees number in this table. Is so, I'll then change the primary key in the EmployeesTbl to be the employee number and the foreign key in the AttendTbl to be the same. If not, we face the task of having to key in 18,000 records!
Thanks for any help,
R. Sanders
I'm trying to change a 1,2,3 in a table to display NA, Yes, No on a form.
I have an Infopath form with a n/a,yes,no drop down that is dumped into Access. Infopath requires a value be tied to them so it is 1,2,3. So in Access, my table shows the numbers and not the text. I would like to know how to get my access forms to show the text instead. Not sure if I should be setting the field criteria in my query to say if this field is a 1 make it Yes, if 2 make it NO, if 3, make it NA. Or if there is something completely different to do.
I found a workaround by going into the query and using Find and Replace but I need this to update itself automatically.
Hi,
I have my main table and one column is a lookup field to another table.
One of the options that was in the dropdown needed to be changed. So I changed the dropdown, but now I need to change all the old entries to reflect this change.
I opened the main table, and thought I could do a find/replace. But it doesn't work. If I do just a 'find' it finds the old text just fine. But if I do the Find/Replace option, it says the 'look for' text was not found. Even tho, I just found it using the find option. If I click on the tab that says find, it will find it again. If I click on the find/replace it can't find it.
any ideas on a way to quickly replace my text in these fileds, without having to go 1 by 1 through each record.
Thanks
Is it possible to do a criteria like the "Between" to pull data like a work order number?
Ex WO#: WO5551212
I would like to be prompted to enter a Work Order Number when I run a query, then have the query display all of the data for that particular work order number.
Is it possible to run a SQL command to update a field within a table with random numbers?
More specifically - random long integers linking back to an ID (autonumber) field in another table?
Background to this is, I have multiple static data tables related to each other by long integer identifiers (autonumbers)
The structure is fine but I haven't been provided with the actual data yet - but for development purposes, I need to work on other functionality which requires that this data be present.
So I want to fill my table with dummy data such that I can go off and work on the remaining functionality, but then just go back and clear it all out once I get the actual data.
I have one 'main' static table, which links back to other tables, which I have already populated with dummy static (i.e. company names, locations etc) Now I want to go into my main table and populate those fields in each record with a random ID. I don't mind doing this field-by-field (there's only a handful) but I've a lot of records in there (~1000) so I'd rather not do this record-by-record.
So I have a table with 2 fields.The first field has the addresses to multiple hyperlink paths to folders on my computer.In this field the display text matches the hyperlink paths.In the second field I have the desired display text for field 1.I have tried using an update query to either:
1) Change the display texts of field 1 to match field 2 while preserving the hyperlink path
2) Add the hyperlink path from field 1 to field 2 without altering the display name.
I cannot get either one to work. Upon updating field 1, I lose the path, and upon updating field 2, I lose the name.
I have a query that I try to update to Alphabetical instead of numerical. I am using MS Access. on the row say "Update to" I Enter Alphabetical letter but it doesn't work. I wonder if any way to do so. Please HELP!
Thanks