Urgent: Passing Date to Stored Procedure during design

[rvejaya_chitra]

Urgent Help needed,Please

I have created a view in Ms Access 2000 which includes some values to be passed to it. the parameters are of date type.


Details of the view as follows

(Two dates are required starting and ending date
Fetch summation of records less that starting date and combine it with the records between two dates)

(SELECT A.TRANSACTION_ID, A.TRANSACTION_DATE, A.SITE_FROM_ID, A.SITE_TO_ID, (IIF(A.TRANSACTION_TYPE = 'A' OR A.TRANSACTION_TYPE = 'R',A.SITE_TO_ID,A.SITE_FROM_ID)) AS BENEFICIARY_SITE, A.TRANSACTION_TYPE, (( A.QUANTITY_RECEIVED_STAGE1+ A.QUANTITY_RECEIVED_STAGE2) - (A.QUANTITY_ISSUED_STAGE1+ A.QUANTITY_ISSUED_STAGE2)- (A.QUANTITY_DAMAGED_STAGE1+ A.QUANTITY_DAMAGED_STAGE2)-( A.QUANTITY_EXPIRED_STAGE1+ A.QUANTITY_EXPIRED_STAGE2)) AS OPENING, 0 AS ISSUED, 0 AS RECEIVED, 0 AS DAMAGED, 0 AS EXPIRED, VACCINE_CATEGORY.CATEGORY_NAME, VACCINE_MASTER.VACCINE_NAME
FROM ITEM_TRANSACTION AS A, VACCINE_MASTER, STOCK_IDENTIFICATION, VACCINE_CATEGORY, STOCK_CROSS_REFERENCE
WHERE (A.ITEM_ID=STOCK_IDENTIFICATION.ITEM_ID) And (STOCK_IDENTIFICATION.STOCK_REFERENCE_ID=STOCK_CROSS_REFERENCE.STOCK_REFERENCE_ID AND STOCK_CROSS_REFERENCE.STOCK_TYPE = 'V' ) And (STOCK_CROSS_REFERENCE.MASTER_ID=VACCINE_MASTER.VACCINE_ID) And (VACCINE_MASTER.VACCINE_PRIME_CATEGORY=VACCINE_CATEGORY.CATEGORY_ID) AND (A.TRANSACTION_DATE< [SDT]))

UNION (SELECT A.TRANSACTION_ID, A.TRANSACTION_DATE, A.SITE_FROM_ID, A.SITE_TO_ID, (IIF(A.TRANSACTION_TYPE = 'A' OR A.TRANSACTION_TYPE = 'R',A.SITE_TO_ID,A.SITE_FROM_ID)) AS BENEFICIARY_SITE, A.TRANSACTION_TYPE,
0 AS OPENING, (A.QUANTITY_ISSUED_STAGE1+ A.QUANTITY_ISSUED_STAGE2) AS ISSUED,( A.QUANTITY_RECEIVED_STAGE1+ A.QUANTITY_RECEIVED_STAGE2) AS RECEIVED, (A.QUANTITY_DAMAGED_STAGE1+ A.QUANTITY_DAMAGED_STAGE2) AS DAMAGED, (A.QUANTITY_EXPIRED_STAGE1+ A.QUANTITY_EXPIRED_STAGE2) AS EXPIRED, VACCINE_CATEGORY.CATEGORY_NAME, VACCINE_MASTER.VACCINE_NAME
FROM ITEM_TRANSACTION AS A, VACCINE_MASTER, STOCK_IDENTIFICATION, VACCINE_CATEGORY, STOCK_CROSS_REFERENCE
WHERE (A.ITEM_ID=STOCK_IDENTIFICATION.ITEM_ID) And (STOCK_IDENTIFICATION.STOCK_REFERENCE_ID=STOCK_CROSS_REFERENCE.STOCK_REFERENCE_ID AND STOCK_CROSS_REFERENCE.STOCK_TYPE = 'V' ) And (STOCK_CROSS_REFERENCE.MASTER_ID=VACCINE_MASTER.VACCINE_ID) And (VACCINE_MASTER.VACCINE_PRIME_CATEGORY=VACCINE_CATEGORY.CATEGORY_ID) AND ( A.TRANSACTION_DATE > [SDT] AND A.TRANSACTION_DATE <= [EDT]));


The view is fine in MS Access and fetches the correct records. Now i have to design a report with this view. For that I opened this view as stored procedure through odbc in crystal reports. In the data explorer window, i need to specify the values for these parameters. As the parameters are of Date type, I tried with the format yyyy-mm-dd,dd-mm-yyyy,mm-dd-yyyy for both sdt and edt. But the stored procedures not added to the report and an error is generated.

The message as follows:

ODBC error: [Microsoft][ODBC Microsoft Access Driver] Invalid SQL data type.

Please specify the correct format for the parameter or any other alternate solution.

Thank you

[mhz_orcl]

Try This way...

Use # before and after date as

.....Between #1-jan-05# And #30-Dec-05#............

[ARPRINCE]

Urgent Help needed,Please

to specify the values for these parameters. As the parameters are of Date type, I tried with the format yyyy-mm-dd,dd-mm-yyyy,mm-dd-yyyy for both sdt and edt. But the stored procedures not added to the report and an error is generated.

The message as follows:

ODBC error: [Microsoft][ODBC Microsoft Access Driver] Invalid SQL data type.

Please specify the correct format for the parameter or any other alternate solution.

Thank you

Have you tried passing "mm/dd/yyyy" as the date format?

[rvejaya_chitra]

Have you tried passing "mm/dd/yyyy" as the date format?

I tried both the format,but it didn't work. I tried with another stored procedure where the parameter is of type string of 1 character length. I tried with values like 'C',"D",['C'],["C"],C,[C]. for every input the same error i am getting. none is working.

I solved the current problem with another view where no parameter is required. but anyway i want to know how to sent the date parameter of stored procedure in crystal report.

Thank you so much