Extracting Values From Table, Using them in a Variable

  • Thread starter Thread starter Larry R Harrison Jr
  • Start date Start date
L

Larry R Harrison Jr

I have Access 97. I have a form which I want to extract the top & bottom 1
value from a table and place the date values resulting from that into a text
box--or into a date variable for usage.

The idea is for it to extract the first and last dates of a log of
transactions for a given machine.

I already have a query and the resulting SQL statements which can give me
these results, but I need the results to (again) either (a) place the date
results in a date variable or (b) make a text box equal to these results.

I don't see a way in which DLOOKUP would do this, although I guess that's
possible.

Tips?

LRH
 
With DLookup() you just get the first value Access finds that matches the
condition, regardless of what value that is.

Could you use DMin() and DMax() give you the earliest and latest dates
matching the condition?

Alternatively, there is an extended lookup() function in this link which
runs about 2ce the speed of DLookup() and also allows you to specify a sort
order so you can get the value you want:
http://allenbrowne.com/ser-42.html
 
I have Access 97. I have a form which I want to extract the top & bottom 1
value from a table and place the date values resulting from that into a text
box--or into a date variable for usage.

The idea is for it to extract the first and last dates of a log of
transactions for a given machine.

I already have a query and the resulting SQL statements which can give me
these results, but I need the results to (again) either (a) place the date
results in a date variable or (b) make a text box equal to these results.

I don't see a way in which DLOOKUP would do this, although I guess that's
possible.
Click to expand...

DLookUp can't (by itself), but DMin() can find the earliest date, and
DMax() the latest. Just set the COntrol Source of each textbox to the
appropriate DMax/DMin call.
 
The following (modified accordingly) will do what you ask. For newest
dates, do not supply GetOldest. if you want the oldest date supply
true to GetOldest

Public Function GetNewestDate(Optional GetOldest As Boolean) As String

Dim recset As Recordset
Set recset = CurrentDb.OpenRecordset("A QUERY WITH A DATE FIELD SORTED
DESCENDING") 'or supply a diferent recordset - i.e.
form.recordsetclone
If Not recset.EOF Then
if GetOldest then
recset.MoveLast
else
recset.MoveFirst
end if
GetNewestDate= recset("NAME OF THE DATE FIELD")
Else
GetNewestDate= ""
End If
recset.close ' do not use if using form.recordsetclone
set recset = nothing ' do not use if using form.recordsetclone
end function
 
Back
Top