BDE 5 does not understand NULL values from Access 97

I have an application developed using Delphi 3 and BDE 4.51 which talks to an Access 97 database. After installation of Delphi 4, this application no longer works. I tried getting some help Inprise/Borland support, but that is like talking to a wall. So I figured, what the h***, I'll just rebuild the program under D4, and all will be well.

Not so.

My application uses a lot of filter expressions of the form
'DateField>=<SomeDate> Or DateField=NULL'. The catch is, that the new BDE does not understand NULL values in filter expressions. Records with NULL values are simply not included in the set of records selected with such a filter.

Does anyone know why, and does anyone know a fix or a workaround?

Thanks in advance for any input,
AndersWP
LVL 1
AndersWPAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
itamarConnect With a Mentor Commented:
Hi AndersWP,

      This workaround will work for you:

      Const
        FIRST_DATE = #39'01/01/1900'#39;
      begin
        Table1.Filter := '(SaleDate> ''20/05/1988'') or (SaleDate < ' + FIRST_DATE + ')';
        Table1.Filtered := True;
      end;


0
 
333Commented:
Try DateField.AsString='' instead of DateField=NULL

A.
0
 
AndersWPAuthor Commented:
Well, 333, since the problem relates to filter expressions, the AsString method is not available.

Thanks for the comment, though.

AndersWP
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
333Commented:
I never work with filters, so I don't know much about it.
But why don't you use TQuery?

A.
0
 
JaccoCommented:
Have you tried

IS NULL

in stead of
 
= NULL

(This is the way it should be described in standard SQL, I don't know about filter expressions since like 333 I never use them)

Regards Jacco
0
 
itamarCommented:
Hi AndersWP,

This workaround will work for you:

Const
  FIRST_DATE = #39'01/01/1900'#39;
begin
  Table1.Filter := '(SaleDate> ''20/05/1988'') or (SaleDate < ' + FIRST_DATE + ')';
  Table1.Filtered := True;
end;

If it's OK, tell me so I can post it as an answer.

IHTH,
Itamar
0
 
KirkGrayCommented:
I'm Not sure about filters and how they work, but your problem seems to stem from what jacco said about IS NULL (This is the ANSI SQL Standard for NULL comparison).

(If U R using ODBC, this is can be a real problem, since the driver can insist on using ANSI SQL)

-Or-

Could be you need to use access/jet syntax,  that would be isnull(datefield)

Cheers!



0
 
AndersWPAuthor Commented:
KirkGray, thank you for your answer. You will have to bear with me for some days. I have a deadline to meet, and I will be able to evaluate your workaround untill sometime next week.

Sorry, and thanks,
AndersWP
0
 
itamarCommented:
Hi Anders,

ATTENTION : The workaround was proposed from me ITAMAR as a comment.

KirkGray posted an answer, that doesn't seem to be an answer to me.
Perhaps he is waiting for some easy points...
0
 
KirkGrayCommented:
Itamar,

Thats not a very sporting thing to say.... If you have an answer I suggest you put your butt on the line and propose it as an answer!

I would also point out that any question one knows the answer to is "Easy points".

Why don't we try to help Anders with his question, instead of slagging one another off?  He's a big boy, I'm sure he knows how to reject an incorrect answer all by himself..

PS:  I thought your workaround was a good one, does null always evaluate to less that 1/1/1900?
 
Fondest Regards,
  Kirk

0
 
itamarCommented:
To Kirk:

1st of all I didn't post my comment as an answer because other people can come out with a better solution. The questions are more visible when they are not locked. If there is no more interesting ideas, the user can ask to me to post the comment as an answer.

2nd I had to make some tests to find out the workaround, I didn't know it at first. What I call easy-points is to place an empty answer waiting for an user mistake.

3rd AndersWP is a big-boy but can make a mistake. And he was about to make it. He put a comment to Kirk about the workaround !! Grading the wrong answer could be the next mistake.

I deal with E-E points just guessing if the site was not free ( it was an old E-E project, remember it ? ), and this kind of situation makes me not believe in it.

Last, but not least, if there is someone who is trying to help Anders in this question, I'm this one !

Regards,
Itamar

P.S.: SBE (Sorry my Bad English)
0
 
KirkGrayCommented:
Anders,

Whatever you say goes, OK.

Have a nice day.

0
 
AndersWPAuthor Commented:
I am happy to say that Itamar's workaround using FIRST_DATE = #39'01/01/1900'#39 seems to do the trick. Thanks, Itamar.

Now, this means that I will have to reject KirkGray's answer (I want to thank you too for your input, KirkGray). Itamar, if you will re-post his solution as an answe, I will give you credit for it.

As a side remark, I would suggest that you lighten up a bit, Itamar. I think you were out of line with your criticism of KirkGray. The only mistake here was mine when I parhaps gave the impression (unintended!) that I would credit KirkGray for your solution. So, if you feel a need to flame someone, flame me.

Regards,
AndersWP
0
 
itamarCommented:
Hi,

well I think I was very "hard" in my comments. It´s dificult to "calibrate" those kind of comments in a foreign language.

Sorry if I had ofended someone.

Regards,
Itamar
0
All Courses

From novice to tech pro — start learning today.