Solved

Date in a Query

Posted on 2012-03-16
4
219 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

752 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