parameter value

csgraves

Board Regular
Joined
Dec 9, 2002
Messages
106
Hi! I need to build a query using a parameter value range. On my query, I need a listing of all agents who have a date range from 01/01 - 12/01. I know how to make a query using a range of 01/01, but I need it to pull all from 01/01,02/01,03/01,04/01 - 12/01 etc.

Anyone know how?

Thanks!
Scott
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
Not totally clear on what you want here.
Are you prompted to enter criteria when you run your query?.. that's a parameter query.

If all you want is Day 1 of 12 months...

In the criteria section of your Date field, enter
Day([YourDateField]) = 1

Test this out.
 

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
If you keep data for multiple years, you'll need to adjust for that also.
Change Criteria to:

Year(Date()) and Day([YourDateField]) = 1

This should limit your return to Day 1 for every month of the current year.
Without this Year(Date()) your results will return Day 1 of every month, of every year you store data for.
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
With formats like 1/01 and 12/01, you will terminally confuse the Board :wink: -- is that 1 Jan to 1 Dec (US dates), or 1 Jan to 12 Jan (international dates)?

Regardless, you can create a parameter that looks like this:

Between [First Date] And [Last Date] (put this in the Criteria row of your date field)

When you run the query, you will be prompted for the dates, and the query will open. A slightly more complex way to do it (but way more user-friendly), is to create a form with 2 text boxes where you enter the first and last dates, then push a button to launch a query filtered on the contents of those text boxes. If you want to try that, let us know.

Denis
 

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
With formats like 1/01 and 12/01, you will terminally confuse the Board :wink: -- is that 1 Jan to 1 Dec (US dates), or 1 Jan to 12 Jan (international dates)?

Regardless, you can create a parameter that looks like this:

Between [First Date] And [Last Date] (put this in the Criteria row of your date field)

When you run the query, you will be prompted for the dates, and the query will open. Denis

That's OK, IF he wants all dates between First Date and Last Date. His request was "I need it to pull all from 01/01,02/01,03/01,04/01 - 12/01 etc. "... granted my assumption is he's referencing US Dates.

This is a perfect example why those looking for assistance need to understand that they need to be specific in their requests. The more information they provide, the quicker a solution can be provided.
 

Forum statistics

Threads
1,136,618
Messages
5,676,844
Members
419,655
Latest member
pd2021vb

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