General

Re: Query with non-blank records

Greg Collins — Sat, 05 Jan 2008 00:59:26 GMT

Yes, editing the data connection SQL command permanently changes the default query. However, the query command is available to be updated at any time using code before you query.

When using LIKE, you need to use the "%"s. The LIKE clause *is* your wildcard...

So if you use:

SELECT * FROM table WHERE column LIKE '%an%'

This will select any row where 'column' has the string "an" in it. This is not case-sensitive. Note the use of the single quote character around the "%"s.

"*" by itself is not a wild card in SQL. But using LIKE with % give you that.

Re: Query with non-blank records

troy0118 — Thu, 03 Jan 2008 20:27:10 GMT

Thanks Greg. The method you suggested is a permanent one. However, it is not necessary to be that. I also tried a wildcard query by Microsoft http://support.microsoft.com/kb/826992

This is the sql command in the above article, but it doesn't do the wildcard function. The query will only work when I give the exact words.
strMySQLCommand = strMySQLCommand + "([ABC].[Comments] LIKE \"" + strComments + "\")";

I did a little bit research again, and found out that someone suggested to put "%" and the query works.
strMySQLCommand = strMySQLCommand + "([ABC].[Comments] LIKE \"%" + strComments + "%\")";
Howerver, when I type "*" the query returns the comments that contain "*"
Again, the wildcard is not functioning.

Pleace advice.

Re: Query with non-blank records

Greg Collins — Wed, 02 Jan 2008 23:49:48 GMT

You can directly edit the SQL Command in your SQL data connection to add your WHERE clause.

Query with non-blank records

troy0118 — Wed, 02 Jan 2008 22:12:59 GMT

How do I pull the records that have values?

In Access, I can do:

SELECT * FROM ABC WHERE ((Not (ABC.Comments)="blank"));

This SQL Command will give me all records that have values in Comments field. How do I make it work via Infopath?

Thanks.