Problem with time in access

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Mid is using Access's VBA implementation and not Microsoft JET SQL, which
is what ADO and ADO.Net will use.

The equivalent string command for JET SQL would be SUBSTRING

See the Access Table of Contents and scroll down to Microsoft Jet SQL
Reference.

Here are the other String commands, listed under ODB Scalar Functions

ASCII LENGTH RTRIM
CHAR LOCATE SPACE
CONCAT LTRIM SUBSTRING
LCASE RIGHT UCASE
LEFT
 
¤ 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

You don't want to use the MID function, although it would be available if sandbox mode for Jet is
enabled. Use the DateDiff function instead in order to determine whether the interval between dates
is greater than five minutes.

See the following in order to determine whether sandbox mode is properly enabled:

http://support.microsoft.com/default.aspx?scid=kb;en-us;294698


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top