SQL query problem with character comparison.

Hi,
I have a problem in Foxpro. We have a query like
Select * from Account, Bank where alltrim(account.bank_id) =
alltrim(bank_bank_id) and alltrim(account.Bac) = alltrim(Bank.Bac) and
alltrim(Bank.Bac) = '0000991' and not Deleted()

Bank table has a column Bac which is type Charater(7). The application
always inserts 7 charactes in this column. Eg. for 71 it will insert
0000071. But i have some records in the table which are marked deleted
and they have bac column as 0001 i.e is less than 7 characters.
The problem is above query does not return a result set even though
there are records present.
Moreover if i pack the table Bac, the query works fine.

I also tried changing the query to Bank.Bac = '0000144'. Initially it
worked fine and returned me 400 records. Then i zapped 300 records
from account and executed the query. this time it agan gave me no
records. By some trial and error i found that the modified query
worked if there were more than 170 records. For any less it gave blank
recordset.

I want to find out the reason for such weird behaviour and if this
some Foxpro limitation

Please reply.

Thank you

.

Relevant Pages

  • Re: Can I force my bank to release funds..?
    ... > Bank has decided to terminate my account, ... My first query is, are there any legal stipulations I ... i'm having trouble getting the bank to return the ... > remaining balance that was in the account, ...
    (uk.legal)
  • RE: Display queried records with Null values (null recordcount)
    ... tables related to Assets and Transactions tables in the query, ... since AssetID from tblAssets is a foreign ... The query would return multiple rows per account, ...
    (microsoft.public.access.gettingstarted)
  • RE: How to Retrieve and diplay the details in the form
    ... query for the main form. ... "naveen prasad" wrote: ... but when i change the account number the appropriate fields are not changing. ... and get the values of d1 and display in other fields in the form. ...
    (microsoft.public.access.queries)
  • Re: Distribute to user account objects in specific AD group in SCC
    ... listed in the query above, however I got the above error. ... It does NOT work the same way as users in a usergroup. ... problem upon removing the computer account from the AD group and invoking ... *could* attempt to leverage the Top Console User, and target "computers ...
    (microsoft.public.sms.admin)
  • Re: SQL query problem with character comparison.
    ... I have a problem in Foxpro. ... Bank table has a column Bac which is type Charater. ... The problem is above query does not return a result set even though ... from account and executed the query. ...
    (microsoft.public.fox.programmer.exchange)
Loading