Using a date picker to fill and run a query

[HangoutGuy]

Hi all,

I have used this link: access-programmers.co.uk/forums/showthread.php?t=298062 to insert a calendar date picker into my form.

What I would like to do with this is use the date picker to run a query using the chosen date to calculate the weeks left until another date.

I have a list of projects in a table with an end date associated with all of them. I would like to chose a date from the calendar and subtract that from the end date of the project and display the number of weeks that are left. I have 50 or so projects all with different end dates so I would like to be able to find the weeks left on all the projects all at once in a report.

Would I be able to do this with the calendar picker?

 

[theDBguy]

Hi. Is this related to your other question. If so, have you seen the image I posted there? Just curious...

 

[HangoutGuy]

Hi yes I saw that thank you. I'm not creating this database and all the accessibility options for myself. I was give parameters to follow from my boss and that option does not exactly fall in line with their requests.

I just want to be able to click a date and return the weeks left from that date until the end of the project's lifetime for all 50 or so of the projects.

 

[theDBguy]

Hi yes I saw that thank you. I'm not creating this database and all the accessibility options for myself. I was give parameters to follow from my boss and that option does not exactly fall in line with their requests.

I just want to be able to click a date and return the weeks left from that date until the end of the project's lifetime for all 50 or so of the projects.

Hi. No worries. It just would be nice to know whether an offered suggestion was helpful or not, so we could try again.


As for your problem, I don't see anything special with the situation. You can create a query showing the list you want and simply add a criteria to it to refer to the date you entered on the form to limit the list. What exactly are you having difficulty with? Do you already have a query? Are you simply asking for the code to display this query?

 

[HangoutGuy]

Thanks for all the help I appreciate it. I have a query with the projects and the end date of the projects. What I don't know is how to have the criteria box filled with the date picked from the calendar. If I could get help with that I would be grateful.

 

[theDBguy]

Thanks for all the help I appreciate it. I have a query with the projects and the end date of the projects. What I don't know is how to have the criteria box filled with the date picked from the calendar. If I could get help with that I would be grateful.

Okay, we can work with that. Can you please post the SQL statement of the query? Thanks.

 

[HangoutGuy]

SELECT Projects.ProjectName, Projects.End
FROM Projects;


Is this all you need?

 

[theDBguy]

SELECT Projects.ProjectName, Projects.End
FROM Projects;


Is this all you need?

We could start with that. So, let's say you want to filter it to list only those ending today, then you could add a criteria like this:

SELECT ProjectName, [End]
FROM Projects
WHERE [End]=Date()

 

[Uncle Gizmo]

In the interests of promoting my own Date Picker the "Nifty Date Picker" I've done you a quick example. See attached...

Edit:-
For the purposes of the demonstration I have added dates for the 12th and 19th of the month. If you click on either the 12th or the 19th you will get a change showing in the query.

Edit 2:- I have moved the attachment to the download manager website here:- "Nifty Date Picker - User Case - Run Query"

AWF members get this Sample for free (Limited Time Offer):- Use the coupon code:- 14ip5p4

 

[HangoutGuy]

We could start with that. So, let's say you want to filter it to list only those ending today, then you could add a criteria like this:

SELECT ProjectName, [End]
FROM Projects
WHERE [End]=Date()

Thanks for this^

Is there a way to reference a form in the SQL code? Once I pick a date from the calendar it populates an unbound text box. Can I reference that text box and put it into the query?

 

[arnelgp]

maybe through this example db, you will get the idea of how to do it.
I am using Tempvars, and subtract it to TargetDate of the projects.

 

[Mark_]

Using DatePart you can use "ww" to return the Week. In your query with the end date for each project, include the "Week". When you pick the date, use DatePart to find out the week it is for. Then it is simple math.

 

[HangoutGuy]

maybe through this example db, you will get the idea of how to do it.
I am using Tempvars, and subtract it to TargetDate of the projects.

THANK YOU! This is EXACTLY what I needed.

 

[theDBguy]

THANK YOU! This is EXACTLY what I needed.

Hi. Glad to hear you got a solution. I was trying to slowly walk you through the process but I see you already got to the end. Good luck!

 

[arnelgp]

you are welcome!

 

[isladogs]

Is there a way to reference a form in the SQL code? Once I pick a date from the calendar it populates an unbound text box. Can I reference that text box and put it into the query?

Hi
I realise you already have a solution using tempvars but for future reference you can use Forms!YourFormName.YourTextboxName in the query filter criteria.
Or you can create a user defined function to grab the value and use that in your query.
So you have three methods to choose from. Spoilt for choice!

 

[Pat Hartman]

@HangoutGuy,
Is your boss a database design expert? I didn't think so. It is up to you to protect him from himself and provide expert guidance, even if it is actually coming from us. The function suggested by Mark may be the "solution" you were looking for but your boss should be presented with a FORM rather than a query. You have no control over him updating anything in the query either on purpose or accidentally unless you specifically make the query not updateable by including some meaningless aggregation. You can make the form look like a data sheet so he would probably not even know. Press a button on the menu to open the "query" and up pops a form that looks just like he wants it to look.