Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2006
    Location
    Lincoln, UK
    Posts
    24

    Unanswered: Tricky Date Manipulation Problem

    Afternoon all

    I have a tricky problem that I can't figure out.

    I have 3 controls on my form: -

    Logged Date
    ERT date
    ERT Day

    Logged date defaults to Now()
    ERT Date is calculated from Logged Date+2
    Ert Day is the day, Sunday to Saturday, of ERT Date.

    So far so good, but, what I'd really like to do is say, if ERT Day = Saturday, then make it Monday, and if ERT Day = Sunday, make it Tuesday, and put the correct date, Monday or Tuesday, in ERT Date.

    As an example

    Logged date = 14 Sept 2006
    Ert Date = 16 Sept 2006
    ERT Day = Saturday

    Should become
    Logged date = 14 Sept 2006
    Ert Date = 18 Sept 2006
    ERT Day = Monday


    Now I'm guessing that I can put some code in the After Update Event, but, and it's a big but, what the heck should the code be. I haven't a clue, and I don't know where to start.

    Could some brave soul help?

    PS the dates are actually in dd/mm/yyy format, I've used long dates to save any confusuion
    The names above, Logged Date etc are labels only not real field/control names.

    Thanks in advance

    Regards
    DAS

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you can transform someDate
    Sat -> Mon
    Sun -> Tues

    with:

    if weekday(someDate, vbsaturday) < 3 then someDate = someDate + 2


    ?? BeforeUpdate might be better

    izy

    LATER (guiltily) officially that should be:
    if weekday(someDate, vbsaturday) < 3 then
    someDate = dateadd("d", 2, someDate)
    endif
    Last edited by izyrider; 09-06-06 at 14:17.
    currently using SS 2008R2

  3. #3
    Join Date
    Aug 2006
    Location
    Lincoln, UK
    Posts
    24
    Thanks Izzy, but I don't understand, (remember the name).

    I presume that someDate refers to one of my controls, but which one?
    Which control do I put it on?
    Why before update and not AfterUpdate?


    Thanks
    EEjit
    ----------------------------------------------------

    One day, I'll have learnt so much that I'll be able to answer questions as well!!

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    _beforeupdate was a silly idea
    ignore
    use _afterupdate as your original plan.

    what are you are currently doing to generate ERTdate?

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Aug 2006
    Location
    Lincoln, UK
    Posts
    24
    It has a default property of Date()+2, but is usually then changed manually.

    Das
    ----------------------------------------------------

    One day, I'll have learnt so much that I'll be able to answer questions as well!!

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    that works fine when the form opens and you have your two boxes showing the defaults Date() and Date()+2, but everything goes banana-shaped when the user overwrites the default Date().

    if it was my form, i would be tempted to kill both default values

    then

    Form_Load()
    LoggedDate = Date()
    LoggedDate_afterupdate
    end sub

    LoggedDate_afterupdate()
    if not isdate(LoggedDate) then
    msgbox "invalid date"
    exit sub
    endif
    ERTdate = dateadd("d", 2, cdate(LoggedDate))
    if weekday(ERTdate, vbsaturday) < 3 then
    ERTdate = dateadd("d", 2, ERTdate)
    endif
    end sub

    lock the ERTdate box to disallow user edits

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Aug 2006
    Location
    Lincoln, UK
    Posts
    24
    Thanks Izy, I'll give it a try
    ----------------------------------------------------

    One day, I'll have learnt so much that I'll be able to answer questions as well!!

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    a brief period of calm at lunchtime inspired a potental zero-code proposal...

    provided LoggedDate is GUARANTEED to be a date by some other mechanism, ERTdate on your form should be able to use:

    = dateadd("d", iif(weekday(LoggedDate, vbthursday) < 3, 4, 2), LoggedDate)

    (and a similar expression should work for queries, reports etc)

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Aug 2006
    Location
    Lincoln, UK
    Posts
    24

    Tricky Date Manipulation Problem

    Izy

    Sorry, still can't get it. I've attached a small example database to this message, any chance you can do the necessary as a demo.


    Thanks, much appreciate it.

    Das
    Attached Files Attached Files
    ----------------------------------------------------

    One day, I'll have learnt so much that I'll be able to answer questions as well!!

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i have ignored your form and added my own.
    see if it speaks to you.

    HEALTH WARNING this example is unbound because that's the way i live. my earlier posts should have worked in bound and unbound environments but i didn't check in bound...

    OBSERVATION i truly hate allowing (forcing?) users to enter dates 'by hand'. if you want to see a possible alternative, visit here for my take on it.

    izy
    Attached Files Attached Files
    currently using SS 2008R2

  11. #11
    Join Date
    Aug 2006
    Location
    Lincoln, UK
    Posts
    24
    Thanks Izy, I'll take a proper look tomorrow.

    As far as allowing users to enter dates is concerned, that means me. I'll probably be the only one who uses this particular database, but, I can see this particular function being used in other databases which I am working on. I'll take a look at your alternative tomorrow as well.

    Thanks again

    Das
    ----------------------------------------------------

    One day, I'll have learnt so much that I'll be able to answer questions as well!!

  12. #12
    Join Date
    Aug 2006
    Location
    Lincoln, UK
    Posts
    24
    Izy, you are an absolute star. With a bit of fiddling, it does exactly what I wanted.

    I take your point about entering dates by hand, but as I said, in this case, I'm the only one that uses it for now, I use it for tracking part of my workload, as well as a test bed for Access stuff,so any date input faults/issues are mine.

    Having looked at your alternative, I'll be building that into any future databases I may get asked to build.


    Mau thanks again for your help

    Regards

    Das
    ----------------------------------------------------

    One day, I'll have learnt so much that I'll be able to answer questions as well!!

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    glad it helped
    meanwhile, something i forgot to mention yesterday.
    you are storing the name of the day in your table but you can get this info from the date when you want with:

    in the _AfterUpdate() for example
    or anywhere else you want to see Friday

    'show the day-name of logged date
    DayOfWeek = WeekdayName(Weekday(boxLoggedDate, vbSunday), False, vbSunday)

    i don't use day names so i was a little bit shocked to see how ugly that expression is ...maybe there is a simpler way to get day name, but it didn't jump up and bite me so far.

    much better to keep calculated values out of the tables whenever possible

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Aug 2006
    Location
    Lincoln, UK
    Posts
    24
    I'm ahead of you on that one Izy.

    This particular database was only the second one I built so I didn't know any better. Maybe it's a good thing you haven't seen the rest of it, some really bad naming in there that is now beginning to jump up and bite me.

    The next version will be much better.

    Das
    ----------------------------------------------------

    One day, I'll have learnt so much that I'll be able to answer questions as well!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •