Question about Microsoft Office Access 2003 (077-02871) for PC

1 Answer

Microsoft Access Query

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.

Posted by on

  • 1 more comment 
  • nvacek Feb 13, 2008

    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

  • nvacek Feb 28, 2008

    Thanks, I will try this once I get back into the office and access to the database.

  • Anonymous Mar 13, 2008

    enable edit vba code

×

1 Answer

  • Level 1:

    An expert who has achieved level 1.

    All-Star:

    An expert that got 10 achievements.

    MVP:

    An expert that got 5 achievements.

    Mentor:

    An expert who has written 3 tips or uploaded 2 video tips.

  • Contributor
  • 51 Answers

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!

Posted on Feb 23, 2008

  • mcc99
    mcc99 Feb 28, 2008

    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.

×

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

3 Answers

What is SQL?


It is a special purpose programing language. Computer language you will never use, unless you are into servers or programing type software

Jan 27, 2015 | Oracle 10g Database Standard (ODBSEONUPP0)

2 Answers

How to Change SQL Server 2005/2008 User Password


There is one SQL password recovery tool, SQL Password Genius, which would help you to successfully change SQL Server 2005/2008/2012 user password instantly, especially when you couldn't think of other ways for it.

1. Open SQL Server Configuration Management and stop SQL Server Services.

2. Install SQL Password Genius full version and run it on computer.

3. Click "Open File" button to import master.mdf database file into this recovery tool.

4. Select user in list and click "Reset" button.
Instantly, new user password would be available for SQL Server logon after you type new password for user.

Now, SQL Server user password has been changed and you can login into SAL server now. But please remember to restart SQL Server Services before you access to SQL Server with user password.

Jul 04, 2014 | Microsoft SQL Server 2008 Enterprise Full...

1 Answer

SQL query to change date and/or time format


The function you're probably looking for is CONVERT. Here's an example (101 applies the format "mm/dd/yyyy", 108 applies "hh:nn:ss"):

SELECT CONVERT(varchar(10),getdate(),101) as JustDate, CONVERT(varchar(8), getdate(), 108) as JustTime


Results:

JustDate JustTime
---------- --------
09/24/2013 13:13:56

(1 row(s) affected)

Jul 15, 2013 | Microsoft SQL Server 2005 Standard Edition

1 Answer

When i run query in access it gives duplicates of the same data


Press the σ (Totals) button and group the query by the necessary fields.
Or eliminate some tables from the query so there isn't a one to many relationship.

May 25, 2009 | Microsoft Office Professional 2007 Full...

1 Answer

Using Microsoft Query to bring Access 2007 data into a spreadsheet


Try using Data > From Other Sources and select From Microsoft Query. In the Excel sheet, select the cell that you want the results to start in. Your MS Query would be pulling the data from your Access database.

May 04, 2009 | Microsoft Excel for PC

1 Answer

Retribing elements from database


Hi,

If you are referring to retrieve only top 10 rows from sql server db then you can simply query like this:

SELECT TOP 10 * FROM YOURTABLENAME

If you want to retrieve latest 10 rows (the last 10 entries) then you need to add ORDER BY clause as well:

SELECT TOP 10 * FROM YOURTABLENAME
ORDER BY COLUMNNAME DESC

where columnname would be your sequential indexed or date or any datatype column by which you mean the latest ones.

Hope this helps.

Thanks.

Mar 16, 2009 | Microsoft SQL Server 2005 Enterprise...

1 Answer

How to print only selected fields in ms access.


open your table in query, follow steps to do this...

1. open new query in access
2. locate the table
3. once table selected, select field name in selected table you only want to include in query
3. once finished selecting the field save the query
4. you can now view selected field in your table and print as well
5. you can edit the query anytime if you want to add or remove field
6. query will not affect your table since this is only query.

hope this will help you....

Oct 22, 2008 | Microsoft Access 2002 for PC

2 Answers

Ms access


before this u should know what is databse.
ms access has various objects likw table,form,query,report etc..
table is a object which stores the actual data and form is used to enter the data in to the table and display the data from the table, while report is object which is used to desplay your data in required formate.Query is used to fetch the data from the table in customised formate.for that u should have knowledge of SQL.

Nov 14, 2007 | Business & Productivity Software

1 Answer

SQL server won't work on my laptop


Two ways to access your server. First, make sure the SQL Server Service is running. Start | Run | Services.msc | Enter. Look for Microsoft SQL Server Service and that it's running. Or Look for the SQL Service Broker icon by the clock and see that it's running. The default instance of SQL Server uses the Hostname. Start | Run | Cmd | Enter. The type hostname. This will be the name to access to server. Then type IPCONFIG, this is then the IP address that you could also use to access it.

SQL Server runs on port 1433, so any firewall has to have it open.

Open Query Analyzer, type in the hostname or ip of the local box, type in "sa" and the password for sa (if setup in mixed mode) or Windows Authentication.

Once you login in, you should now see the databases in the Tree on the left side under the Server | Databases node.

Your description though states "SQL Server won't work on my laptop". TO answer this, the only two SQL Editions that work on anything other than "SERVER" software is SQL Professional and SQL Developer. All others have to be on a "SERVER" platform.

Aug 30, 2007 | Microsoft SQL Server 2000 Standard Edition...

4 Answers

Running SQL queries on Excel


Another way to do it is to use the SQL Drill freeware Excel addin (http://www.sqldrill.com)
hth

Aug 14, 2007 | Microsoft Office 2003 Basic Edition...

Not finding what you are looking for?
Microsoft Office Access 2003 (077-02871) for PC Logo

626 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18299 Answers

Tony

Level 3 Expert

2598 Answers

Are you a Microsoft Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...