 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / April 2007MID(,,) equiv in JET SQL using VB6/ADO
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, Jimb > Hello, > [quoted text clipped - 14 lines] > > Jimb There isn't one. Using the ADO data access library only recognizes 'ADO SQL'. You won't have much luck using DAO either, ie, not quite the way you think. However, you can utilize stored procedures (Queries) through Automation or ADO. -ralph Hmmm.. . . Then what is the meaning of the content of this blog: http://feeds.feedburner.com/TheBlogWithNoName It talks about executing vba expressions, but it's using C# as the outside tool. Maybe that's the difference? I haven't done enough yet with .net languages. >> Hello, >> [quoted text clipped - 27 lines] > > -ralph > Hmmm.. . . > Then what is the meaning of the content of this blog: > http://feeds.feedburner.com/TheBlogWithNoName > It talks about executing vba expressions, but it's using C# as the outside > tool. Maybe that's the difference? I haven't done enough yet with .net > languages. No difference - He is calling stored procedures (Queries). You can do the same. By "outside the Access Environment" he means not using the MSAccess Application. MSAccess is just a fancy user-friendly MDI App/Utility built on top of the "Jet Engine" (cf, Enterprise Manager vs SQL Server). -ralph ¤ 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) Geez oh Pete! You're right, the VBA function is handled and it is when submitted via ADO. The problem is that I was using the reserved keyword [MONTH] as the alias. Thanks to all for the help. > ¤ Hello, > ¤ [quoted text clipped - 28 lines] > ~~~~ > Microsoft MVP (Visual Basic) > ¤ Hello, > ¤ [quoted text clipped - 18 lines] > 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, please explain to me what the OP was attempting to do. I think I missed something? <g> -ralph ¤ ¤ Paul, please explain to me what the OP was attempting to do. I think I ¤ missed something? <g> Hi Ralph, My understanding was that he was attempting to use the VBA Mid function in his Jet SQL query and was encountering some sort of error (not indicated). Apparently he assumed that the error was being generated by the Mid function, and that it wasn't supported, when in fact is was being triggered by using the Month reserved keyword as a column name (sans brackets). Paul ~~~~ Microsoft MVP (Visual Basic) > ¤ > ¤ Paul, please explain to me what the OP was attempting to do. I think I [quoted text clipped - 6 lines] > generated by the Mid function, and that it wasn't supported, when in fact is was being triggered by > using the Month reserved keyword as a column name (sans brackets). Sorry I meant in "context". I thought you couldn't use "VBA functions" in SQL statements using ADO by doing something like this... Set cn As ADODB.Connection cn.Open "Provider=Jet OLEDB, ....." Set cmd As ADODB.Command cmd.Execute "Select Func(someFld) From tblJunk" This would only work if running the SQL/Query inside Jet, ie through a Stored Query (or one defined earilier.) Apparently I have been wrong, I just wanted to make sure how wrong. -ralph ¤ > ¤ Paul, please explain to me what the OP was attempting to do. I think I ¤ > ¤ missed something? <g> ¤ > ¤ > Hi Ralph, ¤ > ¤ > My understanding was that he was attempting to use the VBA Mid function in ¤ his Jet SQL query and was ¤ > encountering some sort of error (not indicated). Apparently he assumed ¤ that the error was being ¤ > generated by the Mid function, and that it wasn't supported, when in fact ¤ is was being triggered by ¤ > using the Month reserved keyword as a column name (sans brackets). ¤ > ¤ ¤ Sorry I meant in "context". ¤ ¤ I thought you couldn't use "VBA functions" in SQL statements using ADO by ¤ doing something like this... ¤ ¤ Set cn As ADODB.Connection ¤ cn.Open "Provider=Jet OLEDB, ....." ¤ Set cmd As ADODB.Command ¤ cmd.Execute "Select Func(someFld) From tblJunk" ¤ ¤ This would only work if running the SQL/Query inside Jet, ie through a ¤ Stored Query (or one defined earilier.) ¤ ¤ Apparently I have been wrong, I just wanted to make sure how wrong. You *cannot* execute user-coded VBA functions (and many built-in VBA functions) through Jet. User-coded functions can only run in queries under Microsoft Access. QueryDefs are parsed to SQL text when executed via the Jet database engine so the limitation remains. You *can* run a subset of VBA functions through Jet. How to configure Jet 4.0 to prevent unsafe functions from running in Access 2003 http://support.microsoft.com/default.aspx?scid=kb;en-us;294698&Product=acc Paul ~~~~ Microsoft MVP (Visual Basic) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.