Date calculation and text values

[Paul Cooke]

Hi all, the following is a request for some code please - I don't like asking striaght out for code but what I need it for is critical to a person's medical records and I don't want to mess it up and to be honest I don't have a clue where to start!

I have a Subform set as Continuous which records a patient's vaccination history.
The Subform has the following controls

1.cboVaccineProdID (fk from VacccinePrdTbl using a Select Qry as the source)
2.cboRoute (a value list)
3.txtDateGiven (simple date control)
4.cboDoseNumber (a value list)
5.txtExpiryDate (simple date control)
6.txtBoosterRequired (simple date control)
7.txtBoosterExpiry (simple date control)

What I need is code to calculate some dates based on the product code that is chosen.

So for example if cboVaccineProdID = HAVR001 and cboDoseNumber = 1 and txtDateGiven = 01/01/10

then

txtExpiryDate = 12 months later
txtBoosterRequired = 6 months later
txtBoosterExpiry = 12 months later

For explanation the reasoning behind this is the HAVR001 vaccine first dose lasts for 1 year, a booster is required 6 months after the first dose is given, if thay don't have a booster after 12 months from the first dose given they have to start the course of vaccines again.

I have quiet a few of these to code all with different time 'sets' but would assume it would be a simple case of just changing the 'variables' in the 'base code' so to speak?


I really would be grateful for help on this as I said I can not afford to get it wrong.

Thank you vey much.

 

[John Big Booty]

Why not simply store those three trigger points as additional (numeric) fields in your Vaccines table? It should then be a relatively simple matter to compare that against the patient vaccination record.

 

[Paul Cooke]

Hi John - sorry I do not have a clue what you mean !! my access knowledge is very limited I only know what I know now thanks to guys like you !!

could you explain please?

 

[DCrake]

In your vaccine table you need to store the number of days between the initial jab to the booster date etc. Then use the DateAdd() function to calculate the exact date when the next jab is required.

txtExpiryDate = 12 months later
txtBoosterRequired = 6 months later
txtBoosterExpiry = 12 months later

txtExpiryDate = DateAdd("yyyy",n,TxtDateGiven)
txtBoosterRequired = DateAdd("m",n,TxtDateGiven)
txtBoosterExpiry = DateAdd("yyyy",n,TxtDateGiven)

Where n is the value taken from the recrod for the vaccine

 

[John Big Booty]

I presume you currently have a table called TB_Vaccines, that looks something like;
TB_Vaccines
VaccineID (PK)
VaccineName

Perhaps there are some other fields as well.

My suggestion is that you add three additional numeric fields called (say);
VaccExpires Numeric value indicating the number of month after initial vaccination that protection becomes infective
BoosterRequired Numeric value indicating the number of month after initial vaccination that a booster is required
BoosterExpiry Numeric value indicating the number of month after initial vaccination that a booster shot becomes infective.

Now the VaccineID would be stored in your patient Vaccination record along with the date of vaccination. So you can use the DLookup() function to retrieve those figures for any given vaccine and add them to the vaccination date using the DateAdd() function and compare that date with the current Date() using the DateDiff() function.

 

[vbaInet]

Paul, short and sweet draws more attention . This was all the information we required:

What I need is code to calculate some dates based on the product code that is chosen.

So for example if cboVaccineProdID = HAVR001 and cboDoseNumber = 1 and txtDateGiven = 01/01/10

then

txtExpiryDate = 12 months later
txtBoosterRequired = 6 months later
txtBoosterExpiry = 12 months later

public function functionName(strDateGiven as Date)
     select case Nz(cboVaccineProdID, vbNullString)
          case "HAVR001"
               if nz(cboDoseNumber, 0) = 1 Then
                    txtExpiryDate = DateAdd("m", 12, txtDateGiven)
                    txtBoosterRequired = DateAdd(...)
                    txtBoosterExpiry = txtExpiryDate
               else
                    ... some code here perhaps ...
               end if
          case else
               ... some code here perhaps ...
     end select
end function

Edit: looks like I was extremely slow :O Weekend!!

 

[DCrake]

Seems that me and John have the same thoughts, combine the two for an answer.

 

[Paul Cooke]

thanks all for the repllies - I think I will try both options to see what is easier for me.

