QBF- Leaving criteria blank

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

Hello again.

I have a query by form that im looking for physical
characteristics. The fields are birthday, race, sex,
hight, weight, hair color, and eye color. I want to be
able to plug any data into any of the fields and leave the
others blank to return results.

I have this statement: Age: DateDiff("yyyy",[DOB],Date())-
IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0)
this is in the Field line of the QBE.
It converts the DOB to a number in order to use:Between
[Forms]![Search Box]![age1] And [Forms]![Search Box]!
[age2].

I then put:[Forms]![Search Box]![Race] Or [Forms]![Search
Box]![Race] is Null. for the race criteria, and similar
one for the sex.
When I get to Height and Weight I want to use another
Between...And. for that, BUT when I do, I find I cant
leave it blank or no records are returned, even if other
criteria is entered.
I can leave either race or sex blank with no problems.

Does anyone know how to write the synatx to allow me to
leave the Between And fields blank. (I cant leave the age
blank either) in order to pull up the records that i have
entered criteria for????

Thank you in advance

Tim
 
Hello.

My favourite approach is to add the search criteria as a
field in the query. In Query Builder, you create a new
field, for example:

seekAgeFrom: [Forms]![Seek Box]![AgeFrom]

Then In then 'Where' row of Query Builder I write the
condition:

Is Null Or <=
.[Age]

and the second search value as the field:

seekAgeTo: [Forms]![Seek Box]!AgeTo

'Where': Is Null Or >=
.[Age]


This treats each field on the form as a query field, so
you can use the 'Where' restriction to compare with the
values of the table.


I hope that this could help.

Regards,
Ivar Svendsen
 
I like the idea of doing it this way, but im having
trouble inputing it.
I have a DOB field that uses mm/dd/yy. I was
given :DateDiff("yyyy",[DOB],Date())-IIf(Format
([DOB],"mmdd")>Format(Date(),"mmdd"),1,0) to add to a new
field thats not in the table called Age. This statement
converts the mm/dd/yy to a number. then I used the between
forms... and forms... to search for the age.
Ive tried entering your way into the age field, it own
fields, and neither work, because there is no field for
that. ive tried" Age: [Forms]![Search Box]![age] And
DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")
Format(Date(),"mmdd"),1,0)" and that doesnt work. The
Click to expand...
dob has to be converted before I search, unless I leave it
blank.
Any ideas? maybe add a field in the table that converts
the dob to age automatically, then searching off of that??

Thank you for your help...I had no idea you could create a
field to do that...thanks
Tim
-----Original Message-----
Hello.

My favourite approach is to add the search criteria as a
field in the query. In Query Builder, you create a new
field, for example:

seekAgeFrom: [Forms]![Seek Box]![AgeFrom]

Then In then 'Where' row of Query Builder I write the
condition:

Is Null Or <=
.[Age]

and the second search value as the field:

seekAgeTo: [Forms]![Seek Box]!AgeTo

'Where': Is Null Or >=
.[Age]


This treats each field on the form as a query field, so
you can use the 'Where' restriction to compare with the
values of the table.


I hope that this could help.

Regards,
Ivar Svendsen
.
Click to expand...
 
Tim,

In the criteria of your Age field in the query, try...
Between Nz([Forms]![Search box]![Age1],0) And Nz([Forms]![Search
box]![Age2],100)

- Steve Schapel, Microsoft Access MVP


I like the idea of doing it this way, but im having
trouble inputing it.
I have a DOB field that uses mm/dd/yy. I was
given :DateDiff("yyyy",[DOB],Date())-IIf(Format
([DOB],"mmdd")>Format(Date(),"mmdd"),1,0) to add to a new
field thats not in the table called Age. This statement
converts the mm/dd/yy to a number. then I used the between
forms... and forms... to search for the age.
Ive tried entering your way into the age field, it own
fields, and neither work, because there is no field for
that. ive tried" Age: [Forms]![Search Box]![age] And
DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")
Format(Date(),"mmdd"),1,0)" and that doesnt work. The
Click to expand...
dob has to be converted before I search, unless I leave it
blank.
Any ideas? maybe add a field in the table that converts
the dob to age automatically, then searching off of that??

Thank you for your help...I had no idea you could create a
field to do that...thanks
Tim
Click to expand...
 
Thanks Steve that worked...kinda..
I put that into the field and it allows me to leave it
blank. But when I add in the other fields that Im
searching:Race Sex Hair eye, Height, and Weight (using
that same between Nz formula for HT and WT) it doesnt
allow me to leave the subsequent fields blank. at first
with just the age and sex it worked leaving blank, but now
if I leave a field blank the fields after wont show the
criteria entered.
Even stranger is the QBE has added 17 of the between nz
statements below the original, and also numerous for the
sex, race, ht, and wt. Then it added [Forms]![Search Box]
[Race] sex, hair and eye fields that are NOT shown with
numerous IS NULLs in the criteria rows.
I take it this is normal and why its not giving me results
if I leave some criteria boxs empty and subsequent ones
full. Is there a way to get around this one?

We're so close....Thank you for your help.
Tim
 
Steve and Ivar

Ok, I figured it out. I used your NZ(... and thanks to
your help, im discovering what the syntax means.
The comma after the forms!search!ht1," " says if there is
no entry then use what ever is after the comma. I used 0
to 700 for the weight and height. For the race,sex, hair
and eyes I used a "*". That worked perfectly.
Thanks again for all your help, and for helping me to
learn how to write it myself..

Thanks again Steve and Ivar

Tim
 
Back
Top