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 ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)