Query Variables Controlled by User

[pilk2008]

I've created a rather simple Access program that I use to import an employee's cookie summary txt file. I then sort the cookies for 1) time of day, and 2) appropriateness.

I found a way to create the queries - in simple design mode using criteria - but I want system administrators in other offices to be able to enter the time of day variables particular to their offices. Same with appropriate words. So I want a form they can use to do that - eg, list their office's break times. In essence, a setup page. Then the resulting queries (time of day and word list) will be customized to the particulars of that office rather than to mine.

Would appreciate some ideas on how to do this. Should I read up on SQL? Should I learn VBA? I'm trying to get around having to tell the other system administrators to go into each query and change the criteria manually.

Thanks!

 

[Rabbie]

Should I read up on SQL? Should I learn VBA? I'm trying to get around having to tell the other system administrators to go into each query and change the criteria manually.

Thanks!

Yes and Yes. You will need to know both of these to do the job properly. You should be able to create a form where the other administrators can set their specific criteria and then run the query to display the results in a sub-form.

 

[pilk2008]

More Specific Request

Thanks Rabbie... I keep going back to my Access Bible for basics. When I get time, I'll study the VBA and SQL books I picked up recently.

Here's something that's really puzzling me. I was able to create a simple query for my little database using as criteria something like this:

Not like "*microsoft*" and not like "*apple*" and not like "*dictionary*"

and so on to create a report that excluded all cookies containing the selected words.

Now I've created a table containing these words, and in the criteria section of my query I've tried all kinds of ways to simplify that long string of "and not like" phrases into something like: "*Not Like "*[acceptable words]![acceptable words]*" referring to the list in the table, acceptable words.

I've created a relationship between the cookie text field in the cookie table with the acceptable words in the other table. I even made the actual words the primary key in that table.

It just seems logical to me that I should be able to simplify that run-on collection of 30 words with a reference to words in a table or query.

What am I missing? Thanks for any help.

 

[petehilljnr]

You could try:

Not In (SELECT [Acceptable Words] FROM [Table Of Acceptable Words])

Pete

 

[pilk2008]

Thanks, Pete. Unfortunately I'm not looking for words per se. I'm sorting cookies - so the field I'm comparing with might be something like "owner@support.microsoft[3].txt" which is the actual cookie, and I want my query to compare an acceptable word such as *microsoft* with that string. In the end, only items that don't relate to the acceptable word list (table) would end up on a report. I've actually been using Access to perform this sort function on about 30 employees for the past couple months. Just trying to ramp up the automation as it were.

Thanks for any help anyone can offer.

 

[DCrake]

Simple Software Solutions

Here is another approach:

Create a function that receives the cookie string then do an instr() search for keywords, then if a keyword is found pass a flag back to denote the outcome.

Function TestCookie(StrCookie As String) As Boolean

Dim bFlag As Boolean

If Instr(StrCookie,"microsoft") = 0  Then
    bFlag = True
ElseIf Instr(StrCookie,"apple") = 0  Then
    bFlag = True
ElseIf Instr(StrCookie,"dictonary") = 0  Then
    bFlag = True
.....
End If

TestCookie = bFlag

End Function

Then in your query add a column

Cookie:TestCookie(FldCookie)

Your query should now have a column that contains a True/False flag you can then use this to filter out the desired records.


CodeMaster:

 

[pilk2008]

Interesting. On my way to work. Will get back to you. Thanks!