Parameters

[hholland]

I have a report that I am trying to edit however the date parameter will not stop popping up, so that I can edit the report. The report is linked to a crosstab query and the crosstab query is linked to a query with the date parameter. I have put the date parameter in the critiera in the query and in the menu under query/parameters. I have also put the date parameter in under the query/parameters in the crosstab query. The query and the crosstab query both run fine. I have also tried to re-create the report but I get an error message about "The microsoft jet database engine could not find the object ~sq_r(then the name of my report)". Can you please help me figure out how to fix this?

Thanks

 

[SydneyGeek]

Is the Date parameter also in the Parameters list for the crosstab? I wasn't sure from your post whether it was just the first query. Reports based on crosstabs often have this problem if the crosstab doesn't have the parameters explicitly listed.

Denis

 

[hholland]

Yes I have the date parameter set in my crosstab query. I selected Query from the menu at the top and choose Parameters and input the date parameter in the box that popped up. If this is not the parameter list that you are talking about, please let me know where else I am suppose to put the date parameter. Thanks

 

[SydneyGeek]

Try going to the base query -- the start point for the crosstab -- and define the parameter there too. Hopefully, that will stop the popup.

Denis

 

[hholland]

I have already set the date parameter in the base query, that did not help.

 

[Philem]

What are the exact prompts that are coming up? If they do NOT match, there is the answer to your problem. Go into the queries, and make sure that the parameter question is PRECISELY the same.

HTH
P

 

[SydneyGeek]

I'm with Phil on this one -- any typos?

Denis

 

[hholland]

The prompt that pops up is the same (no different spelling). The prompt just keeps asking me for the date over and over again and will not let me edit the summary report.

I even re-created the crosstab query using the wizard and still get the error message microsfot jet database engine could not find the object ~sq_r(then the name of my report).

 

[Philem]

Post both SQL statements, please? I know this works in A97, as I've done it many times myself. Let's see what you've got?

P

 

[hholland]

Please see below for the SQL Statements for all the queries that are involved. The crosstab query looks to Query 3 which uses Query 2 which uses Query 1. Could the problem be that I am using to may queries? Query 1 and 2 are also used for other reports that are working fine. We also just converted to Access 2003 with Windows XP.

Query1:
PARAMETERS [AS OF DATE] DateTime;
SELECT [CREDIT BUREAU STOPS].*, [ALLTEL USERID CODES].[USER ID NUMBER], [ALLTEL USERID CODES].NAME, [ALLTEL USERID CODES].DEPARTMENT, IIf([DEPARTMENT] Is Not Null,[DEPARTMENT],"DEPARTMENT NAME MISSING") AS [DEPARTMENT NAME], DateValue([AS OF DATE])-[CREDIT BUREAU STOPS].[CREDIT BUREAU CODE CHANGE DATE] AS AGE, IIf([AGE]>360,"f. >360",IIf([AGE]>180,"e. 181-360",IIf([AGE]>90,"d. 91-180",IIf([AGE]>60,"c. 61-90",IIf([AGE]>30,"b. 31-60",IIf([AGE]>-1,"a. 0-30","g. MISSING")))))) AS [AGE CATEGORY]
FROM ([CREDIT BUREAU STOPS] LEFT JOIN [CREDIT BUREAU STOPS + TASKS OUTSTANDING QUERY] ON [CREDIT BUREAU STOPS].[LOAN NUMBER] = [CREDIT BUREAU STOPS + TASKS OUTSTANDING QUERY].[LOAN NUMBER]) LEFT JOIN [ALLTEL USERID CODES] ON [CREDIT BUREAU STOPS].[CREDIT BUREAU CODE CHG BY ID] = [ALLTEL USERID CODES].[USER ID NUMBER]
WHERE ((([CREDIT BUREAU STOPS + TASKS OUTSTANDING QUERY].[TASK ID]) Is Null) AND (([CREDIT BUREAU STOPS].[CREDIT BUREAU CODE EXPIRE DATE]) Is Null));

