Simulate VLOOKUP in Access, and more...

[jean2002]

Hi all,

I have been looking at some of the posts on the whole issue of finding a VLOOKUP functionality from Excel for Access. And I have also read posts and MS Knowledge Base articles on the DLOOKUP function in Access. But what I need exactly is a little different, and was wondering if anyone can help me or share their experience:

I have a table in Access that contains values that should be looked up, for example:

VALUE1 INCREMENT
0.01345 -2.9
0.01391 -2.10
0.1439 -2.11

When using a VLOOKUP on this (if it was in an Excel table) the function should return the value INCREMENT.

In Access, this can be easily done with DLOOKUP, but I want the following functionality from it too (which comes from VLOOKUP in MS Knowledge Base):

Syntax for VLOOKUP

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

If VLOOKUP cannot find lookup_value and range_lookup is TRUE, VLOOKUP uses the largest value that is less than or equal to lookup_value.

Therefore in my example above, if lookup_value is 0.01385(does not match any value in VALUE1 but is less than or equal to next largest value), it should return –2.10. How do I do this in Access? Is it possible using DLOOKUP?

Now I am going to make this thread a little bit hybrid: I am asking this previous question because I have an Excel spreadsheet that is used as a calculator.
But now I want to implement this in an Access form. In the Excel spreadsheet, there is a box where you enter a value, and then the answer appears in another cell. It uses statistical values in a table (using VLOOKUP) to get to this answer.
Now I am wondering, if its possible (and maybe easier) having my Access form link to this spreadsheet, and whatever gets entered into a textbox on the Access form, goes into the spreadsheet input cell, and returns the calculated answer to the Access form.

I have just started coding VBA mostly on Access, and any techies or gurus (or even novices) who have stumbled upon this issue – can you please give me some advice? Maybe there is some macro that can be written in Excel to accomplish this.

Regards,
Jean

 

[KenReay]

Hi

Forget VLookUp, and DLookUp.

Use a table and SQL Select statements to retrieve a recordset, much more flexible

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[DougP]

Maybe some one new to SQL would like an example:
Not sure waht you are after but...
this will change all values in field 'ParticularField' from 0.01345 to -2.9

UPDATE YourTable SET ParticularField = -2.9
WHERE ParticularField= 0.01345;

Range of values
UPDATE YourTable SET YourTable.ParticularField = -2.9
WHERE ParticularField Between 0.01345 And 0.100;

DougP, MCP, A+

 

[jean2002]

I played around a bit with the SQL and Recordset methods as you guys recommended. Struggled at first, but now I got it working.
I used the following solution:

Dim qdf As QueryDef
Dim rst As Recordset

Private Sub Form_Close()
    qdf.Close
End Sub

Private Sub Form_Open(Cancel As Integer)
    Set qdf = CurrentDb.CreateQueryDef("", "SELECT T1.Increment FROM Table1 AS T1 WHERE T1.Value1=(SELECT Min(T2.Value1) FROM Table1 AS T2 WHERE T2.Value1>=[Value];);")
End Sub

Private Sub Text0_AfterUpdate()
    qdf.Parameters("Value") = CDbl(Me.Text0)
    Set rst = qdf.OpenRecordset
    If rst.RecordCount > 0 Then Me.Text2 = rst!Increment
    rst.Close
End Sub

Hope this can help someone else too.

Regards,

Jean