Has Hierarchy the Parameter between years?
Assumptions:
== None of the 5 fields are ever null or if they are null you don't want the
record returned.
== TYears contains 4 digit years (actually that probably won't matter)
SELECT *
FROM SOMETABLE
WHERE TAA Like [Forms]![Search]![txtAAID] & "*"
AND TBB Like [Forms]![Search]![txtBBName] & "*"
AND TCity Like [Forms]![Search]![txtCityName] & "*"
AND TWrds Like [Forms]![Search]![txtSpWrds] & "*"
AND TYears Between NZ([Forms]![Search]![txtFirstYear],"0000") And
NZ([Forms]![Search]![txtSecdnYear],"9999")
So basically change the criteria for TYears to
Between NZ([Forms]![Search]![txtFirstYear],"0000") And
NZ([Forms]![Search]![txtSecdnYear],"9999")
If some of the fields can contain null values then the query becomes a bit
more complex although you can cheat a bit at the cost of speed of the query by
appending a zero-length string to each of the fields (except TYears) For that
you would need to use NZ function and force the year to be "0000".
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
sebastico wrote:
Hello
Access 2003. I have a query with the following parameters.
Like [Forms]![Search].[txtAAID] & "*" ‘Data are in TAA
Like [Forms]![Search].[txtBBName] & "*" ‘Data are in TBB
Like [Forms]![Search].[txtCityName] & "*" ‘ Data are in TCity
Between [Forms]![Search].[txtFirstYear] And [Forms]![Search].[txtSecdnYear]
‘Data are in TYears
Like [Forms]![Searc].[txtSpWrds] & "*" ‘Data are in TWrds
All fields in tables are string and the db is relational.
I have 5 txtboxes to enter data. To enter data in Yyear parameter I have two
textboxes.
Here is the problem I need to fix
If I enter data in txtAAID nothing happens
If I enter data in txtBBName nothing happens
But
If I enter data (years) in the between parameter the query displays data but
If I enter data in the other boxes it works. How can I solve these problems?
Also I would like to enter data in the boxes without the years.
With txtCityName and txtSpWrds parameters I need to enter one or more
data.(string.) I don’t know to do it without including two or more data.
Your help is really welcome
|