Calculating date based on 3 criteria

  • Thread starter Thread starter DubboPete
  • Start date Start date
D

DubboPete

Hi all,

I am trying to dynamically calculate dates, to populate a field called
[DOSEDATE]

The calculations work like this.
Patients take away doses of medicine, on particular days of the [Monday to
Friday] week. The medicine doses are prepared on any day between Monday
and Friday.

1st scenario:
Clinician signs into database on Monday 12th July, and declares Wednesday
14th July as [PROCDATE]

***This is done on a form [DATEPREP] with [Text0] set to date/time, and
Text0's After Update event hides the [DATEPREP] form and opens up Patient
Records form [DUBBOLIST]***

Patient 'A' has one record, which indicates a dose of medicine is required
as a takeaway each Saturday.
Clinician opens first patient record, and sees that checkbox [SAT] is
selected. Clinician marks record for printing (checkbox) and prints 1
label on Monday 12th July. The label contains the date that the dose is
intended for, in this scenario Saturday 17 July.

Pharmacist prepares dose on Wednesday 14th July, in accordance with label
Patient 'A' attends clinic on Thursday and takes home a dose of medicine for
Saturday 17 July


2nd scenario
Clinician signs into database on Tuesday 13th July, and declares Wednesday
14th July as [PROCDATE]
Pataient 'B' has three records, one for each day that a dose of medicine is
required

Clinician opens first patient record, and sees that checkbox [SAT] is
selected. Clinician marks record for printing (checkbox)
Clinician opens second patient record, and sees that checkbox [MON] is
selected. Clinician marks record for printing (checkbox)
Clinician opens third patient record, and sees that checkbox [WED] is
selected. Clinician marks record for printing (checkbox)

Clinician then prints 3 labels on Tuesday 13th July. Each label contains
the date that the dose is intended for, 17th, 19th and 21st of July
respectively.

Pharmacist prepares doses on Friday 16th July, in accordance with labels
Patient 'B' attends clinic on Friday 16th and takes home three doses of
medicine, one for Saturday 17 July, one for Monday 19th July and one for
Wednesday 21st July.

What I need help with is code to capture the weekday part of the [PROCDATE]
and calculate the [DOSEDATE].

Only one record is allowed for any one dose. Only one dosing date checkbox
is allowed to be clicked at any one time, so there is no doubling up of
dosing dates for one record. This is because the medicine might be
different on any subsequent dose, in either type or quantity, and the
clinician and pharmacist cannot be allowed to double-dose for any one day.

So if the [PROCDATE] is Weekday=4 (Wednesday, 14 July), and the [DOSEDATE]
is Weekday=7 (Saturday 18 July), I need for the [DOSEDATE] field to be
updated dynamically. This should be done when the record is 'MARKED FOR
PRINTING' ([PRINTFLAG] checkbox value = true)

I thought this might work, but the [DOSEDATE] is not getting updated... If
someone could check the code and steer me in the right direction I'd
appreciate it... here's the code for the [PRINTFLAG] checkbox

thanks in anticipation
and for those that don't read beyond this point, thanks for your patience!!
:-)
DubboPete

=================================
Private Sub PRINTFLAG_Click()
On Error GoTo err_PRINTFLAG_Click

'MARKED FOR PRINTING
If Me.PRINTFLAG.Value = True Then
Me.PROCDATE.Value = Forms!DATEPREP![Text0]
End If

'UNMARKING PRINT FLAG
If Me.PRINTFLAG.Value = False Then
Me.PROCDATE.Value = ""
Me.[DOSE DATE].Value = ""
End If

If Me.PRINTFLAG.Value = True Then
'MONDAY
If Forms![DATEPREP]!Text0 = Weekday(Date + 1) Then
If Me.THU.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 3
End If
If Me.FRI.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 4
End If
If Me.SAT.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 5
End If
If Me.SUN.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 6
End If
If Me.MON.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 7
End If
If Me.TUE.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 8
End If
If Me.WED.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 9
End If
If Me.PRINTFLAG.Value = False Then
Me.PROCDATE.Value = ""
End If
End If

