Calling a function as criteria in query 2

[HenryAnthony]

Can anyone offer some general information on creating functions that are used for criteria in a query and how to call the function from the query?

Best regards,

Henr¥

 

[Cagliostro]

make in a separate module
'-----------
function xxx() as string
xxx = "hello from xxx"
end function

function yyy(y as string) as string
return "hello from yyy with "&y
end function
'-----------
after create a new query with its code:
select xxx(), yyy("query&quot, yyy(ColumnName) from YourTale John Fill

ivfmd@mail.md

 

[jflachman]

I don't think you can use a permanent query here, you need to create it on the fly.

In which case you would use a SQL string like:
[tt]
"SELECT * FROM [table 1] WHERE [field 1].[table 1] = " + Function(var1, var2..) + ";"
[/tt]


If you post more specifics about your situation, I'll try to help with the actual code. Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[ComputorMike]

Public Function RemoveX(MyStr As Variant, MyChars As String) As Variant
'Send a string and remove all characters in MyChars
Dim I As Integer
Dim I2 As Integer

If IsNull(MyStr) Then GoTo Jump
For I = 1 To Len(MyChars)
For I2 = 1 To Len(MyStr)
If Mid(MyStr, I2, 1) = Mid(MyChars, I, 1) Then Mid(MyStr, I2, 1) = " "
Next I2
Next I
RemoveX = Trim(MyStr)
Jump:
End Function

Now that I have created this function. I can call it in any Query. Ie RemoveX(Mytable!PhoneNumber, "()-&quot
would take (123) 456-7890 and make it 1234567890
I can pass a null val to a variant that is why the function is a variant.
To object or not to object
That is the question
Alast poor varible I new you well

 

[HenryAnthony]

John and Jonathan,

Thank you very much for your responses. I think they are giving me the kick start I needed.

Jonathan, my query is really quite simple. I have a table that includes company information including SIC codes. I have about 50 ranges of SIC codes by which I need to include as criteria. For example:

>=1500 And <=1599
>=2011 And <= 2081
and so on...

I can only enter 256 characters in the criteria box and the full criteria is about 750 characters. I can get the desired results by running 3 queries, but that is messy and I like to keep it neat )

By &quot;on the fly&quot; are you referring to DAO?

You guys ROCK extensively!

Best regards,

Henr¥

 

[jflachman]

What are you using the query to do? Supply data to a form?
If so, you don't need a permanent query. Use an SQL statement as the record source for the form.

You would use the following in the record source:
[tt]
SELECT * FROM [tablename] WHERE (
(([tablename]![SIC]>1500) AND ([tablename]![SIC]<1599))
OR (([tablename]![SIC]>2011) AND ([tablename]![SIC]<2081))
OR ...
OR ...
)
[/tt]

Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[HenryAnthony]

Jonathan,

This seems like a solution I can do! I will try it out and let you know how it works. As usual when I come to this forum begging for help, the solution usually turns out to be something simpler than I thought it would be. I did not know you could use a SQL statement as a record source for a form. Thank you very, very much.

Best regards,

Henr¥

 

[jflachman]

HenryAnthony,
The only advantage I can see to using a permanent query is for a dataset that is used repeatedly in the database.

I'm not sure if there is a speed of execution difference with a permanent query...can anyone else comment on that? Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[JoeMiller]

Henry:

A suggestion, rather than the code stuff you could make a table that holds all 750 SIC's you want to show. Then in the query relate the SIC field in your SICtoShow table to the SIC in the regular table. Access will automatically limit the list to only the ones you want because of the &quot;Show only where there are matches.&quot; Then you can edit the list in the table rather than having to sort through mounds of code.

HTH Joe Miller
joe.miller@flotech.net

 

[HenryAnthony]

Joe,

That's a good suggestion and I actually found a similar post doing a search. The problem, in my situation, is that I only have a table of about 50 desired ranges not a table of all desired SIC codes. I would have to data enter thousands of individual codes with your method, and that is exactly what I wanted to avoid. I'll tuck this tidbit away for future use.

Best regards,

Henr¥

 

[JoeMiller]

It would be easy to use autofill in excel to fill the ranges you need for the table and copy/paste the records in. Also if your range is based on say the first 3 numbers of the sic make a query that pulls the Left([SIC],3) and selects all the data then a second query that pulls all the data from the query with the grouping. Put the first three digits in your limiting table rather than the hundreds of individual down to the 6th digit and link the group from your table to the field you did the left function in. Maybe more information, but keep that in mind as well.

Joe Joe Miller
joe.miller@flotech.net

 

[HenryAnthony]

OK Joe, your method is sounding better all the time. The less I need to use code the better. I've learned a lot from this thread. That's what I love about Access, there are so many creative ways to get the same result.

Best regards,

Henr¥