dlookup Help please- Access 2000 1

[accountinggeek]

I have a single table with over thirty columns of numeric data. The first column is a text description of the field I want to lookup. So as an example, I have:

ID Lookup_Value Return Value Quantity Cost
1 Quantity 100 100 80
1 Cost 80 100 80
2 Quantity 100 50 40
2 Cost 80 50 40
3 Quantity 100 60 30
3 Cost 80 60 30

As you can see, the return value(from dlookup) returns the same value over and over again.


What I want to see is the following:

ID Lookup_Value Return Value Quantity Cost
1 Quantity 100 100 80
1 Cost 80 100 80
2 Quantity 50 50 40
2 Cost 40 50 40
3 Quantity 60 60 30
3 Cost 30 60 30

Anyone out there that can help? Thanks

 

[PHV]

Any chance you could post your actual DLookUp call ?
Seems you don't specify which ID you're querying.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[accountinggeek]

here it is:

dlookup(lookup_value,tablename)

should give me the return_value

 

[PHV]

As you don't specify the ID you want the value for, you always get the first retrieved value.
In the VBA help for DLookUp pay attention to the 3rd argument.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[accountinggeek]

I've given that a try already, but maybe I am doing something wrong. I'll keep playing and will let you know how it turns out. THANK YOU VERY MUCH FOR YOUR HELP

 

[LittleSmudge]

Try this

For intC = 1 to 3
  Debug.Print dlookup("Quantity",tablename, "Id = " & intC)
  Debug.Print dlookup("Cost",tablename, "Id = " & intC)
Next

THEN read the bit in the help file that warns about how ridiculously slow this approach will be
( Oh Sorry - I remember that's an 'undocumented feature' )


If the number of rows exceeds ONE then you'll be a lot better of using

Dim rst As DAO.Recordset
Dim db As Database
Set db = CurrentDb
Set rst = db.OpenRecordSet("SELECT * FROM tblName")

While Not rst.EOF
    Debug.Print rst!Id, rst!Quantity
    Debug.Print rst!Id, rst!Cost
Next
rst.Close

'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.