Can someone help me with a sophisticated DLookUp?

The following SQL returns PRECISELY what I want:

SELECT TOP 1 tblCorrespondence.CorrespID,
tblOutboundTypes.OTypDescription, tblCorrespondence.OutDate,
tblCorrespondence.VehicleJobID, tblCorrespondence.OutType
FROM tblOutboundTypes INNER JOIN tblCorrespondence ON
tblOutboundTypes.OutType = tblCorrespondence.OutType
WHERE (((tblCorrespondence.OutDate) Is Not Null) AND
((tblCorrespondence.VehicleJobID)=3))
ORDER BY tblCorrespondence.CorrespID DESC;

For a given VehicleJobID value of 3, I wanted to find the last
record in correspondence table and its corresponding OTypDescription
in tblOutboundTypes.

I'd love to be able to do this on-the-fly, so to speak, with a DLookUp
statement. I'm wondering how to structure it. Hope some of you have
suggestions.

re: Can someone help me with a sophisticated DLookUp?

Any reason you can't join your table to this query?

re: Can someone help me with a sophisticated DLookUp?

On 14 Jul 2006 18:38:07 -0700, pietlinden@hotmail.com wrote:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hmmm??? What I'd like to do, actually, is not have
this query as a saved object at all. I have a procedure
in which I'd like to utilize a DLookUp to extract a
specific record (like this SQL does) and find a particular
field value in it. Not sure that can be done, but I thought
I'd ask.

re: Can someone help me with a sophisticated DLookUp?

MLH wrote:I guess try both ways and use GetTickCount and run the test like 10K
times to see which is faster. (see the performance measurement section
in Vogel's "VB Object & Component Handbook" Hard to argue with
empirical evidence.

re: Can someone help me with a sophisticated DLookUp?

MLH wrote:
I may be missing something, so excuse me if my advice is incorrect.
Remove the
tblCorrespondence.VehicleJobID=3
in the where statement.

Save the query to QUery1

Then Me.OTypDescription =
Dlookup("OTypDescription","Query1","tblCorresponde nce.VehicleJobID=3")

re: Can someone help me with a sophisticated DLookUp?

MLH wrote:All Dlookup does is translate your request to SQL, process it and
return the answer.
You already have your SQL. So why translate it to a DLookup so that the
DLookup can translate it back again and use it?

Assuming your string is pointed to by a variable called sql then you
should be able to get your value directly as
CurrentDB.Openrecordset(sql).Collect(1) ... which returns the value of
the second field (fields are numbered zero, one, etc)

Trying to simplity your SQL I get something (probably with syntax
errors) like:

Public Function GetOTypDescription(ByVal vVehicleJobId As Long) As
String
Dim sql As String
sql = "SELECT TOP 1 tot.OTypDescription"
sql = sql & vbNewLine & "FROM tblOutboundTypes tot"
sql = sql & vbNewLine & "INNER JOIN tblCorrespondence tc ON"
sql = sql & vbNewLine & "tot.OutType = tc.OutType"
sql = sql & vbNewLine & "WHERE tc.OutDate Is Not Null AND"
sql = sql & vbNewLine & "tc.VehicleJobID=" & vVehicleJobId
sql = sql & vbNewLine & "ORDER BY tc.CorrespID DESC;"
GetOTypDescription = CurrentDb.OpenRecordset(sql).Collect(0)
End Function

If you create a public function like that (or a private function at the
end of your module) then all you need to do to use
GetOTypDescription(7)
quite simply.

re: Can someone help me with a sophisticated DLookUp?

Speed is not an issue. It isn't repetitive. Running
the query seems instantaneous to me. I can barely
get my finger off the left mouse button after clicking
the BANG button and launching the query before
its results are staring me in the face.

What I would like to know is if DLookUp can be
used to extract the same data. Do you know how?

re: Can someone help me with a sophisticated DLookUp?

MLH wrote:
Not, AFAIK, with joined table. DlookUp deals with one table.

Make it a function, it's easy as pie (air code) - you're using A97, right?

You want to retrieve the OTypDescription value. Just use this very
simple function with the job id as an argument.

Function fRetrievePreciiselyWhatMlhNeeds(lngVehJobId as long) as String

'assumes a long is returned. If there is no result from the
'SQL, then a "" (ZLS) is returned
'
'lngVehJobId is the vehicle job number ID fed from calling proc

dim strSql as string
dim dbs as DAO.Database
dim rst as DAO.recordset

On error goto Err_Proc

'Simplified SQL follows - assumes you have unique field names and
'won't need the overly verbose SQL the excellent Access query
'design grid returns by default. LOOK AT WHAT I've WRITTEN
'CAREFULLY and how lngVehJobId is introduced

strsql = "SELECT TOP 1 OTypDescription " & _
"FROM tblOutboundTypes INNER JOIN tblCorrespondence ON " & _
"tblOutboundTypes.OutType = tblCorrespondence.OutType " & _
"WHERE OutDate Is Not Null AND " & _
"VehicleJobID = " & lngVehJobId & _
"ORDER BY CorrespID DESC"

Set dbs = Access.currentdb

'Open SQL in snapshot mode - this is much faster when
'there are lots of records involved

set rst = dbs.openrecordset(strSql, dbopensnapshot)

with rst

if .eof then

fRetrievePreciiselyWhatMlhNeeds = ""

else

.movefirst 'note that top 1 values can return more than 1 record

fRetrievePreciiselyWhatMlhNeeds = .fields!OTypDescription

end if

End with



Exit_Proc:

On Error Resume Next
rst.close
Set rst = nothing
dbs.close
set dbs = nothing
on error goto 0

exit function

Err_Proc:

Select Case err.number

case else

msgbox "Error " & err.number & " " & _
err.description,vbcritical, _
"Error in fRetrievePreciiselyWhatMlhNeeds", _
Err.HelpFile, Err.HelpContext

Resume exit_proc

End select

end function
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

re: Can someone help me with a sophisticated DLookUp?

Thx, Tim.
AAMOF, that's what I did.
Was curious about another way.
Lyle Fairfield made a suggestion
that I'm gonna try. Looks promising.