Failing Query

[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,..

 

[John Vinson]

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()))))

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.

 

[Syd]

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()))))

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 Vinson]

Then I can get a DateDiff of 1

DateDiff does not return a Date - it returns an Integer. I think you
want to use DateAdd instead of DateDiff.