Solved

BDE 5 does not understand NULL values from Access 97

Posted on 1998-09-23
14
161 Views
Last Modified: 2013-11-23
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
0
Comment
Question by:AndersWP
  • 5
  • 3
  • 3
  • +2
14 Comments
 
LVL 2

Expert Comment

by:333
Comment Utility
Try DateField.AsString='' instead of DateField=NULL

A.
0
 
LVL 1

Author Comment

by:AndersWP
Comment Utility
Well, 333, since the problem relates to filter expressions, the AsString method is not available.

Thanks for the comment, though.

AndersWP
0
 
LVL 2

Expert Comment

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

A.
0
 
LVL 10

Expert Comment

by:Jacco
Comment Utility
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
 
LVL 4

Expert Comment

by:itamar
Comment Utility
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
 

Expert Comment

by:KirkGray
Comment Utility
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
 
LVL 1

Author Comment

by:AndersWP
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 4

Expert Comment

by:itamar
Comment Utility
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
 

Expert Comment

by:KirkGray
Comment Utility
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
 
LVL 4

Expert Comment

by:itamar
Comment Utility
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
 

Expert Comment

by:KirkGray
Comment Utility
Anders,

Whatever you say goes, OK.

Have a nice day.

0
 
LVL 1

Author Comment

by:AndersWP
Comment Utility
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
 
LVL 4

Accepted Solution

by:
itamar earned 150 total points
Comment Utility
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
 
LVL 4

Expert Comment

by:itamar
Comment Utility
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now