DLookup and DMax with dates

Hi,
I have two date/time fields: StartTime and LastActivity.
In the History table, there are Location and LastActivity fields.
I want to run a query which finds the LastActivity date/time closest to

the StartTime and returns the Location for that LastActivity date/time.
In summary, I want to return the location of a part at the specified
StartTime.

The code I'm using in the calculated field is below which returns a
#Error message in the StartLoc column.


StartLoc:
DLookUp([LOCATION],[HISTORY]![LOCATION],"[LASTACTIVITY]=#" &
DMax([LASTACTIVITY] & "#",[HISTORY]![LASTACTIVITY],"[LASTACTIVITY]=#"
& "[KitStartTime]" & "#"))

Thus, the query looks up the maximum LastActivity time which is less
than or equal to the StartTime and returns the Location where
LastActivity equals that maximum LastActivity (or so I wish).

I have the domain fields set to be [Table]![Field] because when I set
them to just [Table], the query asks me for the parameter before it
runs. I've tried splitting up the functions and working them one at a
time but to no avail.

Thanks for any help.


Joe

I would probably write my own function that opens a record set to find the
information. However a quick review of your syntax reveals that you haven't
placed you field and table names in quotes and have place quotes around
expressions that shouldn't. Without testing your logic, I would expect to
change your syntax to:

DLookUp("LOCATION","[HISTORY]","[LASTACTIVITY]=#" & DMax("[LASTACTIVITY]",
"[HISTORY]","[LASTACTIVITY]=#" & [KitStartTime] & "#") & "#")

--
Duane Hookom
MS Access MVP

"Joseph Rosing" wrote in message
oups.com...
Hi,
I have two date/time fields: StartTime and LastActivity.
In the History table, there are Location and LastActivity fields.
I want to run a query which finds the LastActivity date/time closest to

the StartTime and returns the Location for that LastActivity date/time.
In summary, I want to return the location of a part at the specified
StartTime.

The code I'm using in the calculated field is below which returns a
#Error message in the StartLoc column.


StartLoc:
DLookUp([LOCATION],[HISTORY]![LOCATION],"[LASTACTIVITY]=#" &
DMax([LASTACTIVITY] & "#",[HISTORY]![LASTACTIVITY],"[LASTACTIVITY]=#"
& "[KitStartTime]" & "#"))

Thus, the query looks up the maximum LastActivity time which is less
than or equal to the StartTime and returns the Location where
LastActivity equals that maximum LastActivity (or so I wish).

I have the domain fields set to be [Table]![Field] because when I set
them to just [Table], the query asks me for the parameter before it
runs. I've tried splitting up the functions and working them one at a
time but to no avail.

Thanks for any help.


Joe

Thanks for the advice but I'm still not getting what I need. After
fixing the quotations, the query returned one record (and then froze up
on me) but the record did not have anything in the StartLoc column. So
while it didn't give me the #Error, it still didn't return the
location.

