gigifarrow
asked on
Syntax error (missing operator) in query expression 'production ='.
Hello Experts!
I have a issue with some code in a Module. When you type a Production Number int the code I get the following error: "Syntax error (missing operator) in query expression 'production ='."
The code is pointing at my Module:
Option Compare Database
Function tpctf(p) As String
Dim strSQL As String
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
strSQL2 = "Select formula from qryFormulaF where production = " & p
Set rs2 = CurrentDb.OpenRecordset(st rSQL2)
rs2.MoveFirst
f = rs2!formula
strSQL = "select " & f & " as pct from tblFayetteBASSDOnly where production = " & p
Set rs = CurrentDb.OpenRecordset(st rSQL)
rs.MoveFirst
tpctf = rs!pct
rs.Close
rs2.Close
End Function
I checked to see if I had the tables or querys spelled wrong. I have the same code for another form and it works fine.
I have a issue with some code in a Module. When you type a Production Number int the code I get the following error: "Syntax error (missing operator) in query expression 'production ='."
The code is pointing at my Module:
Option Compare Database
Function tpctf(p) As String
Dim strSQL As String
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
strSQL2 = "Select formula from qryFormulaF where production = " & p
Set rs2 = CurrentDb.OpenRecordset(st
rs2.MoveFirst
f = rs2!formula
strSQL = "select " & f & " as pct from tblFayetteBASSDOnly where production = " & p
Set rs = CurrentDb.OpenRecordset(st
rs.MoveFirst
tpctf = rs!pct
rs.Close
rs2.Close
End Function
I checked to see if I had the tables or querys spelled wrong. I have the same code for another form and it works fine.
>Function tpctf(p) As String
Since p is not defined, Access will interpret it as a Variant, which could be anything, which opens the door for downstream code that uses p to fail if it's not the correct type.
Guessing Miriam is correct in that it's a string, so try this...
Along the same lines f is not defined either, so if it's anythign other than a string it will throw an error in the above SELECT statement.
>a Production Number int the code
Does this mean 'in the code' or 'integer, the code...'?
Since p is not defined, Access will interpret it as a Variant, which could be anything, which opens the door for downstream code that uses p to fail if it's not the correct type.
Guessing Miriam is correct in that it's a string, so try this...
strSQL = "select " & f & " as pct from tblFayetteBASSDOnly where production = '" & p & "'"
and this..Function tpctf (p as String) as String
>f = rs2!formulaAlong the same lines f is not defined either, so if it's anythign other than a string it will throw an error in the above SELECT statement.
>a Production Number int the code
Does this mean 'in the code' or 'integer, the code...'?
ASKER
Here is a example sorry I forgot to attach it. Production datatype is Number
HelpwithError3075.zip
HelpwithError3075.zip
ASKER
I tried this Function tpctf (p as String) as String I get "no Current record error 3021"
Then I also change to this :
strSQL = "select " & f & " as pct from tblFayetteBASSDOnly where production = '" & p & "'"
I get a error message "Data type Mismatch in criteria error 3464"
Then I also change to this :
strSQL = "select " & f & " as pct from tblFayetteBASSDOnly where production = '" & p & "'"
I get a error message "Data type Mismatch in criteria error 3464"
What code are you using to call the function?
ASKER
This is what I have now. My Production datatype in my table is Number:
Function tpctf(p As String) As String
Dim strSQL As String
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
strSQL2 = "Select formula from qryFormulaF where production = " & p
Set rs2 = CurrentDb.OpenRecordset(st rSQL2)
rs2.MoveFirst
f = rs2!formula
strSQL = "select " & f & " as pct from tblFayetteBASSDOnly where production = '" & p & "'"
Set rs = CurrentDb.OpenRecordset(st rSQL)
rs.MoveFirst
tpctf = rs!pct
rs.Close
rs2.Close
End Function
I have this same exact code in another table for another form and I have no problem
Function tpctf(p As String) As String
Dim strSQL As String
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
strSQL2 = "Select formula from qryFormulaF where production = " & p
Set rs2 = CurrentDb.OpenRecordset(st
rs2.MoveFirst
f = rs2!formula
strSQL = "select " & f & " as pct from tblFayetteBASSDOnly where production = '" & p & "'"
Set rs = CurrentDb.OpenRecordset(st
rs.MoveFirst
tpctf = rs!pct
rs.Close
rs2.Close
End Function
I have this same exact code in another table for another form and I have no problem
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your help.
My approach was worse. I got this code from one of the experts. Which I felt was sufficient.
You say that it is processing over 360 times how do I stop this?
I hit control G and I dont see were it says it processed 350 times
My approach was worse. I got this code from one of the experts. Which I felt was sufficient.
You say that it is processing over 360 times how do I stop this?
I hit control G and I dont see were it says it processed 350 times
When you click CTRL-G, you should see the a series of lines similar to this:
Pass350
Pass351
Pass352
What is the last Pass you see at the bottom of the list?
Pass351
Pass352
ASKER
When I click Contor G I still dont see anything. I am looking where it says Immediate.
But how do I fix it?
Why am I getting that error?
"Syntax error (missing operator) in query expression 'production ='."
But how do I fix it?
Why am I getting that error?
"Syntax error (missing operator) in query expression 'production ='."
You didn't remove this portion in the code I gave you, right?
'added for testing
Static cntr As Long
cntr = cntr + 1
Debug.Print "Pass" & cntr & " " & p
If you did, then you won't see the what I'm talking about.
ASKER
I changed the code hit control G and still not seeing anything. However, Im not getting that error message any more.
It has to have something in the Immediate window. Can you upload your database with the changes?
ASKER
Here is a example of my database. Thanks for your time and help .
HelpwithFunction.accdb
HelpwithFunction.accdb
ASKER
I dont understand what you did could you please explain? By the way couldnt get nothing with the Conrol G
I don't understand how you're not seeing this. When you open the form frmFayetteBASSDOnly, does it not say "Calculating..." in the Status bar for more than half a minute? Once it's done and you click on CTRL-G, you'll see the Immediate window like the screenshot below which showed that it made 5400 passes.
Now if you remove the calculated textboxes at the top of your form for "Vehicle Complete", "Equivalent Complete", and "% Complete", and reopen your form, you'll see that Status bar doesn't stay on "Calculating..." as long as it previously did. And if you go to the Immediate window, you'll see no change.
Now if you remove the calculated textboxes at the top of your form for "Vehicle Complete", "Equivalent Complete", and "% Complete", and reopen your form, you'll see that Status bar doesn't stay on "Calculating..." as long as it previously did. And if you go to the Immediate window, you'll see no change.
ASKER
Not working I waited until it stoped calculating. then hit contorl g. I then tried it when it was calculating and hit control g. Nothing.
So How do I get it to quit calculating?
How do I get rid of this error
Syntax error (missing operator) in query expression 'production ='
So How do I get it to quit calculating?
How do I get rid of this error
Syntax error (missing operator) in query expression 'production ='
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
also, make sure you app compiles as is.
If your still not seeing stuff in the debug window with Ron's suggested change, then I would consider doing a /decompile to make sure things are executing as they should.
Jim.
If your still not seeing stuff in the debug window with Ron's suggested change, then I would consider doing a /decompile to make sure things are executing as they should.
Jim.
Also note that I've basically summerized what the others have mentioned already, so no points here please.
I just thought I'd toss in the line number trick to help the process along and I will leave you with them.
Jim.
I just thought I'd toss in the line number trick to help the process along and I will leave you with them.
Jim.
You're saying, that you're still getting this error?
Syntax error (missing operator) in query expression 'production ='
What steps are you taking to get this? I don't see that error when I change the production number in the datasheet. Where are you changing the number?
If Text, use this:
Open in new window