For a little background, I have created a database for my company's political action committee (PAC), which among other things, list the contributions received by the PAC as well as the disbursements the PAC has made, each are in seperate tables. As required by the Federal Election Commission, the PAC has to report its contributions and receipts on either a quarterly or by-yearly basis, and I am trying create a query that would sort as such, ie produce a report for the April, July, October, Year-End. The coverage dates are constant, however the years are obviously different.

What I am trying to do is relate, for example, the April quarterly report to 1/1-3/31, and have a seperate field for the year. I do not know if this requires further explanation, but I am at a total loss. I appreciate any and all advice.

Thanks

Well, I'm certainly no guru, but I usually store dates in a mm/dd/yyyy format in my tables. Then, I use query criteria to break the date down and isolate the parts I need (e.g., months or years).

Although I've not used it before, I believe there is a DatePart function that will return quarters. I think it's something like DatePart ("q", [FieldName]), but I'm sure you can find it the Access help files. You may try the search term "date criteria" or simply "query criteria."

Good luck!

Hi -

Strongly urge that you use normal date/time data type.

Have a look here to see how Access deals with dates and times:

http://support.microsoft.com/kb/q130514/

Assuming that your day/month and year fields are strings, here's

an example of converting them to a date/time data type:

x = "7/05"
y = "2008"
z = datevalue(x & "/" & y)
? z
7/5/2008
'to prove that z is in date/time data type
? cdbl(z)
39634

Here's an example, using Northwind's Orders table, of how to
return records from a specified year and quarter.

SELECT
Orders.OrderID
, Orders.CustomerID
, Orders.EmployeeID
, Orders.OrderDate
, Orders.RequiredDate
, Orders.ShippedDate
FROM
Orders
WHERE
((Year([OrderDate])=1995)
AND
DatePart("Q"
, [OrderDate])=1);

HTH - Bob

Thank you for your quick responses. Fortunately, the PAC has only received a few contributions and made only four disbursements thus far, so changing the database around slightly is not going to be a problem. While I am aware of the Datepart function, I need to run additional reports, such as the Pre-General Election and Post-General Reports, as well as Mid-Year and End-Year reports (during off election cycles). This being the case, I was hoping to generate a query based off a form, where I select the April Quarterly, and in a seperate text box enter the date, and Access recognizing that I want to base the query off between the start date of 1/1/[Year] and end date 3/31/[Year]. I unsuccessfully attempted this function,

IIf([Forms]![Reports]![Report Type]=1 And [Receipts].[Date] Between (1/1/[Forms]![Reports]![Year]) And (3/31/[Forms]![Reports]![Year]),1,0)

I do not know what is missing from this function, or if my request is impossible to accomplish in Access. As you can probably tell, I am a novice when it comes to Access.

I apprecite the feedback.

Your syntax is off. You need to create a string that looks like a date. Your current expression is actually doing a series of divisions - 1/1/2008 which equals .000489.... and 1/31/2008 which = 1.606477.... - not what you expected I'll bet.

IIf([Forms]![Reports]![Report Type]=1 And [Receipts].[Date] Between ("1/1/" & [Forms]![Reports]![Year]) And ("3/31/" & [Forms]![Reports]![Year]),1,0)

But, I wouldn't do it that way at all. I would prompt for the quarter by number so I don't have to hard code dates.

IIf([Forms]![Reports]![Report Type]=1 And Format([Receipts].[Date],"qyyyy" = [Forms]![Reports]![Quarter] & [Forms]![Reports]![Year])

Plus, I strongly suggest that you change your data names immediately. NEVER use function names or property names as datanames. You will end up with problems that are insurmountable. Big offenders - Date, Year, Month, Name - especially Name. And while we're at it - also do not use spaces or special characters.

Thank you for the direction, query is now doing exactly what I want it to do. I will change my data names as well in order to alleviate any confusion with Access.

Again I appreciate all the help.