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

    Criteria Choice (2) (2003)

    Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>

    Following on from a previous post (<post#=455626>post 455626</post#>), I've got another problem with the search criteria. I've been asked be able to interrogate the database for who raised the problem, and also change the date search to a range (from and to). I've tried adding another column, as Hans did when he helped me out before, but I'm not getting the desired results.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Criteria Choice (2) (2003)

    It would be helpful if you posted the query you are trying to use.

    Either the SQl or a screen capture in design view if you can show all of it.

    How do the results you get differ from what you want?
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Criteria Choice (2) (2003)

    With the database from your previous thread, this should work:

    PARAMETERS [Enter User] Text ( 255 ), [Start Date] DateTime, [End Date] DateTime, [Raised by Whom] Text ( 255 );
    SELECT [tblDaily Production Main].[Log No], [tblDaily Production Main].Author, [tblDaily Production Main].[Actual Shipped], [tblDaily Production Main].[Date Created], [tblDaily Production].Problem, [tblDaily Production].[Raised By], [tblDaily Production].[Action To Be Taken], [tblDaily Production].[Time Saved], [tblDaily Production].Comments, [tblDaily Production].User, [tblDaily Production].Comments2, [tblDaily Production].Completed, [tblDaily Production].Initial, [tblDaily Production].Date
    FROM [tblDaily Production Main] RIGHT JOIN [tblDaily Production] ON [tblDaily Production Main].[Log No] = [tblDaily Production].[Log No]
    WHERE ((([tblDaily Production Main].[Date Created])>=[Start Date] Or [Start Date] Is Null) AND (([tblDaily Production Main].[Date Created])<=[End Date] Or [End Date] Is Null) AND (([tblDaily Production].[Raised By])=[Raised by Whom] Or [Raised by Whom] Is Null) AND (([tblDaily Production].User) Like "*" & [Enter User] & "*" Or [Enter User] Is Null));

  4. #4
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Criteria Choice (2) (2003)

    With a few modifications, that.s exactly what I want thanks Hans. They've added a completion date to the report as well, but that, as yet has not been added to the query, but no doubt it will eventually. Thanks also to John for your reply.

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Criteria Choice (2) (2003)

    Extremely wide screenshot edited by HansV

    Not working as well as I thought. If I put in dates 01/03/05 to 04/04/05, I get nothing, although there is data there. Also strange things are happening to the query (see attached) If I look at the query in SQL view, it seems to have doubled?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Criteria Choice (2) (2003)

    The display is normal - Access rearranges the criteria. But you haven't entered them correctly, somehow - perhaps the browser messed up the "greater than or equal" and "less than or equal" characters. Attached is a modified version of the database from the previous thread.

    BTW, if they keep adding parameters, it would be more user-friendly to design a form with unbound controls in which the user can enter the parameters, and refer to the controls in the query.

Posting Permissions

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