Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410

    Dynamic Date Paramter Change?

    Hello,
    There are several queries that requires manually change twice a month and was wondering if there's a way to change the current date condition / parameter so the user doesn't have to go in to Design to change. Rather never have to go into the Access database at all.

    Code:
     >=Date()+1
    on the last day of the month at the end of the day.
    Code:
     >=MonthEnd()+1
    on the end of the 3rd week of each month, so end of the day on a Fri. NOTE: MonthEnd is a module in determining the last day of the month.

    The queries are linked in Crystal Reports that generates picking sheets. This is for shipping.



    Wondering if someone knows VB/VBA, save as a Module that can handle the dynamic change twice a month without any manual intervention or opening the Access db?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Put this in a form. The forms parameters would control the query.
    txtDate on the form can be month, day, etc.

    select * from table where [MyDate] = forms!frmRpts!txtDate

    I have controls for users to set dates with a click, i.e. month , or year, or week.
    But the query uses txtStartDate, txtEndDate
    Click image for larger version. 

Name:	reports.png 
Views:	12 
Size:	13.7 KB 
ID:	21809

  3. #3
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    ?
    Noted don't want the Access db to launch.
    Plus you still have to select dates.

    need a formula / script that'll do that without having to open Access nor anyone having to select anything or do anything.

    currently the user doesn't have to do anything except twice a month to change the date range.
    trying to see if there's a script that can be use to also eliminate the twice a month change.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you cant query anything without Access. How did you want to do it?

  5. #5
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    ? It is querying in Access.

    Just don't want someone to have to launch it and select the date range.
    whether with a form or going into the query and editing the criteria fields.

    As shown, the query currently has >=Date()+1 the first 3 weeks of the month, then changed to >=MonthEnd()+1.

    I'm trying to find out if someone that's good with VBS knows a way of changing that or using script (Module) that can be called out in the query, like the MonthEnd() module so that no one has to open Access.

    Right now no one is opening Access and the queries work great except for twice a month when it needs to be edited.
    Trying to see if these steps can also be eliminated.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    ? that link is about sending an email?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    It demonstrates calling an Access VBA procedure from a VBScript. Isn't that what you asked for? Reference whatever public module procedure you want.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    No, I'm looking for a VBS that'll do what I've explained I'm looking for.

    Not sure how else to re-explain what I wrote a couple of times now.

    I'll try it a third time.

    I currently have queries that is using >=Date()+1 and >=MonthEnd()+1 WHERE the MonthEnd() is a module that determine the last day of the month.
    Issue is that in the last day of the month the date criteria is changed from >=MonthEnd()+1 to >=Date()+1
    And in the 3rd week it's changes from >=Date()+1 to MonthEnd()+1
    by someone going into these queries and manually changing them.

    I'm looking for a script that'll do that on it's own.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    That requires modifying query object with QueryDefs. IFAIK, VBScript can't do it. It can call a VBA procedure in Access.

    I NEVER use dynamic parameterized queries. I prefer VBA to set form Filter and FilterOn properties or to pass filter criteria to form or report when they open.

    DoCmd.OpenReport "report name", , , strCriteria

    Review http://www.allenbrowne.com/ser-62.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Why?

    It's either going to be >=Date()+1 at the last end of the day on the last day of each month
    or
    >=MonthEnd()+1 the 1st weekday of the 3rd week.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Then have a function return the correct date value to the query depending on the conditions you described. However, the function will be called by every single record, just as your MonthEnd() function is. This can make queries run slow.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    How?

    It seems like I need something for it to know what day of the month it is to then run >=Date()+1 from the 1st day of the month until it's time to it needs to change to >=MonthEnd()+1 on the 1st weekday of the 3rd week of the month.

    Guess in a sense that is what I'm looking for. A function that I can replace the current conditions with or added to in order to change it what those two days of the month come and go.

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I am confused - is the criteria:

    >= ThisDate

    or

    Between ThisDate AND AnotherDate

    Okay, think I almost get the issue. If it is Jan, Mar, May, Jul, Aug, Oct, Dec the last day will be 31; Apr, Jun, Sep, Nov will be 30; and Feb will be 28 or 29. This would have to be built into the custom function. Something like:
    Code:
    Public Function GetDate() As Date
    Dim IsLast() As Boolean
    Select Case Month(Date())
        Case 1, 3, 5, 7, 8, 10, 12
            If Day(Date()) = 31 Then IsLast = True
        Case 4, 6, 9, 11
            If Day(Date()) = 30 Then IsLast = True
        Case 2
            If Day(Date()) = 29 Then
                IsLast = True
            ElseIf Day(Date()) = 28 And Not IsDate("2/29/" & Year(Date())) Then
                IsLast = True
            End If
    End Select
    If IsLast = True Then
        GetDate = 'this
    Else
        GetDate = 'this
    End If
    End Function
    As you can see, February is tricky because of leap year.

    However, I still don't really understand the criteria and the conditions for changing.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Starting the 1st day of each month it's all records with >=Date()+1

    On the 1st weekday of the 3rd week of each month it's all records with >=MonthEnd()+1

    Then changed back to >=Date()+1 on the 1st of each month and then again on the 1st weekday of the 3rd week.

    It's one or the other pending if it's the 1st or the 1st weekday of the 3rd week. Or if weekday is more involved then just the start of the week of the 3rd week.

    As mentioned, it's for picking sheets which is used for picking inventory to ship.
    They want to send to the shipping department all orders to pick a day out (Date()+1) until it's the beginning of the 3rd week, then they want to ship all orders with ship dates out to the 1st of the next month (hence, MonthEnd()+1)
    Last edited by June7; 08-24-2015 at 08:47 PM.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-05-2015, 04:15 PM
  2. Replies: 3
    Last Post: 10-22-2014, 02:38 AM
  3. Replies: 2
    Last Post: 07-15-2014, 12:00 PM
  4. Dynamic Date Range?
    By aellistechsupport in forum Programming
    Replies: 7
    Last Post: 04-29-2014, 09:25 AM
  5. Replies: 1
    Last Post: 05-25-2011, 08:37 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