Working with Epoch Times 3

[betachristopher]

I am running an Access query and the below works to pull the year out of the Epoch time. However, if I add a year to the criteria section, I get an Overflow error. I don't get this error when the criteria section is blank. What can I do to add criteria? I am using Access 2007 with linked SQL tables.

YEAR: Year(CVDate(DateAdd("s",([Specimen].[date]/1000),'01/01/1970')))

 

[lespaul]

what's the entire SQL of the statement that gives the error?



Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[betachristopher]

hopefully this is easy to read...

SELECT
UCase(Location.name) AS PRACTICE,
UCase(Staff.firstName) & " " & UCase(Staff.lastName) AS PHYSICIAN,
Sum(IIf(Panel.name Like "CF*",1,0)) AS CF,
Sum(IIf(Panel.name Like "CTNG*",1,0)) AS CTNG,
Sum(IIf(Panel.name Like "GBS*",1,0)) AS GBS,
MonthName(Month(CVDate(DateAdd("s",(Specimen.date/1000),'01/01/1970')))) AS MONTH,
Year(CVDate(DateAdd("s",(Specimen.date/1000),'01/01/1970'))) AS YEAR

FROM
(((Requisition INNER JOIN Location ON Requisition.orderingLocationKey = Location.locationKey)
INNER JOIN Staff ON Requisition.orderingPhysicianKey = Staff.staffKey)
INNER JOIN (Panel INNER JOIN OrderedPanel ON Panel.panelKey = OrderedPanel.panelKey) ON Requisition.requisitionKey = OrderedPanel.requisitionKey)
INNER JOIN Specimen ON OrderedPanel.specimenKey = Specimen.specimenKey

WHERE
((Panel.name) Like "CF*" Or (Panel.name) Like "CTNG*" Or (Panel.name) Like "GBS*"))

GROUP BY
UCase(Location.name),
UCase(Staff.firstName) & " " & UCase(Staff.lastName),
MonthName(Month(CVDate(DateAdd("s",(Specimen.date/1000),'01/01/1970')))),
Year(CVDate(DateAdd("s",(Specimen.date/1000),'01/01/1970')))

HAVING
(((Year(CVDate(DateAdd("s",(Specimen.date/1000),'01/01/1970'))))=2007));

 

[lespaul]

what if you move the year criteria to the WHERE clause (which is where it should be, not the HAVING clause):

SELECT
    UCase(Location.name) AS PRACTICE,
    UCase(Staff.firstName) & " " & UCase(Staff.lastName) AS PHYSICIAN,
    Sum(IIf(Panel.name Like "CF*",1,0)) AS CF,
    Sum(IIf(Panel.name Like "CTNG*",1,0)) AS CTNG,
    Sum(IIf(Panel.name Like "GBS*",1,0)) AS GBS,     
    MonthName(Month(CVDate(DateAdd("s",(Specimen.date/1000),'01/01/1970')))) AS MONTH,
    Year(CVDate(DateAdd("s",(Specimen.date/1000),'01/01/1970'))) AS YEAR

FROM
    (((Requisition INNER JOIN Location ON Requisition.orderingLocationKey = Location.locationKey)
    INNER JOIN Staff ON Requisition.orderingPhysicianKey = Staff.staffKey)
    INNER JOIN (Panel INNER JOIN OrderedPanel ON Panel.panelKey = OrderedPanel.panelKey) ON Requisition.requisitionKey = OrderedPanel.requisitionKey)
    INNER JOIN Specimen ON OrderedPanel.specimenKey = Specimen.specimenKey

WHERE
    ((Panel.name) Like "CF*" Or (Panel.name) Like "CTNG*" Or (Panel.name) Like "GBS*")) AND (((Year(CVDate(DateAdd("s",(Specimen.date/1000),'01/01/1970'))))=2007))

GROUP BY
    UCase(Location.name),
    UCase(Staff.firstName) & " " & UCase(Staff.lastName),
MonthName(Month(CVDate(DateAdd("s",(Specimen.date/1000),'01/01/1970')))),   
Year(CVDate(DateAdd("s",(Specimen.date/1000),'01/01/1970')));

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[betachristopher]

I have tried it both ways, and both times get the overflow error.

 

[lespaul]

Is CVDate a function you've built?

Leslie

 

[betachristopher]

No, CVDate is an Access function that allows long integers to be read as dates.

 

[betachristopher]

I should add that without CVDate, I get the overflow error even with the criteria blank.

 

[dhookom]

Is it possible you have nulls in the Specimen.date field? I see you are delimiting dates with single quotes. Have you tried using # rather than the single quotes?

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What happens if you replace this;
'01/01/1970'
with this ?
#1970-01-01#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[betachristopher]

That did not work either. I still get the Overflow error.

 

[dhookom]

What didn't work? Did you check your field for nulls?


Duane
Hook'D on Access
MS Access MVP

 

[betachristopher]

It did not work changing '01/01/1970' to #1970-01-01#

There are no nulls, but there are several invalid dates like this one -63082522800000. But I added a criteria to exclude any dates greater than 13 characters in length.

 

[PHV]

The second argument of the DateAdd function is a Long and thus should be between -2147483648 and 2147483647


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[betachristopher]

Thanks PHV. That may have identified the problem. There are dates as big as 7514076600000 in my table for some reason. Not sure what to do now.

 

[betachristopher]

GOT IT!

I changed:

Year(CVDate(DateAdd("s",([Specimen].[date]/1000),'01/01/1970')))

to:

Year(CVDate((([Specimen].[date]/1000)/86400)+25569))

and did not get an Overflow error when I added the year 2007 to the criteria. I tried using /1000)/86400)+25569 once before but still got the error. I must have had some other syntax incorrect at that time.

Thanks to everyone that helped me brainstorm.

 

[PHV]

A simpler way ?
Year(#1970-01-01#+(Specimen.date/86400000))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[betachristopher]

Thank you! Using your suggestion makes the query much much faster.