Query2:
PARAMETERS [AS OF DATE] DateTime;
SELECT [CREDIT BUREAU STOPS].*, [TASKS OUTSTANDING].[TASK ID], [TASKS OUTSTANDING].[TASK ID DESCRIPTION], [TASKS OUTSTANDING].[TASK RECEIVED DATE], [TASKS OUTSTANDING].[TASK START DATE], [TASKS OUTSTANDING].[TASK LAST UPDATE DATE], [TASKS OUTSTANDING].[TASK FOLLOW UP DATE], [TASKS OUTSTANDING].[TASK ORIG EXPECTED CLOSE DATE], [TASKS OUTSTANDING].[TASK EXPECTED CLOSE DATE], [TASKS OUTSTANDING].[TASK RESPONSIBLE ID], [TASKS OUTSTANDING].[TASK RESPONSIBLE DEPARTMENT ID], [TASK RESPONSIBLE DEPARTMENT].[TASK RESPONSIBLE DEPARTMENT NAME]
FROM ([CREDIT BUREAU STOPS] LEFT JOIN [TASKS OUTSTANDING] ON [CREDIT BUREAU STOPS].[LOAN NUMBER] = [TASKS OUTSTANDING].[LOAN NUMBER]) LEFT JOIN [TASK RESPONSIBLE DEPARTMENT] ON [TASKS OUTSTANDING].[TASK RESPONSIBLE DEPARTMENT ID] = [TASK RESPONSIBLE DEPARTMENT].[TASK RESPONSIBLE DEPARTMENT ID]
WHERE ((([TASKS OUTSTANDING].[TASK ID])="CAREVR" Or ([TASKS OUTSTANDING].[TASK ID])="CALSTP" Or ([TASKS OUTSTANDING].[TASK ID])="CALST2" Or ([TASKS OUTSTANDING].[TASK ID])="CAMICR" Or ([TASKS OUTSTANDING].[TASK ID])="CAMIC2" Or ([TASKS OUTSTANDING].[TASK ID])="CRPTOS" Or ([TASKS OUTSTANDING].[TASK ID])="SLSERV" Or ([TASKS OUTSTANDING].[TASK ID])="SLSSR_" Or ([TASKS OUTSTANDING].[TASK ID])="SLEMSR" Or ([TASKS OUTSTANDING].[TASK ID])="MISAPP" Or ([TASKS OUTSTANDING].[TASK ID])="WCPTOS" Or ([TASKS OUTSTANDING].[TASK ID])="CRFDUE" Or ([TASKS OUTSTANDING].[TASK ID])="CAREVS"));

Query3:
PARAMETERS [AS OF DATE] DateTime;
SELECT [CREDIT BUREAU STOPS].*, [ALLTEL USERID CODES].[USER ID NUMBER], [ALLTEL USERID CODES].NAME, [ALLTEL USERID CODES].DEPARTMENT, IIf([DEPARTMENT] Is Not Null,[DEPARTMENT],"DEPARTMENT NAME MISSING") AS [DEPARTMENT NAME], DateValue([AS OF DATE])-[CREDIT BUREAU STOPS].[CREDIT BUREAU CODE CHANGE DATE] AS AGE, IIf([AGE]>360,"f. >360",IIf([AGE]>180,"e. 181-360",IIf([AGE]>90,"d. 91-180",IIf([AGE]>60,"c. 61-90",IIf([AGE]>30,"b. 31-60",IIf([AGE]>-1,"a. 0-30","g. MISSING")))))) AS [AGE CATEGORY]
FROM ([CREDIT BUREAU STOPS] LEFT JOIN [CREDIT BUREAU STOPS + TASKS OUTSTANDING QUERY] ON [CREDIT BUREAU STOPS].[LOAN NUMBER] = [CREDIT BUREAU STOPS + TASKS OUTSTANDING QUERY].[LOAN NUMBER]) LEFT JOIN [ALLTEL USERID CODES] ON [CREDIT BUREAU STOPS].[CREDIT BUREAU CODE CHG BY ID] = [ALLTEL USERID CODES].[USER ID NUMBER]
WHERE ((([CREDIT BUREAU STOPS].[CREDIT BUREAU CODE EXPIRE DATE]) Is Not Null));

Crosstab Query:
PARAMETERS [AS OF DATE] DateTime;
TRANSFORM Count([CREDIT BUREAU STOPS WITH EXPIRATION DATES QUERY].[DEPARTMENT NAME]) AS [CountOfDEPARTMENT NAME]
SELECT [CREDIT BUREAU STOPS WITH EXPIRATION DATES QUERY].[DEPARTMENT NAME], Count([CREDIT BUREAU STOPS WITH EXPIRATION DATES QUERY].[AGE CATEGORY]) AS [CountOfAGE CATEGORY]
FROM [CREDIT BUREAU STOPS WITH EXPIRATION DATES QUERY]
GROUP BY [CREDIT BUREAU STOPS WITH EXPIRATION DATES QUERY].[DEPARTMENT NAME]
PIVOT [CREDIT BUREAU STOPS WITH EXPIRATION DATES QUERY].[AGE CATEGORY];