tblproduct (simplified)
productid(pk)
productcode
productcost
productprice
warrantycode

any warranty claim is invoiced out as the original productcode but
with a w in front e.g. original code 3005015TPR becomes W3005015TPR
(both of which are in [productcode]) the warrantycode field is only
used where the productcode starts with a W then three digits and is
exactly the same as the productcode

this gives two records, the original product with [productcode] as
3005015TPR and [warrantycode] as null, and the warranty product with
[productcode] as W3005015TPR and [warrantycode] as 3005015TPR.

when the warranty product is invoiced I want to use dlookup to find
the cost of the original product so something like

dlookup([productcost],"tblproduct", productcode = warrantycode)

i.e. lookup the cost of the original product.

keep getting errors.

really want to use this in an update query which will select all the
products begining W### and update the productcost of the warranty
product with the productcost of the original product

thanks in advance

On Jan 21, 1:03=A0pm, BobbyDazzler <david.a.mitch... [at] inbox.com> wrote:
> tblproduct (simplified)
> productid(pk)
> productcode
> productcost
> productprice
> warrantycode
>
> any warranty claim is invoiced out as the original productcode but
> with a w in front e.g. original code 3005015TPR becomes W3005015TPR
> (both of which are in [productcode]) =A0the warrantycode field is only
> used where the productcode starts with a W then three digits and is
> exactly the same as the productcode
>
> this gives two records, the original product with [productcode] as
> 3005015TPR and [warrantycode] as null, and the warranty product with
> [productcode] as W3005015TPR and [warrantycode] as 3005015TPR.
>
> when the warranty product is invoiced I want to use dlookup to find
> the cost of the original product so something like
>
> dlookup([productcost],"tblproduct", productcode =3D warrantycode)
>
> i.e. lookup the cost of the original product.
>
> keep getting errors.
>
> really want to use this in an update query which will select all the
> products begining W### and update the productcost of the warranty
> product with the productcost of the original product
>
> thanks in advance

If your warrantycode field is "text" then you have to enclose your
dlookup in quot marks;
dlookup([productcost],"tblproduct","productcode=3D'" & warrantycode &
"'")

hope that helps
bobh.

Suggest you use ELookup which apparently is much faster than DLookup

Public Function ELookup(expr As String, domain As String, Optional Criteria,
Optional OrderClause)

On Error GoTo Err_ELookup
'Purpose: Faster and more flexible replacement for ELookup()
'Arguments: Same as ELookup, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. abrowne1 [at] bigpond.net.au
'Examples:
'1. To find the last value, include DESC in the OrderClause, e.g.:
' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
'2. To find the lowest non-null value of a field, use the Criteria,
'e.g.:
' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
'Note: Requires a reference to the DAO library.
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT TOP 1 " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(OrderClause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close

Exit_ELookup:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_ELookup:
' MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
ELookup = CVErr(5) 'Out of range.
Else
ELookup = CVErr(Err.Number)
End If
Resume Exit_ELookup

End Function

Then Try ELookup("productcost","tblproduct","productcode= " & chr$(34) &
warrantycode & chr$(34))

Chr$(34) is a double quotation mark and much less confusing than "'"

Phil



"bobh" <vulcaned [at] yahoo.com> wrote in message
news:e926cff3-ca73-4be2-b415-fc85a6408a19 [at] l32g2000hse.google groups.com...
On Jan 21, 1:03 pm, BobbyDazzler <david.a.mitch... [at] inbox.com> wrote:
> tblproduct (simplified)
> productid(pk)
> productcode
> productcost
> productprice
> warrantycode
>
> any warranty claim is invoiced out as the original productcode but
> with a w in front e.g. original code 3005015TPR becomes W3005015TPR
> (both of which are in [productcode]) the warrantycode field is only
> used where the productcode starts with a W then three digits and is
> exactly the same as the productcode
>
> this gives two records, the original product with [productcode] as
> 3005015TPR and [warrantycode] as null, and the warranty product with
> [productcode] as W3005015TPR and [warrantycode] as 3005015TPR.
>
> when the warranty product is invoiced I want to use dlookup to find
> the cost of the original product so something like
>
> dlookup([productcost],"tblproduct", productcode = warrantycode)
>
> i.e. lookup the cost of the original product.
>
> keep getting errors.
>
> really want to use this in an update query which will select all the
> products begining W### and update the productcost of the warranty
> product with the productcost of the original product
>
> thanks in advance

