UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Filter Column For Tomorrow, Office 2013    
 
   
bakersburg9
post Nov 9 2017, 03:25 PM
Post#1



Posts: 4,952
Joined: 2-November 04
From: Downey, CA


I have a macro where I filter on a date field for tomorrow's date currently, it's hard-coded for the actual date (tomorrow is November 10), but I can't figure out the syntax - I've tried Date(), Now() - nothing works

CODE
    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=32, Operator:= _
        xlFilterValues, Criteria2:=Array(2, "11/10/2017")
    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=32
Go to the top of the page
 
doctor9
post Nov 9 2017, 03:32 PM
Post#2


UtterAccess Editor
Posts: 17,910
Joined: 29-March 05
From: Wisconsin


bakersburg9,

Give this a try:

CODE
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=32, Operator:= _
        xlFilterValues, Criteria2:=Array(2, Date + 1)
    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=32

Or, if the column of dates is stored as text...
CODE
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=32, Operator:= _
        xlFilterValues, Criteria2:=Array(2, Format(Date + 1, "mm/dd/yyyy"))
    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=32

Hope this helps,

EDIT: If these don't work, can you describe the layout of your table or attach the file so I can have something to try things out on?

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
bakersburg9
post Nov 9 2017, 06:14 PM
Post#3



Posts: 4,952
Joined: 2-November 04
From: Downey, CA


Dennis,

That worked PERFECT ! Thank YOU !!!!

CODE
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=42, Operator:= _
        xlFilterValues, Criteria2:=Array(2, Date + 1)


hey, the Date+1 is easy, but the rest *whew* - so grateful - thanks ! btw, What is the 2 representing in the =Array(2, Date + 1) piece ?

... also, will this work by just selecting all the data first, so I don't have a scenario where this only works if the data range / table is named "Table2" ?

Steve
Go to the top of the page
 
doctor9
post Nov 10 2017, 10:00 AM
Post#4


UtterAccess Editor
Posts: 17,910
Joined: 29-March 05
From: Wisconsin


Steve,

All I really did was swap out your hard-coded date for an expression. I didn't look at the rest of the line of code, honestly. I'll confess I don't understand all of the properties of the AutoFilter arguments, but I'm guessing the help file should be able to explain it better than I could. At a guess, I'd say it has to do with the fact that you're sorting a Table rather than just a group of cells.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
bakersburg9
post Nov 10 2017, 12:17 PM
Post#5



Posts: 4,952
Joined: 2-November 04
From: Downey, CA


OK, so let's take it up a notch - that works great, now I need to edit it to tomorrow (Saturday) and Monday, if the current date is a Friday - HELP!
Steve
Go to the top of the page
 
doctor9
post Nov 10 2017, 01:26 PM
Post#6


UtterAccess Editor
Posts: 17,910
Joined: 29-March 05
From: Wisconsin


bakersburg9,

I'm assuming that you mean that you want to skip Saturdays and Sundays. You can test for which day of the week a date falls on with the Weekday() function. If it returns a 1, that's Sunday. 7 is Saturday.

CODE
    Dim intNextWorkDayInterval As Integer

    intNextWorkDayInterval = 1

'   If tomorrow is Sunday, skip to Monday
    If Weekday(Date + 1) = 1 Then
        intNextWorkDayInterval = 2
    End If

'   If tomorrow is Saturday, skip to Monday
    If Weekday(Date + 1) = 7 Then
        intNextWorkDayInterval = 3
    End If

    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=42, Operator:= _
        xlFilterValues, Criteria2:=Array(2, Date + intNextWorkDayInterval)

There's probably a more elegant way of doing this, but it should work.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
bakersburg9
post Nov 10 2017, 01:34 PM
Post#7



Posts: 4,952
Joined: 2-November 04
From: Downey, CA


Dennis,

It's kind of quirky - it's the next day if the macro is run Monday-Thursday ... on FRIDAY, it's Saturday and Monday - but that's how it is :-( These people don't work Weekends, so it does kind of make sense from that perspective, because on Monday, the report would be for Tuesday's calls, and Monday's date gets bypassed.

Steve
Go to the top of the page
 
doctor9
post Nov 10 2017, 01:45 PM
Post#8


UtterAccess Editor
Posts: 17,910
Joined: 29-March 05
From: Wisconsin


Steve,

> on FRIDAY, it's Saturday and Monday

So, today (11/17) you want the filter to show data for 11/18 and 11/19? If so, you can probably use the Macro Recorder and manually select those two dates and see what the syntax is for two choices, and adapt my code.

But what if it's Saturday?
And what if it's Sunday?

What data would you show on those days?

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
bakersburg9
post Nov 10 2017, 05:16 PM
Post#9



Posts: 4,952
Joined: 2-November 04
From: Downey, CA


There's no report ran on Saturday or Sunday ... since no one comes in on Sunday to run the report for the next day's calls, and no one works Sunday, so... the scheduled calls for Monday are included Friday - clear as mud ?

Monday's early morning report is for calls scheduled the next day (Tuesday)
Tuesday's early morning report is for calls scheduled the next day (Wednesday)
Wednesday's early morning report is for calls scheduled the next day (Thursday)
Thursday's early morning report is for calls scheduled the next day (Friday)
Friday's early morning report is for calls scheduled the next day (Saturday) AND Monday (No one works Sunday, so there's no calls scheduled for Sundays, and no one to run a list for calls for the next day (Monday)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 12:13 PM