[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

Query Date Field

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
cansevin
Asked:
cansevin
  • 4
  • 2
  • 2
  • +3
1 Solution
 
Jim P.Commented:
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
 
cansevinAuthor Commented:
Unfortunately still not working
0
 
Jim P.Commented:
Change the WHERE clause to:
WHERE DateValue(Nz(qryStatsforDailySalesReport.DateField,#1/1/1950#))=DateValue(Now())

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
Gustav BrockCIOCommented:
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
 
Helen FeddemaCommented:
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
 
PatHartmanCommented:
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
 
Jim P.Commented:
I would switch the Having to a Where.

Uh, Pat,  did you happen to look at my query here?
0
 
PatHartmanCommented:
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
 
Jim P.Commented:
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
 
Gustav BrockCIOCommented:
> 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
 
cansevinAuthor Commented:
Thanks! That was the one that worked! Have a great day
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now