So I'm thinking I could have the location column in the query and use
Dmax as a criteria for that column? The only problem is that I have
several different part numbers in the query and need the last location
for each part number before the start time (i.e. if the query only
returns one record it's no good; I need 1 record per part number).
Does that make sense?

What kind of things could I do in terms of writing my own function?

Thanks,
Joe

Duane Hookom wrote:
I would probably write my own function that opens a record set to find the
information. However a quick review of your syntax reveals that you haven't
placed you field and table names in quotes and have place quotes around
expressions that shouldn't. Without testing your logic, I would expect to
change your syntax to:

DLookUp("LOCATION","[HISTORY]","[LASTACTIVITY]=#" & DMax("[LASTACTIVITY]",
"[HISTORY]","[LASTACTIVITY]=#" & [KitStartTime] & "#") & "#")

--
Duane Hookom
MS Access MVP

"Joseph Rosing" wrote in message
oups.com...
Hi,
I have two date/time fields: StartTime and LastActivity.
In the History table, there are Location and LastActivity fields.
I want to run a query which finds the LastActivity date/time closest to

the StartTime and returns the Location for that LastActivity date/time.
In summary, I want to return the location of a part at the specified
StartTime.

The code I'm using in the calculated field is below which returns a
#Error message in the StartLoc column.


StartLoc:
DLookUp([LOCATION],[HISTORY]![LOCATION],"[LASTACTIVITY]=#" &
DMax([LASTACTIVITY] & "#",[HISTORY]![LASTACTIVITY],"[LASTACTIVITY]=#"
& "[KitStartTime]" & "#"))

Thus, the query looks up the maximum LastActivity time which is less
than or equal to the StartTime and returns the Location where
LastActivity equals that maximum LastActivity (or so I wish).

I have the domain fields set to be [Table]![Field] because when I set
them to just [Table], the query asks me for the parameter before it
runs. I've tried splitting up the functions and working them one at a
time but to no avail.

Thanks for any help.


Joe

Again, I would probably create a small function that would do the lookups.
Nesting domain aggregate functions is too complex for me to maintain.

--
Duane Hookom
MS Access MVP

"Joseph Rosing" wrote in message
ups.com...
Thanks for the advice but I'm still not getting what I need. After
fixing the quotations, the query returned one record (and then froze up
on me) but the record did not have anything in the StartLoc column. So
while it didn't give me the #Error, it still didn't return the
location.

So I'm thinking I could have the location column in the query and use
Dmax as a criteria for that column? The only problem is that I have
several different part numbers in the query and need the last location
for each part number before the start time (i.e. if the query only
returns one record it's no good; I need 1 record per part number).
Does that make sense?

What kind of things could I do in terms of writing my own function?

Thanks,
Joe

Duane Hookom wrote:
I would probably write my own function that opens a record set to find
the
information. However a quick review of your syntax reveals that you
haven't
placed you field and table names in quotes and have place quotes around
expressions that shouldn't. Without testing your logic, I would expect to
change your syntax to:

DLookUp("LOCATION","[HISTORY]","[LASTACTIVITY]=#" &
DMax("[LASTACTIVITY]",
"[HISTORY]","[LASTACTIVITY]=#" & [KitStartTime] & "#") & "#")

--
Duane Hookom
MS Access MVP

"Joseph Rosing" wrote in message
oups.com...
Hi,
I have two date/time fields: StartTime and LastActivity.
In the History table, there are Location and LastActivity fields.
I want to run a query which finds the LastActivity date/time closest to

the StartTime and returns the Location for that LastActivity date/time.
In summary, I want to return the location of a part at the specified
StartTime.

The code I'm using in the calculated field is below which returns a
#Error message in the StartLoc column.


StartLoc:
DLookUp([LOCATION],[HISTORY]![LOCATION],"[LASTACTIVITY]=#" &
DMax([LASTACTIVITY] & "#",[HISTORY]![LASTACTIVITY],"[LASTACTIVITY]=#"
& "[KitStartTime]" & "#"))

Thus, the query looks up the maximum LastActivity time which is less
than or equal to the StartTime and returns the Location where
LastActivity equals that maximum LastActivity (or so I wish).

I have the domain fields set to be [Table]![Field] because when I set
them to just [Table], the query asks me for the parameter before it
runs. I've tried splitting up the functions and working them one at a
time but to no avail.

Thanks for any help.


Joe

Can you give me an idea of what the function would look like? Are
there other things I can do maybe in terms of filtering the data and
using only one aggregate function to pull the locations?

Joe


Duane Hookom wrote:
Again, I would probably create a small function that would do the lookups.
Nesting domain aggregate functions is too complex for me to maintain.

--
Duane Hookom
MS Access MVP

"Joseph Rosing" wrote in message
ups.com...
Thanks for the advice but I'm still not getting what I need. After
fixing the quotations, the query returned one record (and then froze up
on me) but the record did not have anything in the StartLoc column. So
while it didn't give me the #Error, it still didn't return the
location.

So I'm thinking I could have the location column in the query and use
Dmax as a criteria for that column? The only problem is that I have
several different part numbers in the query and need the last location
for each part number before the start time (i.e. if the query only
returns one record it's no good; I need 1 record per part number).
Does that make sense?

What kind of things could I do in terms of writing my own function?

Thanks,
Joe

Duane Hookom wrote:
I would probably write my own function that opens a record set to find
the
information. However a quick review of your syntax reveals that you
haven't
placed you field and table names in quotes and have place quotes around
expressions that shouldn't. Without testing your logic, I would expect to
change your syntax to:

DLookUp("LOCATION","[HISTORY]","[LASTACTIVITY]=#" &
DMax("[LASTACTIVITY]",
"[HISTORY]","[LASTACTIVITY]=#" & [KitStartTime] & "#") & "#")

--
Duane Hookom
MS Access MVP

"Joseph Rosing" wrote in message
oups.com...
Hi,
I have two date/time fields: StartTime and LastActivity.
In the History table, there are Location and LastActivity fields.
I want to run a query which finds the LastActivity date/time closest to

the StartTime and returns the Location for that LastActivity date/time.
In summary, I want to return the location of a part at the specified
StartTime.

The code I'm using in the calculated field is below which returns a
#Error message in the StartLoc column.


StartLoc:
DLookUp([LOCATION],[HISTORY]![LOCATION],"[LASTACTIVITY]=#" &
DMax([LASTACTIVITY] & "#",[HISTORY]![LASTACTIVITY],"[LASTACTIVITY]=#"
& "[KitStartTime]" & "#"))

Thus, the query looks up the maximum LastActivity time which is less
than or equal to the StartTime and returns the Location where
LastActivity equals that maximum LastActivity (or so I wish).

I have the domain fields set to be [Table]![Field] because when I set
them to just [Table], the query asks me for the parameter before it
runs. I've tried splitting up the functions and working them one at a
time but to no avail.

Thanks for any help.


Joe

"Joseph Rosing" wrote:
I have two date/time fields: StartTime and LastActivity.
In the History table, there are Location and LastActivity fields.
I want to run a query which finds the LastActivity date/time closest to

the StartTime and returns the Location for that LastActivity date/time.
In summary, I want to return the location of a part at the specified
StartTime.

The code I'm using in the calculated field is below which returns a
#Error message in the StartLoc column.


StartLoc:
DLookUp([LOCATION],[HISTORY]![LOCATION],"[LASTACTIVITY]=#" &
DMax([LASTACTIVITY] & "#",[HISTORY]![LASTACTIVITY],"[LASTACTIVITY]=#"
& "[KitStartTime]" & "#"))

Thus, the query looks up the maximum LastActivity time which is less
than or equal to the StartTime and returns the Location where
LastActivity equals that maximum LastActivity (or so I wish).

I have the domain fields set to be [Table]![Field] because when I set
them to just [Table], the query asks me for the parameter before it
runs. I've tried splitting up the functions and working them one at a
time but to no avail.

I would think you could add a correlated subquery

SELECT
m.KitStartTime,
.....
(SELECT
First(h.Location)
FROM
Location As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
Location As q
WHERE
q.LastActivity = m.KitStartTime)) As StartLoc
FROM
sometable As m

that should be:

SELECT
m.KitStartTime,
.....
(SELECT
First(h.Location)
FROM
History As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
History As q
WHERE
q.LastActivity = m.KitStartTime)) As StartLoc
FROM
sometable As m


"Gary Walter" typed out erroneously:
"Joseph Rosing" wrote:
I have two date/time fields: StartTime and LastActivity.
In the History table, there are Location and LastActivity fields.
I want to run a query which finds the LastActivity date/time closest to

the StartTime and returns the Location for that LastActivity date/time.
In summary, I want to return the location of a part at the specified
StartTime.

The code I'm using in the calculated field is below which returns a
#Error message in the StartLoc column.


StartLoc:
DLookUp([LOCATION],[HISTORY]![LOCATION],"[LASTACTIVITY]=#" &
DMax([LASTACTIVITY] & "#",[HISTORY]![LASTACTIVITY],"[LASTACTIVITY]=#"
& "[KitStartTime]" & "#"))

Thus, the query looks up the maximum LastActivity time which is less
than or equal to the StartTime and returns the Location where
LastActivity equals that maximum LastActivity (or so I wish).

I have the domain fields set to be [Table]![Field] because when I set
them to just [Table], the query asks me for the parameter before it
runs. I've tried splitting up the functions and working them one at a
time but to no avail.

I would think you could add a correlated subquery

SELECT
m.KitStartTime,
....
(SELECT
First(h.Location)
FROM
Location As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
Location As q
WHERE
q.LastActivity = m.KitStartTime)) As StartLoc
FROM
sometable As m

