ADO SQL text (if statement)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to list all the MIS request calls submitted by either regular
employees (those with employee id) or consultants to be use in an email body
text. I tried converting the 'if' logic below to get the user who placed the
request as one of the select field in the sql text using 'case' statement
(see SQLAssigned_Text) but am getting an error. If I take out the case
statement, it runs fine. Employee_Id is a text field. What is the problem?
TIA.

Iif([Employee_Id]>"0",[User_Name], [Consultant_Name] as [User_Name]


SQLAssigned_Text = "Select Str(CALL_USERS!Call_Id) AS Call_Id,
Call_Category.Call_Desc," _
&" (CASE WHEN Employee_Id > '0' THEN Employee_Name ELSE Consultant_Name
END) AS USER_NAME" _
& " FROM Call_Category INNER JOIN ......
 
Is this a JET or a SQL Server database? And how are you executing it? Are
you calling it from an MDB or ADP front-end?

As far as I can see, your SQL should work against a SQL Server database,
executed via ADO or as a pass-through query. The following test worked for
me in an ADP ...

SELECT (CASE WHEN EmployeeID > '0' THEN EmployeeName ELSE ConsultantName
END) AS Expr1
FROM dbo.test

.... but it certainly will not work against JET, which does not support
'CASE'.
 
Please ignore my first post, I tried your case statement and it works fine on
an SQL server.

Where are you trying to run this statement?
What kind of an error message are you getting?



Ofer said:
Try this

SQLAssigned_Text = "Select Str(CALL_USERS!Call_Id) AS Call_Id,
Call_Category.Call_Desc," _
&" (CASE Employee_Id WHEN > '0' THEN Employee_Name ELSE Consultant_Name
END) AS USER_NAME" _
& " FROM Call_Category INNER JOIN

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


danlin99 said:
I'm trying to list all the MIS request calls submitted by either regular
employees (those with employee id) or consultants to be use in an email body
text. I tried converting the 'if' logic below to get the user who placed the
request as one of the select field in the sql text using 'case' statement
(see SQLAssigned_Text) but am getting an error. If I take out the case
statement, it runs fine. Employee_Id is a text field. What is the problem?
TIA.

Iif([Employee_Id]>"0",[User_Name], [Consultant_Name] as [User_Name]


SQLAssigned_Text = "Select Str(CALL_USERS!Call_Id) AS Call_Id,
Call_Category.Call_Desc," _
&" (CASE WHEN Employee_Id > '0' THEN Employee_Name ELSE Consultant_Name
END) AS USER_NAME" _
& " FROM Call_Category INNER JOIN ......
Click to expand...
Click to expand...
 
I'm getting the error 'Method execute of _object command failed.'

Ofer said:
Please ignore my first post, I tried your case statement and it works fine on
an SQL server.

Where are you trying to run this statement?
What kind of an error message are you getting?



Ofer said:
Try this

SQLAssigned_Text = "Select Str(CALL_USERS!Call_Id) AS Call_Id,
Call_Category.Call_Desc," _
&" (CASE Employee_Id WHEN > '0' THEN Employee_Name ELSE Consultant_Name
END) AS USER_NAME" _
& " FROM Call_Category INNER JOIN

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


danlin99 said:
I'm trying to list all the MIS request calls submitted by either regular
employees (those with employee id) or consultants to be use in an email body
text. I tried converting the 'if' logic below to get the user who placed the
request as one of the select field in the sql text using 'case' statement
(see SQLAssigned_Text) but am getting an error. If I take out the case
statement, it runs fine. Employee_Id is a text field. What is the problem?
TIA.

Iif([Employee_Id]>"0",[User_Name], [Consultant_Name] as [User_Name]


SQLAssigned_Text = "Select Str(CALL_USERS!Call_Id) AS Call_Id,
Call_Category.Call_Desc," _
&" (CASE WHEN Employee_Id > '0' THEN Employee_Name ELSE Consultant_Name
END) AS USER_NAME" _
& " FROM Call_Category INNER JOIN ......
Click to expand...
Click to expand...
Click to expand...
 
Hi-

They are Access tables. I'm not planning to convert it to ADP because
there's a plan to develop a web based application. Is there a work around
creating this recordset with the if or case statement?

