Calendar object at parameter value box (1 Viewer)

shafara7

Registered User.
Local time
Today, 15:45
Joined
May 8, 2017
Messages
118
Hi, does anybody know how do I insert a calendar date-picker at a parameter value input box? See picture.
I have a query that will filter records between the given Start date and End date.
Right now I have to manually type in the dates whenever I need to use the query but now I would like to have the date-picker.
It would be easier and less typo-mistake.

I have the following codes in my query:
Code:
SELECT tbl.......

FROM tbl......

WHERE (((tblBill.datDiscussion)>[Please enter Start Date] And (tblBill.datDiscussion)<[Please enter End Date]));
 

Attachments

  • qryKPIBasis.PNG
    qryKPIBasis.PNG
    13 KB · Views: 117

Minty

AWF VIP
Local time
Today, 14:45
Joined
Jul 26, 2013
Messages
10,354
I don't think you can. Make a small form and pass the parameters in from the form , then the date picker will be available on the form controls.
 

Orthodox Dave

Home Developer
Local time
Today, 14:45
Joined
Apr 13, 2017
Messages
218
Minty is right, but you can at least add a bit of help, changing the parameter to:
[Please enter Start Date (mm/dd/yyyy)]

It's important the date ends up in the US format (mm/dd/yyyy) because you otherwise get ambiguous errors in SQL. If you allow entry a different way (e.g. dd/mm/yy) you need to programmatically convert this to mm/dd/yyyy for the SQL.
 

shafara7

Registered User.
Local time
Today, 15:45
Joined
May 8, 2017
Messages
118
Thank you for your reply.
Okay I did a small form with a 'Set' button but how do I connect it with the query?
Can I change the codes above to the codes below?
Code:
SELECT tbl.......

FROM tbl......

WHERE (((tblBill.datDiscussion)>(tblDatePicker.datStart) And (tblBill.datDiscussion)<(tblDatePicker.datEnd));

Or should the Form for Dates need to be connected to a table?
 

Minty

AWF VIP
Local time
Today, 14:45
Joined
Jul 26, 2013
Messages
10,354
Your form should be unbound (no need to link it to any table or query) - If you called the form frmReportDates and the controls are called datStart and datEnd then in your query you would use;
Code:
WHERE tblBill.datDiscussion >=[Forms]![frmReportDates ]![datStart] And  tblBill.datDiscussion < [Forms]![frmReportDates ]![datEnd]
 

shafara7

Registered User.
Local time
Today, 15:45
Joined
May 8, 2017
Messages
118
Thank you ridders for your reply. I have looked at the example but it is quite complicated for me to do. But I will try it when nothing else works.

Minty, I have tried your suggestion but it still ask for another parameter. See photo.
As you can see in the photo, the textbox for dates is named datStart and datEnd. But do I need to set the control source for the date box?
Also, date-picker does not show up when I open it in formular-view.
 

Attachments

  • qryKPIBasis-2.PNG
    qryKPIBasis-2.PNG
    19 KB · Views: 95
  • qryKPIBasis-3.PNG
    qryKPIBasis-3.PNG
    5.3 KB · Views: 109

Minty

AWF VIP
Local time
Today, 14:45
Joined
Jul 26, 2013
Messages
10,354
On your form you have applied a mask to the input box, simply set it to Short date, then the date picker will appear.

In your query your parameters don't look correct. Use the builder icon to find the correct reference to your forms controls. in my example the [Forms]! bit is not changeable - that is how you refer a form object. You appear to have changed it to Fomulare - and I'm sure even in German that should still be [Forms]!
 

shafara7

Registered User.
Local time
Today, 15:45
Joined
May 8, 2017
Messages
118
Minty you're the bomb!
For the parameter criteria, I actually used [Forms]! but I don't know why it automatically came out as [Formulare]!.
I used the Expression Builder instead of typing it manually and it works perfectly now.
Funny thing is, what I typed in and what the Expression Builder generated is exactly the same, but I don't know why it didn't work out for the mannually typed expression.
Nevertheless, it works okay now. Thank you very much.
 

Minty

AWF VIP
Local time
Today, 14:45
Joined
Jul 26, 2013
Messages
10,354
Might have picked up another object name and "autocompleted" it for you! Glad you sorted it.
 

isladogs

MVP / VIP
Local time
Today, 14:45
Joined
Jan 14, 2017
Messages
18,186
Thank you ridders for your reply. I have looked at the example but it is quite complicated for me to do.

Actually it is extremely simple to use - just one line of code in each place you want to reference the calendar control

Code:
InputDateField txtDate, "Select a date to use this on your form"

Anyway, pleased to see that, with Minty's expert assistance, you've got a solution that works for you
 

shafara7

Registered User.
Local time
Today, 15:45
Joined
May 8, 2017
Messages
118
I think my brain just froze when I see the codes in the example.
But I will keep it for my future reference, in case I will encounter the same problem (I bet I will face the same problem).
Thank you ridders!
 

Users who are viewing this thread

Top Bottom