How to go through each recordset field value?

[Guest]

Hi again, you can disregard my previous post, I have modified code, now I am
just not sure how to loop though all the values in the tbl_Holidays and have
it in the fldHdate for this code to use.
******
Option Compare Database

Option Explicit

Public Function PreviousBD() As Date

Dim dbConn As ADODB.Connection ' Not sure if needed
Dim rsHolidays ' Should be coming from tbl_Holidays in current DB
Dim bdNum
Dim fldHdate As Date

bdNum = Weekday(Date)

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HDate] FROM tbl_Holidays",
dbOpenSnapshot)
'Not sure what parameters to use

fldHdate = rsHolidays.Fields.Item("HDate").Value

If bdNum = 2 And Date <> fldHdate Then
PreviousBD = Date - 3

If bdNum > 2 And Date - 1 = fldHdate Then
PreviousBD = Date - 2

If bdNum > 2 And Date - 1 <> fldHdate Then
PreviousBD = Date - 1

If bdNum = 3 And Date - 1 = fldHdate Then
PreviousBD = Date - 4

If bdNum = 2 And Date - 3 = fldHdate Then
PreviousBD = Date - 4
End If
End If
End If
End If
End If

Debug.Print bdNum
Debug.Print Date
Debug.Print fldHdate
Debug.Print PreviousBD

Set rsHolidays = Nothing

End Function
***********

I may be missing something else, Thanks for the help in advance.

 

[Guest]

Not, sure what you are trying to do, everytime you loop through the records,
you will change the value in the, so in the end the variant fldHdate will
hold the value of the field HDate in the last record in the record set

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HDate] FROM tbl_Holidays",
dbOpenSnapshot)
If not rsHolidays.Eof then
While not rsHolidays.Eof
fldHdate = rsHolidays!HDate
rsHolidays.MoveNext
Wend
End If

 

[Guest]

Thank you for your response. I am trying to check if yesterday was a holiday.


Thanks
--
If at first you don''''t succeed, destroy all evidence that you tried.

Not, sure what you are trying to do, everytime you loop through the records,
you will change the value in the, so in the end the variant fldHdate will
hold the value of the field HDate in the last record in the record set

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HDate] FROM tbl_Holidays",
dbOpenSnapshot)
If not rsHolidays.Eof then
While not rsHolidays.Eof
fldHdate = rsHolidays!HDate
rsHolidays.MoveNext
Wend
End If


--
\\// Live Long and Prosper \\//
BS"D

Hi again, you can disregard my previous post, I have modified code, now I am
just not sure how to loop though all the values in the tbl_Holidays and have
it in the fldHdate for this code to use.
******
Option Compare Database

Option Explicit

Public Function PreviousBD() As Date

Dim dbConn As ADODB.Connection ' Not sure if needed
Dim rsHolidays ' Should be coming from tbl_Holidays in current DB
Dim bdNum
Dim fldHdate As Date

bdNum = Weekday(Date)

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HDate] FROM tbl_Holidays",
dbOpenSnapshot)
'Not sure what parameters to use

fldHdate = rsHolidays.Fields.Item("HDate").Value

If bdNum = 2 And Date <> fldHdate Then
PreviousBD = Date - 3

If bdNum > 2 And Date - 1 = fldHdate Then
PreviousBD = Date - 2

If bdNum > 2 And Date - 1 <> fldHdate Then
PreviousBD = Date - 1

If bdNum = 3 And Date - 1 = fldHdate Then
PreviousBD = Date - 4

If bdNum = 2 And Date - 3 = fldHdate Then
PreviousBD = Date - 4
End If
End If
End If
End If
End If

Debug.Print bdNum
Debug.Print Date
Debug.Print fldHdate
Debug.Print PreviousBD

Set rsHolidays = Nothing

End Function
***********

I may be missing something else, Thanks for the help in advance.

 

[Guest]

I have tested this code by changing my system date to all the variations and
it works, here is the code for anyone who would like to use it if needed,
also since I am very new at this, if anyone has any suggestions I am always
open for it:
All I am missing is if today is holiday then quit as well....
__________
Option Compare Database

Option Explicit
'******************************************************
' written by Vika K. on 02/27/06
'
' Previous business date calculation
' taking Holidays of the company in to considiration
' You would need tbl_Holidays with HID as unique
' record, HolidayName as text and HolidayDate as date
'
'******************************************************

Public Function PreviousBD() As Date

Dim rsHolidays ' Should be coming from tbl_Holidays in current DB
Dim bdNum ' Assign DayOfTheWeek Value
Dim fldHdate As Integer ' will use to store value 1 for holiday and 2 for
non-holiday
Dim Yesterday As Date


Yesterday = Date - 1
bdNum = Weekday(Date)

' if today is sunday or monday then I do not want to run anything at all

If bdNum = 7 Then
DoCmd.Quit
Else:
If bdNum = 1 Then
DoCmd.Quit
End If
End If

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HDate] FROM tbl_Holidays
Where HDate=Date()-1", dbOpenSnapshot)
'Looking if yesterday was a holiday


If rsHolidays.EOF Then ' EOF stands for Empty recordset
fldHdate = 1 ' Assigned value if Yesterday was not a holiday
Else: fldHdate = 2
End If

If bdNum = 2 Then
PreviousBD = Yesterday - 2
Else:
If bdNum = 3 And fldHdate = 2 Then
PreviousBD = Yesterday - 3
Else:
If bdNum = 4 And fldHdate = 2 Or bdNum = 5 And fldHdate = 2 Or
bdNum = 6 And fldHdate = 2 Then
PreviousBD = Yesterday - 1
Else:
If bdNum = 3 Or bdNum = 4 Or bdNum = 5 Or bdNum = 6 Then
PreviousBD = Yesterday
End If
End If
End If
End If

Debug.Print Yesterday
Debug.Print bdNum
Debug.Print fldHdate
Debug.Print PreviousBD

Set rsHolidays = Nothing

End Function
____________
--
If at first you don''''t succeed, destroy all evidence that you tried.

