I need help using SQL code to populate control in an event

[Ted]

given: i am using a2k.

i have a form which has a txtcontrl that is either 'Yes' or 'No' called
LTFU. if LTFU is 'Yes' then (i think) the on current event VBA will enter
the results of

SELECT [Patients on F/U].[Last Name], [Patients on F/U].MR_Number,
Schedules.[IRB#],
IIf(DatePart('w',DateAdd("m",[Months],[RegisteredDate]))=7,DateAdd("m",[Months],[RegisteredDate])-1,IIf(DatePart('w',DateAdd("m",[Months],[RegisteredDate]))=1,DateAdd("m",[Months],[RegisteredDate])+1,DateAdd("m",[Months],[RegisteredDate]))) AS [Next F/U Due3], MasterTable.Months
FROM Schedules INNER JOIN (MasterTable INNER JOIN [Patients on F/U] ON
MasterTable.[Dummy Number] = [Patients on F/U].[Dummy Number]) ON
(Schedules.[IRB#] = [Patients on F/U].[IRB#]) AND (Schedules.Visit =
MasterTable.Months)
ORDER BY [Patients on F/U].[Last Name],
IIf(DatePart('w',DateAdd("m",[Months],[RegisteredDate]))=7,DateAdd("m",[Months],[RegisteredDate])-1,IIf(DatePart('w',DateAdd("m",[Months],[RegisteredDate]))=1,DateAdd("m",[Months],[RegisteredDate])+1,DateAdd("m",[Months],[RegisteredDate])));


into a control called something like 'FollowUpDate'

if/when LTFU is coded 'No' then 'FollowUpDate' will get the valued of
'FollowUpDate' using

SELECT [Test of Query1].[Last Name], [Patients on F/U].MR_Number, [Test of
Query1].[IRB#],
IIf(DatePart('w',DateAdd("m",[Months],[RegisteredDate]))=7,DateAdd("m",[Months],[RegisteredDate])-1,IIf(DatePart('w',DateAdd("m",[Months],[RegisteredDate]))=1,DateAdd("m",[Months],[RegisteredDate])+1,DateAdd("m",[Months],[RegisteredDate]))) AS [Next F/U Due3], MasterTable.Months
FROM ([Patients on F/U] INNER JOIN [Test of Query1] ON [Patients on
F/U].[IRB#] = [Test of Query1].[IRB#]) INNER JOIN MasterTable ON [Patients on
F/U].[Dummy Number] = MasterTable.[Dummy Number]
ORDER BY [Test of Query1].[Last Name], MasterTable.Months;

i know that these are hairy looking SQL strings, but leaving aside for the
moment the 'deep' meaning of each', is it possible to clue me in on what the
overriding principle would be (using perhaps a simpler representation of two
separate SELECT queries)?

tia!

 

[Tim Ferguson]

i know that these are hairy looking SQL strings, but leaving aside for
the moment the 'deep' meaning of each', is it possible to clue me in

i have a form which has a txtcontrl that is either 'Yes' or 'No'
called LTFU. if LTFU is 'Yes' then (i think) the on current event VBA
will enter the results of

.... followed by a SELECT query that returns 5 columns and an
indeterminate number of rows: this cannot be entered

into a control called something like 'FollowUpDate'

unless it's a listbox or something... Ditto for the "No" event. The SQL
is only hairy because of the ridiculous naming system, and the one large
calculated column, which may or may not be simplifiable.

What do you want from an "overriding principle" exactly?

B Wishes


Tim F

 

[Ted]

i perhaps ought to have whittled the queries down to size. you are not wrong
in your assessment .... can we just think of two distinct SQL queries each
of which selects a 'NextFUDate' for the current patient, the value of
'NextFUDate' being stored in a text control on the form called
'FollowUpDate'. as before, exactly which query gets chosen is dependent upon
the value of 'LTFU'?

make more sense?

 

[Tim Ferguson]

make more sense?

Not exactly.

I think this is coming to the use of a parameterised query, along the
lines of

parameters CurrentPatientID numeric;
select top 1 NextFUDate
from Visits
where PatientID = CurrentPatientID
order by VisitDate Desc;

.... yes, I know this does not suit your table layout; but the point is
that it returns one value from one record (actually it could still return
<null>, but that's another story). If you can define your query to return
exactly one result, then you'll be quids in.

My instinct, by the way, is always to let the clinician decide when the
followup is _actually_ going to happen, even if the computer will offer a
suitable suggestion. There are too many complications like weekends
(which you are trying to pick up), holidays (how are you going to avoid a
FU on Easter Monday?), family needs and so on. Still -- it's your
business requirements, and anyway I would say that since I am a
clinician.

No, I don't know how to do write the query for you, if you have fields
named [Dummy Number] and tables like [Patients on F/U] <g>.

All the best


Tim F

 

[Ted]

In this A2K mdb there is a form/table called ‘Screening Log’. It contains
information about the identity of the patient (MR_Number), the study in which
the patient’s been enrolled (IRB_Number) and when (RegisteredDate) the
patient got enrolled on that study (btw – the same patient can appear
enrolled in more than one study since they might fail on some study and then
appear to be good candidates for a 2nd and a 3rd and so on). There are other
variables/fields/controls on ‘Screening Log’ but I don’t think they need
concern this discussion.

The users/consumers of this ‘Screening Log’ would like to have a lazy man’s
way of scheduling future visits for patients. It turns out that each study
(IRB_Number has its own Follow-Up Schedule; some might be geared to expect
patients to arrive 3, 6, 9, 12 months from their RegisteredDate. Others might
want them to appear at after 6,12,18, 24, 48 months. Anyway, you get the
picture. And then there’s this mother-of-all follow-ups schedule which has 29
tiers going out to 20 years (3,6,9,12,15,18,……204,216,228,240 months) to be
dealt with. Now, rather than have two tables with these profiles in them, as
I think a little more about it, I guess it would be possible to create a
single table which has a column that ids the IRB_Number as well as the
FU_Interval into which someone profiles every IRB_Number’s follow-up
schedule.

In case it is still not clear, what I am thinking about is adding the
ability to see the next follow-up visit’s date on the ‘Screening Log’ form.
At one point, I considered having a control on ‘Screening Log’ which would
use a SQL statement when the IRB_Number’s follow-up schedule did not comprise
the one with the 29 tiers (thus require looking into one table of profiles)
and to use another when it did. I was trying to avoid having the user create
additional entries for those studies which utilized the 29-tiered one. I
suppose that’d be elegant, but in the interest of moving forward anyway, it
might be less elegant and just as ultimately functional to have one SQL query
which handles the question of identifying the correct follow-up visit date
for a patient enrolled in a particular study given the constraint that it
must never be allowed to occur before the current date (the user is looking
at the ‘Screening Log’ on). I think once this query were written, and in such
a way as to guarantee that it provided the correct F/U date (based upon
using criteria which matched the IRB_Number value in the underlying profiles
table to the IRB_Number of the text control in the form) that that would do
the job. What do you think. I don’t know that this bears repeateing, but this
is not meant to generate some sort of form letters letting patients know the
EXACT date they’re expected; it’s just meant to serve as a rough ‘n ready
reminder to the study-personnel. Even the built-in aversion of Saturday and
Sunday is probably more than is actually necessary!

Your confusion is well taken, the field names that I used to create a
miniaturized database onto which I could project my experiments with
impugnity were probably kind of opaque to anyone else.

I think I probably have this thing ‘licked’ if I don’t use the initial idea
(involving the ‘LTFU’ criterion).

make more sense?

Not exactly.

I think this is coming to the use of a parameterised query, along the
lines of

parameters CurrentPatientID numeric;
select top 1 NextFUDate
from Visits
where PatientID = CurrentPatientID
order by VisitDate Desc;

.... yes, I know this does not suit your table layout; but the point is
that it returns one value from one record (actually it could still return
<null>, but that's another story). If you can define your query to return
exactly one result, then you'll be quids in.

My instinct, by the way, is always to let the clinician decide when the
followup is _actually_ going to happen, even if the computer will offer a
suitable suggestion. There are too many complications like weekends
(which you are trying to pick up), holidays (how are you going to avoid a
FU on Easter Monday?), family needs and so on. Still -- it's your
business requirements, and anyway I would say that since I am a
clinician.

No, I don't know how to do write the query for you, if you have fields
named [Dummy Number] and tables like [Patients on F/U] <g>.

All the best


Tim F

 

[Tim Ferguson]

In this A2K mdb there is a form/table called

Oops: first problem. A form is not the same as a table, and pretending
they are the same is likely to lead to trouble later on.

Tables are for modelling things in real life

Forms are for modelling processes that either do take place
in real life, or that ought to.

contains information about the identity of the patient (MR_Number),
the study in which the patient’s been enrolled (IRB_Number) and when
(RegisteredDate) the patient got enrolled on that study

Well, that looks like at least three tables to me: Patients, Enrolments,
and Studies. Probably need a fourth one for Visits too...

out that each study (IRB_Number has its own Follow-Up Schedule;

Right: another table of Schedules, then. If the algorithms are complex,
as you seem to suggest, it might take more than one table to work them
completely.

In case it is still not clear, what I am thinking about is adding the
ability to see the next follow-up visit’s date on the ‘Screening
Log’ form. At one point, I considered having a control on
‘Screening Log’ which would use a SQL statement

To be honest, a DLookUp() function is generally easier to use than doing
the whole Create the SQL, Open the Recordset, Get the Answer, Close the
Recordset stuff -- actually it's really just a convenient wrapper for the
same thing.

I suppose that’d be elegant,

Stuff the elegance; this is relational database world, where things are
done because they are Correct..!

Your confusion is well taken,

My confusion is irrelevant: it's you and the poor b****rs who will have
to hack their way through this in nine months' time who are going to
curse you. How good is your documentation?

Best of luck


Tim F

 

[Ted]

well.....i've decided to try another avenue. in my 'Screening Log' form's
On_Current event, i've added this VBA code:

Option Explicit

Private Sub Form_Current()
Me.FollowupDate.Value = "SELECT TOP 1
IIf(DatePart('w',DateAdd('m',[Months],[RegisteredDate]))=7,DateAdd('m',[Months],[RegisteredDate])-1,IIf(DatePart('w',DateAdd('m',[Months],[RegisteredDate]))=1,DateAdd('m',[Months],[RegisteredDate])+1,DateAdd('m',[Months],[RegisteredDate]))) AS FollowupDate" _
& "FROM [Screening Log] INNER JOIN MasterTable ON [Screening Log].[IRB
Number] = MasterTable.IRB_Number" _
& "WHERE ((([Screening Log].[IRB Number])=[Me].[IRB Number]) AND
(([Screening Log].MR_Number)=[Me].[MR_Number]));"
End Sub

wherein hopefully it would have the effect of populating the 'FollowUpDate'
control on 'Screening Log' form with the data selected by the 'SQL' code
displayed.

it's giving me the following error: Run time error 2113: The value you
entered isn't valid for this field.

when i hover my cursor over the left side of the equation, i.e.

Me.FollowupDate.Value

it says it's equal to 'Null'.

am i getting any close?

-ted

 

[Larry Linson]

well.....i've decided to try another avenue. in my 'Screening Log' form's
On_Current event, i've added this VBA code:

Option Explicit

Private Sub Form_Current()
Me.FollowupDate.Value = "SELECT TOP 1
IIf(DatePart('w',DateAdd('m',[Months],[RegisteredDate]))=7,DateAdd('m',[Months],[RegisteredDate])-1,IIf(DatePart('w',DateAdd('m',[Months],[RegisteredDate]))=1,DateAdd('m',[Months],[RegisteredDate])+1,DateAdd('m',[Months],[RegisteredDate])))
AS FollowupDate" _
& "FROM [Screening Log] INNER JOIN MasterTable ON [Screening Log].[IRB
Number] = MasterTable.IRB_Number" _
& "WHERE ((([Screening Log].[IRB Number])=[Me].[IRB Number]) AND
(([Screening Log].MR_Number)=[Me].[MR_Number]));"
End Sub

wherein hopefully it would have the effect of populating the
'FollowUpDate'
control on 'Screening Log' form with the data selected by the 'SQL' code
displayed.

it's giving me the following error: Run time error 2113: The value you
entered isn't valid for this field.

when i hover my cursor over the left side of the equation, i.e.

Me.FollowupDate.Value

it says it's equal to 'Null'.

am i getting any close?

-ted

No. You cannot execute an SQL statement in code in this manner, nor can you
set the Control Source of a Control to an SQL Statement. The Control Source
can be a reference to a Field in the Form's Record Source, or a valid Access
expression. The closest you could come to what you want is to open a
Recordset on an SQL statement in a user-defined-functiont that you use as
the expression in a Control Source or, perhaps, use a DLookup domain
aggregate function in the expression.

Larry Linson
Microsoft Access MVP

 

[Ted]

larry, i won't question 'why' this is so; suffice it to say i'm a vba
almost-newbie and would be helped by knowing _what_ you're talking about. can
you spell out what the code that _would_ work would be?

well.....i've decided to try another avenue. in my 'Screening Log' form's
On_Current event, i've added this VBA code:

Option Explicit

Private Sub Form_Current()
Me.FollowupDate.Value = "SELECT TOP 1
IIf(DatePart('w',DateAdd('m',[Months],[RegisteredDate]))=7,DateAdd('m',[Months],[RegisteredDate])-1,IIf(DatePart('w',DateAdd('m',[Months],[RegisteredDate]))=1,DateAdd('m',[Months],[RegisteredDate])+1,DateAdd('m',[Months],[RegisteredDate])))
AS FollowupDate" _
& "FROM [Screening Log] INNER JOIN MasterTable ON [Screening Log].[IRB
Number] = MasterTable.IRB_Number" _
& "WHERE ((([Screening Log].[IRB Number])=[Me].[IRB Number]) AND
(([Screening Log].MR_Number)=[Me].[MR_Number]));"
End Sub

wherein hopefully it would have the effect of populating the
'FollowUpDate'
control on 'Screening Log' form with the data selected by the 'SQL' code
displayed.

it's giving me the following error: Run time error 2113: The value you
entered isn't valid for this field.

when i hover my cursor over the left side of the equation, i.e.

Me.FollowupDate.Value

it says it's equal to 'Null'.

am i getting any close?

-ted

No. You cannot execute an SQL statement in code in this manner, nor can you
set the Control Source of a Control to an SQL Statement. The Control Source
can be a reference to a Field in the Form's Record Source, or a valid Access
expression. The closest you could come to what you want is to open a
Recordset on an SQL statement in a user-defined-functiont that you use as
the expression in a Control Source or, perhaps, use a DLookup domain
aggregate function in the expression.

Larry Linson
Microsoft Access MVP

 

[Tim Ferguson]

Me.FollowupDate.Value = "SELECT TOP 1

(by the way, if FollowupDate is a control it should be Me!FollowupDate

wherein hopefully it would have the effect of populating the
'FollowUpDate' control on 'Screening Log' form with the data selected
by the 'SQL' code displayed.

No... setting the .Value of a control puts the expression itself in the
control; in this example, the control is bound to a DateTime field, and
the string "SELECT TOP 1..." cannot be coerced into a date.

And I am not sure that this is tbe best place to set the Value -- it will
overwrite whatever value is already in the control when the form arrives
on the record, without warning the user. That is always a Bad Idea, in my
book. What about the Form_BeforeUpdate event or something a bit less
aggressive? Better still would be to use the Default Value to insert it
only when it's needed.

As Larry says, though, you need a VBA expression to supply the value: you
can get one in a variety of ways:

1) Use a simple expression like DLookup(): you can base this against a
stored querydef when the SQL is complex. For example, set up a new
query like

SELECT TOP 1 IIf(DatePart('w',DateAdd('m',[Months],[R...

and call it GetNextFUDate or something. Since you will only be
using this query when the form is open, you can use the GUI parser
to get the parameters. Swap the me.mr_number for something like
Forms!MyFollowupForm!mr_Number

Now you can use the DLookUp() function to get the thing you want by
setting the .Value or the .DefaultValue or whatever to

=DLookUp("FollowUpDate", "GetNextFUDate")

and it'll all work.

2) Use a user-defined function; just encapsulate the correct code
you have into something like

Public Function GetNextFUDate() As Date

dim jetSQL As String ...


and then put that in the appropriate property as above:

=GetNextFUDate()


3) Work it all out in code in a suitable form or control event and
poke in the _actual_ date value you want.

One thing strikes me in the middle of all this. Are there real patients
on the end of this database? What is going to happen if the system
suggests a follow up sometime in June 1945? Who gets to suffer: the
doctors, the patients or the pharmacology company? I know that the whole
of western medical science is built on a mythology of professional and
rigorous research, but even I get scared when the informatics base is
being put together by someone with such superficial understanding...

All the best


Tim F