If your warrantycode field is "text" then you have to enclose your
dlookup in quot marks;
dlookup([productcost],"tblproduct","productcode='" & warrantycode &
"'")

hope that helps
bobh.

"Phil Stanton" <phil [at] stantonfamily.co.uk> wrote in
news:13paam6b2rtq578 [at] corp.supernews.com:

> Suggest you use ELookup which apparently is much faster than DLookup
>
> Public Function ELookup(expr As String, domain As String, Optional
> Criteria, Optional OrderClause)
>
> On Error GoTo Err_ELookup
> 'Purpose: Faster and more flexible replacement for ELookup()
> 'Arguments: Same as ELookup, with additional Order By option.
> 'Return: Value of the Expr if found, else Null or #Error.
> 'Author: Allen Browne. abrowne1 [at] bigpond.net.au
> 'Examples:
> '1. To find the last value, include DESC in the OrderClause, e.g.:
> ' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID
> DESC") '2. To find the lowest non-null value of a field, use the
> Criteria, 'e.g.:
> ' ELookup("ClientID", "tblClient", "Surname Is Not Null" ,
> "Surname") 'Note: Requires a reference to the DAO library.
> Dim MyDb As Database
> Dim rs As Recordset
> Dim SQLStg As String
>
> 'Build the SQL string.
> SQLStg = "SELECT TOP 1 " & expr & " FROM " & domain
> If Not IsMissing(Criteria) Then
> SQLStg = SQLStg & " WHERE " & Criteria
> End If
> If Not IsMissing(OrderClause) Then
> SQLStg = SQLStg & " ORDER BY " & OrderClause
> End If
> SQLStg = SQLStg & ";"
>
> 'Lookup the value.
> Set MyDb = DBEngine(0)(0)
> Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
> If rs.RecordCount = 0 Then
> ELookup = Null
> Else
> ELookup = rs(0)
> End If
> rs.Close
>
> Exit_ELookup:
> Set rs = Nothing
> Set MyDb = Nothing
> Exit Function
>
> Err_ELookup:
> ' MsgBox Err.Description, vbExclamation, "ELookup Error " &
> Err.number
> If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
> CVErr()
> ELookup = CVErr(5) 'Out of range.
> Else
> ELookup = CVErr(Err.Number)
> End If
> Resume Exit_ELookup
>
> End Function
>
> Then Try ELookup("productcost","tblproduct","productcode= " & chr$(34)
> & warrantycode & chr$(34))
>
> Chr$(34) is a double quotation mark and much less confusing than "'"
>
> Phil

That's elaughable, just what we needed on Blue Monday!

On Jan 21, 6:58=A0pm, lyle fairfield <lylef... [at] yah00.ca> wrote:
> "Phil Stanton" <p... [at] stantonfamily.co.uk> wrote innews:13paam6b2rtq578 [at] cor=
p.supernews.com:
> > Suggest you use ELookup which apparently is much faster than DLookup
>
> > Public Function ELookup(expr As String, domain As String, Optional
> > Criteria, Optional OrderClause)
>
> > =A0 =A0 On Error GoTo Err_ELookup
> > =A0 =A0 'Purpose: =A0 Faster and more flexible replacement for ELookup()=

> > =A0 =A0 'Arguments: Same as ELookup, with additional Order By option.
> > =A0 =A0 'Return: =A0 =A0Value of the Expr if found, else Null or #Error.=

> > =A0 =A0 'Author: =A0 =A0Allen Browne. abrow... [at] bigpond.net.au
> > =A0 =A0 'Examples:
> > =A0 =A0 '1. To find the last value, include DESC in the OrderClause, e.g=
..:
> > =A0 =A0 ' =A0ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID=

> > =A0 =A0 DESC") '2. To find the lowest non-null value of a field, use the=

