[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

VB DLOOKUP

Posted on 2002-07-06
6
Medium Priority
?
538 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. 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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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 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

649 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