Replace Last Digit

Oct 7, 2004

How 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, 2014

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"

View 6 Replies View Related

2 Digit Month

Jan 4, 2006

Hi,

How can I get a 2 digit month when I use month(now())? I've getting only 1 instead of 01. Please advise.

View 1 Replies View Related

Mutlipy Every 2nd Digit By 2

Feb 13, 2007

I 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

View 8 Replies View Related

Numer Should Be 7 Digit Only

Jun 13, 2005

Hi 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

View 2 Replies View Related

How Do I Show A 2-digit Day

Oct 5, 2005

I'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.

View 5 Replies View Related

Left Pad With A Zero Digit

Oct 6, 2004

Hi, 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.

View 6 Replies View Related

Set Field To Specific Digit

Mar 25, 2005

Good day,

I need a textbox to a five digit number. How would I write code to reflect this.

Thanks.

View 1 Replies View Related

During Merge Access Is Dropping A Digit

Aug 16, 2007

Ok 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,

View 1 Replies View Related

Adding Another Number Digit Into My Fax Database.

Sep 3, 2007

Hi 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.

View 14 Replies View Related

Deleting Several 3 Digit Codes In Two Columns

Aug 30, 2006

I 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!

View 1 Replies View Related

Help With 4 Digit Numbers To Date Format?

Dec 31, 2006

Hello,

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!

View 1 Replies View Related

Display Double Digit Number

Mar 19, 2008

hey 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]))))

View 6 Replies View Related

Filling In Blank Fields With The Digit 0

Sep 13, 2004

I 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

View 1 Replies View Related

Updating 4-digit Zips For My Addresses

Nov 22, 2004

Hello,

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 <><

View 1 Replies View Related

Queries :: Locating All 3 Digit Numbers

Dec 19, 2014

Is 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...

View 4 Replies View Related

Queries :: How To Remove A Digit From String

Jul 29, 2014

I 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?

View 10 Replies View Related

Tables :: Splitting A Field That Contains A Digit

Jun 29, 2015

I 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 Related

Update The Data To Be In Certain Digit Form?

Jan 17, 2012

I 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 Related

Queries :: Converting Four Digit Numbers To A Date

Jul 30, 2014

I 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?

View 7 Replies View Related

Reports :: Show Only 2 Decimal Places IF Last Digit Is Zero?

Apr 10, 2015

I 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?

View 7 Replies View Related

Queries :: Separate 6 Digit Data In Same Field

Jun 10, 2015

In 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)

View 2 Replies View Related

Queries :: Convert 19 Digit Number To Hex String

Mar 4, 2015

I 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"

View 1 Replies View Related

Convert 5 Digit Serial Date Within Access

Jul 7, 2015

I 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.

View 3 Replies View Related

Access 2007 - Cannot Display 17 Digit Number

Aug 8, 2011

I 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....

View 9 Replies View Related

How To Limit Account Numbers To 4 Digit Numeric Value

Mar 28, 2015

On 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







Copyrights 2005-15 www.BigResource.com, All rights reserved