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
a 6ya expert can help you resolve that 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.
the service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).
click here to download the app (for users in the US for now) and get all the help you need.
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...
May 04, 2009 | Microsoft Excel for PC
Mar 23, 2009 | Microsoft Computers & Internet
Mar 06, 2009 | Microsoft Visual Basic 6.0 for PC
Feb 05, 2009 | Microsoft Computers & Internet
Aug 30, 2007 | Microsoft SQL Server 2000 Standard Edition...
628 people viewed this question
Usually answered in minutes!
Step 2: Please assign your manual to a product: