Overflow

L

Leslie Isaacs

Hello All

Yesterday I created the following query, which worked fine - took about 1-2
seconds to run.
Today, running the same, unaltered query, I get 'Overflow' and the query
doesn't run.
I have compacted and repaired the mdb, closed other applications, no effect.
How can the query be generating an 'overflow' today when it didn't
yetsreday?
Irrespective of the answer to that question, can anyone see how I could
simplify the query to make it run - today, tomorrow and the next day!

Thanks
Les
The query:

SELECT Staff.[staff name], Staff.[hours per week],
[montot]+[tuetot]+[wedtot]+[thutot]+[fritot]+[sattot] AS [calc hrs], [hours
per week]-[calc hrs] AS [hrs disc], Staff.[days per week], Staff.holidays,
IIf([holidays]="1",4*[hours per week]+([hours per week]/[calc
days]),IIf([holidays]="2",5*[hours per week],IIf([holidays]="3",5*[hours per
week]+([hours per week]/[calc days]),IIf([holidays]="4",6*[hours per
week],0)))) AS [calc hol hrs], Staff.[tax code], [calc hol hrs]-[tax code]
AS [hols disc], (IIf([mon pm hrs] Is Null,0,DateDiff("n",CDate(Left([mon pm
hrs],InStr([mon pm hrs],"-")-1)),CDate(Mid([mon pm hrs],InStr([mon pm
hrs],"-")+1))))+IIf([mon am hrs] Is Null,0,DateDiff("n",CDate(Left([mon am
hrs],InStr([mon am hrs],"-")-1)),CDate(Mid([mon am hrs],InStr([mon am
hrs],"-")+1)))))/60 AS montot, (IIf([tue pm hrs] Is
Null,0,DateDiff("n",CDate(Left([tue pm hrs],InStr([tue pm
hrs],"-")-1)),CDate(Mid([tue pm hrs],InStr([tue pm hrs],"-")+1))))+IIf([tue
am hrs] Is Null,0,DateDiff("n",CDate(Left([tue am hrs],InStr([tue am
hrs],"-")-1)),CDate(Mid([tue am hrs],InStr([tue am hrs],"-")+1)))))/60 AS
tuetot, (IIf([wed pm hrs] Is Null,0,DateDiff("n",CDate(Left([wed pm
hrs],InStr([wed pm hrs],"-")-1)),CDate(Mid([wed pm hrs],InStr([wed pm
hrs],"-")+1))))+IIf([wed am hrs] Is Null,0,DateDiff("n",CDate(Left([wed am
hrs],InStr([wed am hrs],"-")-1)),CDate(Mid([wed am hrs],InStr([wed am
hrs],"-")+1)))))/60 AS wedtot, (IIf([thu pm hrs] Is
Null,0,DateDiff("n",CDate(Left([thu pm hrs],InStr([thu pm
hrs],"-")-1)),CDate(Mid([thu pm hrs],InStr([thu pm hrs],"-")+1))))+IIf([thu
am hrs] Is Null,0,DateDiff("n",CDate(Left([thu am hrs],InStr([thu am
hrs],"-")-1)),CDate(Mid([thu am hrs],InStr([thu am hrs],"-")+1)))))/60 AS
thutot, (IIf([fri pm hrs] Is Null,0,DateDiff("n",CDate(Left([fri pm
hrs],InStr([fri pm hrs],"-")-1)),CDate(Mid([fri pm hrs],InStr([fri pm
hrs],"-")+1))))+IIf([fri am hrs] Is Null,0,DateDiff("n",CDate(Left([fri am
hrs],InStr([fri am hrs],"-")-1)),CDate(Mid([fri am hrs],InStr([fri am
hrs],"-")+1)))))/60 AS fritot, IIf([sat am hrs] Is Null,0,0) AS sattot,
IIf([montot]>0,1,0)+IIf([tuetot]>0,1,0)+IIf([wedtot]>0,1,0)+IIf([thutot]>0,1,0)+IIf([fritot]>0,1,0)+IIf([sattot]>0,1,0)
AS [calc days], [calc days]-[days per week] AS [days disc]
FROM Staff;
 
N

NthDegree via AccessMonster.com

Leslie said:
Hello All

Yesterday I created the following query, which worked fine - took about 1-2
seconds to run.
Today, running the same, unaltered query, I get 'Overflow' and the query
doesn't run.
I have compacted and repaired the mdb, closed other applications, no effect.
How can the query be generating an 'overflow' today when it didn't
yetsreday?
Irrespective of the answer to that question, can anyone see how I could
simplify the query to make it run - today, tomorrow and the next day!

Thanks
Les
The query:

SELECT Staff.[staff name], Staff.[hours per week],
[montot]+[tuetot]+[wedtot]+[thutot]+[fritot]+[sattot] AS [calc hrs], [hours
per week]-[calc hrs] AS [hrs disc], Staff.[days per week], Staff.holidays,
IIf([holidays]="1",4*[hours per week]+([hours per week]/[calc
days]),IIf([holidays]="2",5*[hours per week],IIf([holidays]="3",5*[hours per
week]+([hours per week]/[calc days]),IIf([holidays]="4",6*[hours per
week],0)))) AS [calc hol hrs], Staff.[tax code], [calc hol hrs]-[tax code]
AS [hols disc], (IIf([mon pm hrs] Is Null,0,DateDiff("n",CDate(Left([mon pm
hrs],InStr([mon pm hrs],"-")-1)),CDate(Mid([mon pm hrs],InStr([mon pm
hrs],"-")+1))))+IIf([mon am hrs] Is Null,0,DateDiff("n",CDate(Left([mon am
hrs],InStr([mon am hrs],"-")-1)),CDate(Mid([mon am hrs],InStr([mon am
hrs],"-")+1)))))/60 AS montot, (IIf([tue pm hrs] Is
Null,0,DateDiff("n",CDate(Left([tue pm hrs],InStr([tue pm
hrs],"-")-1)),CDate(Mid([tue pm hrs],InStr([tue pm hrs],"-")+1))))+IIf([tue
am hrs] Is Null,0,DateDiff("n",CDate(Left([tue am hrs],InStr([tue am
hrs],"-")-1)),CDate(Mid([tue am hrs],InStr([tue am hrs],"-")+1)))))/60 AS
tuetot, (IIf([wed pm hrs] Is Null,0,DateDiff("n",CDate(Left([wed pm
hrs],InStr([wed pm hrs],"-")-1)),CDate(Mid([wed pm hrs],InStr([wed pm
hrs],"-")+1))))+IIf([wed am hrs] Is Null,0,DateDiff("n",CDate(Left([wed am
hrs],InStr([wed am hrs],"-")-1)),CDate(Mid([wed am hrs],InStr([wed am
hrs],"-")+1)))))/60 AS wedtot, (IIf([thu pm hrs] Is
Null,0,DateDiff("n",CDate(Left([thu pm hrs],InStr([thu pm
hrs],"-")-1)),CDate(Mid([thu pm hrs],InStr([thu pm hrs],"-")+1))))+IIf([thu
am hrs] Is Null,0,DateDiff("n",CDate(Left([thu am hrs],InStr([thu am
hrs],"-")-1)),CDate(Mid([thu am hrs],InStr([thu am hrs],"-")+1)))))/60 AS
thutot, (IIf([fri pm hrs] Is Null,0,DateDiff("n",CDate(Left([fri pm
hrs],InStr([fri pm hrs],"-")-1)),CDate(Mid([fri pm hrs],InStr([fri pm
hrs],"-")+1))))+IIf([fri am hrs] Is Null,0,DateDiff("n",CDate(Left([fri am
hrs],InStr([fri am hrs],"-")-1)),CDate(Mid([fri am hrs],InStr([fri am
hrs],"-")+1)))))/60 AS fritot, IIf([sat am hrs] Is Null,0,0) AS sattot,
IIf([montot]>0,1,0)+IIf([tuetot]>0,1,0)+IIf([wedtot]>0,1,0)+IIf([thutot]>0,1,0)+IIf([fritot]>0,1,0)+IIf([sattot]>0,1,0)
AS [calc days], [calc days]-[days per week] AS [days disc]
FROM Staff;
Click to expand...

I noticed that you do at least one division operation. Is it possible that
the divisor could ever be zero? You can't divide by zero. (e.g. 1/0 =
undefined causing an "overflow") . The query would then be dependent on the
data whether it would fail or not (work one day, not the next).

You could try doing something like this:

