Problems looping through many date fields.

[SnuffTheRooster]

I am using Access2003 for this project.

Here’s my problem. I have 40 date fields per record in my table. When the
user navigates to a new record in my main form, I want this code to run. The
code needs to look at each date field (VDate1 through VDate40) and determine
if this person (current record) has visited more than three times in the last
month. If so, I just want to change the background color of a separate
textbox to red. I need this to happen in reverse order though. So when the
user goes to the next record, VDate40 needs to be looked at first because it
would be their most recent visit, if there is not any data entered in this
field, check VDate39 for data and so on until the first VDate(X) field
containing data is found. Once the first field that contains data is found I
need to look at the previous two date fields to determine if that person has
visited three times in the last month.

FieldNames:
VDate1 thru VDate40 are the date fields.

ClientStatus is the name of the textbox that I want the background color to
change to red if they have visited 3 times in the last month.

I hope someone can either help or point me in the right direction. I’m too
embarrassed to show you the weak code I’ve tried to use.

Thank you in advance.

 

[Dale Fye]

Well,

My first comment is that you need to restructure your database. You have
your table setup like a spreadsheet (separate column for each visit). What
happens once the Client gets to 40 visits?

In a relational database, you should have a ClientVisits table which
contains fields for ClientID, VisitDate, ....

With this method, you just add a new date for each visit. Then, you create
a query that return the most recent three visits for that user and compares
the difference.

To do this in code, I would create a function that returns a True or False,
and use that to determine the background color of the ClientStatus to red or
white. The function might look something like:

Public Function fnThreeVisits(ClientID as long) as Boolean

Dim strSQL as string
Dim rs as DAO.Recordset
Dim intLoop as integer

strSQL = "SELECT * FROM yourTable WHERE [ClientID] = " & ClientID
Set rs = currentdb.Openrecordset(strSQL,,dbfailonerror)
For intLoop = 40 to 1 step -1
if isnull(rs("VDate" & intLoop)) = False then Exit For
Next

if intLoop < 3 Then
fnThreeVisits = False
elseif DateDiff("d", rs("VDate" & (intLoop-2)), rs("VDate" & intLoop)) >
30 Then
fnThreeVisits = false
else
fnThreeVisits = true
endif

rs.close
set rs = nothing

Exit Function

With this function, you could use the forms Current event to set the
background color of the ClientStatus textbox. Something like:

Private Sub Form_Current

me.txtClientStatus.BackgroundColor = iif(fnThreeVisits, 255, 16777215)

End Sub

 

[SnuffTheRooster]