Thank you for your response. I am trying to check if yesterday was a holiday.


Thanks
--
If at first you don''''t succeed, destroy all evidence that you tried.

Not, sure what you are trying to do, everytime you loop through the records,
you will change the value in the, so in the end the variant fldHdate will
hold the value of the field HDate in the last record in the record set

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HDate] FROM tbl_Holidays",
dbOpenSnapshot)
If not rsHolidays.Eof then
While not rsHolidays.Eof
fldHdate = rsHolidays!HDate
rsHolidays.MoveNext
Wend
End If


--
\\// Live Long and Prosper \\//
BS"D

Hi again, you can disregard my previous post, I have modified code, now I am
just not sure how to loop though all the values in the tbl_Holidays and have
it in the fldHdate for this code to use.
******
Option Compare Database

Option Explicit

Public Function PreviousBD() As Date

Dim dbConn As ADODB.Connection ' Not sure if needed
Dim rsHolidays ' Should be coming from tbl_Holidays in current DB
Dim bdNum
Dim fldHdate As Date

bdNum = Weekday(Date)

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HDate] FROM tbl_Holidays",
dbOpenSnapshot)
'Not sure what parameters to use

fldHdate = rsHolidays.Fields.Item("HDate").Value

If bdNum = 2 And Date <> fldHdate Then
PreviousBD = Date - 3

If bdNum > 2 And Date - 1 = fldHdate Then
PreviousBD = Date - 2

If bdNum > 2 And Date - 1 <> fldHdate Then
PreviousBD = Date - 1

If bdNum = 3 And Date - 1 = fldHdate Then
PreviousBD = Date - 4

If bdNum = 2 And Date - 3 = fldHdate Then
PreviousBD = Date - 4
End If
End If
End If
End If
End If

Debug.Print bdNum
Debug.Print Date
Debug.Print fldHdate
Debug.Print PreviousBD

Set rsHolidays = Nothing

End Function
***********

I may be missing something else, Thanks for the help in advance.