Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Will someone please have a look at the tblLog Query2, in the attatched database. I can't see what's wrong (there's obviously something). I'm trying to get option criteria to work ie, if no criteria is entered, all records are shown. If I enter a start and end date, just those records are shown. It works perfectly if I remove the criteria the date field in the query.
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    [quote name='robm' post='792512' date='08-Sep-2009 08:53']Will someone please have a look at the tblLog Query2, in the attatched database. I can't see what's wrong (there's obviously something). I'm trying to get option criteria to work ie, if no criteria is entered, all records are shown. If I enter a start and end date, just those records are shown. It works perfectly if I remove the criteria the date field in the query.[/quote]
    You might want to revisit the selection criteria for Log_date in your query. It is: "<[Start Date] And >=[End Date]"

    Which means you want a log_date that is before [Start Date] and on/after [End Date]! I don't think that is what you had in mind.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Try this
    (>[Start Date] And <=[End Date]) Or ([Start Date] Is Null)

    Note I have reversed the < and > signs.

    You can also remove the Expr 1, 2 3 4 fields at the end.

    This would be much more user friendly if the parameters were entered from a form with combo boxes on it.
    Regards
    John



  4. #4
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks both. I did realize after, that my <> symbols were the wrong way round. I had tried both ways. Thanks John, your method works. How would I use combo boxes on a form to run the query? Would I have to have another table?

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Have a look at the attachment.
    The combo boxes for Part No and IWO no need access to a list of possible values. I have got them from tblDetails, but if there was another list of possible values somewhere else you could use that.
    The query replaces the parameter prompts with reference to the form controls.
    I have also replaced < and > with between ... and. Either way is OK.
    Attached Files Attached Files
    Regards
    John



  6. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks John. I adapted the form to open up a report, which ran from the query. The form stayed on top of the report, so I put code in the form to Minimize, and code in the report, to Restore the form on close. Excellent, thanks again. I also now have an understanding of how to input data into a query, in a more user friendly way.

Posting Permissions

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