Form controlling a querie

[Francisco]

I've a Table that has records regarding birthday, Wheight, Height, sex ond
some others fields, I want to build a form to search/sort my database.

TblOriginal
BD Wheight(Kg) Height (cm) (...)
a 29/8/1976 75 175
b 19/4/1983 70 158
c 24/9/1990 56 138

First I made a query from my original table, with all the fields plus
another with the age ((Toda()- [Birthday])/365), then I builded a Form with
two drop down field for each field (Age, Wheight and Height), one for the
max and another to the min.

I got the criteria to work when I give all the criteria fields a value, and
now I want to only fill the criteria I want, i.e , I only want to know the
subjects with the age bigger than 30 years (a) so I'll only fill in the form
the drop down regarding age min, leaving all the others blank, so the result
will be all the wheights + all the heights + only the animal with 30years old
or more.
Also I would like this to work with the others criterias.

Can this be done?

Hope someone can help me.

Sorry for the confusing problem.

Francisco Teixeira
Universidade de Èvora

 

[John W. Vinson]

I've a Table that has records regarding birthday, Wheight, Height, sex ond
some others fields, I want to build a form to search/sort my database.

TblOriginal
BD Wheight(Kg) Height (cm) (...)
a 29/8/1976 75 175
b 19/4/1983 70 158
c 24/9/1990 56 138

First I made a query from my original table, with all the fields plus
another with the age ((Toda()- [Birthday])/365), then I builded a Form with
two drop down field for each field (Age, Wheight and Height), one for the
max and another to the min.

This isn't actually a very good way to calculate ages. With leapyears in the
mix, the ages will be off by a few days. A real "birthday" age can be
calculated using the expression

Age: DateDiff("yyyy", [BD], Date()) - IIF(Format([BD], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

This will increment the age on the person's birthday.

I got the criteria to work when I give all the criteria fields a value, and
now I want to only fill the criteria I want, i.e , I only want to know the
subjects with the age bigger than 30 years (a) so I'll only fill in the form
the drop down regarding age min, leaving all the others blank, so the result
will be all the wheights + all the heights + only the animal with 30years old
or more.

A criterion of >= 30 will find only those people older than 30 years. An
alternative is to put a criterion on BD of

< DateAdd("yyyy", -30, Date())

to find all people who were born prior to thirty years ago today.

Also I would like this to work with the others criterias.

Use [Enter age cutoff:] instead of 30 in the above and you can enter any age
you like.

John W. Vinson [MVP]

 

[Francisco]

Thank you for the quick reply.

Regarding the age calculation, thank you for the tip, it's a valuable one.


About the criteria stuff, I didn't explain well, I need is some way to tell
each criteria that when I don't give a value on the "search" form, I don't
need to get all the values, this is, I can get any or even none value on that
field. I need to get only the criteria that I filled, those left in blank
would not matter to sort the results.
So I'll can look for all the subjects that have 30 to 35 years, have a
height >160 and all wheights (even those I haven't got data - null).
I could give a value for each field so they could not get a null, and then
give also default values of 0 to the min and a exhagerated value to the max,
this would work, but I was looking for something more clean.

Thanks again for the quick reply.
Francisco Teixeira

P.S. - This is confusing, but what I need is to get a form to work as a
search engine, where I can select what can be the criterias I want to search,
and those that are left in blank won't matter on the search. Like the serches
in a library, where we can choose to search, By title and author, or only by
one of them, or more criterias.
So in my case I can sort cows regarding some of their characteristics.

I've a Table that has records regarding birthday, Wheight, Height, sex ond
some others fields, I want to build a form to search/sort my database.

TblOriginal
BD Wheight(Kg) Height (cm) (...)
a 29/8/1976 75 175
b 19/4/1983 70 158
c 24/9/1990 56 138

First I made a query from my original table, with all the fields plus
another with the age ((Toda()- [Birthday])/365), then I builded a Form with
two drop down field for each field (Age, Wheight and Height), one for the
max and another to the min.

This isn't actually a very good way to calculate ages. With leapyears in the
mix, the ages will be off by a few days. A real "birthday" age can be
calculated using the expression

Age: DateDiff("yyyy", [BD], Date()) - IIF(Format([BD], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

This will increment the age on the person's birthday.

I got the criteria to work when I give all the criteria fields a value, and
now I want to only fill the criteria I want, i.e , I only want to know the
subjects with the age bigger than 30 years (a) so I'll only fill in the form
the drop down regarding age min, leaving all the others blank, so the result
will be all the wheights + all the heights + only the animal with 30years old
or more.

A criterion of >= 30 will find only those people older than 30 years. An
alternative is to put a criterion on BD of

< DateAdd("yyyy", -30, Date())

to find all people who were born prior to thirty years ago today.

Also I would like this to work with the others criterias.

Use [Enter age cutoff:] instead of 30 in the above and you can enter any age
you like.

John W. Vinson [MVP]

 

[John W. Vinson]

P.S. - This is confusing, but what I need is to get a form to work as a
search engine, where I can select what can be the criterias I want to search,
and those that are left in blank won't matter on the search.

This is pretty standard. The usual technique is to build up a SQL string in
code, looping through the unbound controls on the criteria form, adding a
clause to the SQL WHERE for each non - NULL value.

One example (there are several around) is from Allen Browne:

http://allenbrowne.com/ser-62.html

John W. Vinson [MVP]

 

[Francisco]

That's it! Thank you very much for your time and help.

Francisco Teixeira