Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query problem

Posted on 2001-06-15
22
Medium Priority
?
162 Views
Last Modified: 2010-05-02
Hi
i have a query in access which uses a function defined in a modile in access in it where clause.
When i run the query from access it runs fine but when i call it from VB i get a 'undefined function in expression error'
originaly i wanted to pas the SQL to access  but as that didn't work i made it a query in access and i simply pass it a parameter as the function's argument in the where clause.

any ideas??

Cat
0
Comment
Question by:K7
  • 8
  • 7
  • 4
  • +3
22 Comments
 
LVL 4

Expert Comment

by:mcoop
ID: 6194436
Copy your function from Access, and declare it as a function within VB.  Then it won't be undefined... ??
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6194467
Did you try removing the ";" at the end of the query in ACCESS?

If that does not help, what query is it? Is it a JOIN query? Can you paste the query both ACCESS and VB, to check it out?

Cheers.

0
 
LVL 18

Expert Comment

by:deighton
ID: 6194470
...I think K7 wants to pass a query from VB to access

unfortunately declaring function in VB may not help.

e.g

"select * from table where UserFunction(field) = 'x'"



..but I believe that although the .mdb contains the function, it cannot run it without Access running.  

I'd be interested to know


0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 43

Expert Comment

by:TimCottee
ID: 6194474
K7, VB does not have the ability using a Data Access Method (ADO or DAO) to use functions declared within a module in Access. mcoop's solution may work but I am doubtful as the evaluation of the function needs to take place within the query and this will not have direct access to the VB function either.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6194479
When I post, there were no comments before.

looking on mcoop's comment,
you are trying to execute a function from Access module. I did not notice it before.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6194494
0
 
LVL 1

Author Comment

by:K7
ID: 6194541
the SQL is:
SELECT *
FROM tblDish
WHERE checkCat([tblDish].[DishID],'2,3')=True;

checkCat is my function '2,3' is the part that changes from the time so first i compiled the SQL statment in VB and then use it to opena recordset.  As that sisn't work i thought i'd make the '2,3' a unnamed argument and use a ADODB.Command to open the record set but this is returning the same error.  
and as u can see it is not simply a patter of moving the function to VB.  i could write my own code in vb to achive what i want but i'd rather have the DB do the work.

Cat
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6194554
If you use this function, for just a look-up in another table, then, I'd suggest using it directly in a query, since, using functions to check in another table, I think, it is slower, than using queries with JOINS and doing it.

Can you tell what the function does. I assumed that it looks up in another table.

In that case,
SELECT *
FROM tblDish
WHERE checkCat([tblDish].[DishID],'2,3')=True

could be changed, to do, what the function does, but with the query itself.

I dont know, if it makes sense.

Thanks & Cheers.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6194586
I would agree with valli_an, we need to understand what this function is doing. If it can be incorporated in the query itself then the problem is solved much more elegantly.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6194894
You won't be able to use your UDF from elsewhere than Access. See this article: http://support.microsoft.com/support/kb/articles/Q166/1/13.ASP
0
 
LVL 1

Author Comment

by:K7
ID: 6195588
the function looks through the join table of a many-to-many see if a certain dish is in all the required categroies passed in as a string of single digits with , seperators.

the code is:

Function checkCat(intProd As Long, strCats As String) As Boolean
  Dim RS As DAO.Recordset
  Dim i As Integer
 
  checkCat = False
  i = 0
  Set RS = CurrentDb.OpenRecordset("Select * from tblJoin Where DishID = " & intProd)
  If RS.RecordCount >= 1 Then
    RS.MoveFirst
    Do Until RS.EOF = True
      If InStr(strCats, RS!CategoryID) > 0 Then i = i + 1
      RS.MoveNext
    Loop
  End If
  If i > 1 Then checkCat = True
End Function

BTW if ur unclear what the purpose of the query is have a look at http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=msaccess&qid=20131940 

emoreau: that article seems to be specificly about ADO, i don't realy care about the method of getting the record set.  i have even tried it via a public finction in access which returns the wanted RS and calling that function by creating a instance of access and opening the DB.  But i couldent get it to work using the method in the thred sugested by TimCottee. i also searched around the access objects but wasent successful in getting to any function i wrote.


I think i'm just going to write the whole thing in VB, but i'm open to sugestions.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6195750
Try this:

SELECT * FROM tblDish WHERE tblDish.DishID IN (Select DishID From tblJoin Where CategoryID IN (2,3))
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6195894
ya, I think TimCottee's one should work for you.

