Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: Parameter Query

  1. #1
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93

    Unanswered: Parameter Query

    I am tring to get a query to run some criteria before returning results.

    What I need is for the query to return records based on a Sector from Beginning Date at a set time to End Date at a set time.

    So Far in the Query I have set the field Critieria as follows

    Code:
    [Enter Sector] "which would be North, South, East, West, etc..."
    
    Between [Type Beginning Date:] And [Type Ending Date:] "for the date criteria"
    
    Between #7:00pm# and #7:00am# "and for the time criteria"
    The prompts come up and display the proper info, however the is no data returning in the table, or on the report.

    I need to show data from the Beginning date starting at 7pm and stop with the ending date at 7am. i.e.

    7/8/06 7:35pm
    7/8/06 9:30pm
    7/8/06 11:58pm
    7/9/06 1:26am
    7/9/06 3:15am
    7/9/06 6:58am
    7/9/06 7:00am

    I don't want it to display any data on the start date prior to the set time, or anything on the ending date after the set time.

    I am not sure what I am doing wrong, or what steps I am missing. I do know that when I leave the Time criteria out I get the results I need, except it includes all the data for both dates.
    Will Dove
    working hard is better then hardly working ...

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    You will need to Serialize the Date/Time for this to be really successfull. Look up "Serialize Date/Time".

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93

    Where to look

    I am looking on the Microsoft Knowledge base, Access help, and google, but not getting any results.

    can you point me in the right direction.

    Thanks
    Will Dove
    working hard is better then hardly working ...

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    saileast, I suggest you make a "search form" to enter the parameters. this allows you to take take control over the entry of the parameters before running the query, otherwise you'll get errors like "Incorrect data type" when invalid data is entered into the prompts.

    The form can be very simple, with a few fields that match the parameters you quoted.

    You can use the results to open another for with the results, show the results on the same form, open a reports, or even run the original query.

    let us know if you need help.

    tc

  5. #5
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93

    More info

    TC,

    sounds like a better way to me, but I am not sure where to start with that feature.

    please explain a little more.
    Will Dove
    working hard is better then hardly working ...

  6. #6
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93

    This is what I have so far

    Please take a look at this and let me know what I need to do to get it working for the form to modify the query.

    This is a very limited portion of my DB

    Thanks for looking.

    I have never used a form to modify a query.
    Attached Files Attached Files
    Will Dove
    working hard is better then hardly working ...

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Looks like a good start. If the popup form is a search form, you want to make it "unbound" since changing the values on the current form will edit the data in the table. this means removing the recordsource on each field.

    In the popup form, you have the code that opens the report. To limit the report to just the records matching your search criteria, you need to add the "Where Clause" to your open report action:
    DoCmd.OpenReport stDocName, acPreview,,"WHERE CLAUSE"

    There is info in the help file about this, and you can create sample "where clauses" by writing a query that filters the way you want and then viewing the query in SQL view.

    For example, I renamed the controls to txtStartDate and txtEndDate and also set both to defualt to Now().
    DoCmd.OpenReport stDocName, acPreview, , "[PatrolDate] Between #" & Me.txtStartDate & "# And #" & Me.txtEndDate & "#"

    You may notice I enclosed the dates with pound signs. The Where Clasue is a string, but we want Access to know that the values inside the SQL statement are to be treated as date/times, which is done as shown. Otherwise, it won't work correctly. Similarly, if you were using an actual string value, you would enclose it with a single quote on either side.


    have fun,
    tc

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    PS:

    I have never used a form to modify a query.
    We're not actually modifying the query. Rather, the query is giving us everything - we're using the form to provide restrictions to the data the report returns.

  9. #9
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93

    I work on this today

    Sorry I had to work a double yesterday, so I will get started on this today, I will let you know what I end up with. Thanks again for the help.

    Will Dove
    working hard is better then hardly working ...

  10. #10
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93

    working 50%

    I did what you suggested, and I have it working for the date range, however I can not get it to work for the date and time.

    here is the code.

    Code:
    Private Sub PreviewPatrolReport_Click()
    On Error GoTo Err_PreviewPatrolReport_Click
    
        Dim stDocName As String
    
        stDocName = "Rpt_Patrols"
        DoCmd.OpenReport stDocName, acPreview, , "[PatrolDate] Between #" & Me.txtStartDate & "# And #" & Me.TxtEndDate & "#", , "[PatrolTime] Between #" & Me.txtStartTime & "# And #" & Me.TxtEndTime & "#"
    
    Exit_PreviewPatrolReport_Click:
        Exit Sub
    
    Err_PreviewPatrolReport_Click:
        MsgBox Err.Description
        Resume Exit_PreviewPatrolReport_Click
        
    End Sub
    I have made the other changes to the form. It brings up the report, but does not apply the filter based on time or sector. the sector is dropdown menu that pulls values from a table called tbl_PatrolNames
    Last edited by saileast; 07-13-06 at 04:53.
    Will Dove
    working hard is better then hardly working ...

  11. #11
    Join Date
    Nov 2003
    Posts
    1,487
    It looks like half of your Criteria in the OpenReport function WHERE clause is applied to the OpenArgs argument of the function. Just for the heck of it...try this. It may get you started.

    DoCmd.OpenReport stDocName, acPreview, , "[PatrolDate] Between #" & Me.txtStartDate & "# And #" & Me.TxtEndDate & "# AND [PatrolTime] Between " & Format(Me.txtStartTime, "hh.mm") & " And " & Format(Me.TxtEndTime, "hh.mm")

    Format(Me.txtStartTime, "hh.mm") converts the start time to 24 hour (military) time format.

    Format(Me.TxtEndTime, "hh.mm") converts the end time to 24 hour (military) time format.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  12. #12
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93

    closer to what I need

    What you suggested is working to apply filters, however it is not returning the data between the times. I need it to return results starting on the start date at the start time, and ending with the end date and the end time. I can not get it to return the proper times.

    Quote Originally Posted by CyberLynx
    It looks like half of your Criteria in the OpenReport function WHERE clause is applied to the OpenArgs argument of the function. Just for the heck of it...try this. It may get you started.

    DoCmd.OpenReport stDocName, acPreview, , "[PatrolDate] Between #" & Me.txtStartDate & "# And #" & Me.TxtEndDate & "# AND [PatrolTime] Between " & Format(Me.txtStartTime, "hh.mm") & " And " & Format(Me.TxtEndTime, "hh.mm")

    Format(Me.txtStartTime, "hh.mm") converts the start time to 24 hour (military) time format.

    Format(Me.TxtEndTime, "hh.mm") converts the end time to 24 hour (military) time format.

    .
    Will Dove
    working hard is better then hardly working ...

  13. #13
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Are your times stored as seperate fields from the dates? If so, what format are they in (Number or Date/Time)?

    If the answer is Date/Time then you may have a problem where you are getting foiled by the DATE portion of the time value you are storing. I don't remember if Access uses the current date or a default initial date (like Jan 1, 1903 or something).

    If using a Date/Time field type for the time, I suggest you store the Dates and Times together in one field (this is recommended). You can grab the values seperately on the form, but keep them together on the table.

    If the answer is an a number, then it will be tricky making sure time spans that cross midnight sort correctly.

    If using a number, I suggest creating a date/time value in the query that combines the time with the date.

    Either way, you are then guarenteed that 10:37 PM, May 4, 2006 comes before 7:42 AM, May 5, 2006.

    tc

  14. #14
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93

    Times and Dates

    My Times and dates are stored separate, and both are Date/Time in the table.

    I am open to suggestions for this, I am still in the redesign and redevelopment phase of the database. If making the data and time fields into a Now() field would make this an easier process then I will change it to that, and combine them in one field in the table.

    I will mess around with this a little and see what I get.

    However I still need it to sort based on the Dropdown menu that gets it's choices from one table and will lookup records in the other table based on choice.

    i.e.

    tbl_PatrolNames has the choices in field PatrolName
    tbl_Patrols stores the selection in field PatrolSector

    I need to sort the report based on the the following from tbl_Patrols
    Which Patrol sector (PatrolSector)
    Which Start Date and Time (PatrolDateTime)
    Which End Date and Time (PatrolDateTime)
    Will Dove
    working hard is better then hardly working ...

  15. #15
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93

    Got the Date working

    Well I got the date and time feature working, thanks for the suggestions, by combining the date and time, it made the standard coding work.

    Now I am tring the Patrol sector and I am getting an error Message "Type Mismatch"

    here is the code I have been tying, but PatrolSector is a Drop down menu.

    Code:
    Private Sub PreviewPatrolReport_Click()
    On Error GoTo Err_PreviewPatrolReport_Click
    
        Dim stDocName As String
    
        stDocName = "Rpt_Patrols"
        DoCmd.OpenReport stDocName, acPreview, ,"[PatrolSector] = & Me.txtPatrolSector &, AND [PatrolDate] Between #" & Me.txtStartDate & "# And #" & Me.TxtEndDate & "#", ,
    
    Exit_PreviewPatrolReport_Click:
        Exit Sub
    
    Err_PreviewPatrolReport_Click:
        MsgBox Err.Description
        Resume Exit_PreviewPatrolReport_Click
        
    End Sub
    Will Dove
    working hard is better then hardly working ...

Posting Permissions

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