Update Or Delete Query?

[Guest]

I want to delete the zeros in a Text field which was input with numbers
(numbers was in a Text Field, not number),
e.g. 000253878, 000253999, 000250007
there are hundreds of records, all prefixed with 000
I have read through the questions in the Office Discussion Group and found a
REPLACE function, so I have tried the REPLACE function in a Query (I am using
2003 Access):

NewFieldName: Replace(Left([TableField],3),"000","")

The first 3 zeros were deleted but if the number has a 3-digits as zeros,
then they were also deleted, e.g. 000250007 became 257 ??

Please help, can I simply just do a update or delete query, if so, how ?

thanks,

 

[MH]

FORMAT function is good for this one:

UPDATE MyTable
SET MyField = FORMAT([MyField],"0")

This will get rid of any leading zeros, no matter how many there may be.

MH

 

[John Spencer]

Try updating using the Val function.

Val("0000000001") will return 1 as a numeric value.

CStr(Val([Some Field])) will return a string and strip off any leading
zeroes. NOTE: This will error if somefield is null.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

thanks! it works!

FORMAT function is good for this one:

UPDATE MyTable
SET MyField = FORMAT([MyField],"0")

This will get rid of any leading zeros, no matter how many there may be.

MH

I want to delete the zeros in a Text field which was input with numbers
(numbers was in a Text Field, not number),
e.g. 000253878, 000253999, 000250007
there are hundreds of records, all prefixed with 000
I have read through the questions in the Office Discussion Group and found
a
REPLACE function, so I have tried the REPLACE function in a Query (I am
using
2003 Access):

NewFieldName: Replace(Left([TableField],3),"000","")

The first 3 zeros were deleted but if the number has a 3-digits as zeros,
then they were also deleted, e.g. 000250007 became 257 ??

Please help, can I simply just do a update or delete query, if so, how ?

thanks,

 

[Guest]

Thank you Again, John. You are always helpful !

Something different, John, I hope you can read this reply, otherwise I will
have to submit the question separately...

I want to create a table with 2 fields: FIELD1 (Text), FIELD2 (Date/Time).
I will create a form for the users to input the data.

Is it possible for FIELD2 on the Form using =Now() but the date remains
unchanged?

The reason I want to use =now() function, so that the user does not have to
input the date by himself, however the date gets changed when the DB opens
the next day. I have tried some functions to hold it, i.e. using the FIELD2
as a TEXT not DATE/TIME and put a function FORMAT, yet it didn't work out.
Ofcourse I must have done something wrong, not sure what was missing.

I appreciate your help very much,
best regards
Karen

Try updating using the Val function.

Val("0000000001") will return 1 as a numeric value.

CStr(Val([Some Field])) will return a string and strip off any leading
zeroes. NOTE: This will error if somefield is null.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I want to delete the zeros in a Text field which was input with numbers
(numbers was in a Text Field, not number),
e.g. 000253878, 000253999, 000250007
there are hundreds of records, all prefixed with 000
I have read through the questions in the Office Discussion Group and found a
REPLACE function, so I have tried the REPLACE function in a Query (I am using
2003 Access):

NewFieldName: Replace(Left([TableField],3),"000","")

The first 3 zeros were deleted but if the number has a 3-digits as zeros,
then they were also deleted, e.g. 000250007 became 257 ??

Please help, can I simply just do a update or delete query, if so, how ?

thanks,