[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query Date Field

Posted on 2014-01-19
12
Medium Priority
?
325 Views
Last Modified: 2014-01-20
I am trying to run a query from a query that has a list of dates. I want it only for the current day and it is not reading the WHERE properly, it pull up zero results. Below I have the query that pulls up zero result. This same query without the HAVING section works fine. I feel like the column isn't being read properly as a "date". Is there something I have to do to format that?

SELECT qryStatsforDailySalesReport.DateField, qryStatsforDailySalesReport.MessagesRecorded, qryStatsforDailySalesReport.TotalNewClients, qryStatsforDailySalesReport.BookedTrue, qryStatsforDailySalesReport.TalkedTo, qryStatsforDailySalesReport.PercentBooked, qryStatsforDailySalesReport.FiveMinCallBack, qryStatsforDailySalesReport.PercentinFive, qryStatsforDailySalesReport.AvgOfSumOfExtendedPrice, "CurrentDay" AS DateSpec
FROM qryStatsforDailySalesReport
GROUP BY qryStatsforDailySalesReport.DateField, qryStatsforDailySalesReport.MessagesRecorded, qryStatsforDailySalesReport.TotalNewClients, qryStatsforDailySalesReport.BookedTrue, qryStatsforDailySalesReport.TalkedTo, qryStatsforDailySalesReport.PercentBooked, qryStatsforDailySalesReport.FiveMinCallBack, qryStatsforDailySalesReport.PercentinFive, qryStatsforDailySalesReport.AvgOfSumOfExtendedPrice, "CurrentDay"
HAVING (((qryStatsforDailySalesReport.DateField)=Date()));
0
Comment
Question by:cansevin
  • 4
  • 2
  • 2
  • +3
12 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 39792234
Try this:
SELECT qryStatsforDailySalesReport.DateField, qryStatsforDailySalesReport.MessagesRecorded, qryStatsforDailySalesReport.TotalNewClients, qryStatsforDailySalesReport.BookedTrue, qryStatsforDailySalesReport.TalkedTo, qryStatsforDailySalesReport.PercentBooked, qryStatsforDailySalesReport.FiveMinCallBack, qryStatsforDailySalesReport.PercentinFive, qryStatsforDailySalesReport.AvgOfSumOfExtendedPrice, "CurrentDay" AS DateSpec
FROM qryStatsforDailySalesReport
WHERE DateValue(qryStatsforDailySalesReport.DateField)=DateValue(Now())
GROUP BY qryStatsforDailySalesReport.DateField, qryStatsforDailySalesReport.MessagesRecorded, qryStatsforDailySalesReport.TotalNewClients, qryStatsforDailySalesReport.BookedTrue, qryStatsforDailySalesReport.TalkedTo, qryStatsforDailySalesReport.PercentBooked, qryStatsforDailySalesReport.FiveMinCallBack, qryStatsforDailySalesReport.PercentinFive, qryStatsforDailySalesReport.AvgOfSumOfExtendedPrice, "CurrentDay"

Open in new window

0
 

Author Comment

by:cansevin
ID: 39792237
Unfortunately still not working
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 2000 total points
ID: 39792288
Change the WHERE clause to:
WHERE DateValue(Nz(qryStatsforDailySalesReport.DateField,#1/1/1950#))=DateValue(Now())

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 39792314
>I feel like the column isn't being read properly as a "date".
By any chance is column DateField a Text field, which you are applying date logic to?
If yes, what would happen if the column has a non-date value, such as blank, NULL, "banana"?
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39792507
You probably have both date and time. Try removing time:

HAVING Int(qryStatsforDailySalesReport.DateField)=Date();

or (faster if DateField is indexed):

HAVING qryStatsforDailySalesReport.DateField Between Date() And Date() + TimeSerial(23,11,59);

/gustav
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 39792963
What data type is the DateField field?  If it isn't a Date field, you might need to convert its value to a Date variable, using CDate(), maybe after checking that the value in the field can be converted to a Date, using IsDate().  Here is some sample code that checks the value in a form control:

If IsDate(Me![txtFromDate].Value) = True Then
   dteFromDate = CDate(Me![txtFromDate].Value)
End If

Open in new window

0
 
LVL 40

Expert Comment

by:PatHartman
ID: 39792964
You can also use the DateValue() function to extract just the date from a datetime field.

Where DateTime(YourDate) = Date();

I would switch the Having to a Where.  Where is evaluated BEFORE aggregation and Having is evaluated AFTER aggregation.  Since the date isn't going to be affected by the Group By, it is more efficient to get rid of the data you don't need before you make the query engine go through the process of aggregating it.  Having is only used when your criteria doesn't actually exist in the Select clause.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39792998
I would switch the Having to a Where.

Uh, Pat,  did you happen to look at my query here?
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 39793033
I see it now.  I generally don't look at posts that just say "try this" without any explanation of what/why.  

Why use DateValue(Now()) rather than a simple Date()?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39793049
Why use DateValue(Now()) rather than a simple Date()?

I have been bitten more than once where bad reference orders, localities, Win setup, VBA, etc. have given a syntax error or misinterpretation of what you are trying to do. But the Now() is more specific and the stripping back to the date only adds probably a 1/2MS to processing.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39793488
> Why use DateValue(Now()) rather than a simple Date()?

There is no reason. It adds nothing and is marginally slower.

Jim, it will only obfuscate code as any other developer who would happen to maintain the code will ask as Pat: Why did he do this?
As to the issue with references, troubles with Date() and Left() and other very basic functions are only symptoms, never the cause.

/gustav
0
 

Author Closing Comment

by:cansevin
ID: 39794057
Thanks! That was the one that worked! Have a great day
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month18 days, 17 hours left to enroll

834 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