'TUESDAY
If Weekday(Date) = 3 Then
If Me.THU.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 2
End If
If Me.FRI.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 3
End If
If Me.SAT.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 4
End If
If Me.SUN.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 5
End If
If Me.MON.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 6
End If
If Me.TUE.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 7
End If
If Me.WED.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 8
End If
If Me.PRINTFLAG.Value = False Then
Me.PROCDATE.Value = ""
End If
End If

'WEDNESDAY
If Weekday(Date) = 4 Then
If Me.THU.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 1
End If
If Me.FRI.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 2
End If
If Me.SAT.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 3
End If
If Me.SUN.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 4
End If
If Me.MON.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 5
End If
If Me.TUE.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 6
End If
If Me.WED.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 7
End If
If Me.PRINTFLAG.Value = False Then
Me.PROCDATE.Value = ""
End If
End If

'THURSDAY
If Weekday(Date) = 5 Then
If Me.THU.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 7
End If
If Me.FRI.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 1
End If
If Me.SAT.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 2
End If
If Me.SUN.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 3
End If
If Me.MON.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 4
End If
If Me.TUE.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 5
End If
If Me.WED.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 6
End If
If Me.PRINTFLAG.Value = False Then
Me.PROCDATE.Value = ""
End If
End If

'FRIDAY
If Weekday(Date) = 6 Then
If Me.THU.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 6
End If
If Me.FRI.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 7
End If
If Me.SAT.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 1
End If
If Me.SUN.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 2
End If
If Me.MON.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 3
End If
If Me.TUE.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 4
End If
If Me.WED.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 5
End If
If Me.PRINTFLAG.Value = False Then
Me.PROCDATE.Value = ""
End If
End If
End If

Exit_PRINTFLAG_Click:
Exit Sub

err_PRINTFLAG_Click:
MsgBox "YOU HAVE NOT DEFINED THE PROCESSING DATE. PLEASE DO SO ON THE
NEXT SCREEN THAT APPEARS, THEN CONTINUE PROCESSING LABELS. YOU WILL NEED TO
RE-MARK THIS LABEL FOR PRINTING"
Me.PRINTFLAG.Value = False
DoCmd.OpenForm "DATEPREP"
Resume Exit_PRINTFLAG_CLICK
Exit Sub

End Sub
========================
 
Let me make sure what your form looks like and what you want to do.

Your form contains checkboxes indicating what dates a patient is supposed to
get medication, and presumably the type and dosage of the medication.

The clinician is responsible for printing the labels that go on the dosage
cups or bottles, but only prints labels for the next week. So, depending on
what day he proceses the lables, you want to print the labels for the entire
next week. Therefore, the big question is what dates should be on the
label.

What fields do you have in your database? How are you filling the
checkboxes that indicate what day the dose is for? Personnally, I think I
would probably use either an option group with radio buttons instead of
checkboxes or a combo box to display which day of the week the dose is for,
since you can only have one dose per record. Lets assume you are going to
use an option group. Once you have done that, you won't need lots of tests.
The first thing you would need to do is determine whether the weekday of
[ProcDate] is less than or greater than the weekday for the record you are
trying to print. Once you have done that, all you have to do is determine
how many days to add to [ProcDate] to get the [DoseDate].

Dim intAddDays as integer
IF WeekDay([ProcDate]) < me.og_DoseDay then
intAddDays = me.og_DoseDay - Weekday([ProcDate])
Else
intAddDays = me.og_DoseDay + 7 - Weekday([ProcDate])
endif
Me.DoseDate = DateAdd("d", intAddDays, me.ProcDate)

Hope this explaination helps. If you need more detail, let me know

Dale


DubboPete said:
Hi all,

