Field Name with Leading Zero in a Query

  • Thread starter Thread starter JP
  • Start date Start date
J

JP

For some reasons, we have to make up some numerals as name of the fields in
a query. For example:

005
087
091
101

The queries work well in MS Access 2000. But as soon as we have upgraded to
Access 2002, we found that all the leading zeros in the queries are gone.
Thus, the field name 005 becomes 5. Names without a leading zero such as
101 are fine. If we want to keep the old way, are there any solutions other
than downgrading?

Joe
 
I cannot change the datatype becasue they have to be numbers. I don't care
about the actual value because I can use a different mask in my reports.
But I am surprised that there is such an undocumented change in the new
version of Access.

For example, in the query design view, you can put in an expression in the
Field box like this:

Mar-02:IIF([PERIOD]="Feb-04",[Overdue],0)

In the previous version, it will not parse the string to the left of the
COLON so the field name Mar-02 will be retained. With the new version, it
will turn out to be Mar-2 after a SAVE.
 
Back
Top