SQL and Like

[GregC]

I have a table of medical complaints. I am trying to give the user the chance to see if a similar term has been added prior to them adding a new complaint. Ex: The term "seizure" exists and the user wishes to add "seizures". I have taken the term the user has entered and trimed a few letters off it, and placed it in the variable strTerm1.

I have tried to create a sql statment as follows:

strSQL = "SELECT [tblChiefCompl_fe].[ChiefCompl_Description]" & _
" FROM [tblChiefCompl_fe]" & _
" WHERE ((([tblChiefCompl_fe].[ChiefCompl_Description])" & _
" LIKE """ & strTerm1 & "*""" & "))"

And process it using:

DoCmd.RunSQL (strSQL)

Access 2000 responds with error 2342. RunSQL requires an arguement consisting of a sql statement.

Can anyone see what is probably an obvious error on my part?

Thanks

 

[WayneRyan]

Greg,

The DoCmd.RunSQL can't be used to retrieve values. You can
only use it to do updates/inserts/deletes (action queries).
Where would it display the returned value(s)?

You can use the DLookUp function to put the description into
a textbox on your form (or "No Value" if none found):

Me.SomeControl = Nz(DLookUp("[ChiefCompl_Description]", _
                            "[tblChiefCompl_fe]", _
                            "[ChiefCompl_Description] LIKE '*" & strTerm1 & "*'"), "No value")

Wayne

 

[GregC]

Thank you Wayne. I appreciate the response. Time to reconsider how this form will work. Should be easier with yuor help.

 

[GregC]

After working with this a bit, I realize that Dlookup may not meet my needs. DLookup does work to find a single record, but there may be multiple records that fit the criteria.

The user may enter Chest Pain. I would like them to see that
Chest Pain - Cardiac
Chest Pain - Trauma
Chest Pain - Non Cardiac
Chest Wall Pain
Already exists.

I made a list box lstSmlrCmplnts. I've created a method of seperating out each word so that I can search on any one or all. I'm confused how to populate the list box with the results of a query.

 

[GregC]

Got it figured out. Thanks for the ideas.