+ Reply to Thread
Results 1 to 21 of 21

Microsoft Query

  1. #1
    Registered User
    Join Date
    09-26-2009
    Location
    ontario canada
    MS-Off Ver
    Excel 2003
    Posts
    64

    Microsoft Query

    I am importing data that contains specific start dates and I was wondering how to filter the criteria in Microsoft query to only show start dates greater than today or perhaps yesterday. This would eliminate all entries that have already occurred. It seems I can only select a date in the criteria that exists in the data. Any suggestions??

  2. #2
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Microsoft Query

    Searching this forum for 'Microsoft Query Date' yields:
    http://www.excelforum.com/excel-prog...-criteria.html

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Microsoft Query

    I don't think you can do it directly in the MSQUERY criteria.. but see here on a workaround by Debra Dalgleish:

    Today's Date and MSQuery
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Registered User
    Join Date
    09-26-2009
    Location
    ontario canada
    MS-Off Ver
    Excel 2003
    Posts
    64

    Re: Microsoft Query

    The Date() did not work and when I followed the instructions listed by Debra, the "Parameters" option was not available (greyed out). Any more suggestions

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Microsoft Query

    Did you ensure that in this step:

    Remove the date in the query, and enter a parameter. For example:
    [What Date?]
    that the "What Date?" is in between square brackets..

  6. #6
    Registered User
    Join Date
    09-26-2009
    Location
    ontario canada
    MS-Off Ver
    Excel 2003
    Posts
    64

    Re: Microsoft Query

    Thank you, the parameter prompt now comes up but I cannot seem to get the proper format for entering todays date. I keep getting "ORA-1843 Not a valid month" for every date format i try to enter. What am I doing wrong??

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Microsoft Query

    When I do my queries using that method, it doesn't matter how I format the date in Excel, it seems to work fine in extracting from the database.

    Is there a difference in regional setting between your database and your excel...

    ie. is one M/D/Y and the other D/M/Y? so that Sep 30 would give error... but if you do Oct 1, it shouldn't give error.. just incorrect data. (too much/too little)...

    try copying the error:

    ORA-1843 Not a valid month

    and google it for more advice...

  8. #8
    Registered User
    Join Date
    09-26-2009
    Location
    ontario canada
    MS-Off Ver
    Excel 2003
    Posts
    64

    Re: Microsoft Query

    I do not know if there is a difference between my regional settings or how to check this. I am getting a prompt whenever I open the work book that states; "CANNOT FIND XLQUERY.XLA!REGISTER.DCLICK, WHICH HAS BEEN ASSIGNED TO RUN EACH TIME[SCHEDULED TRAINING TEMPLATE.XLS]SCHEDULED TRAINING IS OPENED. CONTINUING COULD CAUSE ERRORS. CANCEL OPENING [SCHEDULED TRAINING TEMPLATE.XLS] SCHEDULED TRAINING? I select "NO" and the information updates without any problem. Is this what you are refering to? or is this another problem that needs to be fixed?

  9. #9
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264

    Re: Microsoft Query

    Hi

    If you eneter a specific date as criteria (ie one that actually exists in the list) what does the generated SQL look like? You can tell this by clicking on the SQl button in MsQuery (it's on the standard toolbar) and pasting the SQL that will appear into your reply.

    Richard
    Richard Schollar
    Microsoft MVP - Excel

  10. #10
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Microsoft Query

    Just thought I'd pitch in with a memory - for slightly different reasons I fell out with MS Query (i.e. it does things to SQL that would make grown men weep). In the end I wrote a pass-through query in Access which I then referenced from Excel - this may be a practicable approach for you.

    CC

  11. #11
    Registered User
    Join Date
    09-26-2009
    Location
    ontario canada
    MS-Off Ver
    Excel 2003
    Posts
    64

    Re: Microsoft Query

    OK; I entered [>What Date?] in the criteria in MS Query and I was able to finally enter a date in the parameter prompt(01OCT2009).I selected a cell with the TODAY() formula and now I am getting "[ORACLE][ODBC][ORA]ORA-1847: day of month must be between 1 and last day of month." No data is being returned. I removed the ">" from the criteria with the same result. I do not write SQL so please be very specific and detailed about any suggestions and yes this grown man is almost weeping!!

  12. #12
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Microsoft Query

    Unless anyone dives in with a better suggestion in the next couple of hours I will try to pursue this idea

    (I ask because it'll take me more than a few minutes and there's no point if there's a better solution).

    CC

  13. #13
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Microsoft Query

    As far as I am concerned.. You can go ahead CC... I am not sure how to fix that either at this point.

  14. #14
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264

    Re: Microsoft Query

    Hi

    From MSQuery when you have the query returning all your data, please hit the SQL button within MSQuery (it's on the standard toolbar below the menu bar on the left hand side) and then copy the SQL into your reply. Please make clear which field is the date field. Thanks.

  15. #15
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Microsoft Query

    ^ Do this, should make things much easier

  16. #16
    Registered User
    Join Date
    09-26-2009
    Location
    ontario canada
    MS-Off Ver
    Excel 2003
    Posts
    64

    Re: Microsoft Query

    I have a date entered and it appears in the SQL as;((TIM_SCHEDULED_TRAINING_EVENTS.START_DT>={ts '2009-09-22 00:00:00'}))

  17. #17
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Microsoft Query

    Please return the entire SQL string

  18. #18
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264

    Re: Microsoft Query

    Also please indicate if that date criteria you've entered actually works or not.

  19. #19
    Registered User
    Join Date
    09-26-2009
    Location
    ontario canada
    MS-Off Ver
    Excel 2003
    Posts
    64

    Re: Microsoft Query

    This criteria works and data is returned. The SQL string:SELECT TIM_EMPLOYEES.GANG_ID, TIM_EMPLOYEES.FIRST_NAME, TIM_EMPLOYEES.LAST_NAME, TIM_EMPLOYEE_ROSTERS.EMP_ID, TIM_SCHEDULED_TRAINING_EVENTS.PEL_ID, TIM_PROGRAM_ELEMENTS.NAME, TIM_SCHEDULED_TRAINING_EVENTS.STE_ID, TIM_EMPLOYEE_ROSTERS.ROSTER_TYPE, TIM_SCHEDULED_TRAINING_EVENTS.START_DT, TIM_SCHEDULED_TRAINING_EVENTS.END_DT, TIM_SCHEDULED_TRAINING_EVENTS.MAX_ATTENDEES, TIM_SCHEDULED_TRAINING_EVENTS.EXTERNAL_LOCATION, TIM_SCHEDULED_TRAINING_EVENTS.PRIMARY_ROOM_NO, TIM_SCHEDULED_TRAINING_EVENTS.CANCELLED_DT, TIM_SCHEDULED_TRAINING_EVENTS.CANCEL_REASON, TIM_EMPLOYEE_ROSTERS.ATTENDANCE_TYPE, TIM_SCHEDULED_TRAINING_EVENTS.START_DT
    FROM TIM.TIM_EMPLOYEE_ROSTERS TIM_EMPLOYEE_ROSTERS, TIM.TIM_EMPLOYEES TIM_EMPLOYEES, TIM.TIM_PROGRAM_ELEMENTS TIM_PROGRAM_ELEMENTS, TIM.TIM_SCHEDULED_TRAINING_EVENTS TIM_SCHEDULED_TRAINING_EVENTS
    WHERE TIM_SCHEDULED_TRAINING_EVENTS.PEL_ID = TIM_PROGRAM_ELEMENTS.PEL_ID AND TIM_SCHEDULED_TRAINING_EVENTS.STE_ID = TIM_EMPLOYEE_ROSTERS.STE_ID AND TIM_EMPLOYEE_ROSTERS.EMP_ID = TIM_EMPLOYEES.EMP_ID AND ((TIM_SCHEDULED_TRAINING_EVENTS.START_DT>={ts '2009-09-22 00:00:00'}))

  20. #20
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Microsoft Query

    Right... fingers crossed everybody:
    Go to Access
    Follow these instructions to link to your database:
    http://www.aspfree.com/c/a/Microsoft...ess-with-ODBC/
    You need these tables:
    TIM_EMPLOYEES
    TIM_EMPLOYEE_ROSTERS
    TIM_SCHEDULED_TRAINING_EVENTS
    TIM_PROGRAM_ELEMENTS
    Now create a pass-through query in Access, paste tweaked SQL:
    Please Login or Register  to view this content.
    Check this works in Access, it should bring up the data you're looking for
    ">=SYSDATE-1" is for anything yesterday or later - edit as preferred
    Assuming this works, you can then go back to Excel and link to your query (not the tables) via the import data wizard you're used to

    Notes:
    There are multiple potential points of failure in this process, please try a few things with each bit because this is quite hard to explain over message board
    The reason it (may) work is because the tricky date element is coded into the SQL, rather than inputted, and not via Access's ropey way of handling dates either!
    If it does work, your life will have meaning and you will owe me beer

    CC

  21. #21
    Registered User
    Join Date
    09-26-2009
    Location
    ontario canada
    MS-Off Ver
    Excel 2003
    Posts
    64

    Solved

    Thanks, this put me on the right path and I was able to make this work. I appreciate the help in solving this issue!
    Last edited by pdmkh; 10-24-2009 at 08:29 AM.

+ 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