Re: Report on Total Population changes

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


"Allen Browne" wrote:

> 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")
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Jeff" <Jeff@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:995A7097-532F-4988-A0B9-502DA557D1BF@xxxxxxxxxxxxxxxx
> > 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
> >
> >
> > "Jeff" wrote:
> >
> >> Thanks Allen, I will give this a try to see if it gives me the result
> >> that I
> >> need.
> >> --
> >> Thanks
> >> Jeff
> >>
> >>
> >> "Allen Browne" wrote:
> >>
> >> > 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
> >> >
> >> > "Jeff" <Jeff@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> > news:6DAC3329-7503-4BF4-B90A-DE0B57682B62@xxxxxxxxxxxxxxxx
> >> > >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?
>
>
>
.

Relevant Pages

  • Re: Report on Total Population changes
    ... If you are using a primary key that consists of a Replication ID field, ... client. ... all I get are nulls. ...
    (microsoft.public.access.reports)
  • Re: Report on Total Population changes
    ... are the KeyValue and FieldNameToGet the same? ... "Jeff" wrote: ... all I get are nulls. ... The client ID is a Primary ...
    (microsoft.public.access.reports)
  • Re: Report on Total Population changes
    ... all I get are nulls. ... Or is housing field, which may include nulls because the case ... The client ID is a Primary ... "Jeff" wrote: ...
    (microsoft.public.access.reports)
  • Re: Grouped MAX Records?
    ... The subquery returns the primary key value of the record where the client ... Allen Browne - Microsoft MVP. ... >> Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)
  • Re: 5 access 2000 users and 1 AccessXP user
    ... > Allen Browne - Microsoft MVP. ... >> MDE. ... The remaining problem is for an XP Client ... >> I should also mention that, before the XP Client running Office 2000 was ...
    (microsoft.public.access.conversion)
Loading