+ Reply to Thread
Results 1 to 25 of 25

Ask the date range

  1. #1
    Registered User
    Join Date
    12-07-2006
    Posts
    15

    Ask the date range

    I am building a pivot table, and I am linking to an external data source (MS Access database).

    I want the ability to program the table to ask for the date range when refreshed...I have tried a few statements,but can not seem to get this to go.

    Does anyone have any ideas?

    Thanks in advance,

    Sham

  2. #2
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Hi Mike,

    Need more information. Is the Pivot Cache a table in Access? Or, are you returning query results from Access backend to Excel front end, then using these query results as the Pivot Cache?

    If the latter, how are you executing the query? ODBC? Automation? Something else?

  3. #3
    Registered User
    Join Date
    12-07-2006
    Posts
    15
    I will do my best to explain...please bear with me...

    I have created a data source on my laptop that points to our quoting programs database with is an Access backend.

    Then I created a pivot table based on "external data source". Then I choose the fields I need/want for the pivot table.

    One of the fields is DateCreated. I would like the option of the enduser using the report to be able to request the table to refresh based on a certain date range. (i.e greater then January 1, 2006, less than February 17, 2006)

    Does that make sense? The hope here is to include a number of reports/pivot tables from a variety of different data sources. I have all completed except for this one which is data sensitive for teh management.

    Thanks
    Mike

  4. #4
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Yes, that makes sense.

    OK, we need the "CommandText" for the PivotCache. Here's how to get it ...

    Insert this macro into a general code module (if this instruction means nothing to you, let me know and I'll take it step-by-step).

    Please Login or Register  to view this content.
    Run the macro. Then, in the VB Editor, display the Immediate Window (View >> Immediate Window).

    This will give you the current SQL string that runs the query on your Access database.

    Post that SQL string here and I will show you how to re-code it. If the SQL string does not currently have any date restrictions on it, we might need to recycle a couple of times. But, have no fear, we will get there pretty quickly.

  5. #5
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Oh, I should have mentioned ... the code I posted assumes that there is only one PivotTable on that sheet and that it is named PivotTable1. If that assumption is bad, the code won't run. But, it can be fixed easily.

    Sorry if I am telling you too much or not enough ... hard to know what level you are at.
    Last edited by MSP77079; 12-19-2006 at 02:41 PM.

  6. #6
    Registered User
    Join Date
    12-07-2006
    Posts
    15
    Quote Originally Posted by MSP77079
    Oh, I should have mentioned ... the code I posted assumes that there is only one PivotTable on that sheet and that it is named PivotTable1. If that assumption is bad, the code won't run. But, it can be fixed easily.

    Yes there is only one pivottable named PivotTable1

    Sorry if I am telling you too much or not enough ... hard to know what level you are at.
    Just enough to be dangerous I think!! I am self taught...sorry.

    You mentioned in your previous post inserting the macro into a general code module...I understand the code, but am afraid I do not know how to insert it into a 'general code module'....but would love to learn!
    Thanks so much for you help and guidance

    Mike

  7. #7
    Registered User
    Join Date
    12-07-2006
    Posts
    15

    FIGURED part out

    Ok, I figured out the 'general code module'....sorry about that...

    Here is SQL string from the macro

    SELECT DocumentHeaders.ID, DocumentHeaders.DocNo, DocumentHeaders.DocType, DocumentHeaders.DocStatus, DocumentHeaders.DocDate, DocumentHeaders.DateCreated, DocumentHeaders.SalesRep, DocumentHeaders.SoldToCompany, DocumentHeaders.ShipToCompany, DocumentHeaders.GrandTotal
    FROM `G:\QuoteWerks\docs`.DocumentHeaders DocumentHeaders


    Hope that helps.
    Thanks again,

    Mike

  8. #8
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Yes, we are getting somewhere.

    As I feared, though. The current SQL statement has nothing restricting the date range. OK, a hill, but not a mountain.

    Which date do you want to use? The DocDate or the DateCreated?

    While you are thinking about that, I will work on the SQL string we will need. Should only take me a minute or 5.

  9. #9
    Registered User
    Join Date
    12-07-2006
    Posts
    15

    you're good

    OK,

    I would think using DateCreated is the date to use.

    Thanks
    Mike

  10. #10
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    OK. Let's try it first with "fixed" dates. Be sure we have the syntax correct before introducing variable dates.

    Change the dates in the code below to where they make sense for your data. Then, see if this works.


    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    12-07-2006
    Posts
    15

    silly question

    sorry for the silly question, but do I create this as another general code module?

  12. #12
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Not necessary.

  13. #13
    Registered User
    Join Date
    12-07-2006
    Posts
    15

    Runtime error

    I entered as outlined as another general module (sorry didn't see your post until after)

    I included the code, then ran the macro and recieved an error

    "Runtime error '1004' - Application-defined or object-defined error"

    I clicked DeBug and it highlights the line

    .CommandText = strSQL


    Mike

  14. #14
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Try this ...

    Comment out the Where statement (line of code for strSQL(7)). No need to change anything else. If that runs, then check that line carefully vs. what I posted.

    The problem is ... what I originally wrote was for DocDate (I guessed wrong on what you would want). Then, I edited it and hit "Submit". Re-reading it, I realized I screwed up and edited it.

    If you grabbed the first version, it has a bogus field name in the Where statement.

  15. #15
    Registered User
    Join Date
    12-07-2006
    Posts
    15

    Hmm...

    Ok, I commented out the where statement and still get the error.

    I have copied exactly what I have in the code here...looks like what you posted, but maybe my eyes are playing tricks?
    Please Login or Register  to view this content.
    Last edited by VBA Noob; 12-19-2006 at 04:04 PM.

  16. #16
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Looks right to me. I'm going back over this line by line, though.

    I have done this dozens of times, and I am working one on my machine in parallel just so I don't miss anything. Even putting in errors on purpose to test what might be wrong.

    Will get back if I find anything.

    You are still connected to your "G" drive, right?

  17. #17
    Registered User
    Join Date
    12-07-2006
    Posts
    15
    thanks.

    Yes I am still connected to my Gdrive

    Mike

  18. #18
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Well, darn. Even when I break mine on purpose, I get at least a more helpful error message.

    For example, if I give it a bum file address, the error message tells me it can't find the file. If I screw up the SQL statement, it tells me I have a problem in my SQL statement.

    What version of Excel are you running and what version of Access? (Neither should matter a great deal, but could be cause of slightly different error messages.)

    The only thoughts that come to mind are:
    + that funky character delineator that is being used, it seems to copy and paste OK on my machine; but, it is possible that it is getting screwed up when you copy and paste it. Try replacing the ` with double quotes "".

    + If I remember correctly, Access97 seemed to prefer all of its dates in the pre-milenium bug format. If you are using Access97, try replacing 2006 with 06.

    + It should not be necessary, but try putting a semi-colon at the end of the last part of the SQL

    Between #12/1/2006# And #12/15/2006#));"

    If none of that works, go back to step one and let's look at the .CommandText again.

  19. #19
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Hold the phone.

    Another thought comes to mind. Let's look at the connection string.

    Similar to the SQL string, run this macro and (maybe) post what comes out in the Immediate Window.

    Please Login or Register  to view this content.
    The reason I wrote "maybe" is that if the database is password protected, the connection string will contain the password. So, you will want to edit that out before posting it.

  20. #20
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Hi Mike,

    This has been bugging me all day. Had one more thought. Can you check the DocDate fields in the Pivot Table and verify that it is actually a date and not text that looks like a date? (Even better would be if you could check the Access database directly.)

    It occurred to me that if the data are originating from a third party source, they might be coming accross as text. That would definitely cause a problem with the way we've written the SQL statement. If that turns out to be the case, it is fixable.

  21. #21
    Registered User
    Join Date
    12-07-2006
    Posts
    15

    tricky tricky

    I have gone through the past posts.

    - It is Acess 2003 not 97
    - I tried with "" instead of ''...no worky worky
    - I ran the macro as posted...immediate window posts

    ODBC;DSN=Quotewerks;DBQ=G:\QuoteWerks\docs.mdb;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;


    Does any of the help or not help? The pivot table returns dates of
    03/14/2006

    Mike

  22. #22
    Registered User
    Join Date
    12-07-2006
    Posts
    15

    Playing with options

    I changed a few things to see if I could get some different error.

    When I changed the code to;

    Sub sqlForPivotCache()
    Dim strSQL(7) As String
    Dim pc As PivotCache

    Set pc = ActiveSheet.PivotTables("PivotTable2").PivotCache


    strSQL(1) = "SELECT DocumentHeaders.ID, DocumentHeaders.DocNo, "
    strSQL(2) = "DocumentHeaders.DocType, DocumentHeaders.DocStatus, "
    strSQL(3) = "DocumentHeaders.DocDate, DocumentHeaders.DateCreated, "
    strSQL(4) = "DocumentHeaders.SalesRep, DocumentHeaders.SoldToCompany, "
    strSQL(5) = "DocumentHeaders.ShipToCompany, DocumentHeaders.GrandTotal "
    strSQL(6) = "FROM 'G:\QuoteWerks\docs'.DocumentHeaders DocumentHeaders "
    strSQL(7) = "WHERE (((DocumentHeaders.DocDate) Between #03/01/2005# And #03/28/2005#))"

    With pc
    .CommandText = strSQL
    .Refresh
    End With

    End Sub

    It returns the same error but the debug points to

    Set pc = ActiveSheet.PivotTables("PivotTable2").PivotCache

    as the issue now.

    THe changes I made were;

    - using docdate instead...I notice the date created on the database includes a time stamp...thought maybe that would be an issue, and I used older dates in the exact format as the pivot table (i.e. 03/01/2005)

    Hope that helps, or at the very least does not cloud the issue more....couldn't help but play with it to see what damage I could do!!

    Mike

  23. #23
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Glad to see you have not given up.

    The connection string is not telling me anything so far.

    Since we never encountered an error on the line
    Please Login or Register  to view this content.
    before now, what has changed?

    Is the sheet with the pivot table no longer the active sheet? We probably should change that anyway. Make it more like:
    Please Login or Register  to view this content.

    Good to test DocDate. The time stamp should not be a problem (the db I am using for testing on my end also has time stamp), as long as the field is defined (in Access) as Date/Time. We only have an issue if it appears to be a date, but is really text.

    It appears from the code you posted this morning that after trying double quotes, you attempted to go back to what you had before, but did not succeed. Note the difference between this character ' and this character `

    You should be able to use double quotes "" or whatever you call this `. But, you CANNOT use '

    Don't ask me why. It is neither an Excel thing, nor an Access thing. It is an ODBC thing.

    If we don't crack this from Excel in the next iteration or two, I really want to test the SQL in Access directly. How facile are you with Access?

  24. #24
    Registered User
    Join Date
    12-07-2006
    Posts
    15
    Ok, I changed back to ` instead of ' ... no luck

    How familiar am I with Access...not so much...but always willing to try!!

    If you post some instructions I can test after my meetings this afternoon.

    Thanks for your patience and efforts!!

    Mike

  25. #25
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Open your database in Access. It is pretty much like any other Office product in this regard. That is, you can either use Windows Explorer, find your database, and double-clicking on it. Or, you can open Access from the Start menu and use the standard file open.

    Exactly what you see when the database opens will depend on how much customization has been done with the start-up settings for this database. Hopefully there is none. Unlike other Office products, Access has a lot of built-in customization features which database developers can use to fill new users with terror; thereby limiting the number of users who will ever become new database developers.

    For now, let’s assume there is no customization of this database. In this case, what you will see when the database opens is a database window within the Access window. Along the left side of this window is a list of with the caption “Objects”. These are “containers” for Tables, Queries, Forms, Reports, et cetera.

    Click on the “Queries” container.

    Just above the “Object” caption is a toolbar. The first 3 buttons should be: “Open”, “Design”, and “New”.

    Click on “New”.

    The New Query dialog will open. Select Design View, then click OK.

    An empty Query design template will open and in front of it you should see the Show Table dialog. By default, it should open with the Tables tab as the tab selected. If not, select the Table tab.

    This database might have only a single table in it. Or, it might have a zillion tables. Find the DocumentHeaders table. Select this table and click “Add”. Then, close the Show Table dialog.

    You should now be looking at a Query design template that has the Table DocumentHeaders at the top and an empty grid below it. The grid is where we define the query.

    To build the query, grab each field you want from the table and drop it in a field of the grid. You can do this one at a time, or you can use multi-select, with either Shift to select a contiguous range or Ctrl to select a range that is not contiguous.

    It might be that the Pivot Table query uses every field in the table. In which case, you would simply select the first field, hold the Shift key, and click on the last field to select all fields, then drag them all at once to the grid.

    At some point you might want to save this query. It will be easy enough to delete it later if you want to. The “Save” button should be the second from the left on the Access toolbar.

    Just to the left of the “Save” button is the “View” button. This is a very important button for us. There are two ways to run the query. The one we will use is the View button (the other is the “Run” button, which looks like an Excel Pivot Table “Refresh” button; some day you might care about the difference between these two buttons, but not today). Right now the icon for this button should look like a little spreadsheet, and the mouse-over tool-tip should read “View”. Clicking this button runs the query.

    When you run the query, the icon changes from a spreadsheet to a designer’s triangle. (I wonder if designers still use triangles?)

    If, instead of clicking the View button, you use the drop-down, you can select the SQL view.

    In the SQL view, you should see code that looks almost identical to what you posted yesterday as the “.CommandText” for the Pivot Table Cache. The only difference should be the semi-colon at the end of the “FROM” statement (signaling the end of the SQL string).

    If all of this is working fine, go back to the Design view. Grab DocDate from the table (at the top) and drop it to the grid, as the last entry on the right. You should now have TWO copies of DocDate in the grid.

    Click “off” the check in the “Show” check box for the second copy of DocDate. In the first line of the “Criteria”, type

    Between #03/01/2005# And #03/28/2005#

    Run the query.

    If it runs successfully, go back to the SQL view. Copy the SQL and save it somewhere safe. Then post it here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1