I think I understand what you mean John & David so will try that first but I'm not 100% sure I am firstly competentant enough to set this up and secondly the details I put in the original post were just for one simple vaccine, other vaccines have multiple doses and multiple boosters and to confuse things even more some boosters can be required earlier than the 'norm' depending on what vaccine regieme is being given. For example HepB vaccine is 3 doses given over 6 months but it can be fast-tracked to be given over 21 days

Whereas I do understand VBA 's post and accept that this may take more time to write for each possible variable I have but as said I undertand it !

sorry for the long original post VBA - mearly trying to give a full ish! explanation of what i was doing

Many thanks again guys I will post back with the results later

Paul

 

[Paul Cooke]

VBA

Firstly I think I understand about the Private Function - needed to return a calculation? But where do I put it? i.e Form Current / Load on on the txtDateGiven control ect..

Also the cboVaccineProductID control is sourced from a select query formatted with column count as 3, column width 0;2 and list width 12. This is so that as soon as a user enters a code the cbo also shows them the acutal vaccine product in the list.

So am i right in changing the Select Case line to ?

Select Case Nz([cboVaccineProductID].[Column](1), vbNullString)

At the moment (uusinf both select case lines) as soon as I enter the cboVaccineProductID an error message appears saying "Procedure Declaration does not match description of event or procedure having the same name)

What I have currently in the afterUpdate event is

Private Function txtDateGiven_AfterUpdate(strDateGiven As Date)

Select Case Nz([cboVaccineProductID].[Column](1), vbNullString)

Case "EPAX001"
If Nz(cboDoseNumber, 0) = 1 Then

txtExpiryDate = DateAdd("M", 12, txtDateGiven)
txtBoosterDate = DateAdd("M", 6, txtDateGiven)
txtBoosterDateExpiry = DateAdd("M", 12, txtDateGiven)
End If

End Select
End Function

Any ideas?

many thanks

 

[Paul Cooke]

ooops - sorry I have just noticted it is a Public Function - not privae - sorry back to the drawing board !

 

[Paul Cooke]

hopefully final question on this one

where do I put the function on the controls in the form. I have tried the following but nothing is coming up this was put in the control sourse of txtExpiryDate

=VacDate([txtDateGiven])

Where VacDate is the functions name

many thanks

 

[vbaInet]

Well it depends on when you want it to display. So I would imagine it should be called in the After Update event of txtDateGiven and the Current event of the form. Amended code:

Private Function VacDate()

    Select Case Nz(Me.cboVaccineProductID.Column(1), vbNullString)
    
        Case "EPAX001"
            If Nz(cboDoseNumber, 0) = 1 Then
                txtExpiryDate = DateAdd("m", 12, txtDateGiven)
                txtBoosterDate = DateAdd("m", 6, txtDateGiven)
                txtBoosterDateExpiry = txtExpiryDate
            End If
        Case Else
            ' do nothing
    End Select
    
End Function

If EPax001 is in the second column of the combo box then that's correct.

For example, in the After Update event of txtDateGiven, you call it like this:

Private Sub txtDateGiven()
    Call VacDate
End Sub

 

[Paul Cooke]

Many Thanks VBA

thats in pllace now but I am getting a complie error 'Arguments not optional'

any ideas on this?

 

[vbaInet]

You didn't copy and paste the code in full.

Before the amendment it was

Public Function VacDate([COLOR=Red]strDateGiven As Date[/COLOR])

and after the the amendment it became:

Private Function VacDate()

 

[Paul Cooke]

I really must open my eyes !! sorry ! will post back with the results - thanks again

 

[Paul Cooke]

sorry me again now I getting the error

'Invalid use of Me.'

Select Case Nz(Me.cboVaccineProductID.Column(1), vbNullString)

 

[vbaInet]

Is cboVaccineProductID not the name of the combo box?

Plus the code must be in the form module, not in a separate module.

 

[Paul Cooke]

Yes it is I have just double checked it.

Not sure if it makes a difference but the control is in a subform called VaccineConsumablesSubform ?

 

[vbaInet]

But like I mentioned, the code must be in the form's module. You have put it in a Module or a Class.

 

[Paul Cooke]

Ahh this is where my lack of knowledge shows up soo much - I thought I had put it there but didn't actually know the difference - I really apprciate the help you and others give people like me !!