[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

Date conversions not working correctly

I have some data with a field for date (created).  I have to adjust the date fields, moving it back 8 hours.  I've done it two ways, one using the DateDiff(field) and the other just subtracting .3333 (1/3 of an day) from the "created" field.

I can filter fine (reducing the list to a specific time frame) on the original date field:

SELECT T_Sales.Created, CDate(DateAdd("h",-8,[Created])) AS NewCreated, CDate([Created]-0.33333333) AS NewCreated2, T_Sales.SalesTotal
FROM T_Sales
WHERE (((T_Sales.Created)>=[from] And (T_Sales.Created)<[to]));

Open in new window


But when I try the same type of filtering on the adjusted date, it doesn't work.  

Any idea what I'm doing wrong?
But when I try to filter on the new field
0
BBlu
Asked:
BBlu
  • 6
  • 5
1 Solution
 
IrogSintaCommented:
Use DateAdd without CDate

DateAdd("h",-8,[Created]) AS NewCreated
0
 
BBluAuthor Commented:
sorry, that's what I meant..dateAdd.  That's what I used.
0
 
IrogSintaCommented:
What I mean is that DateAdd returns a date value so you do not need CDate in there.  Try your query without CDate.

DateAdd("h",-8,[Created]) AS NewCreated
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BBluAuthor Commented:
I tried that at first (and just again).  It returns no records :(
0
 
BBluAuthor Commented:
I'm attaching the database in case that makes it easier.
Onsite.accdb
0
 
IrogSintaCommented:
Add CDate to your From and To parameters.
CDate([from])
CDate([to])
0
 
BBluAuthor Commented:
PERFECT!  I've never had to do that and certainly would never have thought about that solution.  Any idea why it was necessary?
0
 
IrogSintaCommented:
Access is unaware of the type of parameter you are entering and tries to guess.  In this case it thought you were entering a String rather than a date/time.  Adding CDate forced your parameters into Date data type.
0
 
Gustav BrockCIOCommented:
Specify your parameters:

Parameters
  [from] DateTime,
  [to] DateTime
SELECT
  T_Sales.Created,
  DateAdd("h",-8,[Created]) AS NewCreated,
  T_Sales.SalesTotal
FROM
  T_Sales
WHERE
  T_Sales.Created>=[from]
  And
  T_Sales.Created)<[to];
0
 
BBluAuthor Commented:
cactus_data,
how do I specify the parameters, other than the way IrogSinta suggested?
0
 
BBluAuthor Commented:
I never would have thought of this, IrogSintra.  Thank you very much!
0
 
IrogSintaCommented:
To specify the parameters as cactus_data pointed out, you could go into the SQL view of your query and add this right before your select statement:
  Parameters
  [from] DateTime,
  [to] DateTime;

Or you could also go into the design view of your query builder and right click anywhere and select parameters, or click on parameters on the Show/Hide section of the Design tab of the ribbon bar.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now