I am trying to dynamically calculate dates, to populate a field called
[DOSEDATE]

The calculations work like this.
Patients take away doses of medicine, on particular days of the [Monday to
Friday] week. The medicine doses are prepared on any day between Monday
and Friday.

1st scenario:
Clinician signs into database on Monday 12th July, and declares Wednesday
14th July as [PROCDATE]

***This is done on a form [DATEPREP] with [Text0] set to date/time, and
Text0's After Update event hides the [DATEPREP] form and opens up Patient
Records form [DUBBOLIST]***

Patient 'A' has one record, which indicates a dose of medicine is required
as a takeaway each Saturday.
Clinician opens first patient record, and sees that checkbox [SAT] is
selected. Clinician marks record for printing (checkbox) and prints 1
label on Monday 12th July. The label contains the date that the dose is
intended for, in this scenario Saturday 17 July.

Pharmacist prepares dose on Wednesday 14th July, in accordance with label
Patient 'A' attends clinic on Thursday and takes home a dose of medicine for
Saturday 17 July


2nd scenario
Clinician signs into database on Tuesday 13th July, and declares Wednesday
14th July as [PROCDATE]
Pataient 'B' has three records, one for each day that a dose of medicine is
required

Clinician opens first patient record, and sees that checkbox [SAT] is
selected. Clinician marks record for printing (checkbox)
Clinician opens second patient record, and sees that checkbox [MON] is
selected. Clinician marks record for printing (checkbox)
Clinician opens third patient record, and sees that checkbox [WED] is
selected. Clinician marks record for printing (checkbox)

Clinician then prints 3 labels on Tuesday 13th July. Each label contains
the date that the dose is intended for, 17th, 19th and 21st of July
respectively.

Pharmacist prepares doses on Friday 16th July, in accordance with labels
Patient 'B' attends clinic on Friday 16th and takes home three doses of
medicine, one for Saturday 17 July, one for Monday 19th July and one for
Wednesday 21st July.

What I need help with is code to capture the weekday part of the [PROCDATE]
and calculate the [DOSEDATE].

Only one record is allowed for any one dose. Only one dosing date checkbox
is allowed to be clicked at any one time, so there is no doubling up of
dosing dates for one record. This is because the medicine might be
different on any subsequent dose, in either type or quantity, and the
clinician and pharmacist cannot be allowed to double-dose for any one day.

So if the [PROCDATE] is Weekday=4 (Wednesday, 14 July), and the [DOSEDATE]
is Weekday=7 (Saturday 18 July), I need for the [DOSEDATE] field to be
updated dynamically. This should be done when the record is 'MARKED FOR
PRINTING' ([PRINTFLAG] checkbox value = true)

I thought this might work, but the [DOSEDATE] is not getting updated... If
someone could check the code and steer me in the right direction I'd
appreciate it... here's the code for the [PRINTFLAG] checkbox

thanks in anticipation
and for those that don't read beyond this point, thanks for your patience!!
:-)
DubboPete

=================================
Private Sub PRINTFLAG_Click()
On Error GoTo err_PRINTFLAG_Click

'MARKED FOR PRINTING
If Me.PRINTFLAG.Value = True Then
Me.PROCDATE.Value = Forms!DATEPREP![Text0]
End If

'UNMARKING PRINT FLAG
If Me.PRINTFLAG.Value = False Then
Me.PROCDATE.Value = ""
Me.[DOSE DATE].Value = ""
End If

If Me.PRINTFLAG.Value = True Then
'MONDAY
If Forms![DATEPREP]!Text0 = Weekday(Date + 1) Then
If Me.THU.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 3
End If
If Me.FRI.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 4
End If
If Me.SAT.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 5
End If
If Me.SUN.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 6
End If
If Me.MON.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 7
End If
If Me.TUE.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 8
End If
If Me.WED.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 9
End If
If Me.PRINTFLAG.Value = False Then
Me.PROCDATE.Value = ""
End If
End If

