A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

DateDiff function throwing error in Where clause



 
 
Thread Tools Display Modes
  #1  
Old May 3rd, 2010, 07:04 PM posted to microsoft.public.access.queries
Petr Danes[_5_]
external usenet poster
 
Posts: 31
Default DateDiff function throwing error in Where clause

I have a set of queries that generate dates and want to use DateDiff to
select those records with certain dates are greater than others. The
following clause keeps giving me an incompatible data type error:

WHERE DateDiff('d',[From date],[DatumStvoreni])0

I have the exact expression in the SELECT clause and it show positive and
negative integers just fine, but when I add this test, I get the error.
There are no bad values in the dataset, I've looked, and when I export the
DateDiff column to a temp table and run a query on that, it works fine. Only
when I use the test directly with the DateDiff function does it bomb. I also
tried putting CDate() around the field expressions and it didn't help.

Pete



--
This e-mail address is fake, to keep spammers and their address harvesters
out of my hair. If you want to get in touch personally, I am 'pdanes' and I
use yahoo mail. But please use the newsgroup when possible, so that all may
benefit from the exchange of ideas.


  #2  
Old May 3rd, 2010, 07:26 PM posted to microsoft.public.access.queries
Tom Lake[_2_]
external usenet poster
 
Posts: 96
Default DateDiff function throwing error in Where clause


"Petr Danes" wrote in message
...
I have a set of queries that generate dates and want to use DateDiff to select those
records with certain dates are greater than others. The following clause keeps giving me
an incompatible data type error:

WHERE DateDiff('d',[From date],[DatumStvoreni])0

I have the exact expression in the SELECT clause and it show positive and negative
integers just fine, but when I add this test, I get the error. There are no bad values
in the dataset, I've looked, and when I export the DateDiff column to a temp table and
run a query on that, it works fine. Only when I use the test directly with the DateDiff
function does it bomb. I also tried putting CDate() around the field expressions and it
didn't help.


I get an error when I use single quotes. I have to use this:

DateDiff("d",[From date],[DatumStvoreni])

The whole expression I tried was this:

WHERE (DateDiff("d",[From date],[DatumStvoreni])0)

Tom Lake



  #3  
Old May 3rd, 2010, 07:37 PM posted to microsoft.public.access.queries
Petr Danes[_5_]
external usenet poster
 
Posts: 31
Default DateDiff function throwing error in Where clause

Tried that as well - no soap. Sometimes I get a message that the query was
too complicated and can't be evaluated. Here's the whole thing:

SELECT Stvoreni_Convert.DatumStvoreni, DateDiff("d",[From
date],[DatumStvoreni]) AS Rozdil, Stvoreni_Convert.AkcesPodrobnostiAutoID
INTO STV
FROM Stvoreni_Convert
WHERE (((DateDiff("d",[From date],[DatumStvoreni]))0));

The source of that query is this one (Stvoreni_Convert):
SELECT Stvoreni.Stvoreni, getdate([stvoreni]) AS DatumStvoreni,
Stvoreni.AkcesPodrobnostiAutoID
FROM Stvoreni
WHERE (((getdate([stvoreni]))''));

and the source of that one is here(Stvoreni):
SELECT IIf(InStrRev(nz([Inventarizace]),Chr(13) &
Chr(10))0,Mid(nz([Inventarizace]),2+InStrRev(nz([Inventarizace]),Chr(13) &
Chr(10))),nz([Inventarizace])) AS Stvoreni,
AkcesPodrobnosti.AkcesPodrobnostiAutoID
FROM AkcesPodrobnosti
WHERE (((nz([Inventarizace]))'') AND ((AkcesPodrobnosti.EvidenceLetter) Is
Not Null));

AkcesPodrobnosti is a table, with Inventarizace as a memo field.
Getdate is a VBA function extracts a date from a mess of text and returns it
as a string.

All the preceding works fine, until I add the WHERE clause in the top query.

Pete


"Tom Lake" píse v diskusním príspevku
...

"Petr Danes" wrote in message
...
I have a set of queries that generate dates and want to use DateDiff to
select those records with certain dates are greater than others. The
following clause keeps giving me an incompatible data type error:

WHERE DateDiff('d',[From date],[DatumStvoreni])0

I have the exact expression in the SELECT clause and it show positive and
negative integers just fine, but when I add this test, I get the error.
There are no bad values in the dataset, I've looked, and when I export
the DateDiff column to a temp table and run a query on that, it works
fine. Only when I use the test directly with the DateDiff function does
it bomb. I also tried putting CDate() around the field expressions and it
didn't help.


I get an error when I use single quotes. I have to use this:

DateDiff("d",[From date],[DatumStvoreni])

The whole expression I tried was this:

WHERE (DateDiff("d",[From date],[DatumStvoreni])0)

Tom Lake





  #4  
Old May 3rd, 2010, 07:40 PM posted to microsoft.public.access.queries
Petr Danes[_5_]
external usenet poster
 
Posts: 31
Default DateDiff function throwing error in Where clause

I just tried the last query in another form, using a direct comparison
instead of the DateDiff function. Same error.

SELECT Stvoreni_Convert.DatumStvoreni, CDate([DatumStvoreni]) AS Rozdil,
Stvoreni_Convert.AkcesPodrobnostiAutoID INTO STV
FROM Stvoreni_Convert
WHERE (((CDate([DatumStvoreni]))CDate([From date])));

Pete



"Tom Lake" píse v diskusním príspevku
...

"Petr Danes" wrote in message
...
I have a set of queries that generate dates and want to use DateDiff to
select those records with certain dates are greater than others. The
following clause keeps giving me an incompatible data type error:

WHERE DateDiff('d',[From date],[DatumStvoreni])0

