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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|