Solved

BDE 5 does not understand NULL values from Access 97

Posted on 1998-09-23
14
163 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
ID: 1340634
Try DateField.AsString='' instead of DateField=NULL

A.
0
 
LVL 1

Author Comment

by:AndersWP
ID: 1340635
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
ID: 1340636
I never work with filters, so I don't know much about it.
But why don't you use TQuery?

A.
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 10

Expert Comment

by:Jacco
ID: 1340637
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
ID: 1340638
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
ID: 1340639
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
ID: 1340640
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
 
LVL 4

Expert Comment

by:itamar
ID: 1340641
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
ID: 1340642
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
ID: 1340643
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
ID: 1340644
Anders,

Whatever you say goes, OK.

Have a nice day.

0
 
LVL 1

Author Comment

by:AndersWP
ID: 1340645
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
ID: 1340646
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
ID: 1340647
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

786 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