'TUESDAY
If Weekday(Date) = 3 Then
If Me.THU.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 2
End If
If Me.FRI.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 3
End If
If Me.SAT.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 4
End If
If Me.SUN.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 5
End If
If Me.MON.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 6
End If
If Me.TUE.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 7
End If
If Me.WED.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 8
End If
If Me.PRINTFLAG.Value = False Then
Me.PROCDATE.Value = ""
End If
End If

'WEDNESDAY
If Weekday(Date) = 4 Then
If Me.THU.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 1
End If
If Me.FRI.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 2
End If
If Me.SAT.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 3
End If
If Me.SUN.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 4
End If
If Me.MON.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 5
End If
If Me.TUE.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 6
End If
If Me.WED.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 7
End If
If Me.PRINTFLAG.Value = False Then
Me.PROCDATE.Value = ""
End If
End If

'THURSDAY
If Weekday(Date) = 5 Then
If Me.THU.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 7
End If
If Me.FRI.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 1
End If
If Me.SAT.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 2
End If
If Me.SUN.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 3
End If
If Me.MON.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 4
End If
If Me.TUE.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 5
End If
If Me.WED.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 6
End If
If Me.PRINTFLAG.Value = False Then
Me.PROCDATE.Value = ""
End If
End If

'FRIDAY
If Weekday(Date) = 6 Then
If Me.THU.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 6
End If
If Me.FRI.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 7
End If
If Me.SAT.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 1
End If
If Me.SUN.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 2
End If
If Me.MON.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 3
End If
If Me.TUE.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 4
End If
If Me.WED.Value = True Then
Me.[DOSE DATE].Value = PROCDATE + 5
End If
If Me.PRINTFLAG.Value = False Then
Me.PROCDATE.Value = ""
End If
End If
End If

Exit_PRINTFLAG_Click:
Exit Sub

err_PRINTFLAG_Click:
MsgBox "YOU HAVE NOT DEFINED THE PROCESSING DATE. PLEASE DO SO ON THE
NEXT SCREEN THAT APPEARS, THEN CONTINUE PROCESSING LABELS. YOU WILL NEED TO
RE-MARK THIS LABEL FOR PRINTING"
Me.PRINTFLAG.Value = False
DoCmd.OpenForm "DATEPREP"
Resume Exit_PRINTFLAG_CLICK
Exit Sub

End Sub
========================
Click to expand...
 
Hi Dale,

Option group isn't an option here (no pun intended), as it gives the
clinician the freedom to change the dosing days. No can do, as the days are
defined by the prescription from the GP. I have to have the seven
checkboxes representing each day of the week, and the selections of these
can only be changed by the Nurse Unit Manager.
What you have given me though works great as an option group! :-)

If that code was for the range of checkboxes, then it would fit the bill
perfectly!

cheers
DubboPete
 
You could lock the field for the option group, so that it displays as a OG,
but doesn't permit it to be changed from that screen.

However, assuming that you don't want to do that, and if I understood your
previous post correctly, then only one of the checkboxes can be checked for
a given record. Is that correct?

If so, label them chkDose1, chkDose2, ..., chkDose7, where the numbers
correspond to whatever convention you are useing for the dates. Then create
a loop to determine which checkbox is checked, then drive on as though you
had a option group. Something like:

Dim intDay as integer
For intDay = 1 to 7
If me.Controls("chkDose" & intDay) Then Exit For
Next

insert code here similiar to what I gave you in my previous post, except
refer to the Controls collection of the form.

HTH
Dale
 
Hi Dale,

Hope you are still following this thread...:-)

I understand everything you say, except I am stuck on the
last sentence.
Now that I have recreated the seven fields as check boxes
they are no longer grouped per se. yet you refer to
<snip>"except refer to the Controls collection of the
form" </snip>

I inserted the code that you last sent for the loop, and
the error that occurred was that Me.Controls was not
defined.

