Passing multiple arguments to a query from a module

[Cage]

I have a table with Customer Preferences, for each preference I have assigned a binomial code eg preference 0001 = 1, preference 2 = 0010,preference 3 = 0100, preference 4 = 1000 and so on.

I now wish to create a query which will display the preferences of a particular customer.

The way I intend to do this is to give each customer a preference setting, eg 1101 would indicate that that customer has preferences 1,2 and 4.

Therefore I would like to pass 1000, 0100 and 0001 from a function to the criteria of a query. I have over 50 preferences therefore I need a way to pass mulitple criteria from a function.

The preference can either to a long int or a string.

Any ideas?

Regards,

Cage

 

[Tranman]

Cage,
What a fun exercise...here's what I came up with:

Public Function MakeCriteria(strPrefs as String) as String
Dim strBkwd As String
Dim strCriteria As String
Dim intLen As Integer
Dim intPtr As Integer

strCriteria = "88888888" 'some rediculous number that will
'never happen
strBkwd = StrReverse(strPrefs)
intLen = Len(strBkwd)
For intPtr = 1 To intLen
Select Case Mid(strBkwd, intPtr, 1)
Case 1
strCriteria = strCriteria & " OR 1" & String(intPtr - 1, "0&quot
End Select
Next

MakeCriteria = strCriteria

End Function

The way it works is this: You pass the customer preference string to the function.

The strReverse function (I don't think you can use it in Access '97, but it works in Access 2K or XP), flips the preferences string around backwards (this is so that the intPtr will be relative to the original ordinal position of the bit weight of the 1's in the string, starting with the 1 position, then 2, then 4; etc.)

strCriteria is initialized to some value that will never happen, so that we can append "OR 1000...." to the string, instead of wondering whether each entry was the first one, or not.

Each time the For loop encounters a 1 in strBkwd, it appends "OR 1, followed by the number of zeros that reflects the 1's position in the original string, minus 1, to strCriteria.

When it's done, it sets the function equal to strCriteria, and there ya go....

It may be a bit more clear if you single instruct through it a time or two with different values.

Let me know if you need some help patching these criteria onto a query.

Good Luck,
Tranman

 

[JeremyNYC]

Hmm. I didn't read the post above too closely, though it looks like it could do the trick.

But it seems like there's a much easier way to go about this...just set up a many-to-many relationship between people and preferences, with an intermediary table.

It will be much more flexible and much easier to use.

On the other hand, the fact that you've started down such a road may mean that there's good reason for doing it your way.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[cmmrfrds]

You could pass the preferences as a comma delimited list from the function to the query assuming you have built the string.

"Select preference, * from table Where
preference IN" chr(34) & yourfunction() & chr(34)

In standard module.
Public aString as String

Public Function ReturnPreference() as String
ReturnPreference = aString
End function

 

[Cage]

thanks Guys for your assistance, however I am still have trouble implementing them.

Tranman: The string returned by the function is something like '88888888 or 01 or 001 or 0001', but this displayed no results when the query was ran. I changed the code slightly so that it returned ' "888888" or "01" or "001" or "0001" ' but again that did not work either. Could it be anything to do with the way the query interpretates the string from the function, ie i am currently using =MakeCriteria is that correct.

Cmmrfrds: I tried using the following (where Cust_pref is the field, Customer Info is the table and Pref_calc is the function)

"Select Cust_pref, * from Customer Info Where
Cust_pref IN" chr(34) & Pref_calc() & chr(34)

But I keep getting the error "The expression you entered contains invalid syntax, you may have entered an operand without an operator" and the 'chr' function is highlighted.

TIA

Cage

 

[Tranman]

Cage,
Oops, my bad....I assumed you were using a numeric field and still screwed it up. If you're using long data type, it needs to be some value less than 2,14??,??? (whatever the limit is on that data type). so, you might want to shorten it to '888'. If you're using string, the length doesn't matter, but you need quotation marks around the criteria, like so:

Public Function MakeCriteria(strPrefs as String) as String
Dim strBkwd As String
Dim strCriteria As String
Dim intLen As Integer
Dim intPtr As Integer

strCriteria = "'888'" 'some rediculous number that will
'never happen
strBkwd = StrReverse(strPrefs)
intLen = Len(strBkwd)
For intPtr = 1 To intLen
Select Case Mid(strBkwd, intPtr, 1)
Case 1
strCriteria = strCriteria & " OR '1" & String(intPtr - 1, "0&quot & "'"
End Select
Next

MakeCriteria = strCriteria

End Function

Sorry about that....
Tranman

 

[Cage]

Tranman I am using string, although I could quite as easily use long integer. When I try and call the function from the query I am using:

=MakeCriteria("1101&quot or MakeCriteria("1101&quot

This is still returning no query results. Any idea why the query is not recognising the information returned by the function?

Regards,

Cage

 

[Tranman]

Cage,
=MakeCriteria("1101&quot is fine. In the "preferences" table, are the individual preferences coded as a fixed-length string (like '00000001', 00000100; etc.)? If so, the criteria will have to be formatted the same way.

At this point, it might be easier if you posted the SQL for your query.

Let me know.

Tranman

 

[cmmrfrds]

chr(34) is just a function that returns a double quote. Now, if the string contains text literals that are surrounded by quotes you may need to change to single quotes on one or the other. Single quotes is chr(39).

It is in the library
Library VBA
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
Visual Basic For Applications

You may need to set a reference to this library.

 

[Cage]

The query still does not interrupt the arguments from the function whether I use chr(32) or chr(34).

The only way I can get both methods to work is to only pass one argument. The keyword OR does not seem to be recognised when it is passed from the function, neither is the comma delimited list.

Could it be how i am calling the function ie:
query criteria =makecriteria

or

query critieria = "Select Cust_pref, * from Customer Info Where Cust_pref IN" chr(34) & makecriteria() & chr(34)

I have tried entering the above in both the design view and SQL view. I do have the VBE6.DLL library loaded and have tried interchanging chr(32) which chr(34). Do you think it might have any think to do with me running Access 97? (Tranman, I programmed my own StrReverse function as you quite rightly said it is not part of Access 97).

regards

Cage

 

[cmmrfrds]

chr(39) is single quote.

After this.
query critieria = "Select Cust_pref, * from Customer Info Where Cust_pref IN" chr(34) & makecriteria() & chr(34)

Do this.
Debug.Print [query criteria]

Then do Control G
Then Copy and paste the results of what you built so that we can view it.

 

[DaltonVB]

You could do it in code where the function creates the user preferences. This code requires ADO and ADOX (Microsoft ActiveX Data Objects (ADO) 2.8) references and works using MDAC 2.8
You could also create and modify parameters for the query.

    Dim cnn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim cmd As New ADODB.Command
    
    ' Open the Connection
    Set cnn = CurrentProject.Connection
    
    ' Open the catalog
    Set cat.ActiveConnection = cnn
    
    ' Get the Command.  This is your qry
    Set cmd = cat.Procedures("qryCustomerPrefs").Command

    ' Update the CommandText
    cmd.CommandText = "Select CustomerId, PrefID, PrefValue" & _
        "From Customers " & _
        "Where PrefValue in ('" & Value1 & "','" & Value2 _
                           & "','" & value3 & "'"
    
    ' Update the Procedure
    Set cat.Procedures("CustomerById").Command = cmd
    
    'Clean up
    cnn.Close
    Set cat = Nothing
    Set cmd = Nothing
    Set cnn = Nothing