DatePicker and Parameter Query

[dgtampa]

Hello,

I have been working on this for a few hours and have found much information on various forums, but I am having trouble incorporating what I have learned into the DB that I have.

I have a query which members of this forum have (very graciously) helped me develop (please see this thread for details on my DB and on the parameter query in question).

I would like to add one thing to the "design" of the query; I would like to have a DatePicker pop up to enter the parameter instead of the default parameter-entry dialogue box. Everything I have read on the web says I cannot do this directly through the query. Instead, it seems I must first create an unbound form (frmStart_Schedule) using a TextBox (cboDatePick) with format set for Date/Time to provide the parameter for the query.




What I am having trouble with is having the SQL code "point" toward the value entered in cboDatePick in order to run the query (which contains the user-defined function DoReview, which is in the other thread).



I don't know if I am making myself clear or not; I appreciate any help and please let me know what additional information I need to provide in order to get that help!



Thanks in advance!

[alansidman]

this might help to get you started.

http://www.datapigtechnologies.com/f...earchform.html

[dgtampa]

this might help to get you started.

http://www.datapigtechnologies.com/f...earchform.html

Thank you Alan!

I can see that at least I am already on the right track with the design of my small form. I have a text box in there; the format is set so that a DatePicker is triggered. I also have a query already created which I would like the form to run once the user chooses a date. I would like the date value entered through the DatePicker on the form to be used in the query.

Here is my problem: the query uses a user-defined VBA function called "DoReview" (which I didn't create) and the query itself doesn't make sense to me in the context of instructional videos and/or web-pages like the one you referred me to. Whenever I try to insert the full name of the text box from the form (Forms![frmStart_Schedule]![cboDatePick]) into the query as an argument in SQL view, or as a criteria in the query design view, I either get an error, or I continue to get the regular "Enter Parameter" dialogue box, even with frmStart_Schedule open in another tab.

Here is the VBA function code:

Function DoReview(ThursDate As Date, Admit_Date As Date) As Boolean
Dim SunBef As Date, SatAf As Date
Dim AdmitDay As Integer, Sun_Day As Integer, Sat_Day As Integer
SunBef = DateSerial(Year(ThursDate), Month(ThursDate), Day(ThursDate) - 4)  'Date of the preceding Sunday
SatAf = DateSerial(Year(ThursDate), Month(ThursDate), Day(ThursDate) + 2)   'Date of the subsequent Saturday
Sun_Day = Day(SunBef)   'Day of the month
Sat_Day = Day(SatAf)
AdmitDay = Day(Admit_Date)
DoReview = False
If Sun_Day < Sat_Day Then   'week contained in a single month
    If AdmitDay >= Sun_Day And AdmitDay <= Sat_Day Then
        DoReview = True
    End If
  Else                      'week ending on next month
    If AdmitDay >= Sun_Day Or AdmitDay <= Sat_Day Then
        DoReview = True
    End If
End If
End Function

And here is the Query:

PARAMETERS [Enter Review Date] DateTime;
SELECT tblLevel_Review.Beneficiary_Name, tblLevel_Review.Current_Level, tblLevel_Review.Review_Date
FROM tblLevel_Review
WHERE (((DoReview([Enter review date],[Review_Date]))<>False))
ORDER BY tblLevel_Review.Current_Level;

I've attached a relationship report Attachment 7772

Attachment 7772
I'm new at SQL and VBA language rules, so my learning curve is slow right now. I might be going about it all wrong...

[dgtampa]

I wound up solving it myself...I needed to add the identifier string for the control (Forms![frmStart_Schedule]![cboDatePick]) in the query in two places: in the PARAMETERS portion as well as in the argument portion in my WHERE clause after the user-defined function "DoReview".