Thank you Dale for the lightening fast response. If I understand you
correctly, you are suggesting that each visit would become a separate record,
is that right? When this project started I considered that but they want to
see all the visits on one form. I hope I didn't misunderstand you. I'm not a
developer and your coding skills are beyond my comprehension. You are right
that this is set up like a spreadsheet because that is what it is replacing,
if I can ever finish it. I know this is going to sound stupid but the problem
I'm having is on a low level basic knowledge level. I can't figure out how
to loop through the VDate fields. Since each field has a similar name I
wanted to just use a variable for the number that makes up the end of the
field name. (VDate1, VDate2 ....VDate40 where the only thing that changes is
the number. I'm not sure I used the right wording there but the goal was not
to write a line of code for each VDate field but to increment and append the
name using a counter or something like that. Again I apologize for even being
in this discussion page because I can't clearly explain what I need. Thanks
again for your quick reply

Well,

My first comment is that you need to restructure your database. You have
your table setup like a spreadsheet (separate column for each visit). What
happens once the Client gets to 40 visits?

In a relational database, you should have a ClientVisits table which
contains fields for ClientID, VisitDate, ....

With this method, you just add a new date for each visit. Then, you create
a query that return the most recent three visits for that user and compares
the difference.

To do this in code, I would create a function that returns a True or False,
and use that to determine the background color of the ClientStatus to red or
white. The function might look something like:

Public Function fnThreeVisits(ClientID as long) as Boolean

Dim strSQL as string
Dim rs as DAO.Recordset
Dim intLoop as integer

strSQL = "SELECT * FROM yourTable WHERE [ClientID] = " & ClientID
Set rs = currentdb.Openrecordset(strSQL,,dbfailonerror)
For intLoop = 40 to 1 step -1
if isnull(rs("VDate" & intLoop)) = False then Exit For
Next

if intLoop < 3 Then
fnThreeVisits = False
elseif DateDiff("d", rs("VDate" & (intLoop-2)), rs("VDate" & intLoop)) >
30 Then
fnThreeVisits = false
else
fnThreeVisits = true
endif

rs.close
set rs = nothing

Exit Function

With this function, you could use the forms Current event to set the
background color of the ClientStatus textbox. Something like:

Private Sub Form_Current

me.txtClientStatus.BackgroundColor = iif(fnThreeVisits, 255, 16777215)

End Sub

----
HTH
Dale

I am using Access2003 for this project.

Here’s my problem. I have 40 date fields per record in my table. When the
user navigates to a new record in my main form, I want this code to run. The
code needs to look at each date field (VDate1 through VDate40) and determine
if this person (current record) has visited more than three times in the last
month. If so, I just want to change the background color of a separate
textbox to red. I need this to happen in reverse order though. So when the
user goes to the next record, VDate40 needs to be looked at first because it
would be their most recent visit, if there is not any data entered in this
field, check VDate39 for data and so on until the first VDate(X) field
containing data is found. Once the first field that contains data is found I
need to look at the previous two date fields to determine if that person has
visited three times in the last month.

FieldNames:
VDate1 thru VDate40 are the date fields.

ClientStatus is the name of the textbox that I want the background color to
change to red if they have visited 3 times in the last month.

I hope someone can either help or point me in the right direction. I’m too
embarrassed to show you the weak code I’ve tried to use.

Thank you in advance.

 

[Dale Fye]

Snuff,

The code I provided will do what you are talking about. It will loop
through the field names from vDate40 to vDate1, looking for the first
non-NULL value.

When it finds a non-null value, it will compare the data of the visit two
visits before the last one, and if there are less than 30 days between them,
it will return True. Otherwise, it will return FALSE.

----
HTH
Dale

Thank you Dale for the lightening fast response. If I understand you
correctly, you are suggesting that each visit would become a separate record,
is that right? When this project started I considered that but they want to
see all the visits on one form. I hope I didn't misunderstand you. I'm not a
developer and your coding skills are beyond my comprehension. You are right
that this is set up like a spreadsheet because that is what it is replacing,
if I can ever finish it. I know this is going to sound stupid but the problem
I'm having is on a low level basic knowledge level. I can't figure out how
to loop through the VDate fields. Since each field has a similar name I
wanted to just use a variable for the number that makes up the end of the
field name. (VDate1, VDate2 ....VDate40 where the only thing that changes is
the number. I'm not sure I used the right wording there but the goal was not
to write a line of code for each VDate field but to increment and append the
name using a counter or something like that. Again I apologize for even being
in this discussion page because I can't clearly explain what I need. Thanks
again for your quick reply

Well,

My first comment is that you need to restructure your database. You have
your table setup like a spreadsheet (separate column for each visit). What
happens once the Client gets to 40 visits?

In a relational database, you should have a ClientVisits table which
contains fields for ClientID, VisitDate, ....

With this method, you just add a new date for each visit. Then, you create
a query that return the most recent three visits for that user and compares
the difference.

To do this in code, I would create a function that returns a True or False,
and use that to determine the background color of the ClientStatus to red or
white. The function might look something like:

Public Function fnThreeVisits(ClientID as long) as Boolean

Dim strSQL as string
Dim rs as DAO.Recordset
Dim intLoop as integer

strSQL = "SELECT * FROM yourTable WHERE [ClientID] = " & ClientID
Set rs = currentdb.Openrecordset(strSQL,,dbfailonerror)
For intLoop = 40 to 1 step -1
if isnull(rs("VDate" & intLoop)) = False then Exit For
Next

if intLoop < 3 Then
fnThreeVisits = False
elseif DateDiff("d", rs("VDate" & (intLoop-2)), rs("VDate" & intLoop)) >
30 Then
fnThreeVisits = false
else
fnThreeVisits = true
endif

rs.close
set rs = nothing

Exit Function

With this function, you could use the forms Current event to set the
background color of the ClientStatus textbox. Something like:

Private Sub Form_Current

me.txtClientStatus.BackgroundColor = iif(fnThreeVisits, 255, 16777215)

End Sub

----
HTH
Dale

I am using Access2003 for this project.

Here’s my problem. I have 40 date fields per record in my table. When the
user navigates to a new record in my main form, I want this code to run. The
code needs to look at each date field (VDate1 through VDate40) and determine
if this person (current record) has visited more than three times in the last
month. If so, I just want to change the background color of a separate
textbox to red. I need this to happen in reverse order though. So when the
user goes to the next record, VDate40 needs to be looked at first because it
would be their most recent visit, if there is not any data entered in this
field, check VDate39 for data and so on until the first VDate(X) field
containing data is found. Once the first field that contains data is found I
need to look at the previous two date fields to determine if that person has
visited three times in the last month.

FieldNames:
VDate1 thru VDate40 are the date fields.

ClientStatus is the name of the textbox that I want the background color to
change to red if they have visited 3 times in the last month.

I hope someone can either help or point me in the right direction. I’m too
embarrassed to show you the weak code I’ve tried to use.

Thank you in advance.