combining two drop down select menu's with a keywords field

[gavray]

hi,

I'm trying to combine two fields with a keyword but it ignores any keywords in the keywords field.

sSQL="SELECT JobTable.ID, CompanyTable.ID, JobTable.CompanyID, CompanyTable.Company, JobTable.industry, JobTable.Jobtitle, JobTable.region, JobTable.Jobdescription"
sSQL=sSQL & " FROM JobTable"
sSQL=sSQL & " RIGHT JOIN CompanyTable"
sSQL=sSQL & " ON CompanyTable.ID = JobTable.CompanyID"
sSQl=sSQL & " WHERE region='" & varregion & "'"
sSQL=sSQL & " AND industry='" & varindustry & "'"
sSQl=sSQL & " AND Jobdescription LIKE '%" & varJobtitle & "%'"
sSQL=sSQL & " ORDER BY JobTable.Jobtitle;"

oRS2.open sSQL, "DSN=50on"

can anyone help,

thanks

Gavin

 

[palbano]

Gavin,

That is a SQL question. And once again... get the statement correct in your DB IDE tools, not in ASP code. When you absolutly know how to create the SQL in your Database UI tool then you will easily re-create the statement in ASP code.

"But, that's just my opinion... I could be wrong".
-pete

 

[swilliams]

What do you mean by trying to combine two fields..??

Simon

 

[gavray]

okay simon & anyone else,

Maybe I didn't explain myself properly. I have two drop down menus which allow the person to select a job industry and a region the job is in.

This works fine.

But when I want to allow this to be combined with a keyword search, which is an input
field sent to the variable below

AND Jobdescription LIKE '%" & varJobtitle & "%'"

the code seems to ignore any keyword I put in. Palbano 'thoughts' are reasonably logical but you can't test keyword searches in the DB IDE tools. But then again I use homesite 4.5 and access database so i probably don't know any better.

Thanks

Gavin

 

[swilliams]

Try using * instead of % in the where clause.

Simon

 

[Guest_imported]

Hi Simon,

I was just wondering what is * supposed to do?

gav

 

[swilliams]

It is a wild card character in Access, standing for any number of other characters (Similarly, ? is a wildcard character for any single character) I thought % was used in SQL Server as a wildcard character??

Simon

 

[Guest_imported]

me not know

 

[jlkidd]

It is used in SQL Server that way but in Access it is used as a single character. Just a difference in the SQL between databases.

 

[Pandyon]

Try hard coding the job description line with an actual example (instead of using a variable). See if that gives you results.

This might seem like a stupid question, but does JobDescription include Job Title or is there a JobTable.JobTitle field?

Also, if you're using an access database, why not use access to create a quick form and query for what you want to test it out there?