querying for string in cell and how to display msg if no matches found

[cathywoodward]

Good day Everyone, I am looking for some help with a database I have designed for tracking the status of signals used in a system. I have two things I would like to do, however I cannot figure them out yet.

1) My database have over 2000 signals that have signal IDs and signal decriptions. I can query based on the signal ID but I am having troubles with searching by the description.

An example of a description is: AC PORT FAN

What I have is the user clicks a button that opens a window to enter a parameter. The following code I am using in the query design is as follows: Like "*" & [ENTER SIGNAL NAME] & "*"

This works, however I have to type in exactly "PORT AC FAN" or I can type in one of the words like "PORT" and It returns every signal with PORT (which is alot).

What I want is a way to type in "PORT FAN AC" and get the "AC PORT FAN". I want it to take each string entered into the box and find the best match. Also if I serach "PRT FAN AC" and it cant find those three words then it will return the next best match ie: AC STBD FAN / AC PORT FAN / AC FAN / etc

I dont know if this is possible but it would be very helpful if the siganl ID is not know and the user has an idea of the description to zero in on the right signal.

2) My second question is about displaying a msg box if a query returns no records. I can do this with DCount and it works great. However I need to do this with a paramter query. The DCount does not allow this. I want the user to enter text to find in a cell (question 1). If this query based on this text retuns nothing I want a message to pop-up. Is there away to do this with a parameter query?

This is my first post, I eagerly await a reply. Thanks!

 

[Guus2005]

Both questions can be solved using VBA.

The answer for the first question is a bit difficult you could use the soundex function which wanders around on the internet. When you have found it and implemented it and if it doesn't work or you don't know how, please get back.

The second question can be solved when you use a different approach to executing the parameter query.

Using code with a single parameter:

dim qdf as querydef
dim rst as recordset

set qdf = currentdb.querydefs("query1")

qdf.parameters("ENTER SIGNAL NAME") = inputbox("Please ENTER SIGNAL NAME")

set rst = qdf.openrecordset(dbOpenSnapShot)

'rst.movelast:rst.movefirst 'Remove quote when recordcount doesn't work
lngAantalRecords = rst.recordcount

Ofcourse 'query1' is a fictional queryname, use your own.

Share & Enjoy!

 

[cathywoodward]

Thank you for the help, using your provided code I was able to get a msg displayed when a parameter query returns no records, however if there are records, I want to display them still.

I have added the following if statement to test for records:
If lngAantalRecords > 0 Then
'I need to open the query to view recordsets
Else
MsgBox "THERE ARE NO RECORDS FOR YOUR QUERY"
End If

When there is records, I want to view the records without having the user to re-enter the parameters.

Thanks, I am one step closer.