A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Has Hierarchy the Parameter between years?



 
 
Thread Tools Display Modes
  #1  
Old May 12th, 2010, 08:38 AM posted to microsoft.public.access.queries
sebastico
external usenet poster
 
Posts: 74
Default Has Hierarchy the Parameter between years?

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

  #2  
Old May 12th, 2010, 02:00 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default 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

  #3  
Old May 12th, 2010, 06:04 PM posted to microsoft.public.access.queries
sebastico
external usenet poster
 
Posts: 74
Default Has Hierarchy the Parameter between years?

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" wrote:

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

.

  #4  
Old May 12th, 2010, 08:49 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Has Hierarchy the Parameter between years?

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

sebastico wrote:
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" wrote:

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

.

  #5  
Old May 17th, 2010, 01:22 PM posted to microsoft.public.access.queries
sebastico
external usenet poster
 
Posts: 74
Default Has Hierarchy the Parameter between years?

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" wrote:

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

sebastico wrote:
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" wrote:

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

.

.
-

  #6  
Old June 2nd, 2010, 07:44 PM posted to microsoft.public.access.queries
sebastico
external usenet poster
 
Posts: 74
Default Has Hierarchy the Parameter between years?

John

Now is working.
Many thanks

"sebastico" wrote:

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" wrote:

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

sebastico wrote:
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" wrote:

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

.

.
-

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:47 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.