• Registered: 2014-08-02
  • Posts: 913

Topic: SQL Between

Hello Dmitri

Please, would you take a look on my project in attachment ?

I was testing SQL Between to retrieve a range of records depending on the size of the birds.
I have seven checkboxes (all with incremential search behind a button (hidden)
Some of them works fine, others causing me problems :

See button Sparrow, button Blackbird and button Swan.
The first two retrieve values with 3 digits while the request is for 2
The last retrieve nothing while tables has available values.

What is wrong is my SQL script ?

And, less important, why this instruction doesn't works :

If Identification.CheckBox1.Checked = False then
     begin
        Identification.TableGrid1.ClearRows;
     end;

Thanks for your help

JB

Post's attachments

Attachment icon Birds.zip 359.27 kb, 451 downloads since 2016-03-18 

  • Registered: 2014-12-02
  • Posts: 136

Re: SQL Between

Looks like your field Taille is a text field rather than an integer.  If you change the type and rerun your sql it will pull back the proper values.

3 Reply by rjkantor (edited by rjkantor 2016-03-18 19:42:20)

  • Registered: 2014-12-02
  • Posts: 136

Re: SQL Between

The sparrow checkbox has the allowGrayed option as True compared to the other checkboxes.

RK

  • Registered: 2014-08-02
  • Posts: 913

Re: SQL Between

Hello rjkanrtor

Thanks for your answer.
About type field, you're right.

At the beginning, I set up  as integer.
But as values can sometimes be as '25 to 30', I changed it as Text.
This is why i put values to retrieve between 'quotes'.
But that does not solve my problem.

I have also deactived property AllowGray
Perhaps Dmitry has any idea ?

Thanks

JB

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,683

Re: SQL Between

Hello Jean,
I am not sure if I understand your problem 100%. 
When you write ".....value can sometimes be as 25-30", do you mean that each bird could be between 'x' and 'y' - for example, BECASSINE DES MARAIS could be 26-28cms?
If so, then I think you would need to have 2 values in your oiseaux table - taille-minimum and taille-maximum (both defined as integer) and then your SQL query would be something like .....where(taille-minimum > NN and taille-maximum < NN).
Also, some of the ranges in your SQL Queries are overlapping which may cause problems (for example, Raven = 45-71, Buzzard = 57-70) but perhaps this is intentional - I do not know enough about birds!
Derek.

  • Registered: 2014-08-02
  • Posts: 913

Re: SQL Between

Hello Derek

My project was only a test to understand how works SQL queries with numbers.
(There are several ways to do and I wanted to find the best and least complex).
You were right : when I change type from Text to Integer, no problem, it works fine, values retirved are all in the range wanted.

For values wich go 'from X to Y' I think I'm going to your proposal (taille mini) and (taille Maxi).
In all cases, solution depends on type of fields : INTEGER is the best.
If field is of type Text, there are too many problems to manipulate texte and numbers, even by framing values with quotes.
This can be done but it is Paris-Dakar !

About your considerations on some ranges, I'm responsible, I have not updated reals values.
Thank for your sharp eye ... and for your advice.

JB