Using Dlookup and Dmax with dates

[joer]

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 return the Location. 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([HISTORY]![LOCATION],[HISTORY]![LOCATION],"[HISTORY]![LASTACTIVITY]="
&
DMax([HISTORY]![LASTACTIVITY],[HISTORY]![LASTACTIVITY],"[HISTORY]![LASTACTIVITY]<="
& [UserEntryData]![KitStartTime]))

I have the domain fields set to be

![Field] because when I set
them to just

, the query asks me for the parameter before it
runs. I think I need to add "#" in some places to specify date/time
but I'm not sure where they need to go. Thanks for any help.

Joe

 

[Jeff Boyce]

Check Access HELP on the correct syntax for the Dxxx() functions. I think
you might be missing some quote marks...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[joer]

So I went through Access help and adjusted the syntax as I thought
needed. I still came up with #Error in every cell under StartLoc
though. Here's the new code, please help:

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

Thanks,
Joe

Check Access HELP on the correct syntax for the Dxxx() functions. I think
you might be missing some quote marks...

Regards

Jeff Boyce
Microsoft Office/Access MVP

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 return the Location. 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([HISTORY]![LOCATION],[HISTORY]![LOCATION],"[HISTORY]![LASTACTIVITY]="
&
DMax([HISTORY]![LASTACTIVITY],[HISTORY]![LASTACTIVITY],"[HISTORY]![LASTACTIVITY]<="
& [UserEntryData]![KitStartTime]))

I have the domain fields set to be

![Field] because when I set
them to just

, the query asks me for the parameter before it
runs. I think I need to add "#" in some places to specify date/time
but I'm not sure where they need to go. Thanks for any help.

Joe

 

[Jeff Boyce]

I still don't see quotes around the first element in the syntax.

Regards

Jeff Boyce
Microsoft Office/Access MVP

So I went through Access help and adjusted the syntax as I thought
needed. I still came up with #Error in every cell under StartLoc
though. Here's the new code, please help:

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

Thanks,
Joe

Check Access HELP on the correct syntax for the Dxxx() functions. I
think
you might be missing some quote marks...

Regards

Jeff Boyce
Microsoft Office/Access MVP

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 return the Location. 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([HISTORY]![LOCATION],[HISTORY]![LOCATION],"[HISTORY]![LASTACTIVITY]="
&
DMax([HISTORY]![LASTACTIVITY],[HISTORY]![LASTACTIVITY],"[HISTORY]![LASTACTIVITY]<="
& [UserEntryData]![KitStartTime]))

I have the domain fields set to be

![Field] because when I set
them to just

, the query asks me for the parameter before it
runs. I think I need to add "#" in some places to specify date/time
but I'm not sure where they need to go. Thanks for any help.

Joe

 

[joer]

If I place quotes around each element I still get the same error.

Joe

I still don't see quotes around the first element in the syntax.

Regards

Jeff Boyce
Microsoft Office/Access MVP

So I went through Access help and adjusted the syntax as I thought
needed. I still came up with #Error in every cell under StartLoc
though. Here's the new code, please help:

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

Thanks,
Joe

Check Access HELP on the correct syntax for the Dxxx() functions. I
think
you might be missing some quote marks...

Regards

Jeff Boyce
Microsoft Office/Access MVP

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 return the Location. 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([HISTORY]![LOCATION],[HISTORY]![LOCATION],"[HISTORY]![LASTACTIVITY]="
&
DMax([HISTORY]![LASTACTIVITY],[HISTORY]![LASTACTIVITY],"[HISTORY]![LASTACTIVITY]<="
& [UserEntryData]![KitStartTime]))

I have the domain fields set to be

![Field] because when I set
them to just

, the query asks me for the parameter before it
runs. I think I need to add "#" in some places to specify date/time
but I'm not sure where they need to go. Thanks for any help.

Joe

 

[Jeff Boyce]

Were this my issue, I'd stop trying to do it all at once and work on each
separate function until it was working, then look to join them back
together.

Sorry I don't have any other ideas...

Regards

Jeff Boyce
Microsoft Office/Access MVP

If I place quotes around each element I still get the same error.

Joe

I still don't see quotes around the first element in the syntax.

Regards

Jeff Boyce
Microsoft Office/Access MVP

So I went through Access help and adjusted the syntax as I thought
needed. I still came up with #Error in every cell under StartLoc
though. Here's the new code, please help:

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

Thanks,
Joe

Jeff Boyce wrote:
Check Access HELP on the correct syntax for the Dxxx() functions. I
think
you might be missing some quote marks...

Regards