I feel it is very close to being cracked, yet I don't
know how to group the seven checkboxes into Me.Controls

regards
Pete
 
Hi Dale

Further, this is what I have in position, and it returns "Argument not
optional"

any clues?

Pete
code>>>>

Dim intAddDays As Integer
Dim intDay As Integer
For intDay = 1 To 7
If Me.Controls("chkDose" & intDay) Then Exit For
Next

If Weekday([PROCDATE]) < Me.Controls Then
intAddDays = Me.Controls - Weekday([PROCDATE])
Else
intAddDays = Me.Controls + 7 - Weekday([PROCDATE])
End If
Me.[Dose Date] = DateAdd("d", intAddDays, Me.PROCDATE)
 
Pete,

Try:

Dim intAddDays As Integer
Dim intDay As Integer
Dim bFound as boolean

bFound = False
For intDay = 1 To 7
If Me.Controls("chkDose" & intDay) = True Then
bFound = True
Exit For
endif
Next

'make sure that one of the checkboxes was checked
If not bFound Then
msgbox "None of the checkboxes are checked!"
Exit Sub
End if

If Weekday([PROCDATE]) < intDay Then
intAddDays = intDay - Weekday([PROCDATE])
Else
intAddDays = intDay + 7 - Weekday([PROCDATE])
End If
Me.[Dose Date] = DateAdd("d", intAddDays, Me.PROCDATE)

***************
If this still gives an error, indicate in which row the error occurs.

HTH
Dale
 
Hi Dale,

It falls over at this point...

If Weekday([PROCDATE]) < intDay Then

Returns error code 19 - "invalid use of null"

cheers
Pete
 
Pete,

That means that your [procdate] field contains a null value, which cannot be
interpreted by the Weekday function.

You need to check that the field [ProcDate] is filled in before executing
this code. You will need to refresh my memory, as I no longer have access
to your earliest messages. At what point is this code being run? At the
beginning of the procedure, you need to have a sequence of statements
something like the following to test and make sure that the text box
containing the ProcDate field has been entered. Also, depending on where
the code is executed, the current record may not have been updated yet,
which means that instead of testing for the field value, you should check
for the value of the control that holds the data. So, assuming you have a
text box control (txt_ProcDate) on the form that is bound to the ProcDate
field, change all the references to [ProcDate] to me.txt_ProcDate.

If LEN([ProcDate] & "") = 0 then
msgbox "Fill in the procedure date!"
me.txtProcDate.Set focus 'assumes that the procdate is in a text box
with the name txt_ProcDate
Exit Sub 'assumes this is in a subroutine
end if

HTH
Dale


DubboPete said:
Hi Dale,

It falls over at this point...

If Weekday([PROCDATE]) < intDay Then

Returns error code 19 - "invalid use of null"

cheers
Pete

Dale Fye said:
Pete,

Try:

Dim intAddDays As Integer
Dim intDay As Integer
Dim bFound as boolean

bFound = False
For intDay = 1 To 7
If Me.Controls("chkDose" & intDay) = True Then
bFound = True
Exit For
endif
Next

'make sure that one of the checkboxes was checked
If not bFound Then
msgbox "None of the checkboxes are checked!"
Exit Sub
End if

If Weekday([PROCDATE]) < intDay Then
intAddDays = intDay - Weekday([PROCDATE])
Else
intAddDays = intDay + 7 - Weekday([PROCDATE])
End If
Me.[Dose Date] = DateAdd("d", intAddDays, Me.PROCDATE)

***************
If this still gives an error, indicate in which row the error occurs.
Click to expand...
Click to expand...
 
Dale,

IT WORKS!

Thanks so much for your help and guidance! It is really appreciated!

regards
DubboPete

Dale Fye said:
Pete,

That means that your [procdate] field contains a null value, which cannot be
interpreted by the Weekday function.

