Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing the criteria (A2K)

    Good Morning,

    Is there a way to pass the "Between Date And Date" criteria to a query in vba? I know there are a couple of ways of passing the date range to the query but don't know how to pass it through code.

    In the query grid, in the criteria section of a date, I want it to read, for example: "Between 1/1/01 and 12/31/01" ,with the BeginingDate and EndingDate being what is passed in code. Something like this:

    dim dStart as Date
    dim dEnd as Date

    dStart = DLookup("NewStartDate", "qryNewDates")
    dEnd= DLookup("NewEndDate", "qryNewDates")

    but I don't know, if, a) this is the correct approach, if it is, how to complete it and how to pass it to the query.

    If anyone has any suggestions, I would love to hear it.

    Thanks in advance.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Passing the criteria (A2K)

    The easiest way is to have a form into which the user can enter the appropriate dates, then in your query's selection criteria put a reference to each control from the from, something like:

    WHERE SomeDate Between Forms!frmEnterDates!txtStartDate and Forms!frmEnterDates!txtEndDate
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing the criteria (A2K)

    If you are constructing SQL to execute the query, the WHERE clause of the SQL string should look like :
    . . . . . . .WHERE (((<tablename>.<fieldname>) Between #1/1/2001# And #12/31/2001#));
    obviously substituting your values for tablename and fieldname.
    (I didn't work this out; I wrote a query and then had a look at the SQL Access generated!)

    If you are using DLookUp, you can use the criterion part of the clause (this extract from Access help):

    DLookup( expr, domain, [criteria ])
    The DLookup function has the following arguments.

    Argument Description
    expr An expression that identifies the field whose value you want to return. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field . In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.
    domain A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.
    criteria An optional string expression used to restrict the range of data on which the DLookup function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DLookup function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DLookup function returns a Null.

    The criterion can be a complex expression like "condition 1 AND condition 2" so it could be "tablename.fieldname GE Me.StartDate and tablename.fieldname LE Me.Enddate"
    I am sure this is not the whole answer but it may give you some pointers to a solution.
    Hope it helps somewhat, anyway
    Silverback
    Silverback

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing the criteria (A2K)

    Thanks for the suggestion Mark. I do this in most of my processess however, in this particular case I can't. I need the criteria section to read, for example "Between 1/1/01 and 12/31/01".

    The reason it MUST include the dates and not reference to a form because this query data is for an Excel spreadsheet. Where the user will requery the data in the spreadshet and Excel doesn't recognize anything other than the string in the criteria section.

    I'm now looking at dynamic queries, it looks like this is my only opion.

    However, I will use your suggestion many times over for my other projects.

    Again, thank you.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Passing the criteria (A2K)

    You can create the SQL string in code:

    Dim strSQL As String
    strSQL = "SELECT Field1, Field2 FROM SomeTable WHERE SomeDate Between #" & _
    Forms!frmEnterDates!txtStartDate & "# And # " & Forms!frmEnterDates!txtEndDate & "#"

    You can change the SQL of your query to this string, or pass the SQL string to Excel, depending on how you use it.

    Example of changing the SQL of a query:

    CurrentDb.QueryDefs("MyQuery").SQL = strSQL

    (You need a reference to the Microsoft DAO 3.6 Object Library for this)

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Passing the criteria (A2K)

    If you're retrieving the data as an external query from Excel, you might find it easier to have the parameters in Excel and just have your query returning all data to MSQuery, which can then filter it according to the parameters given in Excel.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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