Failing Query

  • Thread starter Thread starter Syd
  • Start date Start date
S

Syd

I have this query that grabs values from the next day and
pulls it back into the previous days shift's values.
However, I need it to drop one (1) minute from the total
values returned to be accurate, I have tried and tried
with no success. Can some who knows how it's done help?
Here is the sample: ReturnedTime After: DateDiff("n",
[LastJob],DateAdd("h",7,DateValue(((GetMonth()+1) & "/"
& "1/" & GetYear()))))

Thanks,..
 
I have this query that grabs values from the next day and
pulls it back into the previous days shift's values.
However, I need it to drop one (1) minute from the total
values returned to be accurate, I have tried and tried
with no success. Can some who knows how it's done help?
Here is the sample: ReturnedTime After: DateDiff("n",
[LastJob],DateAdd("h",7,DateValue(((GetMonth()+1) & "/"
& "1/" & GetYear()))))
Click to expand...

The DateDiff() expression will return an Integer count of minutes; if
you want one minute less, simply subtract 1 from the result:

DateDiff("n", [LastJob], DateAdd("h",7,DateValue(((GetMonth()+1) & "/"
& "1/" & GetYear())))) - 1

Note that the DateSerial function might be simpler than the DateValue
expression - I don't know what GetMonth and GetYear do, but

DateSerial(Year(Date()), Month(Date()), 1)

returns the first day of the current month.
 
John:

Thanks for the assist. However, if I apply what you
suhggest I end up with a -1. Examples are:

Last Job: Month End
8/1/03 7:00:00 AM 8/1/03 7:00:00 AM = -1

What I'd like to have is:

Last Job: Month End
8/1/03 7:00:00 AM 8/1/03 7:00:00 AM = 8/1/03 6:59
AM. Then I can get a DateDiff of 1

Hope the helps...Thanks again.



-----Original Message-----
I have this query that grabs values from the next day and
pulls it back into the previous days shift's values.
However, I need it to drop one (1) minute from the total
values returned to be accurate, I have tried and tried
with no success. Can some who knows how it's done help?
Here is the sample: ReturnedTime After: DateDiff("n",
[LastJob],DateAdd("h",7,DateValue(((GetMonth()+1) & "/"
& "1/" & GetYear()))))
Click to expand...

The DateDiff() expression will return an Integer count of minutes; if
you want one minute less, simply subtract 1 from the result:

DateDiff("n", [LastJob], DateAdd("h",7,DateValue (((GetMonth()+1) & "/"
& "1/" & GetYear())))) - 1

Note that the DateSerial function might be simpler than the DateValue
expression - I don't know what GetMonth and GetYear do, but

DateSerial(Year(Date()), Month(Date()), 1)

returns the first day of the current month.


.
Click to expand...
Click to expand...
 
Back
Top