Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2012
    Posts
    6

    Unanswered: Search form with Date Range

    I have search form with a subform showing the result based on a query. on the search form I have 3 combo boxes and would like to add 2 unbound text boxes for date range from/to.
    here is my query
    SELECT [BasicRoomInfo].BldgName, [Room Activity].RoomID, [Room Activity].InspectionReason, [Room Activity].InspectionResult,, [Room Activity].Technician, [Room Activity].InspectionDate
    FROM [BasicRoomInfo] INNER JOIN [RoomActivity] ON [BasicRoomInfo].[RoomID] = [RoomActivity].RoomID
    WHERE ((([BldgName]=[Forms]![Main Form]![cboBldgName] Or [Forms]![Main Form]![cboBldgName] Is Null)=True) AND (([InspectionResult]=[Forms]![Main Form]![cboinspres] Or [Forms]![Main Form]![cboinspres] Is Null)=True) AND (([InspectionReason]=[Forms]![Main Form]![cboinsprea] Or [Forms]![Main Form]![cboinsprea] Is Null)=True));

    and this is my search buttom on click event
    Private Sub cmdSearch_Click()
    Me.MyQuerySubform.Requery

    End Sub

    Private Sub cmdReset_Click()
    Me.cboBldgName = Null
    Me.cboinspres = Null
    Me.cboinsprea = Null
    Me.MyQuerySubform.Requery

    End Sub

    Private Sub cmdViewQuery_Click()
    DoCmd.OpenQuery "searchquery"
    End Sub

    Private Sub cmdOpenReport_Click()
    DoCmd.OpenReport "BasicRoomInfo3", acViewNormal
    End Sub

    Any help is really appreciated.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    You haven't actually asked a question...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use the between construct
    Google

    if you are using date literals use US (mm/dd/yyyy) or ISO format (yyyy/dd/mm)
    also encapsulate with hash symbols
    eg
    where MyDateColumn between "#01/21/12#" and "#01/31/12#"
    the order of the dates is important the lowest value should be the first one.
    you can also use the intrinsic datetime functions (assuming you have stored your dates as datetime datatypes
    Google
    you can force conversion to a datetime datatype using cdate
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jul 2012
    Posts
    6
    Thank you for quick response
    I tried between "#01/21/12#" and "#01/31/12#" in the criteria of inspectionDate column but when i do the search it pulls all the records, then i tried Between Forms![Main Form]!txtStartDate And Forms![Main Form]!txtEndDate)); same pulls all the records.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so can we see your SQL
    Im expecting to see somehting like
    SELECT my, column, list from MyTable
    Where ANumericValue >0
    and ADateColumn between "#01/21/12#" and "#01/31/12#"
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jul 2012
    Posts
    6
    I got it to work Thank you, now i have another problem on my search form i have a button to open a report of the search result. when i click on the button it ask for txtStartDate and txtEndDate. is there anyway i can open a report without asking for the start and end date and just the search result in the subform. mysubform is based on searchquery. i really appreciate your help.

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    You can reference the names of the controls in the query. If you post the SQL statement for the query, we'll be in a better position to help you.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you have more trust and faith in your users to supply valid dates as parameters to a query. I rarely use user supplied parameters in a query unl;ess they have been validated as sane prior to use. either from listboxes, radio boxes or validation forms
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jul 2012
    Posts
    6
    Thank you here is SQL

    PARAMETERS [txtStartDate] DateTime, [txtEndDate] DateTime;
    SELECT [BasicRoomInfo].BldgName, [RoomActivity].RoomID, [RoomActivity].InspectionReason, [Room Activity].InspectionResult, [RoomActivity].Technician, [RoomActivity].InspectionDate
    FROM [BasicRoomInfo] INNER JOIN [RoomActivity] ON [BasicRoomInfo].[RoomID] = [RoomActivity].RoomID
    WHERE ((([RoomActivity].InspectionDate) Between [Forms]![Main Form]![txtStartDate] And [Forms]![Main Form]![txtEndDate]) AND (([BldgName]=[Forms]![Main Form]![cboBldgtName] Or [Forms]![Main Form]![cboBldgName] Is Null)=True) AND (([InspectionResult]=[Forms]![Main Form]![cboinspres] Or [Forms]![Main Form]![cboinspres] Is Null)=True) AND (([InspectionReason]=[Forms]![Main Form]![cboinsprea] Or [Forms]![Main Form]![cboinsprea] Is Null)=True)) OR ((([BldgName]=[Forms]![Main Form]![cboBldgName] Or [Forms]![Main Form]![cboBldgName] Is Null)=True) AND (([InspectionResult]=[Forms]![Main Form]![cboinspres] Or [Forms]![Main Form]![cboinspres] Is Null)=True) AND (([InspectionReason]=[Forms]![Main Form]![cboinsprea] Or [Forms]![Main Form]![cboinsprea] Is Null)=True) AND (([Forms]![Main Form]![txtStartDate]) Is Null)) OR ((([BldgName]=[Forms]![Main Form]![cboBldgName] Or [Forms]![Main Form]![cboBldgName] Is Null)=True) AND (([InspectionResult]=[Forms]![Main Form]![cboinspres] Or [Forms]![Main Form]![cboinspres] Is Null)=True) AND (([InspectionReason]=[Forms]![Main Form]![cboinsprea] Or [Forms]![Main Form]![cboinsprea] Is Null)=True) AND (([Forms]![Main Form]![txtEndDate]) Is Null));

Posting Permissions

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