1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2007 Day To Date Help

Discussion in 'Business Applications' started by zr3cool, Aug 23, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. zr3cool

    zr3cool Thread Starter

    Joined:
    Jun 11, 2010
    Messages:
    73
    Hello Everyone! :)

    I have a field that has Days (Ex. Monday, Tuesday etc...)

    Is there a way to turn this Days into a nearest date coming up. So for example if i have Wednesday on the field as a value and today is tuesday... I want the date for tomorrow displayed.

    I have been doing research but I cant find anything. I have found the opposite but it doesnt do me any good. Thank you very much.
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Is the field in the table a text field or a date field?
     
  3. zr3cool

    zr3cool Thread Starter

    Joined:
    Jun 11, 2010
    Messages:
    73
    The field containing the Days is a Text Field. Thank you
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    This can be done with VBA on a Form, but I am not sure about a Query with functions.
     
  5. zr3cool

    zr3cool Thread Starter

    Joined:
    Jun 11, 2010
    Messages:
    73
    ok I can do it on a form. Can you please get me started with the code :D
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Well you would need to establish the day of the current date.
    Calculate how many days to the day you want to convert.
    Add it to the Current date and place it in a Field.
    This will only work for a form in Single form mode, not a Continuous form mode.

    You could also use a Query with a VBA Module to do the same thing.
    I will create some code and post it.
    What is the name of your text field with the day name in it?
     
  7. zr3cool

    zr3cool Thread Starter

    Joined:
    Jun 11, 2010
    Messages:
    73
    The name of the text field is "Cutoffdate1" and the value in it is Tuesday
     
  8. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Would be a lot easier if the fields were in a date format.
     
  9. zr3cool

    zr3cool Thread Starter

    Joined:
    Jun 11, 2010
    Messages:
    73
    it would be more to like a drop down field that has monday through friday as options. I dont think i can set that to a date field. I would like to be able to pick for example Thrusday in the drop down and i want another text box to display 08/25/2011 which is the upcoming thursday :D
     
  10. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    Hi hope I am not stepping on your toes, OBP, but I started coding this out a bit ago. I should have jumped on board and said so, but hopefully this adds to the value of the possible solutions... Here is the code

    You can replace form and text box names accordingly.

    Code:
    Function WeekdayConvert()
    Dim i As Integer
    Dim x As Integer
    Dim d As Date
    x = Weekday(Now())
    Select Case Forms!FormTest.txtDay.Value
        Case Is = "Sunday"
            i = 1
        Case Is = "Monday"
            i = 2
        Case Is = "Tuesday"
            i = 3
        Case Is = "Wednesday"
            i = 4
        Case Is = "Thursday"
            i = 5
        Case Is = "Friday"
            i = 6
        Case Is = "Saturday"
            i = 7
    End Select
    Select Case x
        Case Is = i
            i = x + 7
        Case Is < i
            i = i - x
        Case Is > i
            i = 7 - (x - i)
    End Select
    d = Now() + i
    Forms!FormTest.txtNewDay.Value = d
    
    End Function
    
    
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    This is one way to do it, very simple and basic.
    Click the Convert button.

    ps The msgbox is not required, it just provides the values of the variables.
     

    Attached Files:

  12. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    zr3cool, you can put that code in the onChange event of your list / combo box with your day options and it will change the other field you have that stores the new date
     
  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    scotty, I used the old fashioned
    Dim daytoday As Integer, newdate As Date, olddate As Integer
    daytoday = Weekday(Date, 1)
    If Me.Cutoffdate1 = "Sunday" Then olddate = 1
    If Me.Cutoffdate1 = "Monday" Then olddate = 2
    If Me.Cutoffdate1 = "Tuesday" Then olddate = 3
    If Me.Cutoffdate1 = "Wednesday" Then olddate = 4
    If Me.Cutoffdate1 = "Thursday" Then olddate = 5
    If Me.Cutoffdate1 = "Friday" Then olddate = 6
    If Me.Cutoffdate1 = "Saturday" Then olddate = 7

    MsgBox Date & " - " & daytoday & " - " & olddate & " = " & Date + (olddate - daytoday)
    Me.result = Date + (olddate - daytoday)
     
  14. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    Thanks for sharing OBP. Same concept. Much simpler and easier to read code :)
     
  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    It is the old fashioned BASIC :), which I learnt first.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1013957

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice