2. ASPRunnerPro General questions
  3. Advanced search with Empty
This topic is locked

Advanced search with Empty

 		6/1/2005 5:09:38 PM
ASPRunnerPro General questions
sparkg author

Using an Access database, ASPR 3.1 Pro...
Say we have 3 fields - field1, field2, field3.

Field3 contains some NULL values.
When we do an Advanced Search by:

field1 EQUALS xxx

field2 EQUALS yyy

field3 EMPTY
...we get erratic results.
When we search by just field1 and field2, the results are predictable.

When we search with just field3 EMPTY, the results are also predictable.
The mix with EMPTY seems to be messing up the query.
Any thoughts why that doesn't work, trouble with NULL values? Or do we have to put a default value into field3 (like 0 or "0") to make it act properly.
Thanks all!
S
 		Skynet 6/2/2005

The EMPTY value (field 3) what is the type of this value? Is a DATE VALUE?. If a DATE value the problem is becuase when your left empty this value ASPRunner don't assign NULL value until you modify the date. You can make a trick with this. Select any date, and after modify again using empty value. You will see that now works. This is because ASPRunner call to the function that assign date value after you updated.
Other problems can be what you have in field 1 and field 2, If you are using special characters like ' or ´ in database (for example fields like: VILLE D' ORION). This type of value generate error in search option. Remember that ' is a delimiter. In my case I fix this problem removing characters like ' from my database.
Skynet
sparkg author 6/2/2005

All 3 fields are text fields. I fixed it by putting "0" in all null fields, did a SQL to update the database and made "0" the default value in the ASPR Add Record.

Still doesn't explain what was going on though.

It was weird...all data was an import from an Excel file...some of the blank fields were showing up as "" - an empty string and others were NULL. That's might have something to do with the mixed results.
S
 		Skynet 6/3/2005

Some bugs was fixed in version 3.2. I think that you can try if you get same error with new version. You muts generate complete project, don't use your actual 3.1 project.
Skynet