> > =A0 =A0 Criteria, 'e.g.:
> > =A0 =A0 ' =A0ELookup("ClientID", "tblClient", "Surname Is Not Null" ,
> > =A0 =A0 "Surname") 'Note: =A0 =A0 =A0Requires a reference to the DAO lib=
rary.
> > =A0 =A0 Dim MyDb As Database
> > =A0 =A0 Dim rs As Recordset
> > =A0 =A0 Dim SQLStg As String
>
> > =A0 =A0 'Build the SQL string.
> > =A0 =A0 SQLStg =3D "SELECT TOP 1 " & expr & " FROM " & domain
> > =A0 =A0 If Not IsMissing(Criteria) Then
> > =A0 =A0 =A0 =A0 SQLStg =3D SQLStg & " WHERE " & Criteria
> > =A0 =A0 End If
> > =A0 =A0 If Not IsMissing(OrderClause) Then
> > =A0 =A0 =A0 =A0 SQLStg =3D SQLStg & " ORDER BY " & OrderClause
> > =A0 =A0 End If
> > =A0 =A0 SQLStg =3D SQLStg & ";"
>
> > =A0 =A0 'Lookup the value.
> > =A0 =A0 Set MyDb =3D DBEngine(0)(0)
> > =A0 =A0 Set rs =3D MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
> > =A0 =A0 If rs.RecordCount =3D 0 Then
> > =A0 =A0 =A0 =A0 ELookup =3D Null
> > =A0 =A0 Else
> > =A0 =A0 =A0 =A0 ELookup =3D rs(0)
> > =A0 =A0 End If
> > =A0 =A0 rs.Close
>
> > Exit_ELookup:
> > =A0 =A0 Set rs =3D Nothing
> > =A0 =A0 Set MyDb =3D Nothing
> > =A0 =A0 Exit Function
>
> > Err_ELookup:
> > ' =A0 =A0MsgBox Err.Description, vbExclamation, "ELookup Error " &
> > Err.number
> > =A0 =A0 If Err.Number < 0& Or Err.Number > 65535 Then =A0 =A0'Valid rang=
e for
> > CVErr()
> > =A0 =A0 =A0 =A0 ELookup =3D CVErr(5) =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
=A0 =A0 =A0 =A0'Out of range.
> > =A0 =A0 Else
> > =A0 =A0 =A0 =A0 ELookup =3D CVErr(Err.Number)
> > =A0 =A0 End If
> > =A0 =A0 Resume Exit_ELookup
>
> > End Function
>
> > Then Try ELookup("productcost","tblproduct","productcode=3D " & chr$(34)=

> > & warrantycode & chr$(34))
>
> > Chr$(34) is a double quotation mark and much less confusing than "'"
>
> > Phil
>
> That's elaughable, just what we needed on Blue Monday

Lyle,

Your logic is ineluctable. In A97 help (DLookup Function) under Tips:

Although you can use the DLookup function to display a value from a
field in a foreign table, it may be more efficient to create a query
that contains the fields that you need from both tables and then to
base your form or report on that query.

I suppose the outcome of taking that advice can result in manifold
alternatives, some humorous.

I didn't know about Blue Monday until today. We should have Blue
Monday off as a holiday in the U.S., but it would conflict
occasionally :-) with Dr. Martin Luther King Jr.'s Birthday (Blue
Monday is the Monday of the last full week in January and MLK Day is
the third Monday in January). I can't resist deriving a function to
find Blue Mondays.

'-------Module Code-------
Public Function GetBlueMonday(intYear As Integer) As Date
'Monday of the last full week in January
Dim dtThirdMonday As Date

dtThirdMonday =3D NthXDate(3, vbMonday, DateSerial(intYear, 1, 1))
Select Case 31 - Day(dtThirdMonday)
Case 10, 11:
GetBlueMonday =3D dtThirdMonday
Case 12 To 16:
GetBlueMonday =3D DateAdd("ww", 1, dtThirdMonday)
End Select
End Function

Public Function NthXDate(N As Integer, d As Integer, dtD As Date) As
Date
NthXDate =3D DateSerial(Year(dtD), Month(dtD), (7 -
WeekDay(DateSerial(Year(dtD), Month(dtD), 1)) + d) Mod 7 + 1 + (N - 1)
* 7)
End Function
'---------------------

Example:

tblYears
YID AutoNumber
theYear Integer
YID theYear
1 2008
2 2009
3 2010
=2E..
14 2021

qryBlueMondays:
SELECT theYear, GetBlueMonday(theYear) AS BlueMondayDate,
IIf(NthXDay(3, 2, DateSerial(theYear, 1, 1)) =3D Day(BlueMondayDate),
"True", "False") AS SameAsMLK FROM tblYears;
!qryBlueMondays:
theYear BlueMondayDate SameAsMLK
2008 1/21/2008 True
2009 1/26/2009 False
2010 1/25/2010 False
2011 1/24/2011 False
2012 1/23/2012 False
2013 1/21/2013 True
2014 1/20/2014 True
2015 1/26/2015 False
2016 1/25/2016 False
2017 1/23/2017 False
2018 1/22/2018 False
2019 1/21/2019 True
2020 1/20/2020 True
2021 1/25/2021 False

James A. Fortune
CDMAPoster [at] FortuneJames.com