Excel MS Query criteria

Ant555

I have done this before but can't for the life of me remember how I did it.

In Excel how can I ask the user to enter/specify the 'start date'and !nd date' to run a data query on.
I can get the data using set criteria if I enter it manually in the MS Query boxes (Excel 2013 right click in data then #table #edit query) which is fine for this run but ideally I want the user to enter the specific dates each time.

Many thanks

bengalknights

Are you willing to VBA it? If so use input box function and date and time picker control depending on what your trying to do

bingo_bango

Input box would be my preferred option as well. Ask the user to input the dates, populate spare cells (I used A6 & A7 in the example below) with them, and use these for the query.

Or just have the query in VBA as well. Input box code is simple to use:

Sub Get_Date()

Dim strDate As String
Dim endDate As String
  strDate = InputBox("Insert start date in format dd/mm/yyyy", "User date", Format(Now(), "dd/mm/yyyy"))
  Range("A6") = strDate
  endDate = InputBox("Insert end date in format dd/mm/yyyy", "User date", Format(Now(), "dd/mm/yyyy"))
    Range("A7") = endDate

' Your query goes here

End Sub

Ant555

Thanks for the replies - I found an old spreadsheet which revealed how I did it last time.

I simply added

>[START DATE] And <[END DATE]

as the criteria for the date range in MS Query - when you refresh, Excel asks the user to enter [START DATE] AND [END DATE] and returns relevant data as long as you put the right dates in there. There is no error checking that the start date is earlier than the end date etc but for this purpose that is fine.

Cheers.

paddyrg

Great . Just remember dates are one of the hard things, even for professionals, in computing. <[End Date] means 'before the first microsecond of that date', so isn't inclusive. Date formats also cause issues thanks to Americans being out of step with the sensible world and 10/5/2016 meaning either 5th Oct or 10th May depending on some settings you may not have explicitly overridden. Instead, try to always use YYYY-MM-DD which should be unambiguous