Query: Sort Date Question

bsneils

New Member
Joined
Jan 14, 2006
Messages
40
I have a query which sorts by date, project number, work code, and sums the hours. The work date looks like

DateWorked By Month: Format$([Time Card Details].DateWorked,'mmmm yyyy')

I want it to only show one month and year. Right now it shows all data.

How do I prompt the user and ask for the month and year and only give that year. I have tried variations [Enter Month] And [Enter Year], but get blank query results. I also have a section of the query that is

Year([Time Card Details].DateWorked)*12+DatePart('m',[Time Card Details].DateWorked)-1

and I don't understand what this is doing.

Any Help would be appreciated.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

NumbersMax

Board Regular
Joined
Jun 19, 2007
Messages
200
The Year expression cited...don't know! It looks like its taking the year times 12 plus then month minus 1. For 2/2/2000 that would be:
2000*12+2-1 = 24001. Don't know how that is significant...anybody else??

As far as getting user input for month and year, one idea is to create expression columns to calc the month and year using Month() and Year() functions

Then prompt them in the criteria for this information indicating the proper format...

Exp1: Month([Time Card Details].DateWorked)
Totals row: Where
Criteria row: [Enter month (i.e. 01, 02, 03...)]

Exp2: Year([Time Card Details].DateWorked)
Totals row: Where
Criteria row: [Enter year (i.e. 2001, 2002, 2003...)]

Your Date Worked by Month expression will still calc correctly, but should only pick those dates that are in the month/year chosen by the user.

Max
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,184
Messages
5,857,827
Members
431,900
Latest member
Paradime0346

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top