If this date then do this

[matt]

Hello,
I want to add a conditional statement a Sub I have to do something like this:
Psuedo Code:
______________________________________________
If (today = first weekday of the month) Then
MsgBox "Today is the first weekday of the month"
End If
______________________________________________

I was thinking maybe using Datediff, or a Select Case. But you guys are the
experts, open to suggestions.

 

[Doug Robbins - Word MVP]

Try

Dim i As Long
For i = 1 To 3
If Format(Date, "d") = i Then
If Format(Date, "ddd") <> "Sat" And Format(Date, "ddd") <> "Sun"
Then
MsgBox "Today is the first working day of the month."
Exit For
End If
End If
Next i


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[macropod]

Hi Doug,

Marginally more efficient:
If Left(Format(Date, "ddd"),1)<> "S" Then

 

[Doug Robbins - Word MVP]

Hi Paul

And I was expecting you to come up with a field-based method <G>

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Hi Doug,

Marginally more efficient:
If Left(Format(Date, "ddd"),1)<> "S" Then

--
Cheers
macropod
[MVP - Microsoft Word]

Try

Dim i As Long
For i = 1 To 3
If Format(Date, "d") = i Then
If Format(Date, "ddd") <> "Sat" And Format(Date, "ddd") <> "Sun"
Then
MsgBox "Today is the first working day of the month."
Exit For
End If
End If
Next i


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[macropod]

Hi Doug,

Don't tempt me. But for the OP's needs, embedding the field coding for this into a sub wouldn't make sense.

--
Cheers
macropod
[MVP - Microsoft Word]

Hi Paul

And I was expecting you to come up with a field-based method <G>

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Hi Doug,

Marginally more efficient:
If Left(Format(Date, "ddd"),1)<> "S" Then

--
Cheers
macropod
[MVP - Microsoft Word]

Try

Dim i As Long
For i = 1 To 3
If Format(Date, "d") = i Then
If Format(Date, "ddd") <> "Sat" And Format(Date, "ddd") <> "Sun"
Then
MsgBox "Today is the first working day of the month."
Exit For
End If
End If
Next i


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Hello,
I want to add a conditional statement a Sub I have to do something like
this:
Psuedo Code:
______________________________________________
If (today = first weekday of the month) Then
MsgBox "Today is the first weekday of the month"
End If
______________________________________________

I was thinking maybe using Datediff, or a Select Case. But you guys are
the
experts, open to suggestions.

 

[macropod]

Or how about:
Sub TestDate()
Dim strMsg As String
If Format(Date, "d") < 4 And Left(Format(Date, "ddd"), 1) <> "S" Then
strMsg = ""
Else
strMsg = " NOT"
End If
MsgBox "Today is" & strMsg & " the first working day of the month."
End Sub
Eliminating the loop altogether should be even more efficient.

--
Cheers
macropod
[MVP - Microsoft Word]

Hi Paul

And I was expecting you to come up with a field-based method <G>

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Hi Doug,

Marginally more efficient:
If Left(Format(Date, "ddd"),1)<> "S" Then

--
Cheers
macropod
[MVP - Microsoft Word]

Try

Dim i As Long
For i = 1 To 3
If Format(Date, "d") = i Then
If Format(Date, "ddd") <> "Sat" And Format(Date, "ddd") <> "Sun"
Then
MsgBox "Today is the first working day of the month."
Exit For
End If
End If
Next i


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Hello,
I want to add a conditional statement a Sub I have to do something like
this:
Psuedo Code:
______________________________________________
If (today = first weekday of the month) Then
MsgBox "Today is the first weekday of the month"
End If
______________________________________________

I was thinking maybe using Datediff, or a Select Case. But you guys are
the
experts, open to suggestions.

 

[macropod]

Oops! Too clever for my own good ... The sub in my last post doesn't give the correct result in all cases.

--
Cheers
macropod
[MVP - Microsoft Word]

Or how about:
Sub TestDate()
Dim strMsg As String
If Format(Date, "d") < 4 And Left(Format(Date, "ddd"), 1) <> "S" Then
strMsg = ""
Else
strMsg = " NOT"
End If
MsgBox "Today is" & strMsg & " the first working day of the month."
End Sub
Eliminating the loop altogether should be even more efficient.

--
Cheers
macropod
[MVP - Microsoft Word]

Hi Paul

And I was expecting you to come up with a field-based method <G>

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Hi Doug,

Marginally more efficient:
If Left(Format(Date, "ddd"),1)<> "S" Then

--
Cheers
macropod
[MVP - Microsoft Word]


Try

Dim i As Long
For i = 1 To 3
If Format(Date, "d") = i Then
If Format(Date, "ddd") <> "Sat" And Format(Date, "ddd") <> "Sun"
Then
MsgBox "Today is the first working day of the month."
Exit For
End If
End If
Next i


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Hello,
I want to add a conditional statement a Sub I have to do something like
this:
Psuedo Code:
______________________________________________
If (today = first weekday of the month) Then
MsgBox "Today is the first weekday of the month"
End If
______________________________________________

I was thinking maybe using Datediff, or a Select Case. But you guys are
the
experts, open to suggestions.

 

[Karl E. Peterson]

Marginally more efficient:
If Left(Format(Date, "ddd"),1)<> "S" Then

You want *marginally* more efficient?

