Leading Zero in a date field

  • Thread starter Thread starter Starbuck
  • Start date Start date
S

Starbuck

How do you get Access 2003 to not drop the leading zero
in a date field?

e.g.
Access 2003 gives me 2004-9
Format needed = 2004-09

*$
 
Format(Now(), "yyyy-dd")
Click to expand...

Thank you Larry.
These are query field headers dropping the leading zero.
Where do I format query fields headers?
(Properties, options, etc...(?))

Thanks again.
*$
 
What are "query field headers"?

--
-Larry-
--

Starbuck said:
Thank you Larry.
These are query field headers dropping the leading zero.
Where do I format query fields headers?
(Properties, options, etc...(?))

Thanks again.
*$
Click to expand...
 
If I am understanding your question you can right click on the column in the
query design and change the properties that way.
 
What are "query field headers"?
Click to expand...
How about query field names? We use the accounting period as the
field name in certain tables. For example the field name of "2004-09"
refers to that accounting period....September of 2004.


When running a query on this table, Access 2003 changes the field
name!
We end up with "2004-9". This is not data within the field, this is
the field name itself! Even when we are careful to specify 2004-09: as
the desired field name in the query result, Access gives us "2004-9"


All subsequent queries on this resulting table or query then bomb
because the field name "2004-09" cannot be found!


This was never a problem in previous versions of Access. It is new
with Access 2003.


Any ideas?
Thanks!
*$
 
You will need square brackets around that field name. Without them, Access
will interpret 2004-09 as the expression 2004 - 9 (2004 minus 9) and return
the result, 1995.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
You will need square brackets around that field name. Without them, Access
will interpret 2004-09 as the expression 2004 - 9 (2004 minus 9) and return
the result, 1995.
Click to expand...

That sounded like the perfect solution, but
I added the square brackets to the field name ie [2004-09]
and it didn't work.

The message reads:
'[2002-09]' is not a valid name. Make sure that it does not
invalid characters or punctuation and that it is not too long.
(Error 3125)

Any other suggestions?

BTW I really appreciate all of the assistance you guys have
been offering. Thanks.

*$
 
I meant in the query, not in the table definition. For example, I create a
table, tblTest, with a field named 2004-09. Just for demonstration purposes,
I make this a text field, and I enter values 'one', 'two', and 'three'. I
run the following query ...

SELECT 2004-09 FROM tblTest

.... and the result is three rows, all containing the value 1995, under the
column heading 'Expr1000'. Returning to SQL view, I see that Access has
changed the SQL to ...

SELECT 2004-9 AS Expr1 FROM tblTest;

If, on the other hand, I change the query to ...

SELECT [2004-09] FROM tblTest

.... then I get three rows containing the values 'one', 'two' and 'three'
under the heading 2004-09.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Starbuck said:
You will need square brackets around that field name. Without them, Access
will interpret 2004-09 as the expression 2004 - 9 (2004 minus 9) and
return
the result, 1995.
Click to expand...

That sounded like the perfect solution, but
I added the square brackets to the field name ie [2004-09]
and it didn't work.

The message reads:
'[2002-09]' is not a valid name. Make sure that it does not
invalid characters or punctuation and that it is not too long.
(Error 3125)

Any other suggestions?

BTW I really appreciate all of the assistance you guys have
been offering. Thanks.

*$
Click to expand...
 
Back
Top