09-10-2008 10:52 PM
I would like to build an expression to exclude records with a field starting with the string "OLD".
I can't find a way of doing that; nor can I find a good help page explaining the syntax of wildcard characters, advanced queries or the syntax of Act's QRY XML file.
09-11-2008 01:13 PM - edited 09-11-2008 01:18 PM
I'm not familiar with the "QRY XML file", so this may not be applicable to you, but I found that when working in Act, when you do a lookup, you can use the SQL wildcard "%". So for your query you could search for "OLD%" (without the quotes).
I just noticed you're wanting to exclude these records though. So you could create an advanced lookup of "fieldname" "Does Not Contain" "OLD%"
09-11-2008 03:10 PM
Thanks 'snakeroot' for your response. It seems to be logic enough and I had tried it before posting my message, but it doesn't return the correct result!
Here is a little experiment I have done on my database of 42,990 contacts. Performing a query from the Lookup ID/Status and entering "OLD%" (no double quotes) returned 2,299 records. Performing the equivalent query from the Advanced Query dialog with Contains and "OLD%" returned 2,302 records. The three extra records start with other letters but have (end with) "OLD", for ex. "Personal, OLD". Obviously, this is not the way wild characters usually behave.
When I select the records with the "Does Not Contain" function and "OLD%" as the string in the Advanced Query dialog, only 4,981 are selected, which is not correct.
Is that yet another bug of ACT! ? Boy, I wish Act! 4 would run on Windows Vista!
09-11-2008 07:46 PM
Alright, I figured out that the "Contains" and "Does Not Contain" operators don't consider records with a Nothing value for the field being querried. It would be nice to have appropriate documentation with the software...
Can anybody recommend a good reference for building querries in Act! (SQL Server?) or Act! XML QRY file syntax?
09-11-2008 08:05 PM
The "%" is the wildcard character. So by placing the % after OLD, it's looking for anything that starts with OLD and has whatever characters behind it. If you want to search for anything that has OLD in it no matter what characters surround OLD, then you need a wildcard in front of OLD also, so it would be "%OLD%" This would filter out such entries as: "123OLD3433" or "SOLD" or "OLDSMOBILE".
As far as NULL value fields, you can have another line in your query that says "field" "Does Not Contain Data".
So your advanced query would be:
Line 1: "fieldname" "Does Not Contain" "%OLD%" AND
Line 2: "fieldname" "Contains Data"
Hope that helps... Sorry I don't know of any references off hand.
09-11-2008 08:54 PM
I agree with you on the syntax of %, but the results don't. OLD% actually selected the string "Personal, OLD", which it shouldn't have. The expression for that should be %OLD, not OLD%.
Do you know if there is any OLEDB driver that allows Windows applications (ArcGIS) to edit Act! data? I could only find one that would read (and it actually doesn't work with ArcGIS anyway).
09-11-2008 09:21 PM - edited 09-11-2008 09:43 PM
I'm sorry I'm getting confused now. In your first post you said
"I would like to build an expression to exclude records with a field starting with the string "OLD". "
I suggested doesn not contain OLD% and you replied that the results showed:
"three extra records start with other letters but have (end with) "OLD", for ex. "Personal, OLD". "
This is logical because as you stated in your first post you want to exclude records with a field "starting with the string "OLD"." "Personal, OLD" doesn't start with the string OLD, so it would be logical that the expression I suggested wouldn't filter it out.
I believe that "field" "Does not contain" "%OLD%" (notice the % in front and behind OLD) would filter out the records with OLD in them.
If I understand correctly, you then figured out that you need to explicitly show the records that have null values in that field because Act doesn't show them automatically. For this you need to add another line to your query that would read AND "field" "contains data" . Then add a 3rd line OR "field" "Does not contain data". I'm not sure, but you may need parenthesis around the AND statement and another set around the OR statement. Logically, it should read like this:
("field" "does not contain" "%OLD%" AND "field" "contains data" ) OR ("field" "Does not contain data" )
Show me "this" or "this
More specifically, show me records that don't have the word OLD in it or show me records with NULL values. Then combine the two into one big result.
As for ArcGIS, I'm sorry I don't use that program and have no knowledge of it.
11-12-2008 08:14 AM - edited 11-12-2008 08:17 AM
Sorry for the lapse in time... I still haven't figured this out...
I don't think the % wild character has any impact on the negative functions such as "DOES NOT CONTAIN" or "IS NOT EQUAL TO":
fieldname does not contain "Old",
fieldname does not contain "%Old", and
fieldname does not contain "%Old%
return the same records.
I could select all the records that I don't want and then switch the selection, but haven't figured out how to do that last step in ACT! I don't think there is a way.
11-12-2008 09:31 AM - edited 11-12-2008 09:33 AM
Alright, I am not an Advanced Query expert, but I have some insights.
Looking at Advanced Query, I would guess that since the options are "Contains" or "Does Not Contain", ACT! is slipping in the % wildcards for you so that you don't have to be an SQL expert to use it. (Similarly, Starts With probably appends a %, and End with prepends one...) (In fact, on some systems built for novice users which do this type of thing, the % you enter would be escaped so that it only the string "%Old%" would be matched. I don't know if ACT! does this, but since it is obviously putting in the %'s, you never know now, and if not now, what about in the future...)
An alternative aproach might work (as a one-off type of thing, not as a query that can be updated regularly...):
1) Do a "Starts With" "Old" query.
2) Sort by Contact
3) Copy to a temporary location the first and last names in the list.
4) Create a new Advanced query:
Line 1: (Contact "Less Than" <First Name> OR
Line 2: Contact "Greater Than" <Last Name> )
Line 3: <Some query which either explicitly includes or Excludes empty values depending on what you want...>
If you need a query which will work "most of the time", you could try something like:
Line 1: (Contact "Less Than" Old OR
Line 2: Contact "Greater Than" Old<LargeValue > )
Line 3: <Some query which either explicitly includes or Excludes empty values depending on what you want... >
where <LargeValue> is a character with a very large collation order value (such as Z or 64100 (some Unicode character))
NOTE: The character you select will be affected by the colation order, so in my testing 64100 wasn't as "good" as Z. With some testing, I'm sure you can find your "ideal" <LargeValue> character which covers most of your data.
This wouldn't be a 100% guarantee to work, but it would more likely than not work most of the time...
11-12-2008 10:31 AM
Thanks for your input, but I need the exact records all the time and can't create a separate database to achieve this. I don't want to get into witchcrafting either. It's hard to believe that Sage, or any software company, would not include the functionality that I am looking for.
Believe it or not, all this was very simple to do with ACT! 4 (but it doesn't run under Vista). I could do it inside of ACT! 4 or outside; for example by accessing and editing its dBASE file within a Geographic Information System (GIS). Now not only has Sage blocked the OLE/ODBC back door to the ACT! databases, but it doesn't even provide any documentation on how to perform simple queries (even though Sage calls them advanced).