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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

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