Solved

Date in a Query

Posted on 2012-03-16
4
218 Views
Last Modified: 2012-03-16
Dear Experts,

I need to be able to create a field in a query which returns the first and last dates in a range ([DateAndTime]).  So something like "'First date' to 'Last date'"

This needs to be added into a query which then forms the dataset for a count query (the count is based on [DateAndTime]). I have tried playing around with the Expression Builder, but I’m not getting very far
The query I have is below ... Can anybody help?

SELECT FFUsers_Contact_Information.DateAndTime, FFUsers_ConsultantDefs.Description, FFUsers_ConsultantDefs.entry AS FFID
FROM FFUsers_UsersPreferences INNER JOIN (FFUsers_Contact_Information INNER JOIN FFUsers_ConsultantDefs ON FFUsers_Contact_Information.createby = FFUsers_ConsultantDefs.entry) ON FFUsers_UsersPreferences.user_log_in = FFUsers_ConsultantDefs.entry
GROUP BY FFUsers_Contact_Information.DateAndTime, FFUsers_ConsultantDefs.Description, FFUsers_ConsultantDefs.entry, FFUsers_UsersPreferences.is_current
HAVING (((FFUsers_UsersPreferences.is_current)=1)); 

Open in new window


Many thanks
0
Comment
Question by:correlate
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 40

Expert Comment

by:als315
ID: 37728939
Add this field (DateAndTime) twice and select min and max in group functions
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 37728950
I'm not sure I understand what you are asking for.

Are you saying you want to know the values of the oldest and newest datetimes in the field DateAndTime?  

If so then it seems that you need a separate query..

Select min(Dateandtime) as FirstDate, max(dateandtime) as LastDate
from FFUsers_Contact_Information
0
 
LVL 77

Expert Comment

by:peter57r
ID: 37728954
als315
... the query is grouping by DateAndTime so the max and min values will just be the same as the grouping value.
0
 

Author Closing Comment

by:correlate
ID: 37728997
Brilliant - thank you for that, does the trick perfectly
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question