Solved

Date in a Query

Posted on 2012-03-16
4
220 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

626 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