I have the exact expression in the SELECT clause and it show positive and
negative integers just fine, but when I add this test, I get the error.
There are no bad values in the dataset, I've looked, and when I export
the DateDiff column to a temp table and run a query on that, it works
fine. Only when I use the test directly with the DateDiff function does
it bomb. I also tried putting CDate() around the field expressions and it
didn't help.


I get an error when I use single quotes. I have to use this:

DateDiff("d",[From date],[DatumStvoreni])

The whole expression I tried was this:

WHERE (DateDiff("d",[From date],[DatumStvoreni])0)

Tom Lake





  #5  
Old May 3rd, 2010, 10:00 PM posted to microsoft.public.access.queries
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default DateDiff function throwing error in Where clause

You sure you've got valid values for both fields in every row?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Petr Danes" wrote in message
...
I just tried the last query in another form, using a direct comparison
instead of the DateDiff function. Same error.

SELECT Stvoreni_Convert.DatumStvoreni, CDate([DatumStvoreni]) AS Rozdil,
Stvoreni_Convert.AkcesPodrobnostiAutoID INTO STV
FROM Stvoreni_Convert
WHERE (((CDate([DatumStvoreni]))CDate([From date])));

Pete



"Tom Lake" píse v diskusním príspevku
...

"Petr Danes" wrote in message
...
I have a set of queries that generate dates and want to use DateDiff to
select those records with certain dates are greater than others. The
following clause keeps giving me an incompatible data type error:

WHERE DateDiff('d',[From date],[DatumStvoreni])0

I have the exact expression in the SELECT clause and it show positive
and negative integers just fine, but when I add this test, I get the
error. There are no bad values in the dataset, I've looked, and when I
export the DateDiff column to a temp table and run a query on that, it
works fine. Only when I use the test directly with the DateDiff function
does it bomb. I also tried putting CDate() around the field expressions
and it didn't help.


I get an error when I use single quotes. I have to use this:

DateDiff("d",[From date],[DatumStvoreni])

The whole expression I tried was this:

WHERE (DateDiff("d",[From date],[DatumStvoreni])0)

Tom Lake







  #6  
Old May 4th, 2010, 01:31 AM posted to microsoft.public.access.queries
Petr Danes[_3_]
external usenet poster
 
Posts: 52
Default DateDiff function throwing error in Where clause

Pretty sure - when I show the values without trying a compare, I don't see
anything wrong, and when I change the query to spin the DateDiff value out
to a table, the table contains only valid integers, positive and negative. I
can subsequently sort on that integer column and see that there is nothing
bad at either end. I can also select only the positive ones to get the
records I want, but creating an unnecessary temp table is an awkward way to
do it. The comparison should work.

Pete



"Douglas J. Steele" píše v diskusním
příspěvku ...
You sure you've got valid values for both fields in every row?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Petr Danes" wrote in message
...
I just tried the last query in another form, using a direct comparison
instead of the DateDiff function. Same error.

SELECT Stvoreni_Convert.DatumStvoreni, CDate([DatumStvoreni]) AS Rozdil,
Stvoreni_Convert.AkcesPodrobnostiAutoID INTO STV
FROM Stvoreni_Convert
WHERE (((CDate([DatumStvoreni]))CDate([From date])));

Pete



"Tom Lake" píse v diskusním príspevku
...

"Petr Danes" wrote in message
...
I have a set of queries that generate dates and want to use DateDiff to
select those records with certain dates are greater than others. The
following clause keeps giving me an incompatible data type error:

WHERE DateDiff('d',[From date],[DatumStvoreni])0

I have the exact expression in the SELECT clause and it show positive
and negative integers just fine, but when I add this test, I get the
error. There are no bad values in the dataset, I've looked, and when I
export the DateDiff column to a temp table and run a query on that, it
works fine. Only when I use the test directly with the DateDiff
function does it bomb. I also tried putting CDate() around the field
expressions and it didn't help.

I get an error when I use single quotes. I have to use this:

DateDiff("d",[From date],[DatumStvoreni])

The whole expression I tried was this:

WHERE (DateDiff("d",[From date],[DatumStvoreni])0)

Tom Lake








  #7  
Old May 6th, 2010, 03:45 PM posted to microsoft.public.access.queries
Petr Danes[_5_]
external usenet poster
 
Posts: 31
Default DateDiff function throwing error in Where clause

Never did find any answers to this, even when comparing dates directly in
the WHERE clause and avoiding the DateDiff function altogether, so I wound
up converting all my dates to strings, formatted as 'yyyy-mm-dd' and used
ordinary string comparisons to get what I needed.

Year-month-day is the only universally correct way to show dates anyway, but
it's pretty lame that dates can't be compared directly.

Pete


"Petr Danes" píše v diskusním příspěvku
...
I have a set of queries that generate dates and want to use DateDiff to
select those records with certain dates are greater than others. The
following clause keeps giving me an incompatible data type error:

WHERE DateDiff('d',[From date],[DatumStvoreni])0

I have the exact expression in the SELECT clause and it show positive and
negative integers just fine, but when I add this test, I get the error.
There are no bad values in the dataset, I've looked, and when I export the
DateDiff column to a temp table and run a query on that, it works fine.
Only when I use the test directly with the DateDiff function does it bomb.
I also tried putting CDate() around the field expressions and it didn't
help.

Pete



--
This e-mail address is fake, to keep spammers and their address harvesters
out of my hair. If you want to get in touch personally, I am 'pdanes' and
I use yahoo mail. But please use the newsgroup when possible, so that all
may benefit from the exchange of ideas.




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:00 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright Š2004-2024 OfficeFrustration.
The comments are property of their posters.