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
|
|||
|
|||
Union query
I made a form with a button that generates a report based on the info that
the user puts in the form. The record source of the report is a query that composed of a union of select statements. Im testing two fields in the form: company name and document year. When i input the company name and document year, the information on the report pops up correctly when you put in the last 2 digits of the year. When im doing a search for all records in a specific document year, i have to put in the year in the 4 digit format. How can i set the query up to make it work on either year format. |
#2
|
|||
|
|||
On Fri, 2 Sep 2005 11:19:02 -0700, tope12
wrote: I made a form with a button that generates a report based on the info that the user puts in the form. The record source of the report is a query that composed of a union of select statements. Im testing two fields in the form: company name and document year. When i input the company name and document year, the information on the report pops up correctly when you put in the last 2 digits of the year. When im doing a search for all records in a specific document year, i have to put in the year in the 4 digit format. How can i set the query up to make it work on either year format. You're assuming that we can see your database and your query. We cannot. Please post the datatype of the Document Year field; if it's not Date/Time, please also post some sample values of the field. Also post the SQL view of your query. John W. Vinson[MVP] |
#3
|
|||
|
|||
Sorry abou that.
The datatype of the document year field is number. Sample values: 2003, 1902 This is the query: SELECT documents.agreement_num, documents.program, documents.protocol, documents.protocol_chair, documents.date_to_company_version, documents.date_to_niaid_version, documents.date_executed, documents.status, documents.companycode, documents.expiration_date, documents.doc_type1, documents.doc_yr, Company.CompanyName, documents.doc_number From Company INNER JOIN documents ON Company.companycode = documents.companycode Where ((company.CompanyName)=[forms]![report form]![text0]) AND ((documents.doc_yr)=[forms]![report form]![text22]) UNION SELECT documents.agreement_num, documents.program, documents.protocol, documents.protocol_chair, documents.date_to_company_version, documents.date_to_niaid_version, documents.date_executed, documents.status, documents.companycode, documents.expiration_date, documents.doc_type1, documents.doc_yr, Company.CompanyName, documents.doc_number From Company INNER JOIN documents ON Company.companycode = documents.companycode Where ((documents.program)=[forms]![report form]![text4]) UNION SELECT documents.agreement_num, documents.program, documents.protocol, documents.protocol_chair, documents.date_to_company_version, documents.date_to_niaid_version, documents.date_executed, documents.status, documents.companycode, documents.expiration_date, documents.doc_type1, documents.doc_yr, Company.CompanyName, documents.doc_number From Company INNER JOIN documents ON Company.companycode = documents.companycode Where ((documents.date_to_company_version)=[forms]![report form]![text12]) UNION SELECT documents.agreement_num, documents.program, documents.protocol, documents.protocol_chair, documents.date_to_company_version, documents.date_to_niaid_version, documents.date_executed, documents.status, documents.companycode, documents.expiration_date, documents.doc_type1, documents.doc_yr, Company.CompanyName, documents.doc_number From Company INNER JOIN documents ON Company.companycode = documents.companycode Where ((documents.protocol_chair)=[forms]![report form]![text8]) UNION SELECT documents.agreement_num, documents.program, documents.protocol, documents.protocol_chair, documents.date_to_company_version, documents.date_to_niaid_version, documents.date_executed, documents.status, documents.companycode, documents.expiration_date, documents.doc_type1, documents.doc_yr, Company.CompanyName, documents.doc_number From Company INNER JOIN documents ON Company.companycode = documents.companycode Where ((documents.date_executed)=[forms]![report form]![text14]) Union Select documents.agreement_num, documents.program, documents.protocol, documents.protocol_chair, documents.date_to_company_version, documents.date_to_niaid_version, documents.date_executed, documents.status, documents.companycode, documents.expiration_date, documents.doc_type1, documents.doc_yr, Company.CompanyName, documents.doc_number FROM Company INNER JOIN documents ON Company.companycode = documents.companycode WHERE ((documents.date_to_niaid_version)=[forms]![report form]![text10]) UNION SELECT documents.agreement_num, documents.program, documents.protocol, documents.protocol_chair, documents.date_to_company_version, documents.date_to_niaid_version, documents.date_executed, documents.status, documents.companycode, documents.expiration_date, documents.doc_type1, documents.doc_yr, Company.CompanyName, documents.doc_number FROM Company INNER JOIN documents ON Company.companycode = documents.companycode WHERE ((documents.companycode)=[forms]![report form]![text32]) UNION SELECT documents.agreement_num, documents.program, documents.protocol, documents.protocol_chair, documents.date_to_company_version, documents.date_to_niaid_version, documents.date_executed, documents.status, documents.companycode, documents.expiration_date, documents.doc_type1, documents.doc_yr, Company.CompanyName, documents.doc_number FROM Company INNER JOIN documents ON Company.companycode = documents.companycode WHERE ((documents.status)=[forms]![report form]![text17]) UNION SELECT documents.agreement_num, documents.program, documents.protocol, documents.protocol_chair, documents.date_to_company_version, documents.date_to_niaid_version, documents.date_executed, documents.status, documents.companycode, documents.expiration_date, documents.doc_type1, documents.doc_yr, Company.CompanyName, documents.doc_number FROM Company INNER JOIN documents ON Company.companycode = documents.companycode WHERE ((documents.doc_type1)=[forms]![report form]![text20]) UNION SELECT documents.agreement_num, documents.program, documents.protocol, documents.protocol_chair, documents.date_to_company_version, documents.date_to_niaid_version, documents.date_executed, documents.status, documents.companycode, documents.expiration_date, documents.doc_type1, documents.doc_yr, Company.CompanyName, documents.doc_number FROM Company INNER JOIN documents ON Company.companycode = documents.companycode WHERE ((documents.expiration_date)=[forms]![report form]![text18]) UNION SELECT documents.agreement_num, documents.program, documents.protocol, documents.protocol_chair, documents.date_to_company_version, documents.date_to_niaid_version, documents.date_executed, documents.status, documents.companycode, documents.expiration_date, documents.doc_type1, documents.doc_yr, Company.CompanyName, documents.doc_number FROM Company INNER JOIN documents ON Company.companycode = documents.companycode WHERE ((documents.doc_number)=[forms]![report form]![text23]) UNION SELECT documents.agreement_num, documents.program, documents.protocol, documents.protocol_chair, documents.date_to_company_version, documents.date_to_niaid_version, documents.date_executed, documents.status, documents.companycode, documents.expiration_date, documents.doc_type1, documents.doc_yr, Company.CompanyName, documents.doc_number From Company INNER JOIN documents ON Company.companycode = documents.companycode Where ((company.companyname)=[forms]![report form]![text0]) UNION SELECT documents.agreement_num, documents.program, documents.protocol, documents.protocol_chair, documents.date_to_company_version, documents.date_to_niaid_version, documents.date_executed, documents.status, documents.companycode, documents.expiration_date, documents.doc_type1, documents.doc_yr, Company.CompanyName, documents.doc_number From Company INNER JOIN documents ON Company.companycode = documents.companycode Where ((documents.doc_yr)=[forms]![report form]![text22]); "John Vinson" wrote: On Fri, 2 Sep 2005 11:19:02 -0700, tope12 wrote: I made a form with a button that generates a report based on the info that the user puts in the form. The record source of the report is a query that composed of a union of select statements. Im testing two fields in the form: company name and document year. When i input the company name and document year, the information on the report pops up correctly when you put in the last 2 digits of the year. When im doing a search for all records in a specific document year, i have to put in the year in the 4 digit format. How can i set the query up to make it work on either year format. You're assuming that we can see your database and your query. We cannot. Please post the datatype of the Document Year field; if it's not Date/Time, please also post some sample values of the field. Also post the SQL view of your query. John W. Vinson[MVP] |
#4
|
|||
|
|||
On Tue, 6 Sep 2005 06:31:04 -0700, tope12
wrote: The datatype of the document year field is number. Sample values: 2003, 1902 Then you must search for 2003 if you want to find 2003. A Number/Long Integer field might appear to be a year, to you; it certainly does not to Access, it's just a number like any other number. And the number 1998 is a different number than the number 98. I don't understand two things: 1. Under what circumstances does this query return ANYTHING with a criterion of 98 (or any two digit year) on the doc_yr field? 2. WHY the monstrous UNION query, when a simple single select query using OR logic would return exactly the same results? John W. Vinson[MVP] |
#5
|
|||
|
|||
I replaced the union statements with the or operator. I changed the datatype
of the document year attribute to date\time (short date format). I created a input mask that will only show the year, but the data in the table goes back to the short date format . Is this possible? In response to your first question, i have a primary key that contains the last two characters of the year. I think this is why the correct information show up on the report. "John Vinson" wrote: On Tue, 6 Sep 2005 06:31:04 -0700, tope12 wrote: The datatype of the document year field is number. Sample values: 2003, 1902 Then you must search for 2003 if you want to find 2003. A Number/Long Integer field might appear to be a year, to you; it certainly does not to Access, it's just a number like any other number. And the number 1998 is a different number than the number 98. I don't understand two things: 1. Under what circumstances does this query return ANYTHING with a criterion of 98 (or any two digit year) on the doc_yr field? 2. WHY the monstrous UNION query, when a simple single select query using OR logic would return exactly the same results? John W. Vinson[MVP] |
#6
|
|||
|
|||
On Wed, 7 Sep 2005 08:15:02 -0700, tope12
wrote: I replaced the union statements with the or operator. I changed the datatype of the document year attribute to date\time (short date format). I created a input mask that will only show the year, but the data in the table goes back to the short date format . Is this possible? In response to your first question, i have a primary key that contains the last two characters of the year. I think this is why the correct information show up on the report. A Year is not a date. A year is 365 dates times 86400 seconds on each date. A Date/Time value is a Double Float number, a count of days and fractions of a day (times) since midnight, December 30, 1899. As such, although you can *display* the date #1/1/2005# or for that matter #9/8/2005# as "2005" by using a YYYY format, that does NOT affect what's stored; and a query searching for a datefield equal to 2005 will return NOTHING (unless you happen to have a record for June 27, 1905 in your table). In short - you were right in the first place. Use an Integer field for the year, not a date. John W. Vinson[MVP] |
#7
|
|||
|
|||
Thanks for helping me out. Do you know the SQL datatype for a yes/no field?
I tried to use boolean,but that didn't work. "John Vinson" wrote: On Wed, 7 Sep 2005 08:15:02 -0700, tope12 wrote: I replaced the union statements with the or operator. I changed the datatype of the document year attribute to date\time (short date format). I created a input mask that will only show the year, but the data in the table goes back to the short date format . Is this possible? In response to your first question, i have a primary key that contains the last two characters of the year. I think this is why the correct information show up on the report. A Year is not a date. A year is 365 dates times 86400 seconds on each date. A Date/Time value is a Double Float number, a count of days and fractions of a day (times) since midnight, December 30, 1899. As such, although you can *display* the date #1/1/2005# or for that matter #9/8/2005# as "2005" by using a YYYY format, that does NOT affect what's stored; and a query searching for a datefield equal to 2005 will return NOTHING (unless you happen to have a record for June 27, 1905 in your table). In short - you were right in the first place. Use an Integer field for the year, not a date. John W. Vinson[MVP] |
#8
|
|||
|
|||
On Thu, 8 Sep 2005 06:42:03 -0700, tope12
wrote: Thanks for helping me out. Do you know the SQL datatype for a yes/no field? I tried to use boolean,but that didn't work. SQL/Server Booleans are, IIRC, +1 for True and 0 for False; Access Yes/No fields are -1 for True, 0 for False. I'd suggest using a Tinyint in SQL or converting it by using the Abs() function. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
UNION Query Question | [email protected] | Running & Setting Up Queries | 2 | August 9th, 2005 04:06 AM |
UNION ALL | Samora | New Users | 25 | March 16th, 2005 04:15 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |
UNION query question | Dale Peart | Running & Setting Up Queries | 6 | July 14th, 2004 12:26 AM |