zylstrahd
Posts: 6
|
| Posted: 04/08/2005, 11:49 AM |
|
have tried everything I could do to implement an Advanced Search feature for our website
Our database stores all the records of a sales log within one table. Every field from this table is accessible on the Advanced Search page. Also, in each record is a location ID to specify where that logsheet came from (there's a total of four locations - Ames, Elk River, Elgin, and Cherokee). So when I enter criteria for only one field, the results come up fine for that specified location (based on the user's location ID in the users table). But when I enter criteria for more then one field, all records show up for all four locations.
Recently, I just finally noticed that when I select more than one criteria to search the database, it adds Or instead of an And in the SQl statement. Do you know how I can fix this?
Here is what the SQL statement looks like:
SELECT log_id, log_date, log_salesperson, log_lemco1, log_lemco2, log_lemco3, log_lemco4, log_lemco5, log_lemco6, log_lemco7, log_lemco8,
log_lemco9, log_custname, log_homephone, log_workphone, log_cellphone, log_model, log_color, log_price, log_stocknum, log_offer,
log_quote, log_city, log_state, location_name, source_name, status_name, walkphone_desc
FROM (((saleslog INNER JOIN locations ON
saleslog.log_location = locations.location_id) INNER JOIN status ON
saleslog.log_status = status.status_id) INNER JOIN walkphone ON
saleslog.log_walkphone = walkphone.walkphone_id) INNER JOIN source ON
saleslog.log_source = source.source_name
WHERE log_location = {LocID}
AND log_salesperson LIKE '%{s_log_salesperson}%'
AND log_walkphone = {s_log_walkphone}
AND ( log_lemco1 = {s_log_lemco1}
AND log_lemco2 = {s_log_lemco2}
AND log_lemco3 = {s_log_lemco3}
AND log_lemco4 = {s_log_lemco4}
AND log_lemco5 = {s_log_lemco5}
AND log_lemco6 = {s_log_lemco6}
AND log_lemco7 = {s_log_lemco7}
AND log_lemco8 = {s_log_lemco8}
AND log_lemco9 = {s_log_lemco9} )
AND log_custname LIKE '%{s_log_custname}%'
AND log_homephone LIKE '%{s_log_homephone}%'
AND log_workphone LIKE '%{s_log_workphone}%'
AND log_cellphone LIKE '%{s_log_cellphone}%'
AND log_status = {s_log_status}
AND log_model LIKE '%{s_log_model}%'
AND log_color LIKE '%{s_log_color}%'
AND log_price = {s_log_price}
AND log_stocknum LIKE '%{s_log_stocknum}%'
AND log_offer LIKE '%{s_log_offer}%'
AND log_quote = {s_log_quote}
AND log_city LIKE '%{s_log_city}%'
AND log_state LIKE '%{s_log_state}%'
AND log_source LIKE '%{s_log_source}%'
ORDER BY log_date
|
 |
 |
Nicole
Posts: 586
|
| Posted: 04/11/2005, 12:13 AM |
|
Hello,
Open Data Source dialog and verify WHERE parameters settings. Operation should be set to AND.
_________________
Regards,
Nicole |
|
|
|
|