Thanks for the help Gary. I think your logic will work but when I put
it into the SQL screen I get a syntax error (missing operator). Any
idea what would be missing? Do I need to declare m, h, and q in the
beginning?

I'm new to access and do not have much experience with programming so
I'm not sure what else needs to be entered in the SQL screen other than
what you have below.

Thanks for the help,
Joe


Gary Walter wrote:
that should be:

SELECT
m.KitStartTime,
....
(SELECT
First(h.Location)
FROM
History As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
History As q
WHERE
q.LastActivity = m.KitStartTime)) As StartLoc
FROM
sometable As m


"Gary Walter" typed out erroneously:
"Joseph Rosing" wrote:
I have two date/time fields: StartTime and LastActivity.
In the History table, there are Location and LastActivity fields.
I want to run a query which finds the LastActivity date/time closest to

the StartTime and returns the Location for that LastActivity date/time.
In summary, I want to return the location of a part at the specified
StartTime.

The code I'm using in the calculated field is below which returns a
#Error message in the StartLoc column.


StartLoc:
DLookUp([LOCATION],[HISTORY]![LOCATION],"[LASTACTIVITY]=#" &
DMax([LASTACTIVITY] & "#",[HISTORY]![LASTACTIVITY],"[LASTACTIVITY]=#"
& "[KitStartTime]" & "#"))