Brendan Reynolds said:
Is this a JET or a SQL Server database? And how are you executing it? Are
you calling it from an MDB or ADP front-end?

As far as I can see, your SQL should work against a SQL Server database,
executed via ADO or as a pass-through query. The following test worked for
me in an ADP ...

SELECT (CASE WHEN EmployeeID > '0' THEN EmployeeName ELSE ConsultantName
END) AS Expr1
FROM dbo.test

.... but it certainly will not work against JET, which does not support
'CASE'.

--
Brendan Reynolds

danlin99 said:
I'm trying to list all the MIS request calls submitted by either regular
employees (those with employee id) or consultants to be use in an email
body
text. I tried converting the 'if' logic below to get the user who placed
the
request as one of the select field in the sql text using 'case' statement
(see SQLAssigned_Text) but am getting an error. If I take out the case
statement, it runs fine. Employee_Id is a text field. What is the
problem?
TIA.

Iif([Employee_Id]>"0",[User_Name], [Consultant_Name] as [User_Name]


SQLAssigned_Text = "Select Str(CALL_USERS!Call_Id) AS Call_Id,
Call_Category.Call_Desc," _
&" (CASE WHEN Employee_Id > '0' THEN Employee_Name ELSE Consultant_Name
END) AS USER_NAME" _
& " FROM Call_Category INNER JOIN ......
Click to expand...
Click to expand...
 
Try this

SQLAssigned_Text = "Select Str(CALL_USERS!Call_Id) AS Call_Id,
Call_Category.Call_Desc," _
&" (CASE Employee_Id WHEN > '0' THEN Employee_Name ELSE Consultant_Name
END) AS USER_NAME" _
& " FROM Call_Category INNER JOIN
 
If you are using Access tables then use iif instead of case.

SQLAssigned_Text = "Select Str(CALL_USERS!Call_Id) AS Call_Id,
Call_Category.Call_Desc," _
&" IIf(Employee_Id > '0' , Employee_Name , Consultant_Name) AS USER_NAME " _
& " FROM Call_Category INNER JOIN


--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


danlin99 said:
I'm getting the error 'Method execute of _object command failed.'

Ofer said:
Please ignore my first post, I tried your case statement and it works fine on
an SQL server.

Where are you trying to run this statement?
What kind of an error message are you getting?



Ofer said:
Try this

SQLAssigned_Text = "Select Str(CALL_USERS!Call_Id) AS Call_Id,
Call_Category.Call_Desc," _
&" (CASE Employee_Id WHEN > '0' THEN Employee_Name ELSE Consultant_Name
END) AS USER_NAME" _
& " FROM Call_Category INNER JOIN

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


:

I'm trying to list all the MIS request calls submitted by either regular
employees (those with employee id) or consultants to be use in an email body
text. I tried converting the 'if' logic below to get the user who placed the
request as one of the select field in the sql text using 'case' statement
(see SQLAssigned_Text) but am getting an error. If I take out the case
statement, it runs fine. Employee_Id is a text field. What is the problem?
TIA.

Iif([Employee_Id]>"0",[User_Name], [Consultant_Name] as [User_Name]


SQLAssigned_Text = "Select Str(CALL_USERS!Call_Id) AS Call_Id,
Call_Category.Call_Desc," _
&" (CASE WHEN Employee_Id > '0' THEN Employee_Name ELSE Consultant_Name
END) AS USER_NAME" _
& " FROM Call_Category INNER JOIN ......
Click to expand...
Click to expand...
Click to expand...
 
If using a JET database, then the IIf example you gave in your first post is
almost correct ...

