Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2005
    Posts
    10

    Unanswered: Am I really out of memory?

    I am using Micrisoft Access 2003. M ytable has 50,000 records with 80 fields per record. Most of the records only have 30 fields of data.

    I then run a query on 8 of the fields. Each of the 8 fields has a criteria that looks something like this:

    (>=[start] or like [start] is null) and (<=[end] or like [end] is null)

    So far this works fine. As long as I put the criteria directly into the criteria box, it'll work fine.

    The problem starts when I use textboxes on a form to assign the parameters into the parameter queries like below:

    (>=[forms].[panel].[start] or like [forms].[panel].[start] is null) and (<=[forms].[panel].[end] or like [forms].[panel].[end] is null)

    After I do this on all 8 criterias, my query will not work anymore. I can't even go into design view anymore. Whenever I try to go into design view, I get a message that says out of memory. Am I really out of memory or is there something wrong? My comp has 512 mb of ram and I'm running Win XP.

  2. #2
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    Take a look at this website. It has a lot of good tips and explains why this happens.

    http://support.microsoft.com/kb/q103429/

    and

    http://office.microsoft.com/tr-tr/as...874531033.aspx

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i'm struggling to understand the intended result from
    SELECT * FROM blah WHERE this Like that Is Null

    i guess that the sub-expression
    this Like that
    will be Null if either this or that or both are Null

    so your overall expression
    this Like that Is Null
    will (??maybe) evaluate TRUE if either/both is Null and FALSE otherwise

    but
    the earlier part of your SQL will implode if that is null - so the only situation you can catch with this unusual syntax is when this is null.

    Null fields are more conventionally selected with
    this Is Null

    ...and i have no idea at all if this is somehow related to your memory issue.
    i tried a quick experiment with your bizarre syntax and got err 3075

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Just a side note: I lost my memory years ago ... Not to mention my sanity, reason, etc ...

    A question: What is the field type you're doing the LIKE comparison on?

    Also: I'm with izy on the construct of your filter clause ... It does not look to be valid ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Jun 2005
    Posts
    10
    the syntax allows me to leave one side of the parameter query blank. here's the link that describes the rationale.

    http://www.fontstuff.com/access/acctut07.htm

    my overall plan is to have a form with textboxes where the user can enter the parameters.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    fair point.

    i didn't dig too deeply in the link you provided since i don't use parameter queries

    in case it helps your move to a search form, here is my trivial demo.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    had a second look at that site...
    ...good site!

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •