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??

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

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

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??

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?

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

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!!

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

^ Do this, should make things much easier

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'}))

Please return the entire SQL string

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'}))

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:

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>=(SYSDATE-1))

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