Union query

[tope12]

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.

 

[John Vinson]

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]

 

[tope12]

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]

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]

 

[tope12]

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]

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]

 

[tope12]

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]

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]