Results 1 to 13 of 13
  1. #1
    madams is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    15

    combo box to filter based on date range

    I have a combo box that was used to select a procedure code. Initially the lookup table to the Procedure code only had a procedure code and a procedure description. I needed to add a start date / end date to the look up table to give them the ability to change the description to the procedure code based on the date the procedure was performed or just put an ending date so that procedure code can't be selected any more. So I need the combo box to filter and only show procedures that fall within the date range of the procedure date performed.



    For example Procedure code B1.1 with a description of Bone Marrow Transplant would have a start date of 01/01/2021 and end date of 04/30/2021 and a new record would be added as Procedure code B1.1 with a description of Bone Marrow Transplant - Adult with a start date of 05/01/2021 and a blank end date to signify it is the current active description.

    When they enter information for a record they would enter a Procedure date on the form of when the procedure was performed. Based on that procedure date the combo box would filter the list of records to only show the procedure codes/procedure descriptions where the Procedure date falls within the Start/end date of the lookup table.

    I think I need to build a string to assign to the RowSource of the combo box but I'm not quite sure how to build the string to include the date filter and which event to place it on in order for it to work. I started with just trying to assign it without the filter:
    Me.cmbProcedure.RowSource = "SELECT [tblLUProcedure].[Procedure], [tblLUProcedure].[ProcDesc] FROM tblLUProcedure;"

    Or can I create a query that I reference the procedure date from the form so it can filter the records?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I suggest you show us
    -the design of your table
    -a graphic of your form

    Unclear to me if you are basically updating the Description of medical procedure B1.1. Seems you want to keep historical record of medical procedure description by means of start and end dates.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I agree. Lookup tables don't contain such records - unless maybe those dates will only be entered once per procedure and don't change often, if ever.
    Or can I create a query that I reference the procedure date from the form so it can filter the records?
    Either way is fine. If you can't write your own sql yet, use the query builder then copy the sql to the control from the query sql view.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    madams is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    15
    Kinda new to posting in the forum . . . . . How do I attach a document to show you the table / form. I tried to take a screenshot and paste it into the quick reply but the image disappears when I post

  5. #5
    madams is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    15
    I think I figured out how to add the attachment. Let me know if it didn't work.
    Attached Files Attached Files

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That really looks all wrong, but I have no clue what to suggest as a design as I don't recall that you've explained the business that your db has to support. As a start you seem to have things in the wrong places and too many key fields, plus related PK fields. Can't recall ever seeing that. Did you read up on db normalization before diving in to this?
    BTW, the picture icon in the posting toolbar is how you insert images in your post. Much better than having to open files just to see such images, especially if they're zipped
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Still unclear to me.
    We have no real knowledge of your application nor requirement.

    It seems --only a guess - that you
    -want to change the description of medical procedures from time to time
    -want to use the endDate for some other unknown purpose in some unknown conditions
    -want to keep historical record of each medical procedure description.

    I mention "medical" because procedure has a specific meaning in Access.

  8. #8
    madams is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    15
    I'm sure it probably does look "all wrong". It was initially created by someone that programming wasn't their main job. It is a legacy database that was created over 25 years ago so I'm sure db normalization was not even considered when it was created. So I am just trying to maintain what's there until such time they opt to move it to a web based application where it can be truly normalized and re-programmed. Thanks for the posting tip on images as I didn't know that it was going to zip the attachment.

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    We might be able to help further if we could understand. I think that is the hurdle that has to be overcome. Whatever the solution is I suspect it can be arrived at without having to rebuild your db. If you could explain the process and refrain from technical terms that likely won't be understood, that would be a start.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    madams is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    15
    I think you have a pretty good handle so far even without knowledge of the application nor requirement so I will try to expand.

    The database it used to take in patient information in regards to what types of procedures (DRG's and ICD-10 procedure codes) they had performed while in the hospital. You are correct that the "Procedure codes" I am referring to are medical procedures. They have been using the same codes for the past 25 years and would like to change and have the ability to use what CMS puts out as updates to DRG's and ICD-10 procedure codes. So all the existing codes that are in there would get a start date of when the system was created and as they upload/enter new codes if the code already exists with a different description an end date would be placed on the existing record and a new record created with a start date 1 day after the end date of the existing code.(hope that wasn't too confusing LOL) Hence the reason to keep the historical record of each medical procedure code. Using the end date indicates what time frame that description is valid for and by leaving the end date open that indicates that it is the active medical procedure code. They can have patients that last for years if they are having a transplant so keeping the historical description is important when trying to run reports. They run analytical reports on the data so the description may play a role in that.

    The patient information as to when the procedure is performed (GlobalBeginDate) is in table "B" along with other pertinent information and the DRG code or medical procedure code is what is saved in table "A" as they can have multiple procedures performed (one to many relationship). The tblLUProcedure table was initially hard coded as the RowSource for the combo box that I am now trying to filter based on the GlobalBeginDate.
    Click image for larger version. 

Name:	TableRelationship.png 
Views:	20 
Size:	71.8 KB 
ID:	45151
    Click image for larger version. 

Name:	FormComboBox.png 
Views:	19 
Size:	54.9 KB 
ID:	45152
    Click image for larger version. 

Name:	tblLUProcedureExamples.png 
Views:	21 
Size:	31.5 KB 
ID:	45153
    I would like to filter the medical procedure code combo box so that depending on when the patient had the medical procedure performed it will only list those procedures that fall within the date range of when that description is valid. For example if the GlobalBeginDate was 2/17/2017 then the combo box would only show the records highlighted in yellow but if the GlobalBeginDate were prior to 12/31/2016 then the combo box would only show the records highlighted in blue.

    On another similar project another programmer created 2 separate tables for the different medical procedure codes ICD-9 procedures versus ICD-10 procedures in order to differentiate the timeframes and then just put a static date on the form that prior to the specific date use the ICD-9 table as the rowSource and after the specific date use the ICD-10 procedure table but now that client wants the same ability we are trying to do above by adding updates from year to year. or whenever needed. So moving forward we can't keep adding tables and dates it should be centralized as one table that houses the medical procedure codes with a date range indicating when they are valid. I'm trying to work with what I have without totally revamping the structure of the database as that's just not feasible at the current time. Any suggestions you can give would be greatly appreciated.

    Hope this makes better sense. Let me know if you have any further questions.
    Last edited by madams; 05-04-2021 at 09:22 AM. Reason: wasn't done typing when it posted

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Clearer.
    So current procedure descriptions

    Code:
    SELECT Procedure
    , ProcDesc
    ,StartDate
     from tblLUProcedure 
    where EndDate is NULL
    To identify ProcDesc for specific historical records - [Enter ReferralDate] is the date of the referral.

    CODE]SELECT Procedure
    , ProcDesc
    ,StartDate
    from tblLUProcedure
    where [Enter Referral Date] Between StartDate and EndDate[/CODE]

    I am still not following your GlobalDates - sorry.

  12. #12
    madams is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    15
    The GlobalBeginDate is just what they named the field in the table as the range when the patient is eligible for services but it works as how you referenced it as the 'Referral Date'

    I believe I follow what you are saying but where do I put the code?

    I tried putting it on the 'On Enter' event and the 'On Click' event of the combo box of the procedure so as you enter the field it would run the code and it looks like it's accessing the code but the drop-down box still shows all the records.

    I thought maybe because it was still assigned tblLUProcedure as the rowSource so I removed it but that didn't help. I even tried putting it as the rowSource of the combo box but I get an error "Syntax error in date in criteria expression."

    This is the code I was putting on an event of the combo box (cmbProc)

    Me.cmbProc.RowSource = "SELECT Procedure, ProcDesc FROM tblLUProcedure WHERE ((#" & Me.GlobalBeginDate & "#) Between StartDate And Nz(EndDate,#12/31/2099#)) ORDER BY Procedure"

    The only thing I added was if the end date was null to use 12/31/2099 as if it were the ending date because if I didn't I wasn't getting the current procedure codes. The me.GlobalBeginDate is so that I can extract the 'referral date' of the patient record from the form that I'm currently viewing.

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Try this in the GotFocus or Enter event of the combo:
    Code:
    Me.cmbProc.RowSource = "SELECT Procedure, ProcDesc FROM tblLUProcedure WHERE [StartDate] >= #" & Me.GlobalBeginDate & "# And Nz([EndDate],#12/31/2099#) <= #" & Me.GlobalBeginDate & "#  ORDER BY Procedure;"
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 4
    Last Post: 06-15-2017, 11:44 AM
  2. Replies: 21
    Last Post: 04-29-2015, 11:57 PM
  3. Replies: 3
    Last Post: 04-20-2014, 08:39 AM
  4. Replies: 1
    Last Post: 12-29-2013, 11:21 AM
  5. Filter Report by Optional Date Range and Combo Box
    By ARickert in forum SQL Server
    Replies: 3
    Last Post: 10-17-2012, 10:46 AM

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