sql 'or' clause

[labviewproject]

Hi,

I am trying to create a search function vi which allows the user to search some columns of my database with 1 input control. Is it possible to do this if some columns have different datatypes like string & numeric?

I have this vi here which i am using to query some of the column of the same datatype in the database. it seems that there is something wrong with my 'or' clause. when the input is b or l, the some of the data displayed is correct as the firstname column will not work & the 'and' clause for firstname will also not work, resulting in all the data being displayed. however, if i remove the or clause for my lastname column, the firstname column will work but my data for the unit will not work. when the input is 1A, all the occupants in my database will be displayed & they will all have the same unit even though they live in different units. however, if i remove the firstname from my query, leaving only the unit, it works perfectly.

i believe the problem happens if i use the or clause as those conditions before the last 'or' clause & after the last 'or' clause in my query will not work. The AND Address.AddID = PersonDetails.AddID AND PersonDetails.Status = 'active' is being ignored if it is not the last 'or' clause.

Im using the select data vi to retrieve my data as there something strange happens if i use the execute sql query vi. below is my sql query & attached is my database & vi.

WHERE Address.Unit = 'b' OR PersonDetails.FirstName LIKE '%b%' OR PersonDetails.LastName LIKE '%b%' AND Address.AddID = PersonDetails.AddID AND PersonDetails.Status = 'active'

Thanks for reading & hope someones knows why this happens.

Regards,

Wayne

Message Edited by labviewproject on 08-21-2009 10:12 AM

Message Edited by labviewproject on 08-21-2009 10:13 AM

[Yamaeda · Accepted solution]

I cant open your files (got an earlier version), but as you're using a .mdb i'm assuming you have Access? Do the question work there? I have several times build a question in Access and switched to sql-mode to copy it to LabView.

Possibly a couple of paranthesis might clear up the issue. It shouldn't be needed, but it might help to use WHERE ( [OR CLAUSES] ) AND ( [AND CLAUSES] )

/Y

[labviewproject]

Thanks alot Yamaeda. I've now learn how to use parenthesis & solved my sql query problem.

I shall post the new query here for others who might need help in parenthesis.

WHERE Address.AddID = PersonDetails.AddID AND PersonDetails.Status = 'active' AND (Address.Unit LIKE 'b%' OR PersonDetails.FirstName LIKE '%b%' OR PersonDetails.LastName LIKE '%b%')