Thus, the query looks up the maximum LastActivity time which is less
than or equal to the StartTime and returns the Location where
LastActivity equals that maximum LastActivity (or so I wish).

I have the domain fields set to be [Table]![Field] because when I set
them to just [Table], the query asks me for the parameter before it
runs. I've tried splitting up the functions and working them one at a
time but to no avail.

I would think you could add a correlated subquery

SELECT
m.KitStartTime,
....
(SELECT
First(h.Location)
FROM
Location As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
Location As q
WHERE
q.LastActivity = m.KitStartTime)) As StartLoc
FROM
sometable As m

Joe,
Reply back with your exact SQL view from your query.

--
Duane Hookom
MS Access MVP

"Joseph Rosing" wrote in message
oups.com...
Thanks for the help Gary. I think your logic will work but when I put
it into the SQL screen I get a syntax error (missing operator). Any
idea what would be missing? Do I need to declare m, h, and q in the
beginning?

I'm new to access and do not have much experience with programming so
I'm not sure what else needs to be entered in the SQL screen other than
what you have below.

Thanks for the help,
Joe


Gary Walter wrote:
that should be:

SELECT
m.KitStartTime,
....
(SELECT
First(h.Location)
FROM
History As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
History As q
WHERE
q.LastActivity = m.KitStartTime)) As StartLoc
FROM
sometable As m


"Gary Walter" typed out erroneously:
"Joseph Rosing" wrote:
I have two date/time fields: StartTime and LastActivity.
In the History table, there are Location and LastActivity fields.
I want to run a query which finds the LastActivity date/time closest
to

the StartTime and returns the Location for that LastActivity
date/time.
In summary, I want to return the location of a part at the specified
StartTime.

