Simple Custom Function, but

[ease20022002]

Hi,

I have a SELECT query. I also have a custom VBA function that is put in the WHERE Clause. I.e.,

WHERE Line IN (MyFunction());

Now, the Line field is a string DType. If MyFunction = "HELLO", the query will only retrieve records WHERE Line = "HELLO". Here is the problem, because I am using an IN statement, I want to reference more values under the field Line than just "HELLO", but when I make MyFunction = "HELLO, HI", my query doesn't recognize any value and 0 records are returned.

I have tried several variations of the IN statement, i.e., putting single quotes around " 'HELLO', 'HI' ", etc. I have also tried in the WHERE clause, Line LIKE, Line =, and MyFunction only works with one value.

What I want to do is have the query recognize a longer text string with commas, which currently it is not doing. I only want to use the VBA function and the Select query. I do not want to set up another table and do a Join, or anything like that. There must be a way to force the query to recognize something like " 'HELLO', 'HI' ".

Thanks in advance for any help.

 

[Norie]

Sorry, but as far as I'm aware you just can't do this using a function.

What is it you are actually trying to achieve?

What's the problem with setting up another table?

If you're already using code, ie the function, why not create he query with code?

 

[VicRauch]

Why not pass Line into the function and let the function return a True if Line is found in the list your current function builds? And, if Line is not in the list the current function would build, return a False.

HTH,

 

[Norie]

Vic

I think that approach may work, but I also think it would involve evaluating the function for every record.

Now in my experience that's rather time consuming

 

[VicRauch]

Norie,

Thanks for the response. It prompted me to re-think, because I was thinking, "Who cares about the time, at least it will work." But your response, after I thought about it, I realized how doing the query in code would help. Because the In clause would work if the SQL was put together in code.

So, Ease20022002, if you don't know how to write the SQL in code, let us know and someone will help you with that.

Norie: Good job!

 

[ease20022002]

Thanks, Guys...I figured it couldn't be done.

Yeah, I could write it in code and I would if my boss wasn't such an ahole. It would be very easy, but the guy has me and my colleague chasing our tails b.c he has Attention Deficit Disorder up the ying-yang, so he is constantly talking.

Long story short, if you are every interviewing for a job and your would-be boss basically doesn't let you talk and you get the job, I would turn it down unless you are getting paid 6 figures. Just some friendly adive...

Thanks for the try, guys. It is so funny how Access allows for one return, but can not recognize the structure of the IN clause using a function. Maybe the next version will.

 

[VicRauch]

Easy20022002,

Sorry about the boss thing. But don't be too hard on ADD people. We don't mean to be so hyper, we just are. Many people have told me there is no way I could program computers because I'm too hyper, but here I am 34 years after graduating from college, still doing what people told me I couldn't do. AND, loving it. I have worked for some bosses that are so scatterbrained that nobody can get any meaningful work done, or at least it seems that way. But I feel it's usually just because he (or she) got promoted for all the wrong reasons, and those of us working for them will suffer for ever more. But there are some people with ADD that really are nice people. At least my friends say so.

I am truly sorry I don't have any good advice about how to deal with an ADD person, except to pray that some day they have an opportunity to actually see how they are, and how they effect others. In the meantime, GOOD LUCK!!!

ps: I hope you do not feel like you have affended me, because you sure didn't, and uhh, what was I talking about?

 

[SydneyGeek]

Slightly long way round, but here's a suggestion:

1. You need a statement like IN('Hello','Hi') for the SQL to work.
2. When I insert multiple strings as criteria, I usually use a bit of code to build up the IN() statement in an unbound text box, and then feed the contents of that text box to a query. This is usually from a multi-select list box.
3. You should be able to take the output of your custom function, use that to build a string with the correct syntax, and use that as a filter. Does the query form the basis of a report?

Denis

 

[ease20022002]

My friends tell me I have ADD, but I can pick a project and stay on that project.

The ADD isn't the problem and I know I wasn't clear in my last post. It is everything together with his ADD. I know people with ADD who are good people and can manage to follow a direction, they may not be able to stay on the the beaten path, but atleast the direction.

This guy is an ahole to begin with and then he has problems selecting a direction. We don't even get the direction, we just keep spinning in circles b.c the guy is also an egomaniac. It is something to behold, let me tell you.

As for the Form suggestion. Thanks, I am also aware of that. I was hoping I could use just the function. It isn't necessary I use the function, all the queries are currently hardcoded and FWIW, the Line field is for insurance, BI = Bodily Injury, COLL = Collision, etc.

Thanks for the effort, guys

 

[mdmilner]

Never tried to do this with the IN syntax - I'm seeing the same problem.
Is there an issue with building the entire query or concatenating a full string to generate a query?

Public Function RetIn()
Dim dbs As DAO.Database
Dim qry As DAO.QueryDef
Set dbs = CurrentDb

RetIn = "SELECT tblData.Initial, tblData.Number, tblData.City, tblData.St, tblData.Location, tblData.File, tblData.[B/O] "
RetIn = RetIn & "FROM tblData WHERE FILE IN ('D0601428', 'D0501104','D0502039') "
Set qry = dbs.CreateQueryDef("qryList", RetIn)

End Function

Just build the stuff within the IN parenthesis within a loop using recordsets.
You could also use the more common "WHERE fld1 = 'a' or fld1 = 'b'"

Sydney - at first I thought - wow, why in the world would you ever pass data into a form control to re-use it elsewhere. However, do you think this changes the nature of the data in a way that allows it to pass thru into his statement? It seems like quite a 'hack/workaround' if it does - nice though.