Re: Compile error on common function, NOT REFERENCE PROBLEM

From: Wayne Morgan (comprev_gothroughthenewsgroup_at_hotmail.com)
Date: 11/04/04 

Date: Wed, 3 Nov 2004 20:55:35 -0600

I just tied the Mid in a query to make sure and it worked. Here is the SQL
from my test.

SELECT Table10.FirstName, Table10.Middle, Table10.LastName, Table10.Suffix
FROM Table10
WHERE ((Mid([Middle],3,2)="ex"));

Doug brought up a good point about Sandbox mode. Do you have the current
service packs installed for Office, Jet, and MDAC? You don't mention your
operating system, if it is Windows 2000 or Windows XP, you can get the
latest Jet service pack by running Windows Update. If not, go to
http://msdn.microsoft.com/data/downloads/updates/default.aspx.

Will you paste the SQL of your query into a message? 

-- 
Wayne Morgan
MS Access MVP
"rgrantz" <r_grantz@hotmail.com> wrote in message 
news:2umr47F2bltflU1@uni-berlin.de...
>I attempted to put the expression
>
> mid([FieldName],3,2)="ZZ" in the criteria cell for [fieldname] in the 
> Query
> Builder (not SQL view).  When running the query, I get the immediate
> "Compile error. in criteria expression" message.  When searching 
> newsgroups,
> I found that the advice most commonly given for this problem was to check
> the references in Tools-->References, check for missing ones, remove ones,
> add them again, etc. etc.  I've tried removing all ones possible (Visual
> Basic and DAO Objects say they can't be removed because they are in use),
> then adding them back, not adding them back, etc. etc.  I have made a new
> blank database and imported all objects from the old one, I've compacted 
> and
> repaired.  I've also removed and reinstalled Office 2000 Premium and
> installed all Office Updates.
>
> To make sure the visual basic function works, I made a module with:
>
> Sub midtest()
> Dim strtext As String
> strtext = "thisisatest"
> MsgBox Mid(strtext, 3, 2), vbOKOnly
> End Sub
>
> Running this sub returns a message box with prompt "is", which shows, I
> believe, that the mid function operates.
>
> Is anyone able to put a Mid function in their criteria in the query 
> builder?
> Is it possible that it's simply not something that can be used there?

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • RE: Expire accounts from Active Directory after a period of inactivity
    ... One thing to be conscious of- in Windows 2000, ... you could not simply query AD for this information- you would ... How a Hacker Uses SQL Injection to Steal Your SQL ... > box giving hackers complete access to all your backend systems! ...
    (Focus-Microsoft)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)