Leading Zero in a date field

[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

*$

 

[Larry Daugherty]

Format(Now(), "yyyy-dd")

 

[Starbuck]

Format(Now(), "yyyy-dd")

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

Thanks again.
*$

 

[Larry Daugherty]

What are "query field headers"?

--
-Larry-
--

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

Thanks again.
*$

 

[Guest]

If I am understanding your question you can right click on the column in the
query design and change the properties that way.

 

[Starbuck]

What are "query field headers"?

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!
*$

 

[Brendan Reynolds]

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.

 

[Starbuck]

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.

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.

*$

 

[Brendan Reynolds]

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.

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.

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.

*$