Search engine

[egghi]

Hi,

I have a simple ACCESS (ACCESS 2000) form which allows users to search the table by setting 1 or multiple of the 5 different parameters (i.e. customer name, order date, order ID, customer phone number, product name). Some of the parameters use a drop-down menu, and some allow users to manually enter the data (i.e. phone number). How do I write a query or VB script, which will search the ORDER table stored on the SQL server based on the criteria set by the user?

My guess:

SELECT *
FROM ORDER
WHERE CustomerName LIKE "%howtowritethestring" OR
OrderDate LIKE "%howtowritethestring" OR
OrderID LIKE "%howtowritethestring" OR
CustomerPhone LIKE "%howtowritethestring" OR
ProductName LIKE "%howtowritethestring"


Since I am a newbie to using ACCESS's project, any advice in writing this query or VB script will be a tremendous help!

Thank you in advance!

Egghi

 

[AlexCuse]

How is the query to be displayed? If it is in a subform or control on your current form, I would think the best way is to dynamically set the recordsource/rowsource. You would want to use IF statements to build a SQL string based on what controls have been selected.

Using OR will not really give the results the user wants, as if they populate two fields they expect to see everything where OrderDate = 10/9/06 AND CustomerPhone = 555-1212.

This is the command to change a ListBox's row source

Forms("Form1").Controls("List0").RowSource = "select * from inputdata"

Now you could build your sql statement like this:

SQL 1 = "select * from TableName where "

IF Forms("FormName").Controls("CustomerName").Value is null then
SQL2 = "" 
ELSE 
SQL2 = "where CustomerName = [Forms]![FormName]![CustomerName] AND "

IF Forms("FormName").Controls("OrderDate").Value is null then
SQL3 =""
ELSE
SQL3 = "where OrderDate = [Forms]![FormName]![OrderDate] AND "

SQL 4 = "1 = 1"

Then you could create your full SQL String by setting

theSQL = SQL1 & SQL2 & SQL3 & SQL4

And set your list source using this:

Forms("Form1").Controls("List0").RowSource = theSQL

Note the and's at the end of each piece of SQL, this is just the easiest way to incorporate them and have your statement format correctly. The 1 = 1 is needed in order to ensure that there is something after the last AND.

Make sure to test, and you'll probably need to print the SQL Statement in your immediate window quite a few times to get it to properly format.



Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[egghi]

Thank you so much! I will give it a try and let you konw if it works!!

Thank you, thank you, thank you!

 

[AlexCuse]

Hope it works for you. I think that it will, depending on how you are displaying the info. Be careful with the spaces within your SQL string, because you will be combining 7 components or so it could get tricky.

Good Luck,

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.