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
BBluAsked:
Who is Participating?
 
IrogSintaCommented:
Add CDate to your From and To parameters.
CDate([from])
CDate([to])
0
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.