?
Solved

VB DLOOKUP

Posted on 2002-07-06
6
Medium Priority
?
537 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 53

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 150 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

777 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