Replace Last Digit
Oct 7, 2004How would I replace the last digit of all the numbers on a table field from a 1 to a 2? Please help. Thank you.
View Replies
ADVERTISEMENT
Queries :: Make Replace Query To Look In Order To Find What To Replace
May 6, 2014I 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"
2 Digit Month
Jan 4, 2006Hi,
How can I get a 2 digit month when I use month(now())? I've getting only 1 instead of 01. Please advise.
Mutlipy Every 2nd Digit By 2
Feb 13, 2007I have a field “Ref Number” which is number 11digits long. I want to double the value of ever second digit (starting with the first digit)
So:-
8 7 2 5 6 0 0 2 1 2 2 (There is no gaps in actual data)
Would Be:-
16 7 4 5 12 0 0 2 1 2 4
Thank you
Numer Should Be 7 Digit Only
Jun 13, 2005Hi there
I have prop in checking 7 digit numbers.
In my form i have SNo field. This field should get range of value is from 999999 to 9999999(that is only 7 digits). How i can write code in Acces. I am new to MS Access. If i enter wrong number(ie lessthan or greater than 7 digit) , other field should not accept the value or cursor shouldnot move from text box.
i created a small project in Access how i can make username and password.
thanx in advance
by
yasi
How Do I Show A 2-digit Day
Oct 5, 2005I'm trying to extract the day from a date field but the day has to be 2 digits. For example, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, etc. The following extracts the day but in single digit for days under 10:
=Day([FromInclusiveDate])
How do I extract a 2 digit day?
Any help would be appreciated.
Left Pad With A Zero Digit
Oct 6, 2004Hi, when I type 0600 in a text box, it dispays it as 600. How do I make it so it displays the number that I actually type in, with a zero as the first digit?
Please help. Thank you.
Set Field To Specific Digit
Mar 25, 2005Good day,
I need a textbox to a five digit number. How would I write code to reflect this.
Thanks.
During Merge Access Is Dropping A Digit
Aug 16, 2007Ok here is my question. I have two column that I am trying to merge. The first column I have set to be two digits. The second column is set to be three digits. The problem is if the first digit in the second column is a 0 it gets dropped once the two columns are merged. How do I stop this from happening. I need the merged columns to be 5 digits.
Thanks,
Adding Another Number Digit Into My Fax Database.
Sep 3, 2007Hi all, I am happy with my fax database until my goverment decided to add another 6 infront of all the phone and fax number.
How can I change all the numbers together?
Example from : 1234567 to 61234567
Thanks.
Deleting Several 3 Digit Codes In Two Columns
Aug 30, 2006I have a query Im working on in the query are several fields. 2 fields (cat1 and cat 2) have nothing but 3 digit category codes (ex- 333, 334, 335 in the and some null values. Another field is the quantity of items I have which must be >0.
I have 7 codes I want to delete from cat1 and cat2 so my guess is I would place Not "333" And "334" And "335" in criteria for mcat 1. I also need to do the same for cat2. So I would apply the same criteria in the same row under cat2. So heres how my query would look in design view.
cat1 | cat2 | quantity
Not "333" And "334" And "335"| Not "333" And "334" And "335"| > "0"
Its not working, I can only seem to do one "Not" in all of the fields. So If I do Not "333" in cat1 I wont be able to do anything in cat 2 nor will I be able to add 334 to cat 1 or 2. I have tried Or it doesnt work I have tried using different colums and different rows.
There has to be a way to delete the same criteria from 2 different fields for multiple items per the same field.
Any thoughts? Much appreciated!
Help With 4 Digit Numbers To Date Format?
Dec 31, 2006Hello,
I have extracted 4 digit numbers from a long strings of characters and numbers which I have identified as date. Some are inputted as
mmdd (example: 1129)
ddmm (example: 2310)
I have to calculate the number of days between the start and end dates. I tried formatting as date, but I can't get it to work.
Any suggestion?
Thanks!
Display Double Digit Number
Mar 19, 2008hey guys i have the following line in my query for removing seconds from a time/date stamp and all works great except one thing if the time is 10:02 it is showing as 10:2 anyway of getting it showing correctly?
(Day([HTRXTBL].[HTRX_TIMESTAMP]) & "/" & Month([HTRXTBL].[HTRX_TIMESTAMP]) & "/" & Year([HTRXTBL].[HTRX_TIMESTAMP]) & " " & Hour([HTRXTBL].[HTRX_TIMESTAMP]) & ":" & Minute([HTRXTBL].[HTRX_TIMESTAMP]))))
Filling In Blank Fields With The Digit 0
Sep 13, 2004I have a table that tracks call-in's and tardies. Sometimes, a person will have a tardy and not a call-in and vice versa.
I need to perform a calc on both the call in and the tardy fields. Is there a way to tell the database that if [Tardies] is blank (nulll), that I want it to put a zero in the field? I know this must be done by a query, but can't figure it out.
I've also tried splitting these two tables into a call-in's and a tardies table, but can't get a query that will show all tardies AND all call-in's. It will only show either all call-in's with associated tardies or vice versa, so if a person has no tardies, but does have call-in's it won't show them. What can I do? Thanks!
KellyJo
Updating 4-digit Zips For My Addresses
Nov 22, 2004Hello,
I have a database in which I want to automatically update all the addresses
which have United States Zip Codes to contain the 4-digit zip which can be located at
www.usps.com.
I am a novice at setting up dbases, but seem to be able to get around when trying to figure
something out. Yet this one has stumped me. Could anyone help me update my data
automatically without having to do the tedious copy and paste of each record into the usps.com
website to get the correct address information.
Thank you,
tommyatoes <><
Queries :: Locating All 3 Digit Numbers
Dec 19, 2014Is there any code to be able to be used within a query to locate the 1,2 or 3 digit numbers?
such as 1 ,11, 111 - 2, 34, 834 etc...
Queries :: How To Remove A Digit From String
Jul 29, 2014I have a field called PropertyID which stores a unique 13 digit number, the 10th digit is always a 3. I want to remove the 3 to leave a 12 digit number.
I've tried running an Update query using Replace([PropertyID],"3","",10,1) but no joy, what am I doing wrong?
Tables :: Splitting A Field That Contains A Digit
Jun 29, 2015I am trying to split a field that contains a digit, so lets say the field contains the digit 7, I need it split into 7 fields. How I can do this?
View 4 Replies View RelatedUpdate The Data To Be In Certain Digit Form?
Jan 17, 2012I have a series of data that start from 0 to 999. I have to save these data in text form in order to be able to update them to the form I need. I need these data to be in 3 digits vs number form. For instance, if the data is stored as 1, i want it to be updated it to 001. Is there any easy way to update the single digit and double digit numbers to be in the format mentioned above?
View 1 Replies View RelatedQueries :: Converting Four Digit Numbers To A Date
Jul 30, 2014I have a query to create in access based off a query already created in SQL
The SQL query converts a 4 digit number into a date using this code:
CAST(CASE WHEN LEFT(OPDT, 2) > 12 OR LEFT(OPDT, 2) = 00 THEN RIGHT(OPDT, 2) ELSE LEFT(OPDT, 2) END + '/' + '01' + '/' + CASE WHEN LEFT(OPDT, 2) > 12 OR LEFT(OPDT, 2) = 00 THEN LEFT(OPDT, 2) ELSE RIGHT(OPDT, 2) END AS SMALLDATETIME)
OPDT is a digit number in text format. The function converts 9606 into 1996-06-01
What is the access method in order to this?
Reports :: Show Only 2 Decimal Places IF Last Digit Is Zero?
Apr 10, 2015I have a report calculation field that I want conditional decimal places on, for example if the calculation results in the last digit as being anything other than a zero, say $4.325 to show with all three decimal places ...i.e. $4.325 , BUT if the calculation results in a number like $3.920 (a zero on the end) ...I just want it to show on the report as $3.92
How do I approach this?
Queries :: Separate 6 Digit Data In Same Field
Jun 10, 2015In same field Im saving every time 6 digits data and i need to separate them. (It was not my fault. They created DB like this... )
For example my data is
123412(main-field) and i want to see them 1234(field1) 12(field2)
Queries :: Convert 19 Digit Number To Hex String
Mar 4, 2015I need to run a query that converts a 19 digit number stored in a Short Text field to a hex string
Example of the string is "3310854670615838865" the result should look like "2DF284C801000091"
Convert 5 Digit Serial Date Within Access
Jul 7, 2015I am importing a table from a Clarion TPS database which stores several types of data in a "IDVAL" field. The field next to it, "LBLNUM" defines what the data type is. This creates a problem, as IDVAL is a text field, which means all data stored in it (no matter what the type) is then stored as text. This includes date fields. Since they are stored as text, they end up as 5 digit serial dates, similar to how excel handles dates (with each day after 1/1/1900 being +1).
So I have a union query which derives all the values stored in IDVAL for each unique record. My query which creates my export combines this data with several calculated fields to create my final export.
Everything looks great on this final export except for my date fields, which are all 5 digit numbers (like 77945).
I have been searching for a date function that will format this back to a standard date, however I have yet to find anything that works. Is there no way to format a 5 digit serial date back to a standard date within access by using a function? Every answer I find says to redefine the table to a date field, however I don't have this option since the field in question stores a lot more than just dates.
Access 2007 - Cannot Display 17 Digit Number
Aug 8, 2011I can't display a 17 digit number in my table without losing the last couple of digits to 'rounding'. I've tried 'doubling' the field size but to no avail....whatever I attempt loses the last couple of digits to a 'nice round figure'.....
The numbers had initially been imported as text...which is really what they ought to be as they're identification numbers, but I had some issues using the find 'duplicate values' query and conjectured that was because the ID numbers had been defined as text---->though I could be wrong.
The VAL function works (to convert the text to numbers) but again -- I lose the detail of the last couple of digits.
I've been beating my head against this wall the entire day and at the very least, would like to know if what I'm attempting is viable. I've stumbled through function queries (with some success) and react like a deer in the headlights when it comes to VBA....
How To Limit Account Numbers To 4 Digit Numeric Value
Mar 28, 2015On a form, is it possible to have #### appear in the form box when it loads and then the user would type a 4 digit number in place of ####.Also, for the table associated with that form, how do I limit account numbers to a 4 digit numeric value?
View 3 Replies View Related