Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Working with Epoch Times 3

Status
Not open for further replies.

betachristopher

Programmer
Joined
Oct 25, 2006
Messages
54
Location
US
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')))


 
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
 

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));
 
what if you move the year criteria to the WHERE clause (which is where it should be, not the HAVING clause):
Code:
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
 

I have tried it both ways, and both times get the overflow error.
 
Is CVDate a function you've built?

Leslie
 
No, CVDate is an Access function that allows long integers to be read as dates.
 
I should add that without CVDate, I get the overflow error even with the criteria blank.
 
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
 
What happens if you replace this;
'01/01/1970'
with this ?
#1970-01-01#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
That did not work either. I still get the Overflow error.
 
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.
 
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
 
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.
 
GOT IT!

I changed:
Code:
Year(CVDate(DateAdd("s",([Specimen].[date]/1000),'01/01/1970')))
to:
Code:
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.
 
A simpler way ?
Year(#1970-01-01#+(Specimen.date/86400000))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Thank you! Using your suggestion makes the query much much faster.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top