Solved

VB DLOOKUP

Posted on 2002-07-06
6
532 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 49

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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 …
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

932 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now