Results 1 to 8 of 8
  1. #1
    Riptyde is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    4

    How do I toggle between manually imputing a date and using a calculated date field in a form.

    First, I'm very new to access, and new to this site. so any help that comes along - thanks in advanced.

    At my work, we use Excell - A LOT. and most of it would be perfect for Access and generating reports. One particular area is creating timelines. So, I have started looking into how I could build timelines. I figured out how to make the table. Now I'm working on the Forms. We build out timelines two ways. First a rough timeline based on weeks or specific known dates. Then we build them based on actual number of days between milestones.

    what I'm wanting to do is have an option in the form that will let me toggle between manual date inputs and a calculated date. I know how to do them individually, just not toggeling between them. I have spent the last couple days, off and on, looking for something close to this that I could extrapolate from, but haven't been able to find anything close enough.



    Any ideas?

    Thanks.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you would need 1 flag field, [UseCalcDate], 1 date field, [DateFld] , and 3 queries.

    The flag would be to say: UseCalcDate (as boolean) if true use a calculated field
    qsDate1Calc would would use the calculation to produce date , and ignore [DateFld]

    qsDate2NoCalc would would use the field [DateFld] to produce date in that field

    put BOTH queries into a Union query to get all the data, qnDatesUnion
    select * from qsDate1Calc
    union
    select * from qsDate2NoCalc

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think the above assumes you want to store the decision to use calculated dates or not, so that you can make use of that decision going forward, as in be able to repeat the creation of data, or adjust it, according to the decision made. My apologies if that isn't the intent of the suggestion.

    If that's what the suggestion is for but you don't want to make that decision permanent, then consider 2 option buttons in an option frame. Depending on which one is chosen, the method you use goes one way or the other. What you do after that I'm not sure. It seems that you will create data using one method or the other, but you don't say if the method (decision) chosen is to be saved or not.

    At least the button idea might present a user friendly method of making that decision, which is what I thought you were after. Another tool you can use to "toggle" something is exactly that - a toggle button.
    Last edited by Micron; 04-25-2018 at 09:51 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Riptyde is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    4
    @micron - Yes, you are right on with my thinking. I've tried the option button but can't figure out how to add the necessary date functions to the two different options.

    Again, I've tried searching for tuts on doing this but nothing specific enough to what I'm trying to do.

    If you are willing to show me where to place the date functions it would be greatly appreciated.

    Thanks again to both of you for your replies.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The option buttons have to be in a frame. If you didn't do so, you create the frame first, then select the option button control and place one in the frame (drag inside the frame). If I recall, the frame should change colour to indicate the button will be a member of that frame. I'd advise you repeat rather than copy/paste the button until you learn about the Option Value property. See the property sheet for each button. The Option values should be different (perhaps 1 and 2). In code, you say if the frame value is 1 (or whatever is correct for you) do this. If it's 2, then do this.

    If you can at least put the frame and buttons on the form, and present (perhaps in another post) a clear understanding of how it should go, I can help with the form if you post a zipped copy of your db. If you look at paragraph 2 of my last post, you can see I have little idea of what's supposed to happen after a selection, or maybe I've forgotten after looking at so many threads lately. Or you can google option buttons and frames and see if you can figure it out. At least now you know what you should be looking for.

  6. #6
    Riptyde is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    4
    Thanks Micron. I will get a file together for you and a clearer explanation. Give me a couple days as I have some deadlines coming up that I need to focus on.

    Thanks again. The help is greatly appreciated.

  7. #7
    Riptyde is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    4
    Micron,
    I built a simplified version of the db I’m working on. For this purpose it only has a Start Date and End Date (The actual timeline would have upwards of 30 or 40 entries but would be the same as start date and end date). Beside the Start date I have two options – Manual Entry and auto Entry. Next to Auto Entry is a number field with a default number it in. The Auto Entry would adjust the date based on the number in that field.

    The way we build a timeline is by starting with the known end date. We know the approximate number of days for each stage leading up to that end date. This is why the Auto Entry has a field with a default number of days in it. However, sometimes one of the stages also must be completed at a specific date. This is why I also need the Manual Entry date so adjustments can be made.

    For the sake of this exercise and the level of Access user that I am, I think having the forms default to Manual Entry will work. It does not need to remember which option I chose in a prior session. If I had it default to the Auto Entry, then , whenever the date was adjusted manually the number field next to the Auto Entry option would have to update accordingly. If this is easy enough to do then great, but I feel this would be more complicated than my level at this time.

    Timeline_Example.zip

    thanks again for the help.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I've looked at the form and re-read this entire post and I have no idea how it's supposed to function. You'll need to itemize how you expect it to work. Easy enough to hide/show the "prior" field, but what happens to the value? Are the date fields supposed to be locked if one or the other value is chosen? See what I'm getting at?
    Please elaborate.

    EDIT: don't forget: if you're going to use a negative value to establish a date -x days before a specified date, you're going to have to add that value in the calculation, not subtract it. Your label and value don't really match. It says "# of days before..." so it seems to me that the intuitive thing would be to enter 90, not -90. Plus, you have no code to switch the value from a positive entry to a negative one, so the results might surprise some users.

    Also, maybe you have no intention of doing so but as a general rule don't show autonumber id fields. Much of the time, ID fields shouldn't be shown regardless of what type they are.

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

Similar Threads

  1. Replies: 1
    Last Post: 11-27-2017, 02:02 PM
  2. Replies: 6
    Last Post: 05-26-2017, 04:10 PM
  3. Replies: 3
    Last Post: 02-26-2015, 11:01 AM
  4. Toggle switch on form to edit date in a table
    By snipe in forum Programming
    Replies: 5
    Last Post: 10-02-2014, 04:14 PM
  5. Replies: 3
    Last Post: 04-01-2012, 01:40 PM

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