Date format in design

A

acss

I am creating a DB to track invoices and in the future i would like to run
queiries or reports that would separate invoices from one month to the other.
For instance, run a report that would group invoices dated within the month
of april and group invoices within the month of may. Should i be using date
format 00/00/0000 for this function?
 
S

Steve

You can use the Month function. For ex, Month([InvoiceDate]).

Steve
(e-mail address removed)
 
A

acss

Let me be more specific. In order to run dates from one month against another
month, what format should the data entry be used ?

00/00/0000---can this format be keyed into the date field so groupings can
be done per month?

Thanks

Steve said:
You can use the Month function. For ex, Month([InvoiceDate]).

Steve
(e-mail address removed)



acss said:
I am creating a DB to track invoices and in the future i would like to run
queiries or reports that would separate invoices from one month to the
other.
For instance, run a report that would group invoices dated within the
month
of april and group invoices within the month of may. Should i be using
date
format 00/00/0000 for this function?
 
J

John W. Vinson

I am creating a DB to track invoices and in the future i would like to run
queiries or reports that would separate invoices from one month to the other.
For instance, run a report that would group invoices dated within the month
of april and group invoices within the month of may. Should i be using date
format 00/00/0000 for this function?

The date format is irrelevant in this issue. A Date/Time field is not stored
in any particular format; instead it's stored as a double float count of days
and fractions of a day (times) since midnight, December 30, 1899:

?now; cdbl(now)
5/31/2009 6:18:51 PM 39964.7630902778

You can use the builtin Access functions such as Month([InvoiceDate]) to
extract just the month, or use a Totals query grouping by an expression like

InvMonth: Format([InvoiceDate], "yyyy-mm")

to get a text string such as 2008-12, 2009-01, 2009-02 which will sort
chronologically and let you group by month.
 
S

Steve

Yes, enter InvoiceDate as 5/31/09 or 5/31/2009.

For the recordsource of your reports use a query that has a calculated field
using the Month function:
InvoiceMonth:Month([InvoiceDate])

Then group on InvoiceMonth.

Steve
(e-mail address removed)



acss said:
Let me be more specific. In order to run dates from one month against
another
month, what format should the data entry be used ?

00/00/0000---can this format be keyed into the date field so groupings can
be done per month?

Thanks

Steve said:
You can use the Month function. For ex, Month([InvoiceDate]).

Steve
(e-mail address removed)



acss said:
I am creating a DB to track invoices and in the future i would like to
run
queiries or reports that would separate invoices from one month to the
other.
For instance, run a report that would group invoices dated within the
month
of april and group invoices within the month of may. Should i be using
date
format 00/00/0000 for this function?
 
A

acss

Thanks John. What i am trying to accomplish is the separate the invoices that
were from last month from this current month so i know which month charges
were incurred. Using the string in query "yyyy-mm" is it possable for results
to show as day mnth year?

John W. Vinson said:
I am creating a DB to track invoices and in the future i would like to run
queiries or reports that would separate invoices from one month to the other.
For instance, run a report that would group invoices dated within the month
of april and group invoices within the month of may. Should i be using date
format 00/00/0000 for this function?

The date format is irrelevant in this issue. A Date/Time field is not stored
in any particular format; instead it's stored as a double float count of days
and fractions of a day (times) since midnight, December 30, 1899:

?now; cdbl(now)
5/31/2009 6:18:51 PM 39964.7630902778

You can use the builtin Access functions such as Month([InvoiceDate]) to
extract just the month, or use a Totals query grouping by an expression like

InvMonth: Format([InvoiceDate], "yyyy-mm")

to get a text string such as 2008-12, 2009-01, 2009-02 which will sort
chronologically and let you group by month.
 
J

John W. Vinson

Thanks John. What i am trying to accomplish is the separate the invoices that
were from last month from this current month so i know which month charges
were incurred. Using the string in query "yyyy-mm" is it possable for results
to show as day mnth year?

Again:

The format of the date field in the table
IS ABSOLUTELY IRRELEVANT.
It does not come into the problem.

You can use a criterion
= DateSerial([Enter year:], [Enter month number:], 1) AND < DateSerial([Enter year:], [Enter month number:] + 1, 1)

There are some other possible options, such as using a Calendar control on a
form. Depends on how your user interface is set up.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top