SELECT *
FROM tblDish
WHERE tblDish.DishID IN (Select DishID From tblJoin Where CategoryID IN ('2,3'))

Is it a String or a Number, the CategoryID, field. If it is a number, it won't work well, with the method you used.

For e.g. when you pass('22,3'),
now, even if 22 is not there, if 2 is there, then also, it would result to True, if I had understood it properly? So, better you should use like this, if it is a string:

SELECT *
FROM tblDish
WHERE tblDish.DishID IN (Select DishID From tblJoin Where CategoryID IN ('2','3'))

If it is a number, then I hope, TimCottee's query should work.

Cheers.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6195897
Please ignore my first sql statement:
>SELECT *
FROM tblDish
WHERE tblDish.DishID IN (Select DishID From tblJoin Where CategoryID IN ('2,3'))

Cheers.
0
 
LVL 1

Author Comment

by:K7
ID: 6197619
Hi this is now the 3rd time i'm trying to post this:

I'v just writtn a huge post and ee crashed (again) and didn't post it so i have to type the text again :(

OK, ur second SQL first produced a data type mismatch so i removed the ' around 2,3 but then it priduced the same out put as ur previous one. It returned all the Dishes which were in EITHER 2 OR 3 but i neew it to return all the ones which are in both 2 AND 3

Also CategoryID is a number and yes i'm aware of that limitation on that function (in the post i describe teh function as takeing "a string of SINGLE digits with , seperators" also if u folow the link in that post u see that i pointed out that limitation to the person who posted the code).  If woulden't need to re-tipe this post, i would be nearly finidhed updating the function that limitatio isnt the problem it is just soem simple VB code to get the string properly processed the problem is getting to the function, unles u can put some SQL together that works.

I think the problem with the SQL is that u have to not only look at one rcord at a time but at all the others in relation to it.  one whay to get it would be to first get all the DishIDs in category 2 then get all the categories asociated with those DishIDs and then only return the ones which are category 3. but i have no idea of how (of it is possible) to implement this in SQL but my VB is fahr better which is why i'm thinking i'm gona write the whole thing in VB UNLES u can find another solution with SQL or a way of getting to funcitons in a module in access.

Cat
0
 
LVL 1

Author Comment

by:K7
ID: 6197652
I jsut got a e-mail saying that there was a post by TimCottee but it dosent show up in the therd no matter how many times i refresh the window, so could u please e-mail me ur coment mattess@alphalink.com.au
thaks
0
 
LVL 9

Accepted Solution

by:
Valliappan AN earned 400 total points
ID: 6197671
SELECT *
FROM tblDish
WHERE tblDish.DishID, tblJoin A, tblJoin B WHERE  tblDish.DishID = A.DishID AND tblDish.DishID = B.DishID AND A.CategoryID = 2 AND B.CategoryID = 3

(OR)

SELECT *
FROM tblDish
WHERE tblDish.DishID IN (Select DishID From tblJoin Where CategoryID  = 2) AND tblDish.DishID IN (Select DishID From tblJoin Where CategoryID  = 3)

Or, would you require, to process, for multiple CategoryIDS, then, need to consider, over this.

hope this helps,
Cheers.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6197673
You could try use the 2nd query, since, the first one, could return multiple results, if there is a multiple match in the 2nd table.
0
 
LVL 1

Author Comment

by:K7
ID: 6197681
IT WORKS!!!!
Cool, i didn't know you could join IN statements with AND so that is the implementation of the logic i desctibed earlier.
To make this work for any number of categories i just have to add another "AND tblDish.DishID IN (Select DishID From tblJoin Where CategoryID  = 4)" for each extra category??

thnks
Cat
0
 
LVL 1

Author Comment

by:K7
ID: 6197730
valli_an  i git an e-mail telling me that u posted something but it isnt showing up so could u e-mail it please
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6198126
K7, I did not put any comments, may be some problems in EE.

Also, I did not recv a mail, like you got, that Tim Cottee had put a comment.

May be, you could ask EE Support about this. Did you recv it twice only or what. Sometimes, it happens that you receive a mail, with a delay. Is it that reason?

Cheers.
0
 
LVL 1

Author Comment

by:K7
ID: 6198189
A dely could be the explonation, i didn't chek time stamp before deleting.  its not that big a deal

thanks again again fot ur answer

Cat
0

Featured Post

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
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

916 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