Problem with Parameter Query in Access 2003

[aruba]

I am running a query in Access2003 that has a date parameter query in one column. The query works if I type in a specific date in the criteria, however if I use a parameter query, it times out. My date format matches the format on my computer's Regional settings. Can anyone suggest how I can resolve the problem? Thanks in advance!

 

[Darrylles]

aruba,

Show the code used.


atb

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[aruba]

This is the query that I am running:
SELECT TEMPUS_PERIOD_ACTUALS.PA_PEREND_DT AS Week, Sum(TEMPUS_PERIOD_ACTUALS.PA_REG_HRS) AS Reg_Hrs, Sum(TEMPUS_PERIOD_ACTUALS.PA_OT_HRS) AS OT_Hrs, Sum([TEMPUS_PERIOD_ACTUALS].[PA_OT_HRS]+[PA_REG_HRS]) AS Hours, TEMPUS_EMPLOYEE.EMP_EMPLOYEE_TYP
FROM TEMPUS_EMPLOYEE INNER JOIN (tempus_ORG_WRKGRP_XREF INNER JOIN TEMPUS_PERIOD_ACTUALS ON tempus_ORG_WRKGRP_XREF.OW_ORG_ID = TEMPUS_PERIOD_ACTUALS.PA_ORG_ID) ON (TEMPUS_EMPLOYEE.EMP_ID = TEMPUS_PERIOD_ACTUALS.PA_EMP_ID) AND (TEMPUS_EMPLOYEE.EMP_ORG_ID = TEMPUS_PERIOD_ACTUALS.PA_ORG_ID)
GROUP BY TEMPUS_PERIOD_ACTUALS.PA_PEREND_DT, TEMPUS_EMPLOYEE.EMP_EMPLOYEE_TYP
HAVING (((TEMPUS_PERIOD_ACTUALS.PA_PEREND_DT)=#1/10/2007#));

This works however if I change the last line to the following(which is what I need to do) it times out:
HAVING (((TEMPUS_PERIOD_ACTUALS.PA_PEREND_DT)=[Enter date]));

 

[JerryKlmns]

Add this line at the top of your SQL statement

PARAMETERS [Enter date] DateTime;

 

[aruba]

Thanks. I tried it but this query is in Access 2003 and it says the statement is invalid when I add it to the beginning.

 

[JerryKlmns]

Your Select statement as a query mySelect

SELECT TPA.PA_PEREND_DT AS myWeek, 
       Sum(TPA.PA_REG_HRS) AS Reg_Hrs, 
       Sum(TPA.PA_OT_HRS) AS OT_Hrs,
       Sum([TPA].[PA_OT_HRS]+[PA_REG_HRS]) AS Hours,
       TE.EMP_EMPLOYEE_TYP
FROM TEMPUS_EMPLOYEE As TE INNER JOIN (tempus_ORG_WRKGRP_XREF As tOWX INNER JOIN TEMPUS_PERIOD_ACTUALS As TPA ON 
            tOWX.OW_ORG_ID = TPA.PA_ORG_ID) ON 
        (TE.EMP_ID = TPA.PA_EMP_ID) AND 
        (TE.EMP_ORG_ID = TPA.PA_ORG_ID)
GROUP BY TPA.PA_PEREND_DT, 
         TE.EMP_EMPLOYEE_TYP

The parameter query should be

PARAMETERS [Enter Date] DateTime;
SELECT *
FROM mySelect
WHERE myWeek=[Enter Date];

 

[aruba]

Thanks but this won't work. The user is not experienced in VBA. What she is trying to do is be able to change the date every time she runs the query through an input box by using [specify date] in the criteria of design view. If she enters an actual date in the criteria it works. It's also interesting that this problem only occurs when connecting to an Oracle database. The problem does not exist when connecting to a SQL database. Are there any known problems with Access 2003 and Oracle?

 

[JerryKlmns]

There 's no VBA in my answers! But if you 'd like to, here is a way faq705-2531.

BTW, have you tried my suggestion? These are two (2) queries inside access, using the table names you provided and fields (with alias for short). The second query is the paramter one that filters the first for a date through an input box.

I can't tell about Oracle.

And aruba, you should have been more detailed from the first post!

 

[aruba]

Thanks but I have tried that already. Anything I type into an input box does not seems to work. (error message is ODBC call failed)

 

[lespaul]

do you have a working ODBC connection to the Oracle data?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[aruba]

Yes, the Oracle connection works, shows all the data and the query works if an actual #date# is listed in the criteria. It just doesn't work when a parameter query is used.

 

[lespaul]

how are you getting the information into Access? I would expected Linked tables, but by default Access names those with dbo_ prefixes, which you don't have in your SQL.

If you are importing the tables into Access, then you shouldn't be using the ODBC connection. Have you tried a pass-through query? If you use the pass through, I'm pretty sure you would need to remove the Access date delimiters (#).

HTH

 

[aruba]

I have tried both methods with the same results. I renamed the tables when I used ODBC(using Oracle 92 driver) and a pass-through query still times out if I enter a parameter and not the exact date.