Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Alpine Califormia

    Unanswered: Parameters not giving correct values

    I dont want the users to have to put all the parameters in if they dont want to, I would like to have a choice between them. If they want to put in last name then it gives me the correct results or with the date parameters or all the above

    something is wrong with my select statement cause I can get the date range but not the last name
    gives the correct date range but nto the last name. I use this in RS

    SELECT     TanfActivity_tbl.EventDate, TanfActivity_tbl.[Type of Contact], TanfActivity_tbl.[Purpose of Contact], TanfActivity_tbl.[Earned hours], 
                          TanfActivity_tbl.[Catagory for hours], People_tbl.[Parent ID], People_tbl.[Student First Name], People_tbl.[Student Last Name]
    FROM         TanfActivity_tbl INNER JOIN
                          People_tbl ON TanfActivity_tbl.[Parent ID] = People_tbl.[Parent ID]
    WHERE     (TanfActivity_tbl.EventDate BETWEEN @Beginning_EventDate AND @End_EventDate) OR
                          (People_tbl.[Student Last Name] = @Enter_StudentLastName)

  2. #2
    Join Date
    Nov 2007
    Adelaide, South Australia
    I'd say you need some IF's happening.

    I'd also recommend you use a custom dialog rather than using query parameters... it's a whole lot more friendly for your users.

    A combination of both makes it flexible and user-friendly.

    You can build your SQL based on your dialog box content with code such as:

    If Me.txtStudentLastName > "" Then strSQL = strSQL & " AND StudentLastName = """ & Me.txtStudentLastName

    That kind of thing.

    If not you'll have to make use of IIf() but I think that may end up with worse situations, such as the system prompting for parameters more than once.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Dec 2004
    Madison, WI
    I sometimes break these types of queries into separate queries. One filtering the date criteria and the other query using that query to further filter the data with additional criteria (ie. nested queries).
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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