Jeff Boyce
Microsoft Office/Access MVP

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 return the Location. 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([HISTORY]![LOCATION],[HISTORY]![LOCATION],"[HISTORY]![LASTACTIVITY]="
&
DMax([HISTORY]![LASTACTIVITY],[HISTORY]![LASTACTIVITY],"[HISTORY]![LASTACTIVITY]<="
& [UserEntryData]![KitStartTime]))

I have the domain fields set to be

![Field] because when I
set
them to just

, the query asks me for the parameter before it
runs. I think I need to add "#" in some places to specify date/time
but I'm not sure where they need to go. Thanks for any help.

Joe

 

[joer]

Are there any other ideas in terms of filtering the data with criteria
and then using only one of the aggregate functions? I don't have much
experience with Access so I'm looking for ideas.

Joe

Were this my issue, I'd stop trying to do it all at once and work on each
separate function until it was working, then look to join them back
together.

Sorry I don't have any other ideas...

Regards

Jeff Boyce
Microsoft Office/Access MVP

If I place quotes around each element I still get the same error.

Joe

I still don't see quotes around the first element in the syntax.

Regards

Jeff Boyce
Microsoft Office/Access MVP

So I went through Access help and adjusted the syntax as I thought
needed. I still came up with #Error in every cell under StartLoc
though. Here's the new code, please help:

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

Thanks,
Joe

Jeff Boyce wrote:
Check Access HELP on the correct syntax for the Dxxx() functions. I
think
you might be missing some quote marks...

Regards

Jeff Boyce
Microsoft Office/Access MVP

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 return the Location. 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([HISTORY]![LOCATION],[HISTORY]![LOCATION],"[HISTORY]![LASTACTIVITY]="
&
DMax([HISTORY]![LASTACTIVITY],[HISTORY]![LASTACTIVITY],"[HISTORY]![LASTACTIVITY]<="
& [UserEntryData]![KitStartTime]))

I have the domain fields set to be

![Field] because when I
set
them to just

, the query asks me for the parameter before it
runs. I think I need to add "#" in some places to specify date/time
but I'm not sure where they need to go. Thanks for any help.

Joe

 

[Jeff Boyce]

Joe

I'll go back to the top of this thread. It sounds like you want to find
"the location of a part at the specified StartTime".

One way to do this is to build a query that generates the maximum StartTime
for each part, where the StartTime is less than your LastActivity time. You
could do the first part by using the Totals query, grouping by Part and
finding Maximum of StartTime. It might take a second query, joining the
first query to the LastActivity table to find the Location and use the
Max(StartTime)<LastActivity -- you are joining by Part, right?

I guess I'm confused, since your first post mentioned that Location and
LastActivity are in the same table. How is StartTime related to Location?
If I'm reading it right, they AREN'T! You are just looking for some
StartTime before the LastActivity time.

How will you handle "ties" (same StartTime and LastActivity time)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[joer]

Tying start time to last activity is the difficult part of this. There
is an activity log for the part numbers thus when a location for a part
number is changed, that activity is time stamped and logged in the
table. As a result there is a table of part numbers and each part
number has a location at a certain time (LastActivity - being the time
stamp when the location was changed).

The start time comes from a separate form and is the time when an
operation is started Operator presses a "start" button at beginning of
operation). I want to search the activity log table for the locations
of parts at that start time.

The problem is that the start time does not necessarily match up with a
LastActivity time because a part did not change location at the start
time (if they did match up it would be by mere chance). As a result, I
cannot run a simple query for locations at the start time and have to
figure out a way to find the last activity time closest to the start
time to know the location of the part at the start time.

Does this help?

 

[Jeff Boyce]

Now that I re-read your situation, you wouldn't have to find the max()

It sounds like you are finding the first "Location" date greater than the
StartDate for a Part.

But don't your Parts have MANY StartDates? Are you trying to find the
location of a Part at Start for EVERY StartDate for that Part? Or just for
the most recent Part Start?

More info, please,

Jeff Boyce
Microsoft Office/Access MVP

 

[joer]

The start dates will be updated on a rolling basis but I want to select
one start time and search the location of the part at that start time.
Each part has several locations throughout the day, all logged with a
time stamp (last activity). So I want to match the start time with the
nearest last activity time in order to pull the location at the start
time. Last Activity is my link to knowing where a part is at a given
time so I'm trying to use it to find out where the part is at the start
time.

Joe

 

[Jeff Boyce]

Create a query that finds the maximum LastActivity that is less than the
start time you pick.

Regards

Jeff Boyce
Microsoft Office/Access MVP