search in a access database (query help)

[thek1907]

Hi guys
I was wondering how to make a search in a access database. My query looks like this:

SELECT     ID,  Heading, Description, Fix,  Modified
FROM         CoreData
WHERE     (Heading = ?)

Which work fine If I type in the entire “heading” text, but I would like to use an asterisk i the search.

Eg.
Heading field says “This is a test”

If I search for “This” – I get 0 hits
If I search for “This*” – I get 0 hits
If I search for “This%” – I get 0 hits
If I search for “This is a test” – I get 1 hit.

That’s not much of a search function ;-)

Any hint on how to make the search query correct?

Thanks

 

[DCrake]

You need to use the Like operator not the equals operator that way you can use masks.

David

 

[thek1907]

Thanks, DCrake

But I can't get it to work.

This :

WHERE     (Heading LIKE '%')

just returns til entire table.

Can you show me how the syntax should look like?

 

[DCrake]

Have you looked at the help in Access on using the Like Operator?

Select * From Table Where Field Like "*" & [AnyText] & "*"

Use the Asterisk not the percentage

David

 

[thek1907]

Have you looked at the help in Access on using the Like Operator?

No.

I'm making the query in "Query Builder" in .NET (VB), and it still not working.
I get zero results, by using : Where Field Like "*" & [AnyText] & "*"


According the MSDN "Walkthrough: Creating a Form to Search Data in a Windows Application ", then :

Access and OleDb data sources use the question mark '?' to denote parameters, so the WHERE clause would look like this: WHERE City = ?


Also I watch Beth Massi video "How Do I: Create a Search Form?" where she says that it's should look like this:

WHERE (LastName LIKE @LastName + '%')

Which dosn't do the trick for me either.

So I'm back to status quo, on this issue.

Thanks.

 

[DCrake]

If this a .net issue then why have you directed it at Access queries?

 

[thek1907]

Because the application I'm making, must have a database attached to it.

The company that need the application, do not allow the installation of SQL server, but MS Office is installed on all PC's, so it's a easy to use an Access database in the .NET application.

Then I don't have to install SQL server Express and I know that all users have Access 2007.

But are you saying, that I can't make a "access query" in a .NET application? Because the "WHERE field = ?" works, it "just" need the entire string to make a match.

Thanks for your reply's so far and have a nice weekend.

 

[MSAccessRookie]

Thanks, DCrake

But I can't get it to work.

This :

WHERE     (Heading LIKE '%')

just returns til entire table.

Can you show me how the syntax should look like?

To Locate your sample text "This is a test", you should try searching for "This*" or "*This*".

 

[thek1907]

If this a .net issue then why have you directed it at Access queries?

Because Chris Robinson on the .NET forum, told me to.
http://social.msdn.microsoft.com/Fo.../thread/48ab79cb-c33f-4fc9-8a47-a8df55879484/

Is this also the wrong forum?

 

[thek1907]

To Locate your sample text "This is a test", you should try searching for "This*" or "*This*".

Hi again

Sorry for the late reply. But this still return 0 hits.

Any other hints?

Thanks

 

[MSAccessRookie]

Hi again

Sorry for the late reply. But this still return 0 hits.

Any other hints?

Thanks

Can you show the SQL query that does not work?