The code I'm using in the calculated field is below which returns a
#Error message in the StartLoc column.


StartLoc:
DLookUp([LOCATION],[HISTORY]![LOCATION],"[LASTACTIVITY]=#" &
DMax([LASTACTIVITY] & "#",[HISTORY]![LASTACTIVITY],"[LASTACTIVITY]=#"
& "[KitStartTime]" & "#"))

Thus, the query looks up the maximum LastActivity time which is less
than or equal to the StartTime and returns the Location where
LastActivity equals that maximum LastActivity (or so I wish).

I have the domain fields set to be [Table]![Field] because when I set
them to just [Table], the query asks me for the parameter before it
runs. I've tried splitting up the functions and working them one at a
time but to no avail.

I would think you could add a correlated subquery

SELECT
m.KitStartTime,
....
(SELECT
First(h.Location)
FROM
Location As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
Location As q
WHERE
q.LastActivity = m.KitStartTime)) As StartLoc
FROM
sometable As m

Per Gary's suggestion:

SELECT
m.KitStartTime,
PARAMETERS h as number, q as date
.....
(SELECT
First(h.Location)
FROM
History As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
History As q
WHERE
q.LastActivity = m.KitStartTime)) As StartLoc
FROM
[UserEntry] As m

For reference, I have 2 tables loaded in the query: UserEntry, History,
and a previous query, BOM. LastActivty and StartLoc are formatted
date/time and Location is formatted as a number. I'm not sure how the
SQL would look to have the above code with the code which joins the
tables and previous query.

Like I said, my programming experience is very basic and I've never
written SQL for Access, only used set functions. I'm assuming from
Gary's post that h, q, and m are variables which could really be any
name but I'm not sure how to declare them in Access (i.e. I know above,
by setting "PARAMETER h as number" I get an error since "number" is a
reserved word in Access).

Thanks for helping.

Joe


Duane Hookom wrote:
Joe,
Reply back with your exact SQL view from your query.

--
Duane Hookom
MS Access MVP

"Joseph Rosing" wrote in message
oups.com...
Thanks for the help Gary. I think your logic will work but when I put
it into the SQL screen I get a syntax error (missing operator). Any
idea what would be missing? Do I need to declare m, h, and q in the
beginning?

I'm new to access and do not have much experience with programming so
I'm not sure what else needs to be entered in the SQL screen other than
what you have below.

Thanks for the help,
Joe


Gary Walter wrote:
that should be:

SELECT
m.KitStartTime,
....
(SELECT
First(h.Location)
FROM
History As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
History As q
WHERE
q.LastActivity = m.KitStartTime)) As StartLoc
FROM
sometable As m


"Gary Walter" typed out erroneously:
"Joseph Rosing" wrote:
I have two date/time fields: StartTime and LastActivity.
In the History table, there are Location and LastActivity fields.
I want to run a query which finds the LastActivity date/time closest
to

the StartTime and returns the Location for that LastActivity
date/time.
In summary, I want to return the location of a part at the specified
StartTime.

The code I'm using in the calculated field is below which returns a
#Error message in the StartLoc column.


StartLoc:
DLookUp([LOCATION],[HISTORY]![LOCATION],"[LASTACTIVITY]=#" &
DMax([LASTACTIVITY] & "#",[HISTORY]![LASTACTIVITY],"[LASTACTIVITY]=#"
& "[KitStartTime]" & "#"))

Thus, the query looks up the maximum LastActivity time which is less
than or equal to the StartTime and returns the Location where
LastActivity equals that maximum LastActivity (or so I wish).

I have the domain fields set to be [Table]![Field] because when I set
them to just [Table], the query asks me for the parameter before it
runs. I've tried splitting up the functions and working them one at a
time but to no avail.

I would think you could add a correlated subquery

SELECT
m.KitStartTime,
....
(SELECT
First(h.Location)
FROM
Location As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
Location As q
WHERE
q.LastActivity = m.KitStartTime)) As StartLoc
FROM
sometable As m