MS Access Between Dates 1

[OceanBlue]

Hi I'm working on a database for work. I am trying to create a query that will list all the donations made in 1998 so I put in for the Criteria:
Between "#01/01/1998#" And "#12/31/1998#"
That should find the dates from 1/1/98 through 12/31/98.
When I run the query nothing shows up...anyone have any sugggestions?
Thanks

 

[Junior1544]

This question would be best placed in the access queries and jet sql forum...
forum701 would check to see that within the query builder you have * as one of the fields, and make sure there is no check in the check box's for visible for the two with critera you have... ( you don't need to display them twice...)

just my first thoughts...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[JPinter]

You want to type in >=#1/1/98# And <=#12/31/98#, but make sure your field in your tabel is a date field not a text or it will not work.

 

[OceanBlue]

JPinter
I just tried what you suggested. I changed the field in the table as DATE/TIME and I entered >=#1/1/98# And <=#12/31/98# in the query and now it tells me &quot;The expression you entered has an invaild string&quot;

 

[jsolutions]

Ocean - a couple of things to find the problem. First, take out the criteria that you have typed in. Run your query - did you get data?

If so, now enter the criteria value of >= 1/1/98 under the date field (Note Access will put in the # signs for you if you made this field a date field). Run the query. Did you get data?

If so, now add the And statement to get >=#1/1/98# And <=#12/31/98#. Run the query, did you get data?

Whereever you answered &quot;No&quot; to the above questions is where the problem is.

Let me know where it happens and we can move forward.

Also, if you want all the data for a specific year, you can use the criteria like &quot;*&quot; & XX. Where XX is the 2 digit year. So, for you example, like &quot;*&quot; & 98 This will work for Dates or text and prevents any possible screwups with < or > signs.

 

[OceanBlue]

JSolutions

Thank you, it worked now. I used the like &quot;*&quot; & 98 which was much easier and it showed me all the donations made in 1998. I'm not very great with Access. Could you tell me how to get the Sum of all the donations for the year? Do I have to make a new field in the query. And also the Count...how many Donations were made in that year. Would that go in the query or report?

Thanks Again

 

[jsolutions]

Ocean- what you need to do is to turn on grouping in your query. To do this, open the query in design view and click on the Sigma - the greek letter E. If you hover your mouse over it, the tooltip reads &quot;Totals&quot;.

When you click on this, a new row will appear the query grid which reads Total. The default for the Totals row is Group By which means that the selected field will be grouped together (i.e. all donations from Contact 123 will be grouped together.)

You will need to do a couple of things. I am assuming (from your comment) that you simply want to know the number of donations and the total amount of donations for 1998.

When you create a totals query, you only want to include the fields that need to be grouped, totalled or used in a criteria expression.

So, in your example, you want the total number of donations and total amount donated for 1998. So, you need the year and donation amount. Since you want to do two calculations on the donation amount (total and count), you will need to include the donation amount two times in your grid.

Now, turn on the totals row. Make sure the criteria for the year is still there (the one you created earlier). Now, in the totals row, change the year field to Where. This tells Access to include only the data for the criteria you specified but not to show the field in the output. For the first Amount field, change the totals row to Sum. For the second Amount field, change the totals row to Count.

This should give you what you are looking for. Let me know if you have any problems.

Jay

 

[OceanBlue]

Thanks for your help. When I did what you told me it gives me the Sum for each field in 98..the sum for each person. Can I get the total amount of donations from all the people for 98..it should be one number and the same with the count?

 

[jsolutions]

Yes - just a reminder from my previous post : n you create a totals query, you only want to include the fields that need to be grouped, totalled or used in a criteria xpression./
This is what stumps most people on grouping queries. I am guessing that the problem is that you are including the DonorId field or some other field about the donor that uniquely identifies them.

In the grouping query, every field has to be grouped, mathematically manipulated or used in a criteria statement.

To solve the problem, remove the fields relating to the individual donors. So, you should end up with the fields Date, Amount, Amount. Remember you need the amount field twice because you want the sum and the count.

Set the criteria for the Date field as we discussed before. Make sure the Total row reads Where for the date field. Set the first Amount field total to Sum and the second one to Count.

This should give you two numbers a sum of donations and a count of donations. Note it won't show the date - Where conditions do not show up in the query results.

Give this a try and let me know.

Jay

 

[OceanBlue]

Thank you so much, you have been a great help!

 

[jsolutions]

No worries - glad I could be of help.