Hi I am doing program where it reports particular calculations. The calculation depends on the current date. The report shows the data for days Mon-Fri. For instance if the date was on a monday the calculation would be new then on the next day it would add monday's calculation to tuesday and so on until Friday then start over on Monday. My problem is I don't know how to do this in VB. I have looked at the help for firstdayweek but don't quite understand. Can anyone provide me a site where I can look at examples or provide an example so I can get some kind of start? Thanks in advance.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
dayofweek 4
- Thread starter rozzay
- Start date
-
1
- #2
CajunCenturion
Programmer
You could try the Weekday function
Select Case Weekday(<date to check>)
Case vbSunday
MsgBox "its sunday"
Case vbMonday
MsgBox "its monday"
Case vbTuesday
MsgBox "its tuesday"
Case vbWednesday
MsgBox "its wednesday"
Case vbThursday
MsgBox "its thursday"
Case vbFriday
MsgBox "its friday"
Case vbSaturday
MsgBox "its saturday"
End Select
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
Select Case Weekday(<date to check>)
Case vbSunday
MsgBox "its sunday"
Case vbMonday
MsgBox "its monday"
Case vbTuesday
MsgBox "its tuesday"
Case vbWednesday
MsgBox "its wednesday"
Case vbThursday
MsgBox "its thursday"
Case vbFriday
MsgBox "its friday"
Case vbSaturday
MsgBox "its saturday"
End Select
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
- Thread starter
- #3
okay i went ahead and set this up using the select case example above and try to do some calcuations with it by retrieving today's record and yesterday's record but i receive a run-time error saying the overflow of data. What did I do wrong and what can i do in order to fix this??
thanks.
============================================================
Option Explicit
Dim bValidTime As Boolean
Dim iDte As Integer
Dim iDate As Integer
Private Sub cmdNext_Click()
Dim vbResult As VbMsgBoxResult
Dim strSQL As String
If ValidData Then
strSQL = ""
strSQL = strSQL & " SELECT * "
strSQL = strSQL & " FROM edi_log"
rsLogAccess.Open strSQL, oConnAccess, adOpenDynamic, adLockOptimistic
If Not rsLogAccess.BOF Then
rsLogAccess.MoveFirst
End If
GetWEBEDIPOs
GetWEBEDIINVs
GetWeb_POs
GetWEB_Ven
rsLogAccess.Find "[time] = " & Date
If rsLogAccess.EOF Then
rsLogAccess.MoveLast
rsLogAccess.AddNew
rsLogAccess.Fields("time"
= Date
LoadData
Else
rsLogAccess.Update
LoadData
End If
Unload Me
frmLogResult.Show
End If
End Sub
Private Function LoadData()
strEDPO = txtEDPO.Text
rsLogAccess.Fields("edpo" = Trim(strEDPO)
strLoadTime = txtLoadTime.Text
rsLogAccess.Fields("load" = Trim(strLoadTime)
strXMITLoadTime = txtXMITLoadTime.Text
rsLogAccess.Fields("ordernet" = Trim(strXMITLoadTime)
strIINGLoadTime = txtIingLoadTime.Text
rsLogAccess.Fields("iingout" = Trim(strIINGLoadTime)
If txtComment.Text <> "" Then
strComment = txtComment.Text
rsLogAccess.Fields("comment" = Trim(strComment)
End If
rsLogAccess.Fields("po" = Trim(strPOs)
rsLogAccess.Fields("inv" = Trim(strINVs)
rsLogAccess.Fields("WebP_POs" = Trim(strProPO)
rsLogAccess.Fields("WebP_Ven" = Trim(strProVen)
rsLogAccess.Fields("WebS_POs" = Trim(strTestPO)
rsLogAccess.Fields("WebS_Ven" = Trim(strTestVen)
CalculateMF
CalculateWebPO
rsLogAccess.Update
rsLogAccess.Close
End Function
Private Function CalculateMF()
iMf = 0
ValidDay
If Not rsLogAccess.BOF Then
rsLogAccess.MoveFirst
End If
If iDte = 0 Then
strMF = strEDPO
iMf = strEDPO
ElseIf iDte = 1 Then
rsLogAccess.Find "[time] = " & Date - 1
strMF = rsLogAccess("mf".Value + iMf
ElseIf iDte = 2 Then
ElseIf iDte = 3 Then
ElseIf iDte = 4 Then
End If
rsLogAccess.Fields("mf" = Trim(strMF)
End Function
Private Function ValidDay()
Select Case Weekday(Date)
Case vbSunday
iDte = -1
MsgBox "Sunday no POs run!"
Case vbMonday
iDte = 0
iDate = Date
Case vbTuesday
iDte = 1
iDate = Date - 1
Case vbWednesday
iDte = 2
iDate = Date - 2
Case vbThursday
iDte = 3
iDate = Date - 3
Case vbFriday
iDte = 4
iDate = Date - 4
Case vbSaturday
iDte = 5
MsgBox "Saturday, no POs run!"
End Select
End Function
thanks.
============================================================
Option Explicit
Dim bValidTime As Boolean
Dim iDte As Integer
Dim iDate As Integer
Private Sub cmdNext_Click()
Dim vbResult As VbMsgBoxResult
Dim strSQL As String
If ValidData Then
strSQL = ""
strSQL = strSQL & " SELECT * "
strSQL = strSQL & " FROM edi_log"
rsLogAccess.Open strSQL, oConnAccess, adOpenDynamic, adLockOptimistic
If Not rsLogAccess.BOF Then
rsLogAccess.MoveFirst
End If
GetWEBEDIPOs
GetWEBEDIINVs
GetWeb_POs
GetWEB_Ven
rsLogAccess.Find "[time] = " & Date
If rsLogAccess.EOF Then
rsLogAccess.MoveLast
rsLogAccess.AddNew
rsLogAccess.Fields("time"
= DateLoadData
Else
rsLogAccess.Update
LoadData
End If
Unload Me
frmLogResult.Show
End If
End Sub
Private Function LoadData()
strEDPO = txtEDPO.Text
rsLogAccess.Fields("edpo"
= Trim(strEDPO)strLoadTime = txtLoadTime.Text
rsLogAccess.Fields("load"
= Trim(strLoadTime)strXMITLoadTime = txtXMITLoadTime.Text
rsLogAccess.Fields("ordernet"
= Trim(strXMITLoadTime)strIINGLoadTime = txtIingLoadTime.Text
rsLogAccess.Fields("iingout"
= Trim(strIINGLoadTime)If txtComment.Text <> "" Then
strComment = txtComment.Text
rsLogAccess.Fields("comment"
= Trim(strComment)End If
rsLogAccess.Fields("po"
= Trim(strPOs)rsLogAccess.Fields("inv"
= Trim(strINVs)rsLogAccess.Fields("WebP_POs"
= Trim(strProPO)rsLogAccess.Fields("WebP_Ven"
= Trim(strProVen)rsLogAccess.Fields("WebS_POs"
= Trim(strTestPO)rsLogAccess.Fields("WebS_Ven"
= Trim(strTestVen)CalculateMF
CalculateWebPO
rsLogAccess.Update
rsLogAccess.Close
End Function
Private Function CalculateMF()
iMf = 0
ValidDay
If Not rsLogAccess.BOF Then
rsLogAccess.MoveFirst
End If
If iDte = 0 Then
strMF = strEDPO
iMf = strEDPO
ElseIf iDte = 1 Then
rsLogAccess.Find "[time] = " & Date - 1
strMF = rsLogAccess("mf"
.Value + iMfElseIf iDte = 2 Then
ElseIf iDte = 3 Then
ElseIf iDte = 4 Then
End If
rsLogAccess.Fields("mf"
= Trim(strMF)End Function
Private Function ValidDay()
Select Case Weekday(Date)
Case vbSunday
iDte = -1
MsgBox "Sunday no POs run!"
Case vbMonday
iDte = 0
iDate = Date
Case vbTuesday
iDte = 1
iDate = Date - 1
Case vbWednesday
iDte = 2
iDate = Date - 2
Case vbThursday
iDte = 3
iDate = Date - 3
Case vbFriday
iDte = 4
iDate = Date - 4
Case vbSaturday
iDte = 5
MsgBox "Saturday, no POs run!"
End Select
End Function
-
1
- #4
I just coded the GetDayOfWeek function below...I haven't tested it completely, but I believe it will work. Basically, it works by knowing that April, 28, 2002 is a Sunday and then comparing the number of days from that date to the date that you are trying to query.
Private Sub Form_Load()
'Test on Friday Dec 12, 1997
MsgBox GetDayOfWeek("12 / 12 / 1997"
End Sub
Private Function GetDayOfWeek(theDate As Date) As String
Dim x As Long
x = DateDiff("d", "4 / 28 / 2002", theDate) Mod 7
Select Case x
Case 0
GetDayOfWeek = "Sunday"
Case 1
GetDayOfWeek = "Monday"
Case 2
GetDayOfWeek = "Tuesday"
Case 3
GetDayOfWeek = "Wednesday"
Case 4
GetDayOfWeek = "Thursday"
Case 5
GetDayOfWeek = "Friday"
Case 6
GetDayOfWeek = "Saturday"
Case 0
GetDayOfWeek = "Sunday"
Case -1
GetDayOfWeek = "Saturday"
Case -2
GetDayOfWeek = "Friday"
Case -3
GetDayOfWeek = "Thursday"
Case -4
GetDayOfWeek = "Wednesday"
Case -5
GetDayOfWeek = "Tuesday"
Case -6
GetDayOfWeek = "Monday"
End Select
End Function
Private Sub Form_Load()
'Test on Friday Dec 12, 1997
MsgBox GetDayOfWeek("12 / 12 / 1997"
End Sub
Private Function GetDayOfWeek(theDate As Date) As String
Dim x As Long
x = DateDiff("d", "4 / 28 / 2002", theDate) Mod 7
Select Case x
Case 0
GetDayOfWeek = "Sunday"
Case 1
GetDayOfWeek = "Monday"
Case 2
GetDayOfWeek = "Tuesday"
Case 3
GetDayOfWeek = "Wednesday"
Case 4
GetDayOfWeek = "Thursday"
Case 5
GetDayOfWeek = "Friday"
Case 6
GetDayOfWeek = "Saturday"
Case 0
GetDayOfWeek = "Sunday"
Case -1
GetDayOfWeek = "Saturday"
Case -2
GetDayOfWeek = "Friday"
Case -3
GetDayOfWeek = "Thursday"
Case -4
GetDayOfWeek = "Wednesday"
Case -5
GetDayOfWeek = "Tuesday"
Case -6
GetDayOfWeek = "Monday"
End Select
End Function
CajunCenturion
Programmer
iDate = Date - 1
will not work
iDate is an integer and Date is a date.
what are you using iDate for? it is declared as an integer, but Date is a date. If you want iDate to be a date, then declare it as a date and use the DateDiff function to make the assignment.
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
will not work
iDate is an integer and Date is a date.
what are you using iDate for? it is declared as an integer, but Date is a date. If you want iDate to be a date, then declare it as a date and use the DateDiff function to make the assignment.
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
CajunCenturion
Programmer
Also rossay, the WeekDay function actually return an integer with values shown below:
vbSunday 1
vbMonday 2
vbTuesday 3
vbWednesday 4
vbThursday 5
vbFriday 6
vbSaturday 7
You could replace your call to ValidDay with the statement
iDte = WeekDay(Date) - 2
Then you can use the following case statment
Select Case iDte
case 0 ' Monday
strMF = strEDPO
iMf = strEDPO
case 1, 2, e, 4 ' Tuesday thru Friday
rsLogAccess.Find "[time] = " & Date - iDte
strMF = rsLogAccess("mf".Value + iMf
case else
MsgBox "Its a Weekend"
end select
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
vbSunday 1
vbMonday 2
vbTuesday 3
vbWednesday 4
vbThursday 5
vbFriday 6
vbSaturday 7
You could replace your call to ValidDay with the statement
iDte = WeekDay(Date) - 2
Then you can use the following case statment
Select Case iDte
case 0 ' Monday
strMF = strEDPO
iMf = strEDPO
case 1, 2, e, 4 ' Tuesday thru Friday
rsLogAccess.Find "[time] = " & Date - iDte
strMF = rsLogAccess("mf"
.Value + iMfcase else
MsgBox "Its a Weekend"
end select
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
-
1
- #7
petermeachem
Programmer
The easiest way to get day of week is format(now,"w" Peter Meachem
peter@accuflight.com
Peter Meachempeter@accuflight.com
-
1
- #8
Hi,
You might find this usful.
The first part (down to the double line) is
the contents of the .frm file for this procedure.
HTH
Rowland
Version 5#
Begin VB.Form Form1
Caption = "Form1"
ClientHeight = 3192
ClientLeft = 60
ClientTop = 348
ClientWidth = 4680
LinkTopic = "Form1"
ScaleHeight = 3192
ScaleWidth = 4680
StartUpPosition = 2 'CenterScreen
Begin VB.CommandButton cmdDone
Caption = "Quit"
Height = 396
Left = 384
TabIndex = 4
Top = 2016
Width = 972
End
Begin VB.CommandButton cmdReset
Caption = "Reset"
Height = 396
Left = 384
TabIndex = 3
Top = 1440
Width = 972
End
Begin VB.TextBox Text2
Height = 396
Left = 1632
TabIndex = 2
Top = 288
Width = 972
End
Begin VB.CommandButton cmdAddResults
Caption = "Enter Day Results"
Height = 396
Left = 384
TabIndex = 1
Top = 864
Width = 972
End
Begin VB.TextBox Text1
Height = 396
Left = 384
TabIndex = 0
Top = 288
Width = 972
End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
==========================================
Option Explicit
Dim DayNumber
Dim arDaily(1 To 5)
Private Sub cmdAddResults_Click()
Dim i
'-------------------------------------------------
'For test purposes only, hard code numbers
'into days prior to today, then enter
'new data (for today) into the
'first text box. This block of code whould
'not be used in the final program.
arDaily(1) = 12 'Monday results
arDaily(2) = 3 'Tuesday results
arDaily(3) = 0 'Wed
arDaily(4) = 0 'Thu
arDaily(5) = 0 'Fri
'--------------------------------------------------
'This will set the first day of week
'to Monday and return a value (1 to 7)
'for each Subsequent day. You will want
'to use 1 to 5 to set your work week to
'Monday thru Friday.
DayNumber = WeekDay(Now, vbMonday)
'You will enter the days activity in
'Text1. This code will place that data
'into the array position representing that day.
arDaily(DayNumber) = Text1.Text
'This will display the sum of
'all days activity to this point.
For i = 1 To 5
Text2.Text = Val(Text2.Text) + Val(arDaily(i))
Next i
End Sub
Private Sub cmdDone_Click()
End
End Sub
Private Sub cmdReset_Click()
Dim Msg, Style, Response
Msg = "Are you sure you want to clear the array ?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Response = MsgBox(Msg, Style)
If Response = vbNo Then Exit Sub
Erase arDaily() 'Clear the array and
Text1.Text = "" 'initialize the text boxes
Text2.Text = 0 'to prepare for a new week.
Text1.SetFocus
End Sub
You might find this usful.
The first part (down to the double line) is
the contents of the .frm file for this procedure.
HTH
Rowland
Version 5#
Begin VB.Form Form1
Caption = "Form1"
ClientHeight = 3192
ClientLeft = 60
ClientTop = 348
ClientWidth = 4680
LinkTopic = "Form1"
ScaleHeight = 3192
ScaleWidth = 4680
StartUpPosition = 2 'CenterScreen
Begin VB.CommandButton cmdDone
Caption = "Quit"
Height = 396
Left = 384
TabIndex = 4
Top = 2016
Width = 972
End
Begin VB.CommandButton cmdReset
Caption = "Reset"
Height = 396
Left = 384
TabIndex = 3
Top = 1440
Width = 972
End
Begin VB.TextBox Text2
Height = 396
Left = 1632
TabIndex = 2
Top = 288
Width = 972
End
Begin VB.CommandButton cmdAddResults
Caption = "Enter Day Results"
Height = 396
Left = 384
TabIndex = 1
Top = 864
Width = 972
End
Begin VB.TextBox Text1
Height = 396
Left = 384
TabIndex = 0
Top = 288
Width = 972
End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
==========================================
Option Explicit
Dim DayNumber
Dim arDaily(1 To 5)
Private Sub cmdAddResults_Click()
Dim i
'-------------------------------------------------
'For test purposes only, hard code numbers
'into days prior to today, then enter
'new data (for today) into the
'first text box. This block of code whould
'not be used in the final program.
arDaily(1) = 12 'Monday results
arDaily(2) = 3 'Tuesday results
arDaily(3) = 0 'Wed
arDaily(4) = 0 'Thu
arDaily(5) = 0 'Fri
'--------------------------------------------------
'This will set the first day of week
'to Monday and return a value (1 to 7)
'for each Subsequent day. You will want
'to use 1 to 5 to set your work week to
'Monday thru Friday.
DayNumber = WeekDay(Now, vbMonday)
'You will enter the days activity in
'Text1. This code will place that data
'into the array position representing that day.
arDaily(DayNumber) = Text1.Text
'This will display the sum of
'all days activity to this point.
For i = 1 To 5
Text2.Text = Val(Text2.Text) + Val(arDaily(i))
Next i
End Sub
Private Sub cmdDone_Click()
End
End Sub
Private Sub cmdReset_Click()
Dim Msg, Style, Response
Msg = "Are you sure you want to clear the array ?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Response = MsgBox(Msg, Style)
If Response = vbNo Then Exit Sub
Erase arDaily() 'Clear the array and
Text1.Text = "" 'initialize the text boxes
Text2.Text = 0 'to prepare for a new week.
Text1.SetFocus
End Sub
- Status
Similar threads