VBA to prompt user for specific dates

[shabbaranks]

Hi,

Im guessing I would would need a select statement to get specific data but how do I get a prompt to ask the user what date ranges they want?

Thanks

 

[Addyman]

It depends very much on what it is you are doing, or more specifically, how the users are 'triggering' the action that requires a date range. If they are clicking something on a form, you could have two text boxes where they enter a date in each and then your select statement would have something like:

Between '" & Forms!myForm!txtStartDate & "' AND '" & Forms!myForm!txtEndDate & "'"

Or you could use two input boxes and prompt the user to enter the dates and save the values as variables and add the variable names into the select statement.

Can you not base it on a query? Then you could use Between [Enter Start Date] And [Enter End Date] as your criteria in your date field.

Without knowing a little more about what you are trying to achieve it's hard to say what is the best option.

 

[shabbaranks]

Ive tried the Between method but that doesnt work for example if I put between 02/01/2012 and 06/01/2012 then all I get is 02-05, no 06. If I then try the same again with +1 at the end it still doesnt work it says the expression is typed incorrectly or is too complex to be evaluated.

I didnt think querying for a date range could be so difficult.

 

[vbaInet]

I know where this would lead to so I would first of all advise that you use a pop-up form to ask for the dates.

 

[gemma-the-husky]

between #02/01/2012# and #06/01/2012#

this will pick up records between 2/1/12 - time 0.00 and 6/1/12 time 0.00

if your records INCLUDE a time, then say #6/1/12# is LESS than say, #6/1/12 10:40#, so you wont see the values on 6/1/12. Hope that makes sense.

you either need to select just the datepart of your date - or expand the between to include the time value

maybe:

between #02/01/2012# and #06/01/2012 23:59#

 

[nanscombe]

I did an example of something similar here for Shutzy yesterday.

I used the Format() function to convert dates to a common format of YYYYMMDD to avoid any problems with dates which have times, ie FORMAT([OrderDate],"YYYYMMDD") for 05/01/2012 would give 20120105 as would 05/01/2012 13:36:21.

 

[shabbaranks]

Ahh ok I get it, thats why it wasnt working. I thought it was because it was asking for between dates - hence why the 6th wasnt showing as it wasnt between.

So I am going down the form route, I guess I need to apply some code to my button to query the dates. Can I use the above example

Between '" & Forms!myForm!txtStartDate & "' AND '" & Forms!myForm!txtEndDate & "'"

using my form input boxes? Or will I end up in exactly the same problem again?

Thanks

 

[vbaInet]

Almost there:

Between Forms!myForm!txtStartDate AND Forms!myForm!txtEndDate

Then on the click of a button on the form open the query.

 

[shabbaranks]

Firstly thanks for not shooting me down when I asked the question instead of trying it first - I did infact try it was just about to apologise when you replied.

So, Im right back where I started. I have my form which now has the text boxes prompting for 2 dates - but the results still miss off the last day which is I guess due to the time factor??

Thanks again really appreciated

 

[vbaInet]

* Is your date field's datatype really Date/Time?
* What is the format of this field? Did you apply a Long Date format for example?

 

[shabbaranks]

Hi,

Yep its a Date/Time field and its in short date format. Its just the last date which isnt coming back - if there was a problem with the format you would think nothing would be returned?

Heres the sql for that query:

SELECT TimesheetTable.sUser, TimesheetTable.Activity, TimesheetTable.Hours, TimesheetTable.Project, TimesheetTable.[Task Date], TimesheetTable.Description
FROM TimesheetTable
WHERE (((TimesheetTable.sUser)=[Forms]![TimesheetForm]![LoggedInUser]) AND ((TimesheetTable.[Task Date]) Between [Forms]![RePrintTimesheet]![txtWeekCommencing] And [Forms]![RePrintTimesheet]![txtWeekEnding]));

 

[vbaInet]

Did you set the Format property of the textboxes to Short Date as well?

 

[shabbaranks]

I did yes... Its not something with the form it must be with the data as it was happening from the query. As mentioned previously its got to be to do with the date and time within the table as any selection I make it takes off the end date (due to the 00:00 midnight thingy I guess???)

 

[vbaInet]

I thought you had already followed gemma-the-husky's advice? He already mentioned including a time part.

 

[shabbaranks]

Ah right - no, I understood what Gemma meant with regards to midnight being the cutoff for the between dates but I didnt actually get the date part thing working. Could you check my context please as I have looked on the MS guide and what ever I try doesnt work

=datepart(Between [Forms]![RePrintTimesheet]![txtWeekCommencing)] And [Forms]![RePrintTimesheet]![txtWeekEnding])

 

[vbaInet]

DatePart isn't needed, you can still use the criteria as is but with some tweaks.

Do one of the following:

* Drop the time part of the Date/Time - you can use DateValue(), CDate() or Format()
* Format the textbox values to include a fixed time part in the criteria. So concatenate a time part in the query to the parameters.

 

[shabbaranks]

Excuse my ignorance but when you say drop the time part (which is the one which I think makes sense as I dont need the time value) how do I do this? Do you mean at the table? Or at the point of it being added? Or something else?

Thanks

 

[vbaInet]

In that same sentence I mentioned the three functions you can use to exclude the time part. Research those. You can use www.techonthenet.com for that.

 

[shabbaranks]

You little ripper!!! its sorted - converted the date/time function which was set to Format() and is now DateValue() and she works like a trooper!!! Thanks!!! You got a double thumby thanks!!!

 

[vbaInet]

Glad we could help!