How to bring a function into a query

[kat50]

I have created a function (Module)called mID, I would like to have the value returned in a query. When I use the expression builder and just choose this funciton it says I have the wrong number of arguemnts.

mID is:

Public Function mID() As String

If Not IsNull(DLookup("Customer #", "Query1")) Then
mID = "Query1"
ElseIf Not IsNull(DLookup("Customer #", "Query2")) Then
mID = "Query2"
ElseIf Not IsNull(DLookup("Customer #", "Query3")) Then
mID = "Query3"
ElseIf Not IsNull(DLookup("Customer #", "Query4")) Then
mID = "Query4"
ElseIf Not IsNull(DLookup("Customer #", "Query5")) Then
mID = "Query5"
ElseIf Not IsNull(DLookup("Customer #", "Query6")) Then
mID = "Query6"
End If
Exit Function

End Function


Can someone advise what I am missing? I don't want to link all 6 queries...

Thanks a million!

 

[pbaldy]

What are you trying to accomplish? For starters, the field name would have to be bracketed because of the inadvisable space and symbol.

 

[kat50]

Thank you for the quick reply.

I have 6 queries. I need to have the means t o state what customer # is in which query - I (think) I created a function to return that. I am hoping to do this witout liking all 6 queries since that slows down the computer...

 

[pbaldy]

Well, from the looks of it, that will return the name of the first query that returns a record. If that's what you want, then I guess you've got it. To be honest, I don't really understand "state what customer # is in which query".

 

[DCrake]

The example you have given will not progress from the first If statement unless the record count in query 1 is zero.

Because you do not have any criteria the DLookup fill find the first record and the first record only. What condtions, if any, do you have in your Query1 query? Also are the six queries all based on the same table or do they have different sources.

Instead of have queries with filters and looking up the first condition it makes sense to look up the table with the condition contained within the DLookup. For a start it means you do not need a multitude of queries.

If you have a saved query (Query1) which is

SELECT * From Table1 Where CustomerID= 1

Then do a DLookup on that

DLookup("CustomerID","Query1")

Is it not more efficient to do

DLookup("CustomerID","Table1","CustomerID=1")


David

 

[kat50]

Essentially I have a table of customers for a given week. That table is broken down 6 times (6 queries) into various categories. When I want to compare to another week, I do the same thing. Then I use an unmatched query to show what customers are no longer in a given category. I need to show which category these customers have moved to. I would use if(iserror(vlookup etc. if I were using excel, but the dataset is too large. When I select the function in the expression builder I receive a message stating I Have the wrong number of arguments. How can I bring this into my query?

Thanks so much.