Results 1 to 7 of 7
  1. #1
    Sa'El is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2011
    Posts
    40

    Issue with Using Form to Set Date Parameters in a Query

    Hi,

    I have used forms to set date parameters (for date fields) in the past, and I have been stuck for hours with this new database I have been working on. The reason why I have posted this in the Queries thread as I am thinking that the issue is not setting up the form or the syntax to reference the form, but the Query itself.



    Below is an image of the Query.

    Click image for larger version. 

Name:	Query Design.PNG 
Views:	12 
Size:	68.5 KB 
ID:	23587


    The field I want to do a date range is "ReadmitDate" in the first Query. The query generates fine and criteria I set work EXCEPT when referencing the form. I don't get a reference error I get no results. If it matters, there was a time that in the lower left corner I would quckly see "dataset not updateable" or something to that nature.

    I have been evaluating this for hours, including comparing this to queries that have worked when referencing a form.

    For the record these are facts related to the form:


    • "Start Date" and "End Date" text fields that are unbound
    • Formatted as Short Date, same as "ReadmitDate"


    Any help would be greatly appreciated, thanks.

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    since the problem is with referencing your form, we really need to see the sql of your query

  3. #3
    Sa'El is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2011
    Posts
    40
    Thanks for the response.

    Is that the only reason why this could not be working? The one thing I could have done is create a simple, non-join query and see if it works first. Or is it that because this is a complex Query, Access may be having difficulty doing it correctly via the GUI? I am asking these questions for my own education and trying to assist the trouble-shooting process. I don't have access to the database now, but either today or tomorrow I will reply again with the SQL backend.

    Thanks.

  4. #4
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    on the basis of what I can see, it is not a particularly complex query. but perhaps you are trying to join to a table without any records in it. or you have a criteria which does not bring any records through, or could be any one of a number of other things

  5. #5
    Sa'El is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2011
    Posts
    40
    Sorry for the delay. I did a test where I used the same form and did a Query that was based on the same main table (qry_main_4...) without any joins, copied and pasted the same referencing calculation and the query populated results. So I am led to believe there is something about this query that Access is not liking that it is not referencing the form (again, the Query generates results when setting parameters outside of using the form).

    Anyway, below is the backend SQL as requested. I bold-faced the referencing to the form:

    SELECT qry_main_4_CurrReadmission.ReadmitDate, tbl_list_BPCI_Episode.BPCI_EpisodeType, tbl_list_IndexHospital.IndexHospitalName, tbl_list_CareNavigator.CareNavigatorName, qry_main_2_IndexStay.IndexDischargeDate, qry_main_5_PriorInptStay.LACEScore, qry_main_5_PriorInptStay.InptVisit, qry_main_5_PriorInptStay.[24HrCall], qry_main_5_PriorInptStay.[72HrVisit], tbl_list_Location.LocationType, qry_main_4_CurrReadmission.ReadmitDischDate, qry_main_4_CurrReadmission.CurrentReadmission_ID, qry_main_4_CurrReadmission.IndexStay_ID, qry_main_2_IndexStay.Names


    FROM ((((qry_main_2_IndexStay LEFT JOIN tbl_list_BPCI_Episode ON qry_main_2_IndexStay.BPCI_Episode_ID = tbl_list_BPCI_Episode.BPCI_Episode_ID) LEFT JOIN tbl_list_IndexHospital ON qry_main_2_IndexStay.IndexHospital_ID = tbl_list_IndexHospital.IndexHospital_ID)


    RIGHT JOIN (qry_main_5_PriorInptStay RIGHT JOIN qry_main_4_CurrReadmission ON qry_main_5_PriorInptStay.CurrentReadmission_ID = qry_main_4_CurrReadmission.CurrentReadmission_ID) ON qry_main_2_IndexStay.IndexStay_ID = qry_main_4_CurrReadmission.IndexStay_ID) LEFT JOIN tbl_list_CareNavigator ON qry_main_2_IndexStay.CareNavigator_ID = tbl_list_CareNavigator.CareNavigator_ID) LEFT JOIN tbl_list_Location ON qry_main_4_CurrReadmission.LocationPreReadmit = tbl_list_Location.Location_ID


    WHERE (((qry_main_4_CurrReadmission.ReadmitDate) Between [Forms]![_ReportGenerator]![StarteDate] And [Forms]![_ReportGenerator]![EndDate]) AND ((qry_main_4_CurrReadmission.IndexStay_ID) Is Not Null));

    Again, any help would be appreciated. Thanks.

  6. #6
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I think it is because you are using a mixture of left and right joins which may be confusing the query engine.

    I recommend build your query again, starting with qry_main_4_CurrReadmission and your criteria, then add the other tables one by one

  7. #7
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    Quote Originally Posted by Sa'El View Post


    WHERE (((qry_main_4_CurrReadmission.ReadmitDate) Between [Forms]![_ReportGenerator]![StarteDate] And [Forms]![_ReportGenerator]![EndDate]) AND ((qry_main_4_CurrReadmission.IndexStay_ID) Is Not Null));
    Give explicit data types to:
    [Forms]![_ReportGenerator]![StarteDate]
    and
    [Forms]![_ReportGenerator]![EndDate]

    by defining them as query parameters with a data type that aligns to your ReadmitDate (hopefully datetime).

    I've had all kinds of issues with Textboxes not converting to datetime datatypes. The engine tries to figure it out, but fails, a lot.

    Cheers,

    Jeff

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Problems with Date Range Parameters in query
    By ethornto2346 in forum Queries
    Replies: 3
    Last Post: 06-23-2015, 10:06 AM
  2. Replies: 4
    Last Post: 05-01-2013, 01:29 PM
  3. Replies: 8
    Last Post: 12-21-2011, 12:50 PM
  4. date range form values as parameters
    By cfnieder in forum Forms
    Replies: 8
    Last Post: 08-11-2010, 01:48 PM
  5. Replies: 0
    Last Post: 07-27-2009, 07:51 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums