Need help with a "CONTAINS" query.. sending me mad

[phil_s]

Sorry if this is the wrong forum to place this one under, but it is an SQL query I have coded up in VBA for a search on a Access Database.. so sorry if this is the wrong forum to place this one under......

I have a SQL Query I have coded up in some Access VBA as follows:

strSQL = "SELECT * FROM tblLessons WHERE txtRelease LIKE " & Chr$(34) & likeWkStrmProj & Chr$(34) & _
" AND txtArea LIKE " & Chr$(34) & likeArea & Chr$(34) & _
" AND (txtWhySuccFail CONTAINS (" & Chr$(34) & likeFilterCriteria & Chr$(34) & ") > 0);"

Which when I put it in debug.print gives the following:

SELECT * FROM tblLessons WHERE txtRelease LIKE "%" AND txtArea LIKE "%" AND (txtWhySuccFail CONTAINS ("Lesson") > 0);

I am trying to find rows in table tblLessons where the txtRelease and txtArea are like the passed parameters (could be spaces and in this case it was and simply replaced by a simple "%") and the txtWhySuccFail CONTAINS some text (in this case it was "Lesson"). The txtWhySuccFail is looking in a memo type field and I just want to find an occurrence of the passed value (e.g. "Lesson") of it somewhere in that returned txtWhySuccFail row.....

Can you help?? Its sending me mad!!!

I think I need to do this as two queries, something like an inner query that does the Likes and then an Outer query that filters the returned set and looks for the CONTAINS values in txtWhySuccFail...... but honestly my brain is fried and I cant work it out......

Could anybody give me an example of what this should look like, or is there a better way of doing it? As it simply is not working.....

Thanks in advance....

 

[Rabbie]

I have checked Query syntax in Access help and I can't find any reference to CONTAINS. I have 2 suggestions.

1. Try putting your SQL into the SQL window of the Query Designer and see if it runs.

2. Replace (txtWhySuccFail CONTAINS ("Lesson") > 0); with (txtWhySuccFail Like "*Lesson*") > 0);

Good luck

 

[ecawilkinson]

replace

" AND (txtWhySuccFail CONTAINS (" & Chr$(34) & likeFilterCriteria & Chr$(34) & ") > 0);"

with

" AND (txtWhySuccFail LIKE " & Chr$(34) & "*" & likeFilterCriteria & "*" & Chr$(34) & ");"

I did not really understand what you meant about the first 2 parameters. Did you add the %? Do you want TxtArea and txtRelease to be exactly equal to the parameters?

HTH,
Chris

 

[phil_s]

Rabbie,

thask for the quick reply.... yeah got me.. the CONTAINS is not valid in ACCESS... cant remember where my adled brain got that one from....

I tried swapping the "%" for "*" in the query but then got a -2147217913 "Data TypeMismatch in Criteria Expression" error... looks like I'm still racking my brain..... thanks for the thoughts

 

[phil_s]

Hi ecawilkinson..

the two parameters txArea and txtRelease are being passed into the function as arguments from another form, so I want to filter on those, but they may or maynot be present, so that is why I went for a Like and the "%" match if the value was passed as a blank. If a value is passed, then it is concatenated as say "Area1%" and put into the query. I have tried the "*" and it doesnt seem to work... will keeping on bashing away in the SQL editor.

thanks

replace

" AND (txtWhySuccFail CONTAINS (" & Chr$(34) & likeFilterCriteria & Chr$(34) & ") > 0);"

with

" AND (txtWhySuccFail LIKE " & Chr$(34) & "*" & likeFilterCriteria & "*" & Chr$(34) & ");"

I did not really understand what you meant about the first 2 parameters. Did you add the %? Do you want TxtArea and txtRelease to be exactly equal to the parameters?

HTH,
Chris