Solved

VB DLOOKUP

Posted on 2002-07-06
6
534 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:beni_luedi
  • 3
  • 2
6 Comments
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 7137031
Try:

Select MyTable.*, (Select MyValue1 from MyTable Where MyValue2 = 5) As newValue from MyTable.
0
 

Expert Comment

by:JimMaguire
ID: 7139406
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
 

Author Comment

by:beni_luedi
ID: 7140440
I will test this and talk to you later, ...

Thanks ...

BL
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:beni_luedi
ID: 7145165
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
 

Accepted Solution

by:
JimMaguire earned 50 total points
ID: 7146471
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
 

Author Comment

by:beni_luedi
ID: 7150935
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question