Query a Date Field to Return all records within a month period

  • Thread starter bluebean via AccessMonster.com
  • Start date
B

bluebean via AccessMonster.com

how to Query a Date Field to Return all records within a month period ?

e.g. Jan (return all records within Jan)
Feb (return all records with Feb)
So on...
 
A

Allen Browne

If you want just the records for one month of one year, and you want to
specify the month and year when the query runs:
1. In the Criteria row under your Date/Time field, enter this (all on one
row):
= DateSerial([What year (4-digit)], [What Month (1-12)], 1)
And < DateSerial([What year (4-digit)], [What Month (1-12)] + 1, 1)

2. Choose Parameters on the Query menu.
Access opens the Parameters dialog.
Enter 2 rows:
[What year (4-digit)] Integer
[What Month (1-12)] Integer
The query will now ask for a year and month number when you run the query.

Or perhaps you wanted the query to give you totals for each month, instead
of just the records for one month? We will assume your date/time field is
named MyDate for this example:

1. Depress the Totals icon on the toolbar in query design.
(Upper sigma icon.)
Access adds a Total row to the grid.

2. Type this into a fresh column in the Field row:
Year([MyDate])
Accept Group By in the Total row.
Choose Ascending in the Sort row.

3. In the next column, in the Field row, type:
Month([MyDate])
Accept Group By in the Total row.
Choose Ascending in the Sort row.

4. Drag the field you want to total into the grid.
In the Total row under this field, choose:
Sum

This query gives you a total by month.
 
B

bluebean via AccessMonster.com

thanks Allen !

But why the query asks the same questions twice, then only will display the
results?
For example it will prompt to key in the following:

1) what year ( 4 digits)
2) what month (1-12)

Again it will prompt out the same questions

1) what year ( 4 digits)
2) what month (1-12)

I just copy and paste your following query
= DateSerial([What year (4-digit)], [What Month (1-12)], 1) And < DateSerial([What year (4-digit)], [What Month (1-12)] + 1, 1)

many thanks

Allen said:
If you want just the records for one month of one year, and you want to
specify the month and year when the query runs:
1. In the Criteria row under your Date/Time field, enter this (all on one
row):
= DateSerial([What year (4-digit)], [What Month (1-12)], 1)
And < DateSerial([What year (4-digit)], [What Month (1-12)] + 1, 1)

2. Choose Parameters on the Query menu.
Access opens the Parameters dialog.
Enter 2 rows:
[What year (4-digit)] Integer
[What Month (1-12)] Integer
The query will now ask for a year and month number when you run the query.

Or perhaps you wanted the query to give you totals for each month, instead
of just the records for one month? We will assume your date/time field is
named MyDate for this example:

1. Depress the Totals icon on the toolbar in query design.
(Upper sigma icon.)
Access adds a Total row to the grid.

2. Type this into a fresh column in the Field row:
Year([MyDate])
Accept Group By in the Total row.
Choose Ascending in the Sort row.

3. In the next column, in the Field row, type:
Month([MyDate])
Accept Group By in the Total row.
Choose Ascending in the Sort row.

4. Drag the field you want to total into the grid.
In the Total row under this field, choose:
Sum

This query gives you a total by month.
how to Query a Date Field to Return all records within a month period ?

e.g. Jan (return all records within Jan)
Feb (return all records with Feb)
So on...
 
A

Allen Browne

Hmm: It should only ask for them once each.

Are you sure you used exactly the same name in both instances, and also in
the Parameters dialog?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

bluebean via AccessMonster.com said:
thanks Allen !

But why the query asks the same questions twice, then only will display
the
results?
For example it will prompt to key in the following:

1) what year ( 4 digits)
2) what month (1-12)

Again it will prompt out the same questions

1) what year ( 4 digits)
2) what month (1-12)

I just copy and paste your following query
= DateSerial([What year (4-digit)], [What Month (1-12)], 1) And <
DateSerial([What year (4-digit)], [What Month (1-12)] + 1, 1)

many thanks

Allen said:
If you want just the records for one month of one year, and you want to
specify the month and year when the query runs:
1. In the Criteria row under your Date/Time field, enter this (all on one
row):
= DateSerial([What year (4-digit)], [What Month (1-12)], 1)
And < DateSerial([What year (4-digit)], [What Month (1-12)] + 1, 1)

2. Choose Parameters on the Query menu.
Access opens the Parameters dialog.
Enter 2 rows:
[What year (4-digit)] Integer
[What Month (1-12)] Integer
The query will now ask for a year and month number when you run the query.

Or perhaps you wanted the query to give you totals for each month, instead
of just the records for one month? We will assume your date/time field is
named MyDate for this example:

1. Depress the Totals icon on the toolbar in query design.
(Upper sigma icon.)
Access adds a Total row to the grid.

2. Type this into a fresh column in the Field row:
Year([MyDate])
Accept Group By in the Total row.
Choose Ascending in the Sort row.

3. In the next column, in the Field row, type:
Month([MyDate])
Accept Group By in the Total row.
Choose Ascending in the Sort row.

4. Drag the field you want to total into the grid.
In the Total row under this field, choose:
Sum

This query gives you a total by month.
how to Query a Date Field to Return all records within a month period ?

e.g. Jan (return all records within Jan)
Feb (return all records with Feb)
So on...
 
B

bluebean via AccessMonster.com

Hi Allen,

The following is the SQL statements:
PARAMETERS [What Year (4-digit)] Short, [What Month(1-12)] Short;
SELECT Transaction.TransactionID, [Customer Records].[Customer Name],
Transaction.[Amount Purchased], Transaction.TransactionDate
FROM [Customer Records] INNER JOIN [Transaction] ON [Customer Records].
CustomerID=Transaction.CustomerID
WHERE (((Transaction.TransactionDate)>=DateSerial([What year (4-digit)],[What
Month (1-12)],1) And (Transaction.TransactionDate)<DateSerial([What year (4-
digit)],[What Month (1-12)]+1,1)));

is that okie ?

Allen said:
Hmm: It should only ask for them once each.

Are you sure you used exactly the same name in both instances, and also in
the Parameters dialog?
thanks Allen !
[quoted text clipped - 60 lines]
 
A

Allen Browne

The parameters are not the same as used in the WHERE clause.

The declared parameter has no space following the word Month.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

bluebean via AccessMonster.com said:
Hi Allen,

The following is the SQL statements:
PARAMETERS [What Year (4-digit)] Short, [What Month(1-12)] Short;
SELECT Transaction.TransactionID, [Customer Records].[Customer Name],
Transaction.[Amount Purchased], Transaction.TransactionDate
FROM [Customer Records] INNER JOIN [Transaction] ON [Customer Records].
CustomerID=Transaction.CustomerID
WHERE (((Transaction.TransactionDate)>=DateSerial([What year
(4-digit)],[What
Month (1-12)],1) And (Transaction.TransactionDate)<DateSerial([What year
(4-
digit)],[What Month (1-12)]+1,1)));

is that okie ?

Allen said:
Hmm: It should only ask for them once each.

Are you sure you used exactly the same name in both instances, and also in
the Parameters dialog?
thanks Allen !
[quoted text clipped - 60 lines]
Feb (return all records with Feb)
So on...
 

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

Similar Threads


Top