Iif([Employee_Id]>"0",[User_Name], [Consultant_Name] as [User_Name]

.... the only thing that is wrong with this (as far as I can see) is that it
is missing the closing parenthesis after "[Consultant_Name]"

--
Brendan Reynolds

danlin99 said:
Hi-

They are Access tables. I'm not planning to convert it to ADP because
there's a plan to develop a web based application. Is there a work around
creating this recordset with the if or case statement?

Brendan Reynolds said:
Is this a JET or a SQL Server database? And how are you executing it? Are
you calling it from an MDB or ADP front-end?

As far as I can see, your SQL should work against a SQL Server database,
executed via ADO or as a pass-through query. The following test worked
for
me in an ADP ...

SELECT (CASE WHEN EmployeeID > '0' THEN EmployeeName ELSE
ConsultantName
END) AS Expr1
FROM dbo.test

.... but it certainly will not work against JET, which does not support
'CASE'.

--
Brendan Reynolds

danlin99 said:
I'm trying to list all the MIS request calls submitted by either
regular
employees (those with employee id) or consultants to be use in an email
body
text. I tried converting the 'if' logic below to get the user who
placed
the
request as one of the select field in the sql text using 'case'
statement
(see SQLAssigned_Text) but am getting an error. If I take out the case
statement, it runs fine. Employee_Id is a text field. What is the
problem?
TIA.

Iif([Employee_Id]>"0",[User_Name], [Consultant_Name] as [User_Name]


SQLAssigned_Text = "Select Str(CALL_USERS!Call_Id) AS Call_Id,
Call_Category.Call_Desc," _
&" (CASE WHEN Employee_Id > '0' THEN Employee_Name ELSE
Consultant_Name
END) AS USER_NAME" _
& " FROM Call_Category INNER JOIN ......
Click to expand...
Click to expand...
Click to expand...
 
Hi Brendan,

& " Iif([Employee_Id]>"0",[User_Name], [Consultant_Name]) as [User_Name]" _


I tried it but I'm getting an error, 'expected end of statement' right where
the cursor is at >0". I think it's because of the enclosed double quotes
around the zero. How do I fix the expression.

Brendan Reynolds said:
If using a JET database, then the IIf example you gave in your first post is
almost correct ...

Iif([Employee_Id]>"0",[User_Name], [Consultant_Name] as [User_Name]

.... the only thing that is wrong with this (as far as I can see) is that it
is missing the closing parenthesis after "[Consultant_Name]"

--
Brendan Reynolds

danlin99 said:
Hi-

They are Access tables. I'm not planning to convert it to ADP because
there's a plan to develop a web based application. Is there a work around
creating this recordset with the if or case statement?

Brendan Reynolds said:
Is this a JET or a SQL Server database? And how are you executing it? Are
you calling it from an MDB or ADP front-end?

As far as I can see, your SQL should work against a SQL Server database,
executed via ADO or as a pass-through query. The following test worked
for
me in an ADP ...

SELECT (CASE WHEN EmployeeID > '0' THEN EmployeeName ELSE
ConsultantName
END) AS Expr1
FROM dbo.test

.... but it certainly will not work against JET, which does not support
'CASE'.

--
Brendan Reynolds

I'm trying to list all the MIS request calls submitted by either
regular
employees (those with employee id) or consultants to be use in an email
body
text. I tried converting the 'if' logic below to get the user who
placed
the
request as one of the select field in the sql text using 'case'
statement
(see SQLAssigned_Text) but am getting an error. If I take out the case
statement, it runs fine. Employee_Id is a text field. What is the
problem?
TIA.

Iif([Employee_Id]>"0",[User_Name], [Consultant_Name] as [User_Name]


SQLAssigned_Text = "Select Str(CALL_USERS!Call_Id) AS Call_Id,
Call_Category.Call_Desc," _
&" (CASE WHEN Employee_Id > '0' THEN Employee_Name ELSE
Consultant_Name
END) AS USER_NAME" _
& " FROM Call_Category INNER JOIN ......
Click to expand...
Click to expand...
Click to expand...
 
The double quote would definitly prompt you with an error, change it to
single quote

Iif([Employee_Id]>'0',[User_Name], [Consultant_Name]) as [User_Name]"

As you can see in the example I gave you in the previous post


danlin99 said:
Hi Brendan,

& " Iif([Employee_Id]>"0",[User_Name], [Consultant_Name]) as [User_Name]" _


I tried it but I'm getting an error, 'expected end of statement' right where
the cursor is at >0". I think it's because of the enclosed double quotes
around the zero. How do I fix the expression.

Brendan Reynolds said:
If using a JET database, then the IIf example you gave in your first post is
almost correct ...

Iif([Employee_Id]>"0",[User_Name], [Consultant_Name] as [User_Name]

.... the only thing that is wrong with this (as far as I can see) is that it
is missing the closing parenthesis after "[Consultant_Name]"

--
Brendan Reynolds

danlin99 said:
Hi-

They are Access tables. I'm not planning to convert it to ADP because
there's a plan to develop a web based application. Is there a work around
creating this recordset with the if or case statement?

:

Is this a JET or a SQL Server database? And how are you executing it? Are
you calling it from an MDB or ADP front-end?

As far as I can see, your SQL should work against a SQL Server database,
executed via ADO or as a pass-through query. The following test worked
for
me in an ADP ...

SELECT (CASE WHEN EmployeeID > '0' THEN EmployeeName ELSE
ConsultantName
END) AS Expr1
FROM dbo.test

.... but it certainly will not work against JET, which does not support
'CASE'.

--
Brendan Reynolds

I'm trying to list all the MIS request calls submitted by either
regular
employees (those with employee id) or consultants to be use in an email
body
text. I tried converting the 'if' logic below to get the user who
placed
the
request as one of the select field in the sql text using 'case'
statement
(see SQLAssigned_Text) but am getting an error. If I take out the case
statement, it runs fine. Employee_Id is a text field. What is the
problem?
TIA.

Iif([Employee_Id]>"0",[User_Name], [Consultant_Name] as [User_Name]


SQLAssigned_Text = "Select Str(CALL_USERS!Call_Id) AS Call_Id,
Call_Category.Call_Desc," _
&" (CASE WHEN Employee_Id > '0' THEN Employee_Name ELSE
Consultant_Name
END) AS USER_NAME" _
& " FROM Call_Category INNER JOIN ......
Click to expand...
Click to expand...
Click to expand...
 
JET SQL will accept either single or double quotes, it's VBA that is getting
confused between the literal quotes within the SQL string and the quotes
that delimit the string in VBA. The simplest solution is to use single
quotes, as Ofer suggests.

Alternatives include doubling up the quotes ...

strSQL = "some string" & " Iif([Employee_Id]>""0"",[User_Name],
[Consultant_Name]) as [User_Name]"

.... or using the Chr$() function ...

strSQL = "some string" & " Iif([Employee_Id]>" & Chr$(34) & "0" &
Chr$(34) & ",[User_Name], [Consultant_Name]) as [User_Name]"

--
Brendan Reynolds

danlin99 said:
Hi Brendan,

& " Iif([Employee_Id]>"0",[User_Name], [Consultant_Name]) as [User_Name]"
_


I tried it but I'm getting an error, 'expected end of statement' right
where
the cursor is at >0". I think it's because of the enclosed double quotes
around the zero. How do I fix the expression.

Brendan Reynolds said:
If using a JET database, then the IIf example you gave in your first post
is
almost correct ...

Iif([Employee_Id]>"0",[User_Name], [Consultant_Name] as [User_Name]

.... the only thing that is wrong with this (as far as I can see) is that
it
is missing the closing parenthesis after "[Consultant_Name]"

--
Brendan Reynolds

danlin99 said:
Hi-

They are Access tables. I'm not planning to convert it to ADP because
there's a plan to develop a web based application. Is there a work
around
creating this recordset with the if or case statement?

:

Is this a JET or a SQL Server database? And how are you executing it?
Are
you calling it from an MDB or ADP front-end?

As far as I can see, your SQL should work against a SQL Server
database,
executed via ADO or as a pass-through query. The following test worked
for
me in an ADP ...

SELECT (CASE WHEN EmployeeID > '0' THEN EmployeeName ELSE
ConsultantName
END) AS Expr1
FROM dbo.test

.... but it certainly will not work against JET, which does not
support
'CASE'.

--
Brendan Reynolds

I'm trying to list all the MIS request calls submitted by either
regular
employees (those with employee id) or consultants to be use in an
email
body
text. I tried converting the 'if' logic below to get the user who
placed
the
request as one of the select field in the sql text using 'case'
statement
(see SQLAssigned_Text) but am getting an error. If I take out the
case
statement, it runs fine. Employee_Id is a text field. What is the
problem?
TIA.

Iif([Employee_Id]>"0",[User_Name], [Consultant_Name] as [User_Name]


SQLAssigned_Text = "Select Str(CALL_USERS!Call_Id) AS Call_Id,
Call_Category.Call_Desc," _
&" (CASE WHEN Employee_Id > '0' THEN Employee_Name ELSE
Consultant_Name
END) AS USER_NAME" _
& " FROM Call_Category INNER JOIN ......
Click to expand...
Click to expand...
Click to expand...
 
Back
Top