How to show a certain dateformat in access ?

  • Thread starter Thread starter Fred
  • Start date Start date
F

Fred

Hi,
Well,
I'd like to have a function like :
which would have a result of :

February 1st, 2004 - February 29th, 2004

in a textfield !
How can I do this ?
I've tried some
MonthName(Date()) with some other functions, but its not recognized in my
French version of access...
and how to tell access to write the the first day and the last day ?

So my question is :
how to make a function, that will show me the above result,
for every month.. If current month is February, it's the result above,
it month is March, it should be :
March 1st, 2004 . March 31st, 2004
And so..

I mean, if a tell it's March, it will show me the first and the last day of
this month including the date..
Do I have to make a VBA script ? and if yes, how to implemante this in my
textfield ?

Thank you for your time !
 
Fred,

The following expression will give you the first and last day of the month:

Format(DateSerial(Year(Date()),Month(Date()),1),"mmmm d, yyyy") & " - " &
Format(DateSerial(Year(Now()),Month(Now())+1,0),"mmmm d, yyyy")

If you want this to display on a report, just precede the expression with an
equal sign in the Control Source property of a text box.

hth,
 
Access tell me :
BAD syntax, check your expression..
I 'm using a french version. what do I have to change ?

=Format(DateSerial(Year(Date()),Month(Date()),1),"mmmm d, yyyy") & " - " &
Format(DateSerial(Year(Now()),Month(Now())+1,0),"mmmm d, yyyy")

But does this function show me the name of the month, or the unmber? because
I d like to know the name.
 
=SérieDate(Année(Date());Mois(Date());1) & " - " &
SérieDate(Année(Maintenant());Mois(Maintenant())+1;0)

This work,
but when I try to use FORMAT, it does'nt...
 
See whether mmmm j, aaaa works as the format.

I'm guessing j for jour (rather than d for day), and aaaa for annee (rather
than yyyy for year). m should be valid in French (mois and month)

In English versions of Access, using mmmm to format the Month returns the
full name of the month (January, February, March), while using mmm will
return a 3 character text representation (Jan, Feb, Mar)
 
Fred,

The rather long expression I sent you does a couple of things; however, to
answer your question "But does this function show me the name of the month,
or the unmber?", the Format() function allows you to display a Date in a
variety of formats. Try the following:

Format(Date(), "mmmm d, yyyy") ' is designed to display the Full Month
Name plus day and year

Format(Date(), "mmm d, yyyy") ' is designed to display an Abbreviated
Month Name plus day and year

The Access Web has a variety of date manipulation functions:

http://www.mvps.org/access/datetime/date0007.htm

I would also recommend that you open the VBA help file for your version of
Access: Open any code module, then select Help. In the Answer Wizard
(second tab) search on: Date functions. One of the dozen or so topics
returned should be: "User defined Date/Time formats (Format function)".
 
Yes !
Thank you..
I m looking around on the web for a site with all the functions of access,
but I cannot find anythinggood !!
Like javadoc for java !
:-(
But the french version is strange..
some functions like :
MonthName dosen't exist, but in my english book it's written I can use it !!
Also, I have to declare the function in the Englaish name, but to use it
with french parameters..
Strange...
But the inside the SerialDate fonction, I cannot add the Fromat() for the
month...
And is I dont know how to write the last day of the month, without this
function, well...
I've juste written the name of the month above in another text field .. :-(

By the way,
How can I open my statement and tell it to show a certain month... I mean I
can set this in my SQL query, but how to interact with the state form
?because the function above shows methe current month.. what if I'd like to
see anonther one ?

thank you !
 
How can I open my statement and tell it to show a certain month... I mean
I
can set this in my SQL query, but how to interact with the state form
?because the function above shows methe current month.. what if I'd like to
see anonther one ?
Click to expand...

You could experiment with the function and replace Date() with a control or
field name which contains a significant date from your form or report.
 
Did you try using

=Format(DateSerial(Year(Date()),Month(Date()),1),"mmmm j, aaaa") & " - "
& Format(DateSerial(Year(Now()),Month(Now())+1,0),"mmmm j, aaaa")

like I suggested yesterday?
 
Back
Top