VB DLOOKUP

What is the VB equivalent of the Access VBA:

DLookup("MyValue1","MyTable","MyValue2 = 5")

I have command objects in a Data Environement Control.
The DMax, DMin and DCount would be helpful too.
Could you give me an example?

Thanks ...

BL
beni_luediAsked:
Who is Participating?
 
JimMaguireCommented:
Just to make sure it's not a data type error or something,
-Open the Add-Ins/Visual Data Manager
-got to Files/OpenDatabase/MS Access DB/ ..find your MDB
-highlight the table in question in the Database Window
-type in the SELECT Max(fldMyField) FROM tblMyTable in the SQL Window (no variables etc)
-Execute the SQL
You should get the number; likewise for the other two functions.

Let me know if this works or not.

Jim Maguire
0
 
Ryan ChongCommented:
Try:

Select MyTable.*, (Select MyValue1 from MyTable Where MyValue2 = 5) As newValue from MyTable.
0
 
JimMaguireCommented:
Beni,
'I'll assume you're dealing with "integer" or "long 'integer" datatypes in the Access fields.
'I'll also assume you have a Data Environment named
' deMyDataEnv and a Command in the environment named
' rsComMyTable, and you know how to Open the rsComMyTable
'
'You "Look up" the value you're looking for with the
' "Find" method of the command, as in:
'deMyDataEnv.rsComMyTable.Find(....)  'see below
'But, we'll start with easier stuff.

'Declare some variables to hold values you're looking for
Dim iMyIntMax As Integer
Dim iMyIntMin As Integer
Dim iMyIntCount As Integer
Dim iMySearchValue As Integer

Assign values to the variable with SQL statements
' DMax from VBA becomes
iMyIntMax = SELECT Max(fldMyField) FROM tblMyTable

'DMin from VBA becomes
iMyIntMin = SELECT Min(fldMyField) FROM tblMyTable

'DCount from VBA becomes
iMyIntCount = SELECT COUNT(fldMyField) FROM tblMyTable

'Note that COUNT() doesn't actually "look at" the values
' in fldMyField but counts rows; 'you can also use
' the COUNT(*) to get the same effect.
' But if you use COUNT(fldMyField) any NULL values in the
' field are ignored.  
'If you want to get a true count of rows, including
' NULL's it's almost easier to do the following:
With deMyDataEnv.rsComMyTable  'already Opened
    .MoveLast
    .MoveFirst
    iMyIntCount = .RecordCount    
End With

'Now for the DLookup analogy:
With deMyDataEnv.rsComMyTable
   iMySearchValue = 5  'or some value from a text box, etc
   .Find ("(fldMyField) = '" _
        & iMySearchValue & "'"), , , adBookmarkFirst
'adBookmarkFirst begins all the searches at first record
     
     If .EOF Then   'no match found
       'vent your anger!!! but remember you're at EOF        
     Else  'sweet success
        'you're on the record you're looking for
        ' - go to town
     End If
End With

'For more info, check out "Aggregate Functions" and "Find Method" in VB Help.
'Also, if you're looking up string fields or date fields, the
'FIND syntax get a little hairy, e.g.:
  .Find("(fldMyStringField) = '" & sStringSearch _
       & "'"), , , adBookMarkFirst
  .Find("(fldMyDateField) = # " & dDateSearch _
       & " # "), , , adBookMarkFirst

Hope this helps.

Jim Maguire
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
beni_luediAuthor Commented:
I will test this and talk to you later, ...

Thanks ...

BL
0
 
beni_luediAuthor Commented:
With this part I have problems:

Assign values to the variable with SQL statements
' DMax from VBA becomes
iMyIntMax = SELECT Max(fldMyField) FROM tblMyTable

'DMin from VBA becomes
iMyIntMin = SELECT Min(fldMyField) FROM tblMyTable

'DCount from VBA becomes
iMyIntCount = SELECT COUNT(fldMyField) FROM tblMyTable

I get only error messages.

What does the command property looks like?

Is it based on a table or a SQL statement?

Thanks ...

BL
0
 
beni_luediAuthor Commented:
It's just a bit confusing. I read the MSDN articles about DE and your experts comments, but I just can't figure out, how to put these two things together.

I would like to build a command object, that is based on a table (MyTable), that has a grouping on MyField2 and gives back the max value of MyField1. I was hoping to use a input parameter for MyField2 and an output parameter for MyField1. And the same for Min, Count, and so on. right now it is the max value that I need.

Is your example working with stored procedures and recordset? See the following question, that describes my problem in an other way:

http://www.experts-exchange.com/jsp/qManageQuestion.jsp?qid=20322444

I hope this link works.

Thanks ...

BL
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.