queries >> remove first three 000 in the field?

by Q2Ft » Wed, 04 Feb 2009 05:09:01 GMT


I have a query with field that have 000 in the beginning of a 9 digits number.
What function in query to use to return the value less the first three 000?

queries >> remove first three 000 in the field?

by Michel Walsh » Wed, 04 Feb 2009 05:42:05 GMT

If it is always three zeros, then:

SELECT MID( fieldname, 4 )
FROM tableName

Vanderghast, Access MVP

queries >> RE: remove first three 000 in the field?

by QnJldHQgU3RvbmU » Wed, 04 Feb 2009 05:45:00 GMT

Change the field from a text field to an integer and those preceeding 0's
will disappear. If you want to keep it a string then right([fieldname], 6)
should give you the right 6 digits of the field without the 3 0's.

queries >> remove first three 000 in the field?

by QU1JVEE » Fri, 06 Feb 2009 02:25:47 GMT

I am trying to extract the birth year from a field that has month, date and
year of birth as a text. How can I run a query where only the year is
extracted. Or where the year of birth is used to generate age.

queries >> remove first three 000 in the field?

by Michel Walsh » Fri, 06 Feb 2009 03:01:51 GMT

YEAR( CDATE( " January 2, 1995" ))

returns 1995, so, maybe you can try:

YEAR(CDATE( yourFieldOrExpression ))

Vanderghast, Access MVP

queries >> remove first three 000 in the field?

by John W. Vinson » Fri, 06 Feb 2009 05:59:06 GMT

On Thu, 5 Feb 2009 10:25:47 -0800, AMITA < XXXX@XXXXX.COM >

What's the actual format of your field? Is it a text date such as "January 14,
1962" or a number string such as "19620114" or what?


John W. Vinson [MVP]

Similar Threads

1. how to remove Part Numbers with zeros in front (column with >200,000 records)

Clearly your [PartNumber] Fields in both Tables are Text Fields rather than
Numeric Fields and if they are Text, then "27791001" (which has 8
characters) is *different* from "00000027791001" (which has 14 characters)!

"William Poh Ben" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
> Hi Experts,
> I encounter this problem in MS Access which I have not been able to
> solve for 2 years in office work. I hope some experts out there can
> lend me a hand and thanks so much.
> An example below.
> I have one of the columns in Access2000 with about 220,000 records of
> Part Numbers. The problem is when I used query to lookup the Part
> Numbers, my ref table with part number like 27791001 failed to find the
> match in the Lookup table  because the other part Number has 6 zeros in
> front like 00000027791001.
> How do I resolve this by removing the 6 zeros in all the affected part
> numbers in the Lookup table ?
> I really hope someone can help me.
> Part Numbers in ref table
> ==================
> 12220-89210
> 12220-89211E
> 27791001
> 12221-00110
> 25667233
> 55781123
> Part Numbers in Lookup table
> =====================
> 12220-89210
> 12220-89211E
> 00000027791001
> 12221-00110
> 00000025667233
> 00000055781123

2. How to remove the first three characthers from a string

3. format a field in "mm:ss.000"


I have data in an Excel sheet which I want to get into Access. One field is 
formatted with a custom format of mm:ss.000

to show minutes, seconds and thousands of a second e.g


like a stop watch might.

How can I format the corresponding field in my Access table so that the data 
will be treated the same way.

Currently I can store it as text, but I will want to do calculations like 
averages on it.

I tried a custom format of


but i think with no luck I think it tried to just add three zeroes at the end


4. How do I preset a number field from 000-999 - Access Tables DB Design

5. How to use Append Query on 00-00-000 formatted field

To all:

Hi!  I am trying to use an append queries on a table where the key
field is numeric, formatted as 00-00-000.  The query collects the
information corectly, and there are no records duplicated, but I get a
key violation each time I try to run the query.  Any ideas?

More details: The query collects, concatenates, and turns the key field
to be appended into a value.  I have tried formatting the query's field
as 00-00-000, as well as with no formatting.  



6. Three different fields in one record referencing to three different records in another table - Access Forms Programming

7. ssl.....6.00 into $ 10.,000 realy works