Dlookup

[dhstein]

I have a product table tblProduct) with a SKU for each product (text field)
I also have a category table (tblCategories) which has a list of categories
like this (also text fields) :

Category FirstEntry
NextEntry
Widgets 101
162
Gizmos 205
262
Doodads 324
387


So I'd like to use Dlookup to get the category value for a given SKU. So
the criteria would check for ">= FirstEntry" and "< NextEntry"

1) Can this be done with Dlookup and what would the syntax be?

2) Would this be better as a function ?

Thanks for any help on this.

 

[John Spencer]

Assumption:
FirstEntry, NextEntry and SKU are all number fields.

DLookup("Category","tblProduct","FirstEntry>=" & SKU & " AND
NextEntry<=" & SKU)

If the fields are text fields with number characters then

DLookup("Category","tblProduct","FirstEntry>=""" & SKU & """ AND
NextEntry<=""" & SKU & """")


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[dhstein]

John,

Thanks for the reply, but the compiler doesn't like that syntax. The
fields are all text fields.

 

[John Spencer]

Where are your trying to use this expression?

On a form? In a Report? In a query?

The best I can do is to say the second version should work.

DLookup("Category","tblProduct","FirstEntry>=""" & [SKU] & """ AND
NextEntry<=""" & [SKU] & """")

in a query that should be fine all on one line as a calculated field

On a control you will need to preface that with an = sign.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[dhstein]

John,

Thanks for your help. I did get it to work like this:

Category = DLookup("Category", "tblCategories", "FirstEntry <= '" &
ProductUpdateSKU & "'" & " AND " & "NextEntry > '" & ProductUpdateSKU & "'")

Where are your trying to use this expression?

On a form? In a Report? In a query?

The best I can do is to say the second version should work.

DLookup("Category","tblProduct","FirstEntry>=""" & [SKU] & """ AND
NextEntry<=""" & [SKU] & """")

in a query that should be fine all on one line as a calculated field

On a control you will need to preface that with an = sign.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John,

Thanks for the reply, but the compiler doesn't like that syntax. The
fields are all text fields.