If Left$(Format$(Date, "ddd"), 1) <> "S" Then

<bg>

 

[matt]

Hey guys, I had to tweak it a little bit because I ran into some problems
when I was doing this. I started thinking about holidays and non-work days
etc... so I ended up using this and just comparing the current month to the
save date month. And If there different then the user gets prompted.
THANKS!!!

 

[iris]

Hi doug,

I know this is not a question about this specific post and I already posted
the question as a new post... but I'm desperate... and very close to my
deadline... so I ask your forgiveness in advance and post my question here
hoping you will be able to help me...

I have an access table called: "categories"

this table contains all the documents I have created with their delivery date.

in "categories" I have created 2 columns: "delivery_date", "subject"

I have created a userform in WORD with the following fields:

textbox - Txt_start_date

textbox - Txt_end_date

listbox - listbox1

search button

When I press the "search" button I want to start a search that will upload
to the list box all the documents between Txt_start_date and Txt_end_date.

this is the code I wrote... which, unfortunatly doe's not work:

Private Sub searchdate_Click()

On Error Resume Next

Dim dbDatabase As Database
Dim rsi As Recordset
Dim i As Integer
Set dbDatabase = OpenDatabase("C:\masterfood.mdb")

Set rsi = dbDatabase.OpenRecordset("SELECT * FROM categories WHERE
delivery_date BETWEEN '" & Txt_start_date.Text & "' AND '" &
Txt_start_date.Text & "' order BY delivery_date;", dbOpenSnapshot)

i = 0

ListBox1.Clear

With rsi
Do Until .EOF
ListBox1.AddItem (i)
ListBox1.ColumnCount = 9
ListBox1.BoundColumn = 9
ListBox1.ColumnWidths = "3.5 in;0 in;0.7 in;1.5 in;1.5 in;0.7 in;0.7
in;0.7 in;0.7 in;"

ListBox1.Column(9, i) = ![remarks]
ListBox1.Column(8, i) = ![sender_l_name]
ListBox1.Column(7, i) = ![sender_f_name]
ListBox1.Column(6, i) = ![l_name]
ListBox1.Column(5, i) = ![f_name]
ListBox1.Column(4, i) = ![tafkid]
ListBox1.Column(3, i) = ![irgun]
ListBox1.Column(2, i) = ![delivery_date]
ListBox1.Column(1, i) = ![Path]
ListBox1.Column(0, i) = ![Subject]

.MoveNext
i = i + 1
Loop
End With

rsi.Close
dbDatabase.Close
Set rsi = Nothing
Set dbDatabase = Nothing

End Sub

I hope you can help me with that...

Thank you in advance!

Iris

 

[Doug Robbins - Word MVP]

See response in the vba.Cusotmization newsgroup

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Hi doug,

I know this is not a question about this specific post and I already
posted
the question as a new post... but I'm desperate... and very close to my
deadline... so I ask your forgiveness in advance and post my question here
hoping you will be able to help me...

I have an access table called: "categories"

this table contains all the documents I have created with their delivery
date.

in "categories" I have created 2 columns: "delivery_date", "subject"

I have created a userform in WORD with the following fields:

textbox - Txt_start_date

textbox - Txt_end_date

listbox - listbox1

search button

When I press the "search" button I want to start a search that will upload
to the list box all the documents between Txt_start_date and Txt_end_date.

this is the code I wrote... which, unfortunatly doe's not work:

Private Sub searchdate_Click()

On Error Resume Next

Dim dbDatabase As Database
Dim rsi As Recordset
Dim i As Integer
Set dbDatabase = OpenDatabase("C:\masterfood.mdb")

Set rsi = dbDatabase.OpenRecordset("SELECT * FROM categories WHERE
delivery_date BETWEEN '" & Txt_start_date.Text & "' AND '" &
Txt_start_date.Text & "' order BY delivery_date;", dbOpenSnapshot)

i = 0

ListBox1.Clear

With rsi
Do Until .EOF
ListBox1.AddItem (i)
ListBox1.ColumnCount = 9
ListBox1.BoundColumn = 9
ListBox1.ColumnWidths = "3.5 in;0 in;0.7 in;1.5 in;1.5 in;0.7
in;0.7
in;0.7 in;0.7 in;"

ListBox1.Column(9, i) = ![remarks]
ListBox1.Column(8, i) = ![sender_l_name]
ListBox1.Column(7, i) = ![sender_f_name]
ListBox1.Column(6, i) = ![l_name]
ListBox1.Column(5, i) = ![f_name]
ListBox1.Column(4, i) = ![tafkid]
ListBox1.Column(3, i) = ![irgun]
ListBox1.Column(2, i) = ![delivery_date]
ListBox1.Column(1, i) = ![Path]
ListBox1.Column(0, i) = ![Subject]

.MoveNext
i = i + 1
Loop
End With

rsi.Close
dbDatabase.Close
Set rsi = Nothing
Set dbDatabase = Nothing

End Sub

I hope you can help me with that...

Thank you in advance!

Iris

Try

Dim i As Long
For i = 1 To 3
If Format(Date, "d") = i Then
If Format(Date, "ddd") <> "Sat" And Format(Date, "ddd") <> "Sun"
Then
MsgBox "Today is the first working day of the month."
Exit For
End If
End If
Next i


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP