Debug errors using my search function

[vampyr07au]

I have come across an issue with my database search query.

I have a field in my table set as text because I want to use numbers and/or letters in the values I enter into it.

When I try searching for a numeric value that I know is listed in the table I get a debug error as follows:
Runtime error "3464" Data type mismatch in criteria expression

When I try searching for the value that is letters only I get debug error as follows:
Rumtime error "2001" You cancelled the previous operation.



This is the code
The me.volume section of the code is the field that the code refers to.
the dCount section of the code is where the debug highlight appears

Please Login or Register  to view this content.

[davegugg]

What is this line for:

Please Login or Register  to view this content.

?

If you are searching for data formatted as text, the entire phrase that you are searching for needs to be surrounded by single quotes.

[vampyr07au]

To be perfectly honest I don't know exactly what that line does.
The code was provided by someone here helping me out.

I never had to surround anything I was searching for with quotes before, for example when using the search function to search for a title (no single qts reqd - the search works fine without them).

The only thing that is different now is that I changed the data type for the volume part of the table to text so that it can contain both numbers or letters.
This has then caused the errors as detailed above.

[davegugg]

Which field did you change to text, Volume, Movie Title, or Rating? I'd guess Volume. If so, change this:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

btw, the line I asked about before was to clear the trailing " AND " from the criteria statement.

[vampyr07au]

ah ok I see ....

yes it was the volume field that changed to text. Will try the code change and post back.

[vampyr07au]

Made the change as instructed and the searching seems to be working except for one small problem.

Now when I do a volume search it is not recognising a partial search criteria - for example if I have a volume labelled Prog13 - doing a search just for P or Pro or 13 will popup the "no search results" msgbox instead of returning the partial results as it should.

I have checked my query and it is definitely setup with "like" search criteria however I wonder of the search button code is causing this somehow ?

[davegugg]

I wouldn't really be able to tell ya as the code you've posted above doesn't contain your query. Maybe mark this solved and start a new thread to look at the query?

[vampyr07au]

well thats the thing tho .... I don't think it is the query because that hasn't changed ... the query and search was working fine until I changed the data type to text.

The query contains search criteria that uses "like" to match partial criteria.
The query is the same for the other 2 fields and they both work as expected.

The problem I am facing now only started after I changed the data type to text for the volume field.
I didnt make any changes to the actual query at all.

[davegugg]

Right, but you may need to make changes to the query because the data type has changed. To be honest, I'm not sure how you would have used the Like operator with a date field. However, usually dates need to be surrounded with pound signs (#) in a query. If that is changing to text, you will need single quotes instead.

[vampyr07au]

Right, but you may need to make changes to the query because the data type has changed. To be honest, I'm not sure how you would have used the Like operator with a date field. However, usually dates need to be surrounded with pound signs (#) in a query. If that is changing to text, you will need single quotes instead.

NOt a date field ... Just a data field.

OK will have to have a look at it again.

Question though,

If I have search criteria in my button code do I actually NEED a query setup in the database itself ?


I tried deleting the query and have found that I do indeed still require the query to be there.

[davegugg]

It looks to me like your button checks whether the user has filled out all the information the query needs to run. If there is enough, the form will be opened to display the results. The query must run on the "search results" form open event.

[vampyr07au]

It looks to me like your button checks whether the user has filled out all the information the query needs to run. If there is enough, the form will be opened to display the results. The query must run on the "search results" form open event.

Yes thats about right .... when the button is clicked the search results are displayed based on the information provided.

The problem is ... as I said ... that it's supposed to be able to match partial search strings not just complete ones .... and it's suddenly stopped doing that now.
It still does it for the title field and the rating field just not the volume field since the change was made as above.