You need to check that the field [ProcDate] is filled in before executing
this code. You will need to refresh my memory, as I no longer have access
to your earliest messages. At what point is this code being run? At the
beginning of the procedure, you need to have a sequence of statements
something like the following to test and make sure that the text box
containing the ProcDate field has been entered. Also, depending on where
the code is executed, the current record may not have been updated yet,
which means that instead of testing for the field value, you should check
for the value of the control that holds the data. So, assuming you have a
text box control (txt_ProcDate) on the form that is bound to the ProcDate
field, change all the references to [ProcDate] to me.txt_ProcDate.

If LEN([ProcDate] & "") = 0 then
msgbox "Fill in the procedure date!"
me.txtProcDate.Set focus 'assumes that the procdate is in a text box
with the name txt_ProcDate
Exit Sub 'assumes this is in a subroutine
end if

HTH
Dale


DubboPete said:
Hi Dale,

It falls over at this point...

If Weekday([PROCDATE]) < intDay Then

Returns error code 19 - "invalid use of null"

cheers
Pete

Dale Fye said:
Pete,

Try:

Dim intAddDays As Integer
Dim intDay As Integer
Dim bFound as boolean

bFound = False
For intDay = 1 To 7
If Me.Controls("chkDose" & intDay) = True Then
bFound = True
Exit For
endif
Next

'make sure that one of the checkboxes was checked
If not bFound Then
msgbox "None of the checkboxes are checked!"
Exit Sub
End if

If Weekday([PROCDATE]) < intDay Then
intAddDays = intDay - Weekday([PROCDATE])
Else
intAddDays = intDay + 7 - Weekday([PROCDATE])
End If
Me.[Dose Date] = DateAdd("d", intAddDays, Me.PROCDATE)

***************
If this still gives an error, indicate in which row the error occurs.
Click to expand...
Click to expand...
Click to expand...
 
Glad I could help.

DubboPete said:
Dale,

IT WORKS!

Thanks so much for your help and guidance! It is really appreciated!

regards
DubboPete

Dale Fye said:
Pete,

That means that your [procdate] field contains a null value, which
Click to expand...
cannot
be
interpreted by the Weekday function.

You need to check that the field [ProcDate] is filled in before executing
this code. You will need to refresh my memory, as I no longer have access
to your earliest messages. At what point is this code being run? At the
beginning of the procedure, you need to have a sequence of statements
something like the following to test and make sure that the text box
containing the ProcDate field has been entered. Also, depending on where
the code is executed, the current record may not have been updated yet,
which means that instead of testing for the field value, you should check
for the value of the control that holds the data. So, assuming you have a
text box control (txt_ProcDate) on the form that is bound to the ProcDate
field, change all the references to [ProcDate] to me.txt_ProcDate.

If LEN([ProcDate] & "") = 0 then
msgbox "Fill in the procedure date!"
me.txtProcDate.Set focus 'assumes that the procdate is in a text box
with the name txt_ProcDate
Exit Sub 'assumes this is in a subroutine
end if

HTH
Dale


DubboPete said:
Hi Dale,

It falls over at this point...

If Weekday([PROCDATE]) < intDay Then

Returns error code 19 - "invalid use of null"

cheers
Pete

Pete,

Try:

Dim intAddDays As Integer
Dim intDay As Integer
Dim bFound as boolean

bFound = False
For intDay = 1 To 7
If Me.Controls("chkDose" & intDay) = True Then
bFound = True
Exit For
endif
Next

'make sure that one of the checkboxes was checked
If not bFound Then
msgbox "None of the checkboxes are checked!"
Exit Sub
End if

If Weekday([PROCDATE]) < intDay Then
intAddDays = intDay - Weekday([PROCDATE])
Else
intAddDays = intDay + 7 - Weekday([PROCDATE])
End If
Me.[Dose Date] = DateAdd("d", intAddDays, Me.PROCDATE)

***************
If this still gives an error, indicate in which row the error occurs.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top