Problem with Time Sysntax error

[Guest]

HI

In Access the date is like

21/12/2004 08:57:17 a.m.

I need to update the record only if it was not created more than five
minutes ago


cmd = "UPDATE Sales SET Status = 'A' WHERE Mid(SalesDate,13,20) > #" &
Mid(Microsoft.VisualBasic.DateAdd(DateInterval.Minute,

-5, DateTime.Parse(DateTime.Now.ToShortTimeString, myFormat)), 13, 20) & "#"

e.g

When the time is 2:07:00 p.m.

I can see the query goes to ADO like this

"UPDATE Sales SET Status = 'B' WHERE Mid(SalesDate,13,20) > #2:02:00 p.m.#"

but there is a syntax error, any idea

thks

 

[Ken Snell [MVP]]

Try this:

UPDATE Sales
SET Status = "A"
WHERE DateDiff("n",[SalesDate], Now())<5;

 

[Duane Hookom]

You shouldn't treat a date/time field as text using string functions. You
can get the time difference in minutes using
DateDiff("n",[YourDateTimeField],Now()). Your where clause would be
WHERE DateDiff("n",[YourDateTimeField],Now())> 5

 

[Marshall Barton]

In Access the date is like

21/12/2004 08:57:17 a.m.

I need to update the record only if it was not created more than five
minutes ago


cmd = "UPDATE Sales SET Status = 'A' WHERE Mid(SalesDate,13,20) > #" &
Mid(Microsoft.VisualBasic.DateAdd(DateInterval.Minute,

-5, DateTime.Parse(DateTime.Now.ToShortTimeString, myFormat)), 13, 20) & "#"

e.g

When the time is 2:07:00 p.m.

I can see the query goes to ADO like this

"UPDATE Sales SET Status = 'B' WHERE Mid(SalesDate,13,20) > #2:02:00 p.m.#"

but there is a syntax error, any idea

It looks like you're comparing a string to a date/time
value.

Maybe I'm missing something, because I don't understand all
this manipulation of a date/time value when there is a
function for finding the difference between two date/time
values.

Have a try with this:

WHERE DateDiff("n", SalesDate, Now() ) > 5