Question about Microsoft Office Access 2003 (077-02871) for PC
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:
Hope this helps!
Posted on Feb 23, 2008
Save hours of searching online or wasting money on unnecessary repairs by talking to a 6YA Expert who can help you resolve this issue over the phone in a minute or two.
Best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.
Here's a link to this great service
Posted on Jan 02, 2017
Tips for a great answer:
Jul 04, 2014 | Microsoft SQL Server 2008 Enterprise Full...
Jul 15, 2013 | Microsoft SQL Server 2005 Standard Edition
Oct 15, 2009 | Microsoft Designing ASP.NET Applications...
Mar 23, 2009 | Microsoft Computers & Internet
Mar 06, 2009 | Microsoft Visual Basic 6.0 for PC
Feb 05, 2009 | Microsoft Computers & Internet
Nov 14, 2007 | Computers & Internet
Aug 30, 2007 | Microsoft SQL Server 2000 Standard Edition...
Oct 22, 2017 | Microsoft Office Access 2003 (077-02871)...
Jun 24, 2017 | Microsoft Office Access 2003 (077-02871)...
630 people viewed this question
Usually answered in minutes!
Step 2: Please assign your manual to a product: