Server Linked dBase Table Not Filtering Boolean Criteria

[ROgden]

Greetings all. Came across an odd situation today in troubleshooting an Access solution I created for a customer.

They have a server resident dBase table that is linked into the Access solution. When I create a query based on this table and tell the boolean field to only return 'TRUE' values, it returns TRUE and FALSE. I even tried to created a blank access solution, only link to that table and only query on that field. Still, it returns all values. I've tried this on their system in Access 2010 and 2003 with the same results.

Now, when I run the solution with the exact same table linked on my local system, it runs/queries just fine.

Has anyone seen this type of behavior before with server linked tables? Any direction that anyone can provide will be greatly appreciated. Thanks for your time. ~R

 

[boblarson]

instead of true use

<> 0

if you want the true values and

= 0

if you want false.

I've run into that problem before on even some Access databases and found that using those alternatives work better.

 

[ROgden]

instead of true use

<> 0

if you want the true values and

= 0

if you want false.

I've run into that problem before on even some Access databases and found that using those alternatives work better.

Thanks for the reply Bob. I actually tried the criteria '<>0 AND <> FALSE AND <> NO', and still got the 'untrue' values. It's really starting to get on my nerves.

 

[vbaInet]

Try this:

WHERE ([FieldName] & "") = "True"

But the word True would depend on what value is used for true in your table, so match that up.

 

[ROgden]

Try this:

WHERE ([FieldName] & "") = "True"

But the word True would depend on what value is used for true in your table, so match that up.

I'll give that a shot.

I noticed something else odd with the table. When I go into the query results and change the boolean field focus from one record to another with the arrows keys, the value in the fields changes from 0 to -1 or vice-versa. If I hold the key down or up, the values will change in succession. However, looking at the same query results and doing a 'DLookup' in the immediate window, that returns, what I'm assuming is, the actual value. Weird behavior. I'm sure it's a server environment thing.

 

[vbaInet]

I noticed something else odd with the table. When I go into the query results and change the boolean field focus from one record to another with the arrows keys, the value in the fields changes from 0 to -1 or vice-versa. If I hold the key down or up, the values will change in succession. However, looking at the same query results and doing a 'DLookup' in the immediate window, that returns, what I'm assuming is, the actual value. Weird behavior. I'm sure it's a server environment thing.

Wondering if the results of that field is derived from a function?

 

[ROgden]

Actually no. It's from the raw field.

 

[vbaInet]

I see. It may just be your screen not refreshing on time because of the vast amount of records in the db?

 

[ROgden]

It could be a screen refresh issue for the random changing, but that doesn't account for the false records showing up.

I found this issue because I had original query string thrown from VBA. My dev system returned the correct number of records, but their internal system brought back too many, because of this boolean field issue.

 

[vbaInet]

No it doesn't. I was referring to the key up/down problems.

My only guess is that the field's datatype is of type String but the values saved into the field represent boolean values. But see if what was suggested works and then we can go from there.

 

[ROgden]

You know, as I think about how they source table is created, you may be correct about the string field. They actually have a 'Date' field that has values such as 1072009, which is kind of hard to parse out for a query. I'll try your original suggestion, hopefully tomorrow as I've got to be on-site to run it. I'll give status after wards. Thanks for your help. ~R

 

[ROgden]

So, latest update. I tried to format the boolean field as [Field] & "", and I also tried [Field] & 'TEST', then crited it for '-1', '-1TEST' etc., all with the same result.

Even when they put the linked table local, it still had the same issue, but it does not on my dev system.

I wound up having them re-export the table out with that field set as 'Text' with 'T' & 'F' values. This alternative worked, but I'm still perplexed about the original issue. If anyone has any more feedback regarding this, let me know. It's going to chew on my brain for awhile.

Thanks for to all for the help, and I hope that everyone enjoys their weekend. ~R