One query that returns records by each fiscal year

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I design one query that returns values based each two digit fiscal
year? For example, the data is organized by fiscal year and then the project
number (i.e. 99 -3). I would like a list of all values for fiscal year 99,
but not any other years. I have tried the expression Like "99*" or Like "98*"
or Like "97*" (etc...) but it's not working for me. Do you have any
suggestions? Anything would be helpful. Thank you.
 
Arkie said:
How do I design one query that returns values based each two digit fiscal
year? For example, the data is organized by fiscal year and then the project
number (i.e. 99 -3). I would like a list of all values for fiscal year 99,
but not any other years. I have tried the expression Like "99*" or Like "98*"
or Like "97*" (etc...) but it's not working for me. Do you have any
suggestions? Anything would be helpful. Thank you.
Click to expand...

It'd be helpful if you posted your query & a sample of the data it is
reading.
 
Hello. This is an small example of the data table I am trying to query. I am
new to this so I don't know how to post the actual query to this newsgroup.
Thanks for your time.

FY_REPORT # DATE_RCVD SURVEYOR PROJECT/ACTION 90 - 20 5/8/1995
CRADDOCK BONNIE BAKER ROW GP 90 - 20 5/8/1995 CRADDOCK
TUNNEL VISION TS GP
90 - 23 2/22/1993 WINTHROP LOST FORTUNE TS GL
90 - 25 6/23/1995 CRADDOCK CROOKED EAR TS GP 91 -
15 4/26/1995 WINTHROP SCATTERED TS GL
 
OK, can you copy and post the SQL of your query?

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

Also, what version of Access and is this an .mdb data base or an .adp database?
Wild cards are different - * vs %, ? vs _.
 
Hello. Here is the SQL of my query. I hope this helps. Also, I am using
Access 2002 and it is an .mdb database. Thanks so much for your help.

