I have a query set so I pull DISTINCT dates in the SQL from 1/1/08 thru 12/31/08 to take out duplicate accounts, this works great when I run the report quarterly or yearly but I need to run this report monthly. I have 3 tables that work together and these dates are coming from my Activities table, I keep track of when I have had contact with them. I am looking to only count these people 1 time in a year (filter by start date does not work is it is driven by actibvity date) for example if I work with them in 1/08 run my report in 1/08 they should show up. If I work with them in 2/08 I do not want them in my 2/08 report as they were counted in 1/08.How can I filter these clients out. What expression, operator word(s) or do I need to do it in the SQL can I use. I have tried Select DISTINCT [Actibity Date] between 1/1/08 and 12/31/08 What string do I need to use to filter out the people I counted in 1/08 and so on.
Your date form is what it wrong. In order to use dates in Access with a BETWEEN function clause the values have to specifically be dates.
You can create dates in literal strings using the # sign to bound them, as in:
Select DISTINCT [ActivityDate] between #1/1/08# and #12/31/08#
#....# tells Access to treat this literal string as a date.
If you are using non-literal values, such as a parameter handed into the query, which would then be a variable, you can't use #some_variable# to cause Access to view it as a Date since #...# is for literal values only. To convert (also called "casting" a variable), use the CDate function. CDate likes to know what format to expect the string to come in though so it helps to pass that along, as in:
Select DISTINCT [ActivityDate] between CDate(begin_dt_string) and CDate(end_dt_string)
Remember the date string you pass into CDate has to be readable as a date by CDate. So make sure you pass in something that it can handle (use the IsDate() function to check to be sure so you can avoid a runtime datatype casting error). If your locale is US, then passing in x/y/z it will take for MM/DD/YYYY. If Europe, it will take it as DD/MM/YYYY. So keep that in mind.
To convert out of a date, use the FORMAT() function. That is how you get a date data type value to become a string value.
More fun reading:
http://www.techonthenet.com/access/functions/datatype/cdate.php
http://www.techonthenet.com/access/functions/advanced/isdate.php
http://www.techonthenet.com/access/functions/date/format.php
Hope this helps!
Hope it works. I have to correct myself though here:
"CDate likes to know what format to expect the string to come in though so it helps to pass that along, as in:
Select DISTINCT [ActivityDate] between CDate(begin_dt_string) and CDate(end_dt_string)"
Actually CDate() doesn't require a format string to be passed in; I didn't have it in the example line anyway! Instead it takes what you hand it and tries to cast it to a date. So using IsDate() is important to do to avoid runtime class cast exceptions.
×
669 views
Usually answered in minutes!
Re-vised Question I have a query set so I pull DISTINCT dates in the SQL from 1/1/08 thru
12/31/08 to take out duplicate accounts, this works great when I run the
report quarterly or yearly but I need to run this report monthly. I have 3
tables that work together and these dates are coming from my Activities
table, I keep track of when I have had contact with them.
I am looking to only count these people 1 time in a year (filter by start
date does not work is it is driven by actibvity date) for example if I work
with them in 1/08 run my report in 1/08 they should show up. If I work with
them in 2/08 I do not want them in my 2/08 report as they were counted in
1/08.
How can I filter these clients out. What expression, operator word(s) or do
i need to do it in the SQL can I use.
I have tried Select DISTINCT [Actibity Date] between 1/1/08 and 12/31/08
What string do I need to use to filter out the people I counted in 1/08 and
so on. IS there a what if string that will say if i had activity with them in 1/08 do not count them in 2/08
Thanks, I will try this once I get back into the office and access to the database.
enable edit vba code
×