Results 1 to 11 of 11
  1. #1
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237

    Query based on values on a form

    Hi,

    I have a query to show some records based on date selections. The dates are taken from a dialogue form.


    In the criteria of the query design view, I have the following: Between [Forms]![frmOverDueDialogue]![txtStartDate] And [Forms]![frmOverDueDialogue]![txtEndDate]
    The query works fine when a date exists in the two text boxes of the form.

    What I need is that the query shows ALL the records if no dates are entered.
    In order to run the query, I need to press the OK button on the dialogue form .
    Can anyone help in doing it?

    Khalil

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What is the recordsource of the Form [frmOverDueDialogue]?

  3. #3
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Unbounded Form. It has no record source

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please show the SQL for the query that gets its criteria from your form.

    I think this is the general format of what you are seekinguse your own fields and table names)
    Code:
    SELECT desiredFields
    FROM YourtableName
    WHERE (
    		yourDate BETWEEN [Forms] ! [frmOverDueDialogue] ! [txtStartDate]
    			AND [Forms] ! [frmOverDueDialogue] ! [txtEndDate]
    		OR yourdate IS NULL
    		);
    Last edited by orange; 06-08-2020 at 06:35 AM. Reason: spelling

  5. #5
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Here is the SQL statement:

    Code:
    SELECT tblBorrowedBooks.DateBorrowed, DateAdd("d",21,[DateBorrowed]) AS DueDate, tblBorrowedBooks.DateReturned, Date()-[DateBorrowed]-21 AS DaysLate, [CallNumberNum] & " " & [CallNumberText] AS CallNumberFinal, tblBooks.BookTitle, [FirstName] & " " & [FathersName] & " " & [FamilyName] AS [Member Name], concatrelated("PhoneNumber","QryPhoneFaxTypes","fkMemberId=" & [pkMemberId]) AS PhoneNumbers, tblBorrowedBooks.pkBorowerId
    FROM tblBooks INNER JOIN (tblMembers INNER JOIN tblBorrowedBooks ON tblMembers.pkMemberId = tblBorrowedBooks.fkMemberId) ON tblBooks.pkBookId = tblBorrowedBooks.fkBookId
    WHERE (((DateAdd("d",21,[DateBorrowed])) Between [Forms]![frmOverDueBooksDialogue]![txtStartDate] And [Forms]![frmOverDueBooksDialogue]![txtEndDate]) AND ((tblBorrowedBooks.DateReturned) Is Null) AND ((Date()-[DateBorrowed]-21)>0))
    ORDER BY DateAdd("d",21,[DateBorrowed]);
    

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is the Sql after formatting with PoorSQL for anyone reading the thread.
    Code:
    SELECT tblBorrowedBooks.DateBorrowed
    	,DateAdd("d", 21, [DateBorrowed]) AS DueDate
    	,tblBorrowedBooks.DateReturned
    	,DATE () - [DateBorrowed] - 21 AS DaysLate
    	,[CallNumberNum] & " " & [CallNumberText] AS CallNumberFinal
    	,tblBooks.BookTitle
    	,[FirstName] & " " & [FathersName] & " " & [FamilyName] AS [Member Name]
    	,concatrelated("PhoneNumber", "QryPhoneFaxTypes", "fkMemberId=" & [pkMemberId]) AS PhoneNumbers
    	,tblBorrowedBooks.pkBorowerId
    FROM tblBooks
    INNER JOIN (
    	tblMembers INNER JOIN tblBorrowedBooks ON tblMembers.pkMemberId = tblBorrowedBooks.fkMemberId
    	) ON tblBooks.pkBookId = tblBorrowedBooks.fkBookId
    WHERE (
    		(
    			(DateAdd("d", 21, [DateBorrowed])) BETWEEN [Forms] ! [frmOverDueBooksDialogue] ! [txtStartDate]
    				AND [Forms] ! [frmOverDueBooksDialogue] ! [txtEndDate]
    			)
    		AND ((tblBorrowedBooks.DateReturned) IS NULL)
    		AND ((DATE () - [DateBorrowed] - 21) > 0)
    		)
    ORDER BY DateAdd("d", 21, [DateBorrowed]);

  7. #7
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Hi again,
    I tried the code but it seems it is not working well.
    It gives the correct records when two dates are specified. If txtStartDate and txtEndDate are cleared then it shows no records at all.

    The two text boxes txtStartDate and EndDate are cleared from the form with the following code:
    Code:
    Private Sub cmdClear_Click()
    On Error GoTo ProcError
        Me.txtStartDate = Null
        Me.txtEndDate = Null
        
        Me.txtHidden.SetFocus
    ExitProc:
        Exit Sub
    ProcError:
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
              vbCritical, "Error in procedure cmdClear_Click..."
        Resume ExitProc
    End Sub
    
    Thanks

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You need 2 queries, one with criteria and one without.
    If the date textboxes are null open the query without criteria.

  9. #9
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Thank you all.
    The two queries solve it.

    So it seems that it cannot be done with one query and some VBA code ?

    Khalil

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    So it seems that it cannot be done with one query and some VBA code ?
    You could do it this way. The code below will create a named query from an SQL string.
    Pass it the sql with the criteria and you get that query.
    Pass it the sql without the criteria and you get that query.
    Both queries created can have the same name (with only one existing at a time).

    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : fcnMakeNamedQuery
    ' DateTime  : 9/26/2006 20:57
    ' Author    : davegri
    ' Purpose   : Attach new SQL property to an existing querydef. If the Query doesn't exist,
    '           : create it with the passed SQL.
    '---------------------------------------------------------------------------------------
    '
    Function fcnMakeNamedQuery(qName As String, strPassedSQL As String)
        Dim qthisQuery As DAO.QueryDef
        If DCount("Name", "MSysObjects", "[Name] = " & Chr$(39) & qName & Chr$(39)) = 0 Then
            Set qthisQuery = CurrentDb.CreateQueryDef(qName, strPassedSQL)
        Else
            Set qthisQuery = CurrentDb.QueryDefs(qName)
            qthisQuery.SQL = strPassedSQL
        End If
        Application.RefreshDatabaseWindow
        Set qthisQuery = Nothing
    End Function

  11. #11
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    Quote Originally Posted by Khalil Handal View Post
    So it seems that it cannot be done with one query and some VBA code ?


    you do not need two queries.

    what you are looking for is the situation where a blank parameter means select all records.

    the basic structure for the WHERE statement to do this is as follows:

    Code:
    WHERE ((([enter a date]) Is Null)) OR (((your chosen field)=[enter a date]) AND (([enter a date]) Is Not Null))

    good luck with your project,


    Cottonshirt

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

Similar Threads

  1. Replies: 3
    Last Post: 03-18-2020, 04:54 PM
  2. Replies: 27
    Last Post: 10-30-2019, 01:27 PM
  3. Clear values on query based form
    By JackieEVSC in forum Forms
    Replies: 1
    Last Post: 04-21-2016, 11:17 AM
  4. Change Table Index Values Based on query Values
    By thuzkee02 in forum Import/Export Data
    Replies: 2
    Last Post: 11-24-2015, 11:45 PM
  5. Replies: 1
    Last Post: 12-24-2012, 09:36 AM

Tags for this Thread

Posting Permissions

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