SELECT [Surveys & Projects (1990-1999)].[REPORT #], [Surveys & Projects
(1990-1999)].[DATE RCVD], [Surveys & Projects (1990-1999)].SURVEYOR, [Surveys
& Projects (1990-1999)].[PROJECT/ACTION], [Surveys & Projects
(1990-1999)].RA, [Surveys & Projects (1990-1999)].T, [Surveys & Projects
(1990-1999)].R, [Surveys & Projects (1990-1999)].S, [Surveys & Projects
(1990-1999)].FINDING, [Surveys & Projects (1990-1999)].[15' USGS QUAD],
[Surveys & Projects (1990-1999)].[TOTAL ACRES], [Surveys & Projects
(1990-1999)].[SURVEYED ACRES]
FROM [Surveys & Projects (1990-1999)]
WHERE ((([Surveys & Projects (1990-1999)].[REPORT #])=[PLEASE ENTER THE TWO
DIGIT FISCAL YEAR OF THE PROJECT] And ([Surveys & Projects
(1990-1999)].[REPORT #])="90")) OR ((([Surveys & Projects
(1990-1999)].[REPORT #]) Like "90*")) OR ((([Surveys & Projects
(1990-1999)].[REPORT #]) Like "91*")) OR ((([Surveys & Projects
(1990-1999)].[REPORT #]) Like "92*")) OR ((([Surveys & Projects
(1990-1999)].[REPORT #]) Like "93*")) OR ((([Surveys & Projects
(1990-1999)].[REPORT #]) Like "94*"));
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your query is asking for FY 90 thru 94, which will pull all data between
90 and 94. To get the data for the FY the user enters in the pop-up
parameter use a WHERE clause like this:

WHERE [REPORT #]) Like [PLEASE ENTER THE TWO DIGIT FISCAL YEAR OF THE
PROJECT] & "*"

If the user enters "94" this criteria will evaluate to

WHERE [REPORT #] Like "94*"

and only data for FY 94 will be selected.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd33a4echKqOuFEgEQLqlwCeK2+e8MRVbuK6e3jtrS1b23PagNkAoJkg
MswrlDYtCFwFB6rZweGbUC7i
=EFip
-----END PGP SIGNATURE-----

Hello. Here is the SQL of my query. I hope this helps. Also, I am using
Access 2002 and it is an .mdb database. Thanks so much for your help.

SELECT [Surveys & Projects (1990-1999)].[REPORT #], [Surveys & Projects
(1990-1999)].[DATE RCVD], [Surveys & Projects (1990-1999)].SURVEYOR, [Surveys
& Projects (1990-1999)].[PROJECT/ACTION], [Surveys & Projects
(1990-1999)].RA, [Surveys & Projects (1990-1999)].T, [Surveys & Projects
(1990-1999)].R, [Surveys & Projects (1990-1999)].S, [Surveys & Projects
(1990-1999)].FINDING, [Surveys & Projects (1990-1999)].[15' USGS QUAD],
[Surveys & Projects (1990-1999)].[TOTAL ACRES], [Surveys & Projects
(1990-1999)].[SURVEYED ACRES]
FROM [Surveys & Projects (1990-1999)]
WHERE ((([Surveys & Projects (1990-1999)].[REPORT #])=[PLEASE ENTER THE TWO
DIGIT FISCAL YEAR OF THE PROJECT] And ([Surveys & Projects
(1990-1999)].[REPORT #])="90")) OR ((([Surveys & Projects
(1990-1999)].[REPORT #]) Like "90*")) OR ((([Surveys & Projects
(1990-1999)].[REPORT #]) Like "91*")) OR ((([Surveys & Projects
(1990-1999)].[REPORT #]) Like "92*")) OR ((([Surveys & Projects
(1990-1999)].[REPORT #]) Like "93*")) OR ((([Surveys & Projects
(1990-1999)].[REPORT #]) Like "94*"));


:
Click to expand...
 
Thanks for the suggestion, but I am unable to make it work. When I enter the
suggestion below as the criteria, the query returns with nothing. Any other
thoughts?. Thanks for your effort.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your query is asking for FY 90 thru 94, which will pull all data between
90 and 94. To get the data for the FY the user enters in the pop-up
parameter use a WHERE clause like this:

WHERE [REPORT #]) Like [PLEASE ENTER THE TWO DIGIT FISCAL YEAR OF THE
PROJECT] & "*"

If the user enters "94" this criteria will evaluate to

WHERE [REPORT #] Like "94*"

and only data for FY 94 will be selected.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd33a4echKqOuFEgEQLqlwCeK2+e8MRVbuK6e3jtrS1b23PagNkAoJkg
MswrlDYtCFwFB6rZweGbUC7i
=EFip
-----END PGP SIGNATURE-----

Hello. Here is the SQL of my query. I hope this helps. Also, I am using
Access 2002 and it is an .mdb database. Thanks so much for your help.

SELECT [Surveys & Projects (1990-1999)].[REPORT #], [Surveys & Projects
(1990-1999)].[DATE RCVD], [Surveys & Projects (1990-1999)].SURVEYOR, [Surveys
& Projects (1990-1999)].[PROJECT/ACTION], [Surveys & Projects
(1990-1999)].RA, [Surveys & Projects (1990-1999)].T, [Surveys & Projects
(1990-1999)].R, [Surveys & Projects (1990-1999)].S, [Surveys & Projects
(1990-1999)].FINDING, [Surveys & Projects (1990-1999)].[15' USGS QUAD],
[Surveys & Projects (1990-1999)].[TOTAL ACRES], [Surveys & Projects
(1990-1999)].[SURVEYED ACRES]
FROM [Surveys & Projects (1990-1999)]
WHERE ((([Surveys & Projects (1990-1999)].[REPORT #])=[PLEASE ENTER THE TWO
DIGIT FISCAL YEAR OF THE PROJECT] And ([Surveys & Projects
(1990-1999)].[REPORT #])="90")) OR ((([Surveys & Projects
(1990-1999)].[REPORT #]) Like "90*")) OR ((([Surveys & Projects
(1990-1999)].[REPORT #]) Like "91*")) OR ((([Surveys & Projects
(1990-1999)].[REPORT #]) Like "92*")) OR ((([Surveys & Projects
(1990-1999)].[REPORT #]) Like "93*")) OR ((([Surveys & Projects
(1990-1999)].[REPORT #]) Like "94*"));


:

OK, can you copy and post the SQL of your query?

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

Also, what version of Access and is this an .mdb data base or an .adp database?
Wild cards are different - * vs %, ? vs _.

Arkie wrote:

Hello. This is an small example of the data table I am trying to query. I am
new to this so I don't know how to post the actual query to this newsgroup.
Thanks for your time.

FY_REPORT # DATE_RCVD SURVEYOR PROJECT/ACTION 90 - 20 5/8/1995
CRADDOCK BONNIE BAKER ROW GP 90 - 20 5/8/1995 CRADDOCK
TUNNEL VISION TS GP
90 - 23 2/22/1993 WINTHROP LOST FORTUNE TS GL
90 - 25 6/23/1995 CRADDOCK CROOKED EAR TS GP 91 -
15 4/26/1995 WINTHROP SCATTERED TS GL
:


Arkie wrote:

How do I design one query that returns values based each two digit fiscal
year? For example, the data is organized by fiscal year and then the project
number (i.e. 99 -3). I would like a list of all values for fiscal year 99,
but not any other years. I have tried the expression Like "99*" or Like "98*"
or Like "97*" (etc...) but it's not working for me. Do you have any
suggestions? Anything would be helpful. Thank you.

It'd be helpful if you posted your query & a sample of the data it is
reading.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top