I hope someone can help with a coding problem I am having in a series of reports.
unitswap = "FF101aDate"
mycheck = IsNull(FF101aDate)
If mycheck = True Then
mydate = DateDiff("d", initdate, Now)
datecheck
Else
mydate = DateDiff("d", FF101aDate, Now)
freqcheck
End If
I want to set unitswap to read FF101aDate in this instant and be referred to in another function or procedure.
The next time round the detail loop in the report it may be FF101b1Date.
The subs I am using are to change the date boxes on the report depending on their value.
Ie. If no data present and longer than a specific time (compared to an initialisation date, global variable that is pre-set) the box goes red.
If data is entered and it is within certain criteria it will be 1 of four colours.
I have got this to work by coding for each individual date but surely there must be a more efficient way of doing this, if only to prevent a lot of repetitive code.
The subs I am using for the colour changes are as follows and work fine.
Public Sub freqcheck()
If mydate >= 366 Then ' it is out of date
Me(unitswap).BackColor = 255 ' red
ElseIf mydate < 366 And mydate >= 350 Then ' 16 days left yellow
Me(unitswap).BackColor = 65535 ' yellow
ElseIf mydate < 350 And mydate >= 334 Then ' 32 days left
Me(unitswap).BackColor = 65280 ' green
Else
Me(unitswap).BackColor = 13408767 ' pale pink
End If
End Sub
Public Sub datecheck()
If mydate >= 366 Then
Me(unitswap).BackColor = 255 ' changes the date box to red as this does not hold a date within the time frame
End If
End Sub
My problem seems to be with this line of code
mydate = DateDiff("d", FF101aDate, Now)
What I really need is mydate to hold the field FF101aDate, which actually contains the date the user enters.
I can get it to hold the date itself or the text “FF101aDate” but this is no good as I want to check if mydate is null. This obviously doesn’t work if it contains the string “FF101aDate”.
Once this check is done I want to refer mydate to the next date field that may be FF101b1Date and so on.
I hope I have explained myself clearly enough with this one. I apologise if I havent.
Thanx in advance for help in my quest ti make my code more efficient.
Ian
unitswap = "FF101aDate"
mycheck = IsNull(FF101aDate)
If mycheck = True Then
mydate = DateDiff("d", initdate, Now)
datecheck
Else
mydate = DateDiff("d", FF101aDate, Now)
freqcheck
End If
I want to set unitswap to read FF101aDate in this instant and be referred to in another function or procedure.
The next time round the detail loop in the report it may be FF101b1Date.
The subs I am using are to change the date boxes on the report depending on their value.
Ie. If no data present and longer than a specific time (compared to an initialisation date, global variable that is pre-set) the box goes red.
If data is entered and it is within certain criteria it will be 1 of four colours.
I have got this to work by coding for each individual date but surely there must be a more efficient way of doing this, if only to prevent a lot of repetitive code.
The subs I am using for the colour changes are as follows and work fine.
Public Sub freqcheck()
If mydate >= 366 Then ' it is out of date
Me(unitswap).BackColor = 255 ' red
ElseIf mydate < 366 And mydate >= 350 Then ' 16 days left yellow
Me(unitswap).BackColor = 65535 ' yellow
ElseIf mydate < 350 And mydate >= 334 Then ' 32 days left
Me(unitswap).BackColor = 65280 ' green
Else
Me(unitswap).BackColor = 13408767 ' pale pink
End If
End Sub
Public Sub datecheck()
If mydate >= 366 Then
Me(unitswap).BackColor = 255 ' changes the date box to red as this does not hold a date within the time frame
End If
End Sub
My problem seems to be with this line of code
mydate = DateDiff("d", FF101aDate, Now)
What I really need is mydate to hold the field FF101aDate, which actually contains the date the user enters.
I can get it to hold the date itself or the text “FF101aDate” but this is no good as I want to check if mydate is null. This obviously doesn’t work if it contains the string “FF101aDate”.
Once this check is done I want to refer mydate to the next date field that may be FF101b1Date and so on.
I hope I have explained myself clearly enough with this one. I apologise if I havent.
Thanx in advance for help in my quest ti make my code more efficient.
Ian
