Forum Discussion
MS Access SQL - No Current Record Error
Hi I am getting this error when I run a query below query: SELECT *, (SELECT STDEV(LOG_PRICE_CHANGE) FROM QRY_LOG_PRICE_CHANGES AS T1 WHERE T1.UNDERLYING_SYMBOL = QRY_LOG_PRICE_CHANGES.UNDER...
Sorry about that confusion.
No, that subquery will not run. It is saying "QRY_LOG_PRICE_CHANGES.UNDERLYING_SYMBOL" is a parameter and wants a value.
My settings are the same as yours.
No, that subquery will not run. It is saying "QRY_LOG_PRICE_CHANGES.UNDERLYING_SYMBOL" is a parameter and wants a value.
My settings are the same as yours.
OK. You need to ensure that works as a standalone query before you can use it as a subquery.
Try inserting a value that you know exists and then see if it works as standalone / subquery.
If so, you need to find a way of adding the parameter to the query
Try inserting a value that you know exists and then see if it works as standalone / subquery.
If so, you need to find a way of adding the parameter to the query
- a few things...
1. i re-ran the subquery and put in random values for the two parameters. it ran without any errors but the value it produced was NULL.
2. secondly, that subquery you mentioned definitely does work. one of the things i tried when I was troubleshooting this was try the below query:
SELECT T2.UNDERLYING_SYMBOL, T2.QUOTE_DATE, (SELECT STDEV(LOG_PRICE_CHANGE)
FROM TBL_LOG_PRICE_CHANGES AS T1
WHERE T1.UNDERLYING_SYMBOL = T2.UNDERLYING_SYMBOL AND
T2.ROW_NUMBER-T1.ROW_NUMBER BETWEEN 0 AND 20) AS STD_DEV_21
FROM TBL_LOG_PRICE_CHANGES AS T2;
noticed that TBL_LOG_PRICE_CHANGES is used and not QRY_LOG_PRICE_CHANGES
QRY_LOG_PRICE_CHANGES runs fine on its own
for some unknown reason, the query doesn't like it when qry is used in the FROM clause- I ran the query again and now I am getting a different error. I am getting "Invalid value to function"
SELECT T2.UNDERLYING_SYMBOL, T2.QUOTE_DATE, (SELECT STDEV(LOG_PRICE_CHANGE)
FROM TBL_LOG_PRICE_CHANGES AS T1
WHERE T1.UNDERLYING_SYMBOL = T2.UNDERLYING_SYMBOL AND
T2.ROW_NUMBER-T1.ROW_NUMBER BETWEEN 0 AND 20) AS STD_DEV_21
FROM QRY_LOG_PRICE_CHANGES AS T2;
If I run the below query then no issues:
SELECT T2.UNDERLYING_SYMBOL, T2.QUOTE_DATE, (SELECT STDEV(LOG_PRICE_CHANGE)
FROM TBL_LOG_PRICE_CHANGES AS T1
WHERE T1.UNDERLYING_SYMBOL = T2.UNDERLYING_SYMBOL AND
T2.ROW_NUMBER-T1.ROW_NUMBER BETWEEN 0 AND 20) AS STD_DEV_21
FROM TBL_LOG_PRICE_CHANGES AS T2;
Only difference is the FROM part. The first is referencing a query and the second is referencing a table
Any idea on what is going on here? So strange...- Its difficult to know based on the info you've supplied.
Can you upload a cut down version of your database with all relevant items?
Remove or change any confidential data