General :: Update Query To Replace Letters And Digits
Feb 27, 2015
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")
Im working on an update query to add 2 zeros to the front of a field where it is less than 8 digits long. I'm not sure how to go about this, can anyone help me out?
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?
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"
I'm using Access 2010, though am also familiar with Access 2007. I have a listbox populated with a query (concatenated last and first names with a bound ID field that's invisible). One of my users asked if it would be possible to search the listbox by more than the first letter of the name. As it stands now, if you type Smith, the focus would go to the first name starting with the letter "H", rather than Smith.
I've spent a lot of time researching this and come to the conclusion that this functionality does not exist within a listbox. However, I've discovered, a popular suggestion is to change the listbox to a combobox, which has this functionality. Since my listbox is multiselect, that would not work for me.
When enforcing referential integrity, does access get angry about capital letters?Or just in general, if an address gets put in with a capital letter in one instance and then without in another does it make a difference?
When I print a report from Access, it prints fine. When a colleague does it to the same printer, it misses out lots of letters. Another colleague does it, and it prints fine. The font is standard (Calibri). My IT dept. say it isn't the printers fault. Is it something wrong with my report ?
I have a form with an unbound textbox. I want to be able to enter a sequence of digits and spaces (e.g., 02 950 4187); however, when I paste this into the box, I always have to delete the two spaces until the number is 9 digits long. Then it will accept the number and go look for the prize. How do I get the textbox to accept the number I paste in?
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!
I'm trying to derive the month in digits from a date/time stamp field. Any month between 10 and 12, I need the first 2 digits but anything <=9 is only 1 digit.
MONTH: IIf(Left([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS],2)="10" Or "11" Or "12", (Left([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS],2),(Left([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS],1))))
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'm trying to export from a text file. However one of the fields contains 2 items of data, ie the code and name of a person. eg 'po2 Paul O Mahony'. What I want to do is query only the code(first 3 digits) from this field. ie 'po2'. Is there a way of doing this within a query (sql) or do I have to use vbscript to solve this? thanks
I have a requirement to export an Access 2013 table query to XML and i need to be able to replace all the invalid XML characters before I can export it. How can I do a table wide find and replace for these.
For instance: & needs to be replaced with & < needs to be replaces with < > needs to be replaced with > ' needs to be replaced with &pos; " needs to be replaced with "
I have to search the entire table and replace all these wherever it sees them and i have not found a way to do it
I have been given a project from work and although I have built multiple databases before, for some reason this one is stumping me. I need to create a database to capture data which was previously being captured on multiple spreadsheets to simplify the statistics program at my company. Each spreadsheet captured key pieces of information, I've attached an example of each. Each sheet needs to be set up so that an individual can enter their information for their location and that one person (me) can enter the full company information. For example the hours form, from this form I need to capture the employee count and manhours, whereas from the district sheet I need to capture contractor hours, vehicle data (second tab), and performance data on a monthly basis. The final entry needs to be able to enter all of the counts on the counts link form in order to create a table similar to the link form.
Hi, I'm trying to import data from another database (codes), and the text field in that database is set to 7.
Our new database only needs the first 5 digits of the codes, so I'm wondering is it possible to create a query that filters any entries where the code field has more then 5 digits?
The field SECL DDI has the users phone number unfortunately over time these have been entered in different formats so there are 5 digits, 6 digits, 7 digits etc...Can I run a query that counts the number of digits in each filed and then tell me how many of each exist
I have a query in a subform on the main form. I have a search box that updates the subform/query as you type something (using the On Change event). You then click on the record you want which transfers the information to the appropriate text boxes (one of these txt boxes is the clientID I talk about below) located next to the search box.
I have a Contacts subform/query much like the serarch box I created and I am using a txt box (on the main frm) clientID (which I get from the above process) to filter the contacts.Now when I pass the ID to the txt box on the main form I am having trouble getting a event to trigger and update the subform/query correctly.
I am using VBA to create a simulated Click action which seems to work but is not updating the Contact subform/query, it is just resetting the subform/query. If I manually click on the txtbox with a ID in there all works wonderfully. I have attached the database. I made the clientID and btn next to it visible(this would not be visible normally).I just realised I left a button on the main form next to the clientID txt box just ignore that and click on a client then the clientID txt box to see how it updates the contacts subform..
I am trying to create an update query. I am trying to update a field in a table with the current date as a request.
I have a table named tblTest and a field named Date2 that I am trying to update with the current date, the button that the VBA is applied to is in a form name frmTest. This is my code:
Private Sub Command39_Click() Dim t1 As Date t1 = Date db.Execute("update tblTest set tblTest.Date2") = t1 End Sub
But when I press the button I get: Run time error '424' Object Required
Need a little bit of help, I have a report I pull out of an inhouse system with sales, shortages etc. I want to use a query to alter the formatting slightly and append it in to a different table. Most of this i can do fine, except for one thing.
Two of the sales fields in my report I'm uploading come out in the following text format: 19.0PC 27.0PC 45.0PC etc...
I need to change this before appending as its going in to a number field, all i need to do is lose the PC, e.g. 19.0 27.0 45.0 etc...
I can do this manually, it's a simple find and replace in excel before uploading, gowever I want to automate this as much as possible. Looking around I'm pretty sure it can be done in a query using the REPLACE function but I'm unsure on the formats to use.
The only other thing is once its converted even though it will visually be a numbe (e.g. 45.0) it will still technically be a text field, will this prove a problem on the appending?
