Results 1 to 5 of 5
  1. #1
    Lowell is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2012
    Location
    Manitoba, Canada
    Posts
    54

    Between/And parameter not working

    I have searched for an answer but to no avail.

    I have a query to pull up DOB for a person and give them an age. That works perfect.

    I want to use that query for serveral different reports.

    Then I made a query running off the age query to bring up user selected ages.

    If I put in "2" it brings up 2, 21, etc.

    If I put in "6" it brings up 6, 65, etc.

    I guess I have done something wrong?

    I use this parameter for my age:

    Age: DateDiff("yyyy",[BirthDate],Date())+Int(Format(Date(),"mmdd")<Format([BirthDate],"mmdd"))

    I used this parameter for between/and:



    Between [Start Age] And [End Age]

    Help?
    Last edited by Lowell; 10-17-2012 at 09:54 PM. Reason: Posted accidentally before finished typing

  2. #2
    Bob Fitz is online now Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,196
    I think you need:

    Between CInt([Start Age]) And CInt([End Age])
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  3. #3
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,034
    Bob's Suggestion is the best way to accomplish what you want. However, I would add one other thing.

    Age: CINT(DateDiff("yyyy",[BirthDate],Date())+Int(Format(Date(),"mmdd")<Format([BirthDate],"mmdd")))

    This gaurantees the field and the Criteria to be the same datatype.

    Also I am curious Do you use the " when you enter the age? When you use Criteria in a query as you do Access assumes a Variant Datatype. If you're including the " when you enter the "start age" and "end age" Access determines it's a string which would account for the results you're receiving. "6", "60", "69" are all between "6" and "7"

  4. #4
    Lowell is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2012
    Location
    Manitoba, Canada
    Posts
    54
    I have changed my parameter and it works good as far as I can tell. I never knew that option existed. Many thanks to you for taking the time to help me. That's why I keep reading this forum, searching this forum, and posting my questions.

    About the last question Ray, I don't use the quotations. After I had posted the question I realized it looked a little odd, but hoped my point would still get across. Thanks for mentioning it though.

    Good day.

  5. #5
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,034
    Honestly, I didn't think you did but after 20 years doing this I've learned to validate everything, assume nothing.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Input Parameter Not Working
    By cc143most in forum Queries
    Replies: 2
    Last Post: 08-21-2012, 07:37 AM
  2. Parameter Value
    By snormannews in forum Access
    Replies: 3
    Last Post: 05-03-2012, 07:12 AM
  3. Replies: 13
    Last Post: 01-10-2012, 09:56 AM
  4. Parameter Query not working
    By cphelps48 in forum Queries
    Replies: 3
    Last Post: 10-06-2011, 04:42 PM
  5. parameter query not working
    By denny in forum Access
    Replies: 3
    Last Post: 02-20-2011, 01:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums