Has Hierarchy the Parameter between years?

S

sebastico

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
 
J

John Spencer

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
 
S

sebastico

John
Yes, none of the fields are null
Tyears contains in my case is string (4 characters). I use string because I
don't know how to format field Year with 4 digits. Could this format affect
the query?

Your sql works great. However if I enter data only in a txtbox nothing
happens. But if I first step enter years txtboxes the query shows records,
which is OK.
But also what I need to do is If I enter data in any txtbox (except for
Years) the query must displays records.

Thanks again

John Spencer said:
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
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
Click to expand...
.
Click to expand...
 
J

John Spencer

It should work. Unfortunately, I will be offline for the next several days.
I suggest you post what you have (the full SQL statement) and an explanation
of how things are working in a new post if someone does not pickup this thread
in the next 24 hours.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
John
Yes, none of the fields are null
Tyears contains in my case is string (4 characters). I use string because I
don't know how to format field Year with 4 digits. Could this format affect
the query?

Your sql works great. However if I enter data only in a txtbox nothing
happens. But if I first step enter years txtboxes the query shows records,
which is OK.
But also what I need to do is If I enter data in any txtbox (except for
Years) the query must displays records.

Thanks again

John Spencer said:
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
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
Click to expand...
.
Click to expand...
Click to expand...
 
S

sebastico

John

Thank you very much.
I,m still trying to work witn my parameter. I'm user that if the squl des
not workd is due to me. I hope I can find the error. I'm working in a new
post.

John Spencer said:
It should work. Unfortunately, I will be offline for the next several days.
I suggest you post what you have (the full SQL statement) and an explanation
of how things are working in a new post if someone does not pickup this thread
in the next 24 hours.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
John
Yes, none of the fields are null
Tyears contains in my case is string (4 characters). I use string because I
don't know how to format field Year with 4 digits. Could this format affect
the query?

Your sql works great. However if I enter data only in a txtbox nothing
happens. But if I first step enter years txtboxes the query shows records,
which is OK.
But also what I need to do is If I enter data in any txtbox (except for
Years) the query must displays records.

Thanks again

John Spencer said:
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

.
Click to expand...
Click to expand...
.
-
Click to expand...
 
S

sebastico

John

Now is working.
Many thanks

sebastico said:
John

Thank you very much.
I,m still trying to work witn my parameter. I'm user that if the squl des
not workd is due to me. I hope I can find the error. I'm working in a new
post.

John Spencer said:
It should work. Unfortunately, I will be offline for the next several days.
I suggest you post what you have (the full SQL statement) and an explanation
of how things are working in a new post if someone does not pickup this thread
in the next 24 hours.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
John
Yes, none of the fields are null
Tyears contains in my case is string (4 characters). I use string because I
don't know how to format field Year with 4 digits. Could this format affect
the query?

Your sql works great. However if I enter data only in a txtbox nothing
happens. But if I first step enter years txtboxes the query shows records,
which is OK.
But also what I need to do is If I enter data in any txtbox (except for
Years) the query must displays records.

Thanks again

:

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

.
Click to expand...
.
-
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Parameters to search between years 2
Parameter Query w/ search form 3
Pls Help - "between" Criterea returning no results 11
Query Parameter Syntax 2
Query Parameter Syntax 6
Parameter Query using date ranges 2
parameter passthrough query 1
Parameter Values prompted for twice 5

Top