Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

BDE 5 does not understand NULL values from Access 97

Posted on 1998-09-23
14
Medium Priority
?
171 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 450 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

715 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