IIf([holidays]="1",4*[hours per week]+([hours per week]/iif([calc days]=0,1,
[calc days])),

This way if calc days is zero it would divide by 1 (which would return the
value in the first part of the iif statement, in other words the divide by 1
would not change the value). If the calc hours is not zero then the divide
would occur as you intended.
 
L

Leslie Isaacs

Thanks for this: I have now got round the problem by using the Nz function
around any of the fields that might have come to zero.
I understand what you say about normalisation etc, and will comply ... one
day (honest!)
Thanks again
Les


Jerry Whittle said:
Last answere first: you really, really need to normalize your data. If
you
have fields across like [mon am hrs], [mon pm hrs], [tue am hrs], [tue pm
hrs], etc., you are committing spreadsheet which is a grevious sin when
working inside a database. Your query shows the hoops that you need to
jump
through to get what probably is a simple answer if the tables were
properly
set up. I highly suggest getting some relational database training or
reading
"Database Design for Mere Mortals" by Hernandez before proceeding any
further
on this database.

Now I'll get off my high horse which is standing on my soapbox and look at
the problem in question.

CDate will bomb out if you have a null, empty string, or something that
can't be evaluated as a date in any of the records for that field. It will
cause your problem if a date string or number is too large.
debug.Print cdate(11111111111)

IIf(IsDate([date])=True,Cdate([date]),#1/1/1950#) AS NotDate

I always run the IsDate function past the data before doing the CDate.

IIf(IsDate(Left([mon am hrs], InStr([mon am hrs], "-")-1)=True,
CDate(Left([mon am hrs], InStr([mon am hrs], "-")-1)),#1/1/1950#)

If not a valid date, it puts in the bogus #1/1/1950# date.

You might be getting a divide by zero error if [calc days] is 0.

There are plenty of places in that query for errors to creep in. You could
try simplifying it unti the error goes away. However if it ran the day
before
but now doesn't run, what else changed during that time? How about someone
adding, deleting, or editing a record? I'd look into that.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Leslie Isaacs said:
Hello All

Yesterday I created the following query, which worked fine - took about
1-2
seconds to run.
Today, running the same, unaltered query, I get 'Overflow' and the query
doesn't run.
I have compacted and repaired the mdb, closed other applications, no
effect.
How can the query be generating an 'overflow' today when it didn't
yetsreday?
Irrespective of the answer to that question, can anyone see how I could
simplify the query to make it run - today, tomorrow and the next day!

Thanks
Les
The query:

SELECT Staff.[staff name], Staff.[hours per week],
[montot]+[tuetot]+[wedtot]+[thutot]+[fritot]+[sattot] AS [calc hrs],
[hours
per week]-[calc hrs] AS [hrs disc], Staff.[days per week],
Staff.holidays,
IIf([holidays]="1",4*[hours per week]+([hours per week]/[calc
days]),IIf([holidays]="2",5*[hours per week],IIf([holidays]="3",5*[hours
per
week]+([hours per week]/[calc days]),IIf([holidays]="4",6*[hours per
week],0)))) AS [calc hol hrs], Staff.[tax code], [calc hol hrs]-[tax
code]
AS [hols disc], (IIf([mon pm hrs] Is Null,0,DateDiff("n",CDate(Left([mon
pm
hrs],InStr([mon pm hrs],"-")-1)),CDate(Mid([mon pm hrs],InStr([mon pm
hrs],"-")+1))))+IIf([mon am hrs] Is Null,0,DateDiff("n",CDate(Left([mon
am
hrs],InStr([mon am hrs],"-")-1)),CDate(Mid([mon am hrs],InStr([mon am
hrs],"-")+1)))))/60 AS montot, (IIf([tue pm hrs] Is
Null,0,DateDiff("n",CDate(Left([tue pm hrs],InStr([tue pm
hrs],"-")-1)),CDate(Mid([tue pm hrs],InStr([tue pm
hrs],"-")+1))))+IIf([tue
am hrs] Is Null,0,DateDiff("n",CDate(Left([tue am hrs],InStr([tue am
hrs],"-")-1)),CDate(Mid([tue am hrs],InStr([tue am hrs],"-")+1)))))/60 AS
tuetot, (IIf([wed pm hrs] Is Null,0,DateDiff("n",CDate(Left([wed pm
hrs],InStr([wed pm hrs],"-")-1)),CDate(Mid([wed pm hrs],InStr([wed pm
hrs],"-")+1))))+IIf([wed am hrs] Is Null,0,DateDiff("n",CDate(Left([wed
am
hrs],InStr([wed am hrs],"-")-1)),CDate(Mid([wed am hrs],InStr([wed am
hrs],"-")+1)))))/60 AS wedtot, (IIf([thu pm hrs] Is
Null,0,DateDiff("n",CDate(Left([thu pm hrs],InStr([thu pm
hrs],"-")-1)),CDate(Mid([thu pm hrs],InStr([thu pm
hrs],"-")+1))))+IIf([thu
am hrs] Is Null,0,DateDiff("n",CDate(Left([thu am hrs],InStr([thu am
hrs],"-")-1)),CDate(Mid([thu am hrs],InStr([thu am hrs],"-")+1)))))/60 AS
thutot, (IIf([fri pm hrs] Is Null,0,DateDiff("n",CDate(Left([fri pm
hrs],InStr([fri pm hrs],"-")-1)),CDate(Mid([fri pm hrs],InStr([fri pm
hrs],"-")+1))))+IIf([fri am hrs] Is Null,0,DateDiff("n",CDate(Left([fri
am
hrs],InStr([fri am hrs],"-")-1)),CDate(Mid([fri am hrs],InStr([fri am
hrs],"-")+1)))))/60 AS fritot, IIf([sat am hrs] Is Null,0,0) AS sattot,
IIf([montot]>0,1,0)+IIf([tuetot]>0,1,0)+IIf([wedtot]>0,1,0)+IIf([thutot]>0,1,0)+IIf([fritot]>0,1,0)+IIf([sattot]>0,1,0)
AS [calc days], [calc days]-[days per week] AS [days disc]
FROM Staff;
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating in Queries 6
How can I find, when overtime starts 11
Change the form source through a VB code 2
add time with out weekends 3
Week Commencing 2
how can I pick the days apart again 13
Listing activities by Week - Revisited 5
Calculating overtime 1

Top