Re: MID(,,) equiv in JET SQL using VB6/ADO

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Mon, 23 Apr 2007 14:19:36 -0500, "jpm" <uh@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

¤ Hello,
¤
¤ What equivalent of the MID(,,) vba expression can I use in a query against a
¤ JET database? I'm using VB6 and ADO 2.6.
¤
¤ I think that with DAO I might use this function; I know that with T-SQL
¤ querying I'd use the subString(,,) function.
¤ Both functions are failing to execute when running the query against the Jet
¤ file:
¤
¤ >>>"SELECT order_num, Mid([invoicedate], 3, 2) as Month, mid([InvoiceDate],
¤ >>>1,2) as Yr, cust_Code , soldtoName, ProdLine INTO tmpOrders FROM ADtl
¤ >>>INNER JOIN AHd ON Adtl.Lorder = AHd.HOrder WHERE InvoiceDate Between
¤ >>>'060101' And '060301' And (fg = 'xXX-NC' or fg = 'xXX.NC')"<<<<
¤
¤ Thanks for any input,

The Mid function is supported by Jet SQL, however just as in VBA if Mid attempts to process invalid
data an error will be generated.

What type of error are you encountering? You may have to use the IIf function to validate your data
before attempting to execute Mid.


Paul
~~~~
Microsoft MVP (Visual Basic)
.

Relevant Pages

  • Re: Behaviour between ADO on ODBC Access Driver and Jet OLE DB
    ... Microsoft Access Driver ... Jet Connection String:- ... I use the itemdata from this selection ... ¤ was suggested to me earlier this month that I change over to the Jet OLEDB ...
    (microsoft.public.vb.database.ado)
  • Re: Any reason why accessing Access should slowdown?
    ... On the MSACCESS / JET OLEDB front, you responded to a query I had about the ... Declared connection to Access database using string based on Jet OLDB ... Set up recordset filter in response to earlier input / selection ... ¤ I am writing a VB6 App using an Access database backend ...
    (microsoft.public.vb.database)
  • Re: Access vs SQL
    ... How about if we say that an .mdb file is an Access file that wraps a JET ... An Access file is DATA stored in a JET database ... > ¤ Not so, Cor. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Access vs SQL
    ... the Microsoft Jet database engine has been ... > ¤ How about if we say that an .mdb file is an Access file that wraps a JET ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Access vs SQL
    ... ¤> its own things (in addition to the standard Jet things). ... It makes calls to a database engine API in order to perform certain database ... support certain features such as executing VBA code routines used in SQL or Access QueryDefs. ...
    (microsoft.public.dotnet.languages.vb)