By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

Showing January 1 of current year in calculated field

P: n/a
I have an unbound field in which I would like to show january 1 of the
current year like this 01-01-2007. I tried this code but it doesn't work:
=dateserial(Year(date()),1,1)
Can someone help me out?
Thanks in advance,
john
Jan 27 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
If you have that in the Control Source of the text box, it should work fine.

Set the Format property of the text box so that Access know to treat it as a
date. Use:
Short Date
or format it how ever you want, e.g.:
mmmm d yyyy

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"john" <jo**@test.comwrote in message
news:bI******************************@casema.nl...
>I have an unbound field in which I would like to show january 1 of the
current year like this 01-01-2007. I tried this code but it doesn't work:
=dateserial(Year(date()),1,1)
Can someone help me out?
Thanks in advance,
john
Jan 27 '07 #2

P: n/a
Thanks.
If you have that in the Control Source of the text box, it should work
fine.
I found the problem:
=dateserial(Year(date()),1,1) doesn't work.
=dateserial(Year(date());1;1) does.

Strangely enough I have another unbound field in another form in this app
with the code:
=DSum("[Bedrag]";"Transactie";"[Rentedatum]>=dateserial(Year(date()),1,1)")
Here the comma's do work. Does this have to to with the quotation marks?

john
"Allen Browne" <Al*********@SeeSig.Invalidschreef in bericht
news:45***********************@per-qv1-newsreader-01.iinet.net.au...
If you have that in the Control Source of the text box, it should work
fine.

Set the Format property of the text box so that Access know to treat it as
a date. Use:
Short Date
or format it how ever you want, e.g.:
mmmm d yyyy

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"john" <jo**@test.comwrote in message
news:bI******************************@casema.nl...
>>I have an unbound field in which I would like to show january 1 of the
current year like this 01-01-2007. I tried this code but it doesn't work:
=dateserial(Year(date()),1,1)
Can someone help me out?
Thanks in advance,
john

Jan 27 '07 #3

P: n/a
"john" <jo**@test.comwrote in message
<se*********************@casema.nl>:
Thanks.
>If you have that in the Control Source of the text box, it should
work fine.

I found the problem:
=dateserial(Year(date()),1,1) doesn't work.
=dateserial(Year(date());1;1) does.

Strangely enough I have another unbound field in another form in this
app with the code:
=DSum("[Bedrag]";"Transactie";"[Rentedatum]>=dateserial(Year(date()),1,1)")
Here the comma's do work. Does this have to to with the quotation
marks?

john
"Allen Browne" <Al*********@SeeSig.Invalidschreef in bericht
news:45***********************@per-qv1-newsreader-01.iinet.net.au...
>If you have that in the Control Source of the text box, it should
work fine.

Set the Format property of the text box so that Access know to treat
it as a date. Use:
Short Date
or format it how ever you want, e.g.:
mmmm d yyyy

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"john" <jo**@test.comwrote in message
news:bI******************************@casema.nl.. .
>>>I have an unbound field in which I would like to show january 1 of
cthe urrent year like this 01-01-2007. I tried this code but it
doesn't work: =dateserial(Year(date()),1,1)
Can someone help me out?
Thanks in advance,
john
I think it has partly to do with the quote marks. Here are some
thoughts on what I think happens.

First, the reason for comma not working, is that in some locale, comma
is used as decimalseparator, and therefore cannot be used as arguement
separator in expressions, controlsources etc in the interface (also
Excel formulas). We have to use semicolon.

In the second sample, you're passing three different arguements to the
domain aggregate function. Those three needs to be separated by
semicolon, as they are "interpreted" in the interface, where your
locale settings take precedence.

But the last part, the criterion, is a *string* passed to the domain
aggregate function. I think what follows next, is that this string is
passed to, and evaluated by the Jet expression service, which I believe
needs US/unambiguous format on what is passed.

Some of this can be observed when you work with queries. When working
in design view, you will have your format, your delimiters and
separators, if you switch to SQL view, you will see the same
expressions in US format.

--
Roy-Vidar
Jan 27 '07 #4

P: n/a
Roy, thanks for the explanation.
john

"RoyVidar" <ro*************@yahoo.noschreef in bericht
news:mn***********************@yahoo.no...
"john" <jo**@test.comwrote in message
<se*********************@casema.nl>:
>Thanks.
>>If you have that in the Control Source of the text box, it should
work fine.

I found the problem:
=dateserial(Year(date()),1,1) doesn't work.
=dateserial(Year(date());1;1) does.

Strangely enough I have another unbound field in another form in this
app with the code:
=DSum("[Bedrag]";"Transactie";"[Rentedatum]>=dateserial(Year(date()),1,1)")
Here the comma's do work. Does this have to to with the quotation
marks?

john
"Allen Browne" <Al*********@SeeSig.Invalidschreef in bericht
news:45***********************@per-qv1-newsreader-01.iinet.net.au...
>>If you have that in the Control Source of the text box, it should
work fine.

Set the Format property of the text box so that Access know to treat
it as a date. Use:
Short Date
or format it how ever you want, e.g.:
mmmm d yyyy

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"john" <jo**@test.comwrote in message
news:bI******************************@casema.nl. ..
I have an unbound field in which I would like to show january 1 of
cthe urrent year like this 01-01-2007. I tried this code but it
doesn't work: =dateserial(Year(date()),1,1)
Can someone help me out?
Thanks in advance,
john

I think it has partly to do with the quote marks. Here are some
thoughts on what I think happens.

First, the reason for comma not working, is that in some locale, comma
is used as decimalseparator, and therefore cannot be used as arguement
separator in expressions, controlsources etc in the interface (also
Excel formulas). We have to use semicolon.

In the second sample, you're passing three different arguements to the
domain aggregate function. Those three needs to be separated by
semicolon, as they are "interpreted" in the interface, where your
locale settings take precedence.

But the last part, the criterion, is a *string* passed to the domain
aggregate function. I think what follows next, is that this string is
passed to, and evaluated by the Jet expression service, which I believe
needs US/unambiguous format on what is passed.

Some of this can be observed when you work with queries. When working
in design view, you will have your format, your delimiters and
separators, if you switch to SQL view, you will see the same
expressions in US format.

--
Roy-Vidar


Jan 27 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.