Results 1 to 5 of 5

Thread: Query confusion

  1. #1
    Join Date
    Nov 2005
    Posts
    3

    Unanswered: Query confusion

    I am having problems with a simple query. I want the query to search for rental properties between a certain price range e.g $150 and $200...that the user inputs. I have propaddress and rentamount in design view. Under rentamount criteria I tried Between[enter amount] AND [enter amount] and of course this doesn't do what I want....What am I doing wrong?...
    Also I want to set it up so that the user can only enter the fields DateVacated or DateOccupied at one time (i.e a property can not be vacant and occupied at the same time)
    Cheers
    HellNZ(rookie)

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sounds like you need to put front end to handle the business logic. sounds grand - but its 'just' a form to do your validations

    how you handle the processing of the data is up to you. You can do it as a paramterised query, or write the SQL based on the values in the form

    the code to access the values in yoiur form is
    forms!<myformname>!<myvariablename>

    the between clause is the right one for your rental range - assuming of course that you have a range (i'm not too sure what JET woudl do if you cited a range 299-299, it'll probably work but you need to check it works for you.

    As an alternative to your date problem you could consider having a single date called say DateofLastChangeover, and a status flag (I'd suggest an intger or byte - possible values could be rented, vacant, vacant - not available etc.

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by HellNZ
    I am having problems with a simple query. I want the query to search for rental properties between a certain price range e.g $150 and $200...that the user inputs. I have propaddress and rentamount in design view. Under rentamount criteria I tried Between[enter amount] AND [enter amount] and of course this doesn't do what I want....What am I doing wrong?...
    Also I want to set it up so that the user can only enter the fields DateVacated or DateOccupied at one time (i.e a property can not be vacant and occupied at the same time)
    Cheers
    HellNZ(rookie)
    Are you trying to include the ($) sign as well? Leave that part out. I just tried a sample of what you have and it worked fine.

    BUD

  4. #4
    Join Date
    Jan 2005
    Posts
    31
    Quote Originally Posted by HellNZ
    I am having problems with a simple query. I want the query to search for rental properties between a certain price range e.g $150 and $200...that the user inputs. I have propaddress and rentamount in design view. Under rentamount criteria I tried Between[enter amount] AND [enter amount] and of course this doesn't do what I want....What am I doing wrong?...
    HellNZ(rookie)
    You appear to be using the same parameter twice in the BETWEEN, "[enter amount]".

    This is like saying "WHERE col1 BETWEEN 5 AND 5"

    You need, "BETWEEN [first amount] and [second amount]".

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by chris_n_osborne
    You appear to be using the same parameter twice in the BETWEEN, "[enter amount]".

    This is like saying "WHERE col1 BETWEEN 5 AND 5"

    You need, "BETWEEN [first amount] and [second amount]".
    Hey, good catch there. Thought it was just a typo.

Posting Permissions

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