Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Dates using parameters (2000)

    New to Acess - Using query by form to have the user enter a date criteria on the form (the date, or <, or >, or <=, or >=, or Between And) to be used in the query. Getting the error, "Expression is typed incorrectly, or it is too complex to be evaluated. For example a numeric expression may contain too many complicated elements. try simplifying the expression by assigning parts of the expression to variables." Is it possible to use a parameter on a form to collect the date criteria?

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

    Re: Query Dates using parameters (2000)

    You can use a form to enter or select values such as "10/10/2003" (without the quotes) to be used as parameters in a query. You can't enter expressions such as ">1/20/2003" or "Between 1/1/2003 And 4/1/2003".

    An example of an allowed condition with date parameters from a form is

    Between [Forms]![frmDateSelect]![txtDateFrom] And [Forms]![frmDateSelect]![txtDateThru]

    This expression would be entered in the Criteria: line of the query design window. The keywords "Between" and "And" are in this line, they are not taken from the form. If you want to enter expressions on the form, it'll take Visual Basic code to process them.

  3. #3
    New Lounger
    Join Date
    Oct 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Dates using parameters (2000)

    Thanks that's what i feared. I have very basic VB skills.
    Can you point me in the right direction for using VB to perform this process? Any help will be greatly appreciated.

    I found this code which allows for the entry of two dates and returns the range between the two, if only one is entered then everything before or after that date. i believe this will work for me, but not sure how to convert it to work using a form that then opens a report with the results. Any assistance will be appreciated. Thanks,

    Dim strReport As String 'Name of report to open.
    Dim strField As String 'Name of your date field.
    Dim strWhere As String 'Where condition for OpenReport.
    Const conDateFormat = "#mm/dd/yyyy#"

    strReport = "rptSales"
    strField = "SaleDate"

    If IsNull(Me.txtStartDate) Then
    If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
    strWhere = strField & " < " & Format(Me.txtEndDate, conDateFormat)
    End If
    Else
    If IsNull(Me.txtEndDate) Then 'Start date, but no End.
    strWhere = strField & " > " & Format(Me.txtStartDate, conDateFormat)
    Else 'Both start and end dates.
    strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
    & " And " & Format(Me.txtEndDate, conDateFormat)
    End If
    End If

    ' Debug.Print strWhere 'For debugging purposes only.
    DoCmd.OpenReport strReport, acViewPreview, , strWhere

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

    Re: Query Dates using parameters (2000)

    Let's say you have a report rptSales whose record source contains a date field SaleDate (as in the sample code you posted). You want to let the user specify optional start and end dates to restrict what is displayed in the report.

    To this end, create a form and put two text boxes and a command button on it.

    Name the text boxes txtStartDate and txtEndDate. They should be unbound, i.e. leave their Control Source property blank. Set their Format property to the date format you prefer.

    Name the command button cmdReport and set its Caption property to "Open Report" or something like that.

    With the command button selected, activate the Event tab of the Properties window.
    Click in the On Click event.
    Select Event Procedure from the dropdown list.
    Click the Builder button - the three dots ... to the right of the dropdown arrow.
    You will be taken to the Visual Basic Editor, and the "skeleton" of the On Click event procedure will be ready for you to complete:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Private Sub cmdReport_Click()

    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

    Make it look like this - you can copy and paste code from this post into the Visual Basic Editor.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Private Sub cmdReport_Click()
    Dim strWhere As String

    On Error GoTo ErrHandler

    If IsNull(Me.txtStartDate) Then
    ' No start date
    If IsNull(Me.txtEndDate) Then
    ' Both dates empty - no restriction
    strWhere = ""
    Else
    ' Only end date specified
    strWhere = "SaleDate <= #" & Format(Me.txtEndDate, "mm/dd/yyyy") & "#"
    End If
    Else
    ' Start date specified
    If IsNull(Me.txtEndDate) Then
    ' Only start date specified
    strWhere = "SaleDate >= #" & Format(Me.txtStartDate, "mm/dd/yyyy") & "#"
    Else
    ' Both dates specified
    If Me.txtEndDate < Me.txtStartDate Then
    ' Warn user and get out
    MsgBox "The end date can't be earlier than the start date!", vbExclamation
    Me.txtEndDate.SetFocus
    Exit Sub
    End If
    strWhere = "SaleDate Between #" & Format(Me.txtStartDate, "mm/dd/yyyy") & _
    "# And #" & Format(Me.txtEndDate, "mm/dd/yyyy") & "#"
    End If
    End If

    ' Open the report with the Where condition
    DoCmd.OpenReport "rptSales", acViewPreview, , strWhere
    Exit Sub

    ErrHandler:
    Select Case Err
    Case 2501
    ' Report canceled (e.g. no data), ignore
    Case Else
    ' Display error message
    MsgBox Err.Description, vbExclamation
    End Select
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

    If you copied this code, you must replace the control, field and report names by the names you use.
    Save the form design and close it. Then open it in form view to test it.

  5. #5
    New Lounger
    Join Date
    Oct 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Dates using parameters (2000)

    This looks great, thank you.
    The only part that i am not following is how it knows the record set to start with?
    For instance if i wanted it to search a table or the results of a query that was already run?
    I don't see in the script provided where it is pointed to the record set to filter?
    Maybe i am missing it?
    Thanks again for your additional help with this.

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

    Re: Query Dates using parameters (2000)

    Perhaps I misunderstood you. I was under the impression that you wanted the user to provide a start date and end date, then open a report with only those records whose date falls between the two dates provided. The code I posted will do that. You don't need to specify a query or recordset; the code will pass a Where condition to the report; the report will apply the Where condition to its record source.

    If you want to do something else, please post back with details.

  7. #7
    New Lounger
    Join Date
    Oct 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Dates using parameters (2000)

    Hans you are terrific - I did not set the record source for the report. Thanks it is working!

    Let me tell you the entire project that i was working on and how i did it, maybe there is a more efficient way to do it.

    I have a table that has 3 columns that users want to run queries on: Case, State and Date. Users need to be able to run queries on all or any combination of these.

    This is how I set it up:
    I have a form that has three combo boxes that pull the data from the table for the case, state and date. User can select the case and/or state and/or a specific date from drop down lists on this form. I have a macro that opens the query to run the users input from the form. In the query i put the following criteria for each field: { [Forms]![Create_Status_Report]![CaseName] or [Forms]![Create_Status_Report]![CaseName] is null }. I put the same criteria in the State and Date fields.

    That left giving the user the ability to select a date range instead of a specificdate. That's where your genius comes in. After the above query is run, the macro opens a another form that has the information that you provided to take the date range if desired. The report has the above query results as it's recordsource.

    Can all of this be combined to one form?

    So thankful!

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

    Re: Query Dates using parameters (2000)

    I will look at this later, not enough time now.

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

    Re: Query Dates using parameters (2000)

    I don't understand why your macro would open a query. Is it an action query (for example a make-table query)?

  10. #10
    New Lounger
    Join Date
    Oct 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Dates using parameters (2000)

    Hans,
    I created a macro QBF-Macro that is activated by a command button on the first form Create_Status_Report . This macro opens the query Create_Status_Report to filter the table Status Report based on the Case and/or State and/or Date entered by the user on the Create_Status_Report form. This macro then opens the form test, which is the form and code that you assisted me with which opens the report which has it's record source as the results of the first query.

    It would seems that i should be able to combine all of this to one form - but am not sure how to perform the first query in code. My attempts have not been succesful.

    Hopefully this helps. Any suggestiohs to make it more efficient and have all the searches on one form would be appreciated.

    Thanks.

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

    Re: Query Dates using parameters (2000)

    Could you please do the following for me? Thanks in advance.

    Open the Create_Status_Report query in design view.
    What is the complete caption (title) of the Properties window?
    Switch to SQL view (View | SQL)
    Copy the SQL text to the clipboard and paste it into your reply.

  12. #12
    New Lounger
    Join Date
    Oct 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Dates using parameters (2000)

    Title is Create_Status_Report : Select Query

    SQL View:
    SELECT [Status Report].ID, [Status Report].CaseName, [Status Report].State, [Status Report].Date, [Status Report].Description
    FROM [Status Report]
    WHERE ((([Status Report].CaseName)=[Forms]![Create_Status_Report]![CaseName]) AND (([Status Report].State)=[Forms]![Create_Status_Report]![State]) AND (([Status Report].Date)=[Forms]![Create_Status_Report]![Date])) OR ((([Status Report].State)=[Forms]![Create_Status_Report]![State]) AND (([Status Report].Date)=[Forms]![Create_Status_Report]![Date]) AND (([Forms]![Create_Status_Report]![CaseName]) Is Null)) OR ((([Status Report].CaseName)=[Forms]![Create_Status_Report]![CaseName]) AND (([Status Report].Date)=[Forms]![Create_Status_Report]![Date]) AND (([Forms]![Create_Status_Report]![State]) Is Null)) OR ((([Status Report].Date)=[Forms]![Create_Status_Report]![Date]) AND (([Forms]![Create_Status_Report]![CaseName]) Is Null) AND (([Forms]![Create_Status_Report]![State]) Is Null)) OR ((([Status Report].CaseName)=[Forms]![Create_Status_Report]![CaseName]) AND (([Status Report].State)=[Forms]![Create_Status_Report]![State]) AND (([Forms]![Create_Status_Report]![Date]) Is Null)) OR ((([Status Report].State)=[Forms]![Create_Status_Report]![State]) AND (([Forms]![Create_Status_Report]![CaseName]) Is Null) AND (([Forms]![Create_Status_Report]![Date]) Is Null)) OR ((([Status Report].CaseName)=[Forms]![Create_Status_Report]![CaseName]) AND (([Forms]![Create_Status_Report]![State]) Is Null) AND (([Forms]![Create_Status_Report]![Date]) Is Null)) OR ((([Forms]![Create_Status_Report]![CaseName]) Is Null) AND (([Forms]![Create_Status_Report]![State]) Is Null) AND (([Forms]![Create_Status_Report]![Date]) Is Null))
    ORDER BY [Status Report].CaseName, [Status Report].State, [Status Report].Date;

    Thanks!

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

    Re: Query Dates using parameters (2000)

    This confirms what I thought: it is a Select query. A Select query just returns records that conform to certain criteria. It doesn't "do" anything. I don't understand why you would open this query before opening the form. The user doesn't need to see the query, and the form will open exactly the same way if you don't open the query first. If you want to filter the data displayed in the report, you must set the Record Source property of the report to the name of the query; you do this in design view, not in a macro. Am I missing something?

  14. #14
    New Lounger
    Join Date
    Oct 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Dates using parameters (2000)

    No, i was missing it... thanks a lot, i made the necessary changes.

    I combined all of the parameter combo boxes to one form and have the select query as the report's record source. It seems to be working fine but i would like to confirm how it's working. When the command is clicked, does it first do the code query and then the select query. Or does the report open, the query filter is done first and then the code query which you provided. I don't think it matters, but just wanted to know what the order was.

    I have a follow-up question, can the user provide on the fly how they want the report to be sorted? In other words can sort order be a parameter?

    Thanks again Hans, your information has been very helpful and i have learned a lot. I have since used the things i learned to create two other DB.

    Thanks.

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

    Re: Query Dates using parameters (2000)

    The sort order can't be a parameter, but you can create a popup form that lets the user specify the sort order. See MSKB article ACC2000: How to Sort a Report from a Pop-Up Form for a description (you can also download a demo database from a link in the article).

    Note: if you use the technique from the MSKB article, you should *not* specify a sort order in the Sorting and Grouping window, for that would overrule the sort order set in the popup form. Grouping levels are OK.

Page 1 of 2 12 LastLast

Posting Permissions

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