Hi Allen,
I found the ELookup procedure, created a standard module and save it as
ELookUp. I t is visible in the Modules section of the Database Window. In
the VB Window it said "Option Compare Database" and I pasted the
information
below. Should it be title something different then "Option Compare
Database"?
When I open a query builder window, looked under Functions, there are 2
folders, Built In and Acuity. ELookup appears in the Acuity folder. In
the
Visual Basic Window I clicked Objects Browser, ELookUp does appear in the
global list for All Libraries. There is also a reference to the DAO
libraries.
ID Field [czn_fk] is text, example {97gj856kmd}. When I add the ELookUp
to
my SQL Statement Builder:
PriorHousing: ELookup("Housing","Acuity","([czn_fk] = """ & [czn_fk] &
""")
AND ([AcDate] > " & Format([AcDate],"\#mm\/dd\/yyyy hh\:nn\:ss\#") &
")","[AcDate] DESC")
I get an error message when I try to run it. "Undefined function 'ELookUp'
in expression"
What am I missing?
Thanks for being patient with me.
--
Thanks
Jeff
:
Hi,
czn_fk is the replicated ID field (Primary Key) which is a text field.
Do I
need to create a seperate Number ID field? If I have to do that, how
does
the program know the difference between clients? I will give your
suggestions
a try tomorrow.
Have a good night.
--
Thanks
Jeff
:
To use the ELookup() function, you need to copy the code from the web
page
and paste it into a standard module in your database (through the
Modules
tab of the Database window.
You need a reference to the DAO Library, which is there by default in
all
versions of Access except 2000 and 2002. More info on references:
http://allenbrowne.com/ser-38.html
The request for a Parameter means that Access is unable to resolve one
of
the names or arguments correctly. You need a field named Housing, in a
table
named Acuity. If czn_fk is a Text type field, you need exta quotes:
"([czn_fk] = """ & [czn_fk] & """) AND
I have no idea what your Function contains, what its name is, what
libraries
you need for it, or whether it is defined as Public in a standard
module and
uniquely named so that you can call it in a query.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Hi Allen,
Thanks for spending some time with me. I just want to be clear and I
will
use the following to and I will use a real senerio:
Function: Find changes in total populations changes for the field
Housing.
Client ID is a replicated number which is also Primary
AcDate: is Date of Acuity
Housing: is field to I want to see how many change increased and
decreased
on Total client population
PriorHousing: ELookup("Housing","Acuity","([czn_fk] = " & [czn_fk] &
")
AND
([Acdate] > " & Format([Acdate],"\#mm\/dd\/yyyy hh\:nn\:ss\#") &
")","[Acdate] DESC")
I get an error as undefined function when it is typed as is in a
report.
I
tried placing it in both the report and in the SQL statement builder.
When
in the report the error is in valid control source. Is this for a
form
only
or do I need to import or add a library for ELookUp. It also appear
to be
missing a paran, 3 opens & 4 close.
--
Thanks
Jeff
:
If you are using a primary key that consists of a Replication ID
field,
the
values will not be sequential, so you cannot use this field to
determine
which is the "previous" record.
You will need another field, such as a date/time type field, to
determine
when the record was added. For any record you can the retrieve the
value
of
the most recent record (based on the date time field) that matches
the
client.
You may need to use this extended version of DLookup() to achieve
that:
http://allenbrowne.com/ser-42.html
The expression to type into a fresh column in the Field row of your
query
would be something like this:
PriorAcuity: ELookup("Acuity", "Table1", "([ClientID] = " &
[ClientID] &
") AND ([EntryDateTime] > " & Format([EntryDateTime],
"\#mm\/dd\/yyyy hh\:nn\:ss\#") & ")", "[EntryDateTime] DESC")
Hi Allen,
I am still stuck. When I did the DLookUp, all I get are nulls.
The
client
ID, which is a replicated ID begins and ends with { }. Is that
the
cause
of
the nulls? Or is housing field, which may include nulls because
the
case
manager did not answer that particular item? The client ID is a
Primary
field so it does not have any null values.
--
Thanks
Jeff
:
Thanks Allen, I will give this a try to see if it gives me the
result
that I
need.
--
Thanks
Jeff
:
Sounds like you need to compare the one record to the value in
the
previous
one.
See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504/en-us
I have request to find out changes in total population. I
have a
database
with mutiple clients, each of these clients have an Acuity
every
so
often
based on a time period, so the are going to multiple records
for
each
client.
Let's say, I need to find out how many client went up or down
from
there
last Acuity on housing needs. Each record has an Acuity
Date as
a
primery
field.
I think this can be done if an Iff statement, just not sure
how to
do
it.
Can you help me?
