Searching database based on 3 different criteria

[ycim]

I have a form that has 3 possible search criteria.
ItemID
Category
Description

The user can enter one or all three to search on. It needs to find the closest match.

The SQL statement I am runnning looks like this.

If Me.txtSearchCategory = "" Then
    Me.txtSearchCategory = "*"
end If
If Me.txtSearchDesc = "" Then
    Me.txtSearchDesc = "*"
End If
If Me.txtSearchItemID = "" Then
    Me.txtSearchItemID = "*"
End If
strSQL = "itemid like '" & Me.txtSearchItemID & "' and " & _
         "category like '" & Me.txtSearchCategory & "' and " & _
         "description like '" & Me.txtSearchDesc & "'"
frmInfoItems.dataInfoItem.Recordset.FindFirst strSQL

When I stop at the itemid and category, the findfirst works just fine. However, it does not like the description added to it.

I am stumped. Description field is string (just like category) and there is nothing I can see. The category is more likely to have data in it, whereas description is likely to be null.

Any suggestions? Is there something wrong with the code, or should I be looking somewhere else for the issue? Any help would be appreciated.

 

[mattKnight]

Have you tried adding the wildcard character?

dim strCategory as string
dim strDesc as string
dim ItemID as strin

strCategory = txtSearchCategory.text
'If strCategory = "" Then
   ' strCategory = "*"
'else
    strCategory = strCategory & "*"
'end If

strDesc = txtSearchDesc.text
'If strDesc = "" Then
   'strDesc = "*"
'else
    strDesc =  strDesc & "*"
'End If

strItem = txtSearchItemID.text
'If strItem = "" Then
   ' strItem = "*"
'else
    strItem = strItem & "*" 
'End If

strSQL = "itemid like '" & strItem & "' and " & _
         "category like '" & strCategory & "' and " & _
         "description like '" & strDesc & "'"


frmInfoItems.dataInfoItem.Recordset.FindFirst strSQL

'

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[ycim]

Thanks. I changed it up this AM, but...Yup...same results. The strSQL has the correct output.

itemid like '543' and category like '*' and description like '*'

I am not sure, but I don't think that it is in the sql call. I looked at the database itself, but it is a pretty simple one. 3 fields (as listed above)

ItemID - text
Category - text
Description - text

Any other suggestions?

 

[ycim]

After further searching, I have found that the query does not work when the field has a null value in it. If it has a value in it, then all is good.

How do you work around that?

 

[mattKnight]

I assume you are using Jet/Access as your db engine. Please let me know if that isn't the case.

hve you tried running the query from Access i.e. from within msaccess

try the following

select * from <tablename> where itemid like '543'

select * from <tablename> where itemid like '543' and category like '*' 

select * from <tablename> where itemid like '543' and category like '*' and description like '*'

and report back

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[ycim]

Thanks. I don't know why I didn't think of that! Anyway, the query returned only the records with values in them. If I use the nz(description) function in Access, it returns all records ok. I think you have definately got me on the right track.

It does not appear VB6 has a nz function to recognize null values. Is there an equivelant?

 

[Golom]

You can use the NZ function in VB by including a reference to Access OR you can use something like

Select * From tbl 
Where (itemid LIKE '543' or itemid IS NULL)

Note however that itemid LIKE '543' is exactly equivalent to itemid = '543' because you have no wildcard characters. (i.e. *, ? or _)

Similarly, clauses like Description LIKE '*' translate to "Description contains any string" and that is what you would get if you dropped the clause completely.

You could also use

Where IIF(IsNull(itemid),"",itemid) LIKE '543'

 

[ycim]

Thanks alot for your input. Funny how such a simple thing seemed so huge! Indeed, it is one of the many challenges going to VB6 from VBA (Access). So much of it is done in VBA, that you forgot some of the basics.

Again...thanks for your help.

 

[unborn]

Thanks think this may be what im looking for! I will report back if so but this should work but i have a 14 different criterias. eek.

Running in circles is what I do best!