Recordset Help 1

[random75]

I am opening a recordset based on a match of the BatchID field between a form and a query. It works fine until I add the Expr1 < 0 condition, with the error indicating I need to define a variable for Expr1. I suspect this might also be a syntax issue. Expr1 is a calculated value in the query. Here is the code. I can declare the variable if that is the issue, but I wanted to know if a modification to my recordset statement would also solve the problem. Thanks!

Set rs = CurDB.OpenRecordset("SELECT * from UnprocessedBatchNegativeQuantityOnHandQuery WHERE" & _
" BatchID =" & Me.BatchID And Expr1 < 0)

 

[HarleyQuinn]

How about:

Set rs = CurDB.OpenRecordset("SELECT * from UnprocessedBatchNegativeQuantityOnHandQuery WHERE" & _
" BatchID =" & Me.BatchID & " And Expr1 < 0")

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[random75]

Thanks Harley:

I don't suppose you could give me a real quick explanation for the & "____" format that eliminated the problem.

 

[HarleyQuinn]

Thanks

No problem, basically with strings, anything not in double quotes is assumed to be something that the application has to populate with data it has (Me.BatchID is an example). As your app doesn't have Expr1 declared it doesn't know what it is. As I included it in "" it takes it to be a string literal and therefore doesn't have to try and give it a value and just adds it to the string (using the & as the character to mean 'add the next part to this string').

Hope that makes sense

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[random75]

Thanks Harley, very helpful!