missing operand?

S

Steven

I get a missing operand message with the following:


Call retValues(vTestCo, CurrentBatch & " LEFT JOIN Co ON (" & CurrentBatch &
".Co = Co.Co) LEFT JOIN ChartOfAccounts ON ((" & CurrentBatch & ".Co =
ChartOfAccounts.Co) AND (" & CurrentBatch & ".Account =
ChartOfAccounts.Account))", "Nz([Co].[Status],0)=0")

What is wrong?

Thank you,

Steven
 
P

pietlinden

I get a missing operand message with the following:

Call retValues(vTestCo, CurrentBatch & " LEFT JOIN Co ON (" & CurrentBatch &
".Co = Co.Co) LEFT JOIN ChartOfAccounts ON ((" & CurrentBatch & ".Co =
ChartOfAccounts.Co) AND (" & CurrentBatch & ".Account =
ChartOfAccounts.Account))", "Nz([Co].[Status],0)=0")

What is wrong?

Thank you,

Steven
Click to expand...

include a line in your code to print the SQL statement to the debug
window. Then copy and paste it into a blank query and run it. Does
it work?
What does the signature of retValues look like?
 
S

Steven

Thank you for responding.
Here is retValues function and here is what the strSql will be in the
function.

strSQL will be :
SELECT Count(*) As vCount FROM Batch09 LEFT JOIN Co ON (Batch09.Co = Co.Co)
LEFT JOIN ChartOfAccounts ON ((Batch09.Co = ChartOfAccounts.Co) AND
(Batch09.Account = ChartOfAccounts.Account)) WHERE Nz([Co].[Status],0)=0


On the: Set rs = CurrentDb.OpenRecordset(strSQL) 'It errors here saying
missing operand.

Is it because it has more than one LEFT JOIN ?

--------------------------------------------------------------------------
Public Function retValues(ByRef vTestCo, TableName As String, WhereClause As
String) As String
On Error GoTo Macro11_Err
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Count(*) As vCount " & _
"FROM " & TableName & " " & _
"WHERE " & WhereClause

Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveFirst
vTestCo = Nz(rs!vCount, 0)

MsgBox vTestCo

rs.Close
Set rs = Nothing

Macro11_Exit:
Exit Function

Macro11_Err:
MsgBox Error$
Resume Macro11_Exit

End Function


Thank you for your help in solving this.

Steven


[email protected] said:
I get a missing operand message with the following:

Call retValues(vTestCo, CurrentBatch & " LEFT JOIN Co ON (" & CurrentBatch &
".Co = Co.Co) LEFT JOIN ChartOfAccounts ON ((" & CurrentBatch & ".Co =
ChartOfAccounts.Co) AND (" & CurrentBatch & ".Account =
ChartOfAccounts.Account))", "Nz([Co].[Status],0)=0")

What is wrong?

Thank you,

Steven
Click to expand...

include a line in your code to print the SQL statement to the debug
window. Then copy and paste it into a blank query and run it. Does
it work?
What does the signature of retValues look like?
Click to expand...
 
S

Steven

Now I see. It took me a minute to get what you are doing. I really did not
get it but it made me think. I already had created a query so then I just
took the hardcode SQL out of the query and put it into this code and it
worked then I just changed the Batch09 to the variable and it worked also.
Thank you. I have an additional related question that I am creating another
question.

Thanks again.....

Steven said:
Thank you for responding.
Here is retValues function and here is what the strSql will be in the
function.

strSQL will be :
SELECT Count(*) As vCount FROM Batch09 LEFT JOIN Co ON (Batch09.Co = Co.Co)
LEFT JOIN ChartOfAccounts ON ((Batch09.Co = ChartOfAccounts.Co) AND
(Batch09.Account = ChartOfAccounts.Account)) WHERE Nz([Co].[Status],0)=0


On the: Set rs = CurrentDb.OpenRecordset(strSQL) 'It errors here saying
missing operand.

Is it because it has more than one LEFT JOIN ?

--------------------------------------------------------------------------
Public Function retValues(ByRef vTestCo, TableName As String, WhereClause As
String) As String
On Error GoTo Macro11_Err
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Count(*) As vCount " & _
"FROM " & TableName & " " & _
"WHERE " & WhereClause

Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveFirst
vTestCo = Nz(rs!vCount, 0)

MsgBox vTestCo

rs.Close
Set rs = Nothing

Macro11_Exit:
Exit Function

Macro11_Err:
MsgBox Error$
Resume Macro11_Exit

End Function


Thank you for your help in solving this.

Steven


[email protected] said:
I get a missing operand message with the following:

Call retValues(vTestCo, CurrentBatch & " LEFT JOIN Co ON (" & CurrentBatch &
".Co = Co.Co) LEFT JOIN ChartOfAccounts ON ((" & CurrentBatch & ".Co =
ChartOfAccounts.Co) AND (" & CurrentBatch & ".Account =
ChartOfAccounts.Account))", "Nz([Co].[Status],0)=0")

What is wrong?

Thank you,

Steven
Click to expand...

include a line in your code to print the SQL statement to the debug
window. Then copy and paste it into a blank query and run it. Does
it work?
What does the signature of retValues look like?
Click to expand...
Click to expand...
 
P

pietlinden

Now I see.  It took me a minute to get what you are doing.  I really did not
get it but it made me think.  I already had created a query so then I just
took the hardcode SQL out of the query and put it into this code and it
worked then I just changed the Batch09 to the variable and it worked also..  
Thank you.  I have an additional related question that I am creating another
question.
Click to expand...
When I had to write really screwy queries where I modified some field
names and table names, I did something like this:

1. create a Constant variable with my template SQL statement.
2. Used Replace() several times in my function to replace placeholder
object names with real object names. (tables, fields, expressions,
whatever!)
3. returned the result as a string and created a temporary querydef
based on it. Then I can pass in parameters when I call the function
to build the SQL and then I can pass query parameters when I execute
the SQL statement/query/stored procedure... Just a LOT easier to
maintain and debug that way.

HTH,
Pieter
 
S

Steven

Definitely, Thank you.

[email protected] said:
When I had to write really screwy queries where I modified some field
names and table names, I did something like this:

1. create a Constant variable with my template SQL statement.
2. Used Replace() several times in my function to replace placeholder
object names with real object names. (tables, fields, expressions,
whatever!)
3. returned the result as a string and created a temporary querydef
based on it. Then I can pass in parameters when I call the function
to build the SQL and then I can pass query parameters when I execute
the SQL statement/query/stored procedure... Just a LOT easier to
maintain and debug that way.

HTH,
Pieter
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sqlStmt problem 1
returning more than one variable on Call retValue() 4
Export using strSQL 2
Returning values from a RecordSet 3
Using SQL vs Query Grid 2
Can You Figure Out What's Wrong W/ This Query 4
Loading libraries to other comps 2
IIF Condition 5

Top