SQL - Find the latest entry

T

Tatakau

I am trying to auto-fill worker hours into a form based off of that
individual's latest hours.

Table: Schedule
Important Fields: Name, date, start time, stop time

For example, when I select "Bob" from the combo box, I would like to search
the Schedule table for the entry that has the name "Bob" with the most recent
date, and put the start time and stop time into text fields. I think SQL
might be the answer, or maybe DLookUp, but I am very good with either.

Thanks!

Nick
 
R

Ron Weiner

Nick

Nz(Dlookup("StartTime","Schedule", "Name='" & ComboBox& "' AND Datefield =
#" & Dmax("DateColumn", "Schedule","Name='" & ComboBox& "' ) & "#"),"")

If I did not make a typo, the above will return the starttime of the most
recent record nases on DateColum in Schedule whose name is the one selected
in the combo box. To get the stop time change the forst prameter of the
Dlookup to the name of the column that holds the stop time.

Put it all on one line. It assumes the Start Time column is called
StartTime, thet table is named "Schedule" The combo box is named
"ComboBox", the most recent date is in a column called "DateColumn", the
name column is called "Name".
 
D

Douglas J Steele

Your code will not work reliably if the user's Short Date format is set to
dd/mm/yyyy. While that may not be a problem to you, it can be to many users
in other parts of the world.

You'd be better off using:

Nz(Dlookup("StartTime","Schedule", _
"Name='" & ComboBox& "' AND " & _
"Datefield = " & _
Format(Dmax("DateColumn", "Schedule", _
"Name='" & ComboBox & "'"), _
"\#mm\/dd\/yyyy\#"))

(For what it's worth, you did forget a closing quote inside the DMax
statement)
 
R

Ron Weiner

Doug

Really sorry about the closing quote.

As for the date formatting I am so locked into my little part of the world
here in southeastern Pennsylvania, I find I am hard pressed to even think of
New Jersey, much less other parts of the planet. I guess one of these days,
one of my projects, will wind up in a Day/Month/Year place and my phone will
ring with a customer complaint. Old programming habits are hard to break,
especially when there is no direct or immediate negative feed back.

Actually we earthlings ought to take a new look at all things temporal. Why
24 hours, why 60 minutes. Hell, I say we should Metricize (new word) the
whole shebang. How about:

1 year = 10 months
1 month = 10 days
1 day = 10 hours
1 hour = 10 minutes
1 minute = 10 seconds
1 Second = 10 centaseconds
1 centaseconds = 10 millisecond

and so on until we get really small increments of time. We do not need to
fix up the bigger increments of time as they already fit nicely into the way
the rest of the world does metric thinkspeak stuff. Man think how easy date
math would be! We call the whole new system the "Terran Calendar". We
could set it up so old 1/30/1947 (my born on date in mm/dd/yyyy format)
works out to 00010101 (it just looks binary 21, but its not really). That
is year 1 month one and day one. I could celebrate my next birthday on New
Years Eve.

Hmmm.... Let me see If I just paid $3.50 for a gallon of gas that'd be
what?? 92 and a half cents per liter? What's that in Canadian dollars,
euros, pesos? Ah Hell, as long as we are doing the time stuff, lets fix all
the currency stuff too. We could call it the Terra. Yea that's it, we set
a date, and everyone turns their old money in for Terra's. We could put my
picture on the one terran note. Just think of the standardization we could
achieve in cash register design!

How about language, do the residents of this one little planet really need
all of these languages? Even those of us who speak English can't agree on
something simple like how the hell to spell color (colour). Lets start a
new language Terran! Think of the wasted resources our planet could reclaim
in "English to Spanish" dictionaries alone. Boggles the mind.

Come on you and me!! We could fix up the whole planet in just a couple
weeks if we really put our minds to it. We'll put your picture on the 5
terran note.
 
B

Basil

I think you lost it there. Hope you found the penitentiary before it found
you =o)
 
Top