Solved

BDE 5 does not understand NULL values from Access 97

Posted on 1998-09-23
14
167 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

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 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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses
Course of the Month8 days, 3 hours left to enroll

617 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