SQL syntax

G

Guest

I'm trying to use a SQL statement that I cut and pasted from a working query.
I want to use it in code, but I can't get the syntax to stop erroring...I'm
going crazy with it. Here it is

SELECT IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)) AS FNAME, [morecells].[LAST NAME],
[morecells].[PHONE NUMBER], IIf(InStr([FIRST NAME]," ")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST NAME] &
"@Mycompany.net" AS Email
FROM morecells
GROUP BY IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)), [morecells].[LAST NAME],
[morecells].[PHONE NUMBER], IIf(InStr([FIRST NAME]," ")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST NAME] &
"@Mycompany.net"
ORDER BY [morecells].[LAST NAME];
 
A

Allen Browne

In VBA code, the entire SQL statement has to be contained in quotes. But you
have quote marks inside the string. You need to double them up so VBA
undersands it is not the end of the string.

For example, to code:
This string has a "word" in quotes
you write:
"This string has a ""word"" in quotes"

If the quotes are at the end, you finish up with 3 in a row, i.e.:
"This string has a ""word"""

So:
strSql = "SELECT IIf(InStr([FIRST NAME],"" "")=0, ...
 
G

Guest

OK, I followed the rules you mentioned, getting this

I have a Dim strSQL As String declaration, then

strSQL = "SELECT IIf(InStr([FIRST NAME],"" "")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME],"" "")-1)) AS FNAME,
morecells.[LAST NAME], morecells.[PHONE NUMBER], IIf(InStr([FIRST NAME],""
"")=0,[FIRST NAME],Left([FIRST NAME],InStr([FIRST NAME],"" "")-1)) & ""."" &
[LAST NAME] & ""@Assurant.net"" AS Email FROM morecells GROUP BY
IIf(InStr([FIRST NAME],"" "")=0,[FIRST NAME],Left([FIRST NAME],InStr([FIRST
NAME],"" "")-1)), morecells.[LAST NAME], morecells.[PHONE NUMBER],
IIf(InStr([FIRST NAME],"" "")=0,[FIRST NAME],Left([FIRST NAME],InStr([FIRST
NAME],"" "")-1)) & ""."" & [LAST NAME] & ""@Assurant.net"" HAVING
(((morecells.[LAST NAME]) Not Like ""Kroll"")) ORDER BY morecells.[LAST
NAME];"

but my DoCmd.RunSQL strSQL statement errors out with Runtime Error 2342,
saying that a RunSQL argument requires an argument consisting of a SQL
statement




Allen Browne said:
In VBA code, the entire SQL statement has to be contained in quotes. But you
have quote marks inside the string. You need to double them up so VBA
undersands it is not the end of the string.

For example, to code:
This string has a "word" in quotes
you write:
"This string has a ""word"" in quotes"

If the quotes are at the end, you finish up with 3 in a row, i.e.:
"This string has a ""word"""

So:
strSql = "SELECT IIf(InStr([FIRST NAME],"" "")=0, ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ray S. said:
I'm trying to use a SQL statement that I cut and pasted from a working
query.
I want to use it in code, but I can't get the syntax to stop
erroring...I'm
going crazy with it. Here it is

SELECT IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)) AS FNAME, [morecells].[LAST NAME],
[morecells].[PHONE NUMBER], IIf(InStr([FIRST NAME]," ")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST NAME] &
"@Mycompany.net" AS Email
FROM morecells
GROUP BY IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)), [morecells].[LAST NAME],
[morecells].[PHONE NUMBER], IIf(InStr([FIRST NAME]," ")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST NAME] &
"@Mycompany.net"
ORDER BY [morecells].[LAST NAME];
Click to expand...
Click to expand...
 
A

Allen Browne

When you have built the string, add the line:
Debug.Print strSql

When it fails, open the Immediate Window (Ctrl+G).
Copy the SQL string, and paste it into SQL View in a new query.
You should then be able to see what is wrong with the result.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ray S. said:
OK, I followed the rules you mentioned, getting this

I have a Dim strSQL As String declaration, then

strSQL = "SELECT IIf(InStr([FIRST NAME],"" "")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME],"" "")-1)) AS FNAME,
morecells.[LAST NAME], morecells.[PHONE NUMBER], IIf(InStr([FIRST NAME],""
"")=0,[FIRST NAME],Left([FIRST NAME],InStr([FIRST NAME],"" "")-1)) & "".""
&
[LAST NAME] & ""@Assurant.net"" AS Email FROM morecells GROUP BY
IIf(InStr([FIRST NAME],"" "")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST
NAME],"" "")-1)), morecells.[LAST NAME], morecells.[PHONE NUMBER],
IIf(InStr([FIRST NAME],"" "")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST
NAME],"" "")-1)) & ""."" & [LAST NAME] & ""@Assurant.net"" HAVING
(((morecells.[LAST NAME]) Not Like ""Kroll"")) ORDER BY morecells.[LAST
NAME];"

but my DoCmd.RunSQL strSQL statement errors out with Runtime Error 2342,
saying that a RunSQL argument requires an argument consisting of a SQL
statement




Allen Browne said:
In VBA code, the entire SQL statement has to be contained in quotes. But
you
have quote marks inside the string. You need to double them up so VBA
undersands it is not the end of the string.

For example, to code:
This string has a "word" in quotes
you write:
"This string has a ""word"" in quotes"

If the quotes are at the end, you finish up with 3 in a row, i.e.:
"This string has a ""word"""

So:
strSql = "SELECT IIf(InStr([FIRST NAME],"" "")=0, ...

Ray S. said:
I'm trying to use a SQL statement that I cut and pasted from a working
query.
I want to use it in code, but I can't get the syntax to stop
erroring...I'm
going crazy with it. Here it is

SELECT IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)) AS FNAME, [morecells].[LAST NAME],
[morecells].[PHONE NUMBER], IIf(InStr([FIRST NAME]," ")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST NAME]
&
"@Mycompany.net" AS Email
FROM morecells
GROUP BY IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)), [morecells].[LAST NAME],
[morecells].[PHONE NUMBER], IIf(InStr([FIRST NAME]," ")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST NAME]
&
"@Mycompany.net"
ORDER BY [morecells].[LAST NAME];
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Great idea, I think someone had suggested that once before and I had
forgotten about it. Anyway, when I put the Debug.Print strSQL, it doesn't err
out. I only get the error when I put DoCmd.RunSQL strSQL. The error is 2342,
a RunSQL command requires an argument consisting of a SQL statement.

Allen Browne said:
When you have built the string, add the line:
Debug.Print strSql

When it fails, open the Immediate Window (Ctrl+G).
Copy the SQL string, and paste it into SQL View in a new query.
You should then be able to see what is wrong with the result.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ray S. said:
OK, I followed the rules you mentioned, getting this

I have a Dim strSQL As String declaration, then

strSQL = "SELECT IIf(InStr([FIRST NAME],"" "")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME],"" "")-1)) AS FNAME,
morecells.[LAST NAME], morecells.[PHONE NUMBER], IIf(InStr([FIRST NAME],""
"")=0,[FIRST NAME],Left([FIRST NAME],InStr([FIRST NAME],"" "")-1)) & "".""
&
[LAST NAME] & ""@Assurant.net"" AS Email FROM morecells GROUP BY
IIf(InStr([FIRST NAME],"" "")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST
NAME],"" "")-1)), morecells.[LAST NAME], morecells.[PHONE NUMBER],
IIf(InStr([FIRST NAME],"" "")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST
NAME],"" "")-1)) & ""."" & [LAST NAME] & ""@Assurant.net"" HAVING
(((morecells.[LAST NAME]) Not Like ""Kroll"")) ORDER BY morecells.[LAST
NAME];"

but my DoCmd.RunSQL strSQL statement errors out with Runtime Error 2342,
saying that a RunSQL argument requires an argument consisting of a SQL
statement




Allen Browne said:
In VBA code, the entire SQL statement has to be contained in quotes. But
you
have quote marks inside the string. You need to double them up so VBA
undersands it is not the end of the string.

For example, to code:
This string has a "word" in quotes
you write:
"This string has a ""word"" in quotes"

If the quotes are at the end, you finish up with 3 in a row, i.e.:
"This string has a ""word"""

So:
strSql = "SELECT IIf(InStr([FIRST NAME],"" "")=0, ...

I'm trying to use a SQL statement that I cut and pasted from a working
query.
I want to use it in code, but I can't get the syntax to stop
erroring...I'm
going crazy with it. Here it is

SELECT IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)) AS FNAME, [morecells].[LAST NAME],
[morecells].[PHONE NUMBER], IIf(InStr([FIRST NAME]," ")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST NAME]
&
"@Mycompany.net" AS Email
FROM morecells
GROUP BY IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)), [morecells].[LAST NAME],
[morecells].[PHONE NUMBER], IIf(InStr([FIRST NAME]," ")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST NAME]
&
"@Mycompany.net"
ORDER BY [morecells].[LAST NAME];
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Standard module is fine.

If you still can't get the right result, post the result of the Debug.Print,
and I will look at it tomrrow. (About to quit for today.)
 
G

Guest

OK, thanks for your help...
here's the SQL string:
strSQL = "SELECT IIf(InStr([FIRST NAME],"" "")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME],"" "")-1)) AS FNAME,
morecells.[LAST NAME], morecells.[PHONE NUMBER], IIf(InStr([FIRST NAME],""
"")=0,[FIRST NAME],Left([FIRST NAME],InStr([FIRST NAME],"" "")-1)) & ""."" &
[LAST NAME] & ""@Mycompany.net"" AS Email FROM morecells GROUP BY
IIf(InStr([FIRST NAME],"" "")=0,[FIRST NAME],Left([FIRST NAME],InStr([FIRST
NAME],"" "")-1)), morecells.[LAST NAME], morecells.[PHONE NUMBER],
IIf(InStr([FIRST NAME],"" "")=0,[FIRST NAME],Left([FIRST NAME],InStr([FIRST
NAME],"" "")-1)) & ""."" & [LAST NAME] & ""@Mycompany.net"" ORDER BY
morecells.[LAST NAME];"

and here's the Debug.Print result from the Immediate window:

SELECT IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)) AS FNAME, morecells.[LAST NAME],
morecells.[PHONE NUMBER], IIf(InStr([FIRST NAME]," ")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST NAME] &
"@Mycompany.net" AS Email FROM morecells GROUP BY IIf(InStr([FIRST NAME],"
")=0,[FIRST NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)),
morecells.[LAST NAME], morecells.[PHONE NUMBER], IIf(InStr([FIRST NAME],"
")=0,[FIRST NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST
NAME] & "@Mycompany.net" ORDER BY morecells.[LAST NAME];

Since the code doesn't fail, I'm pretty sure they're the same.

So, I can't understand why I get the error when I comment out the
Debut.Print and add DoCmd.RunSQL strSQL

I hope you can give me some tip tomorrow ... Thanks again.
 
D

Dirk Goldgar

Ray S. said:
OK, thanks for your help...
here's the SQL string:
strSQL = "SELECT IIf(InStr([FIRST NAME],"" "")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME],"" "")-1)) AS FNAME,
morecells.[LAST NAME], morecells.[PHONE NUMBER], IIf(InStr([FIRST
NAME],"" "")=0,[FIRST NAME],Left([FIRST NAME],InStr([FIRST NAME],""
"")-1)) & ""."" & [LAST NAME] & ""@Mycompany.net"" AS Email FROM
morecells GROUP BY IIf(InStr([FIRST NAME],"" "")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME],"" "")-1)),
morecells.[LAST NAME], morecells.[PHONE NUMBER], IIf(InStr([FIRST
NAME],"" "")=0,[FIRST NAME],Left([FIRST NAME],InStr([FIRST NAME],""
"")-1)) & ""."" & [LAST NAME] & ""@Mycompany.net"" ORDER BY
morecells.[LAST NAME];"

and here's the Debug.Print result from the Immediate window:

SELECT IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)) AS FNAME, morecells.[LAST NAME],
morecells.[PHONE NUMBER], IIf(InStr([FIRST NAME]," ")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST
NAME] & "@Mycompany.net" AS Email FROM morecells GROUP BY
IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)), morecells.[LAST NAME],
morecells.[PHONE NUMBER], IIf(InStr([FIRST NAME]," ")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST
NAME] & "@Mycompany.net" ORDER BY morecells.[LAST NAME];

Since the code doesn't fail, I'm pretty sure they're the same.

So, I can't understand why I get the error when I comment out the
Debut.Print and add DoCmd.RunSQL strSQL
Click to expand...

RunSQL only works to execute action queries (e.g., update, delete,
append, or make-table queries). This is a SELECT query -- one that
returns a set of records. The error message you get is misleading, but
that's what it means.

What do you want to do with the records returned by this query? If you
want to display them in a datasheet, you need to do more than just "run
it".
 
G

Guest

What I'm going to try to do with it is to use the results of the grouped
query to send an email to each of the employees showing them the multiple
phone numbers I show assigned to them and requesting a confirmation or
explanation of service.

Dirk Goldgar said:
Ray S. said:
OK, thanks for your help...
here's the SQL string:
strSQL = "SELECT IIf(InStr([FIRST NAME],"" "")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME],"" "")-1)) AS FNAME,
morecells.[LAST NAME], morecells.[PHONE NUMBER], IIf(InStr([FIRST
NAME],"" "")=0,[FIRST NAME],Left([FIRST NAME],InStr([FIRST NAME],""
"")-1)) & ""."" & [LAST NAME] & ""@Mycompany.net"" AS Email FROM
morecells GROUP BY IIf(InStr([FIRST NAME],"" "")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME],"" "")-1)),
morecells.[LAST NAME], morecells.[PHONE NUMBER], IIf(InStr([FIRST
NAME],"" "")=0,[FIRST NAME],Left([FIRST NAME],InStr([FIRST NAME],""
"")-1)) & ""."" & [LAST NAME] & ""@Mycompany.net"" ORDER BY
morecells.[LAST NAME];"

and here's the Debug.Print result from the Immediate window:

SELECT IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)) AS FNAME, morecells.[LAST NAME],
morecells.[PHONE NUMBER], IIf(InStr([FIRST NAME]," ")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST
NAME] & "@Mycompany.net" AS Email FROM morecells GROUP BY
IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)), morecells.[LAST NAME],
morecells.[PHONE NUMBER], IIf(InStr([FIRST NAME]," ")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST
NAME] & "@Mycompany.net" ORDER BY morecells.[LAST NAME];

Since the code doesn't fail, I'm pretty sure they're the same.

So, I can't understand why I get the error when I comment out the
Debut.Print and add DoCmd.RunSQL strSQL
Click to expand...

RunSQL only works to execute action queries (e.g., update, delete,
append, or make-table queries). This is a SELECT query -- one that
returns a set of records. The error message you get is misleading, but
that's what it means.

What do you want to do with the records returned by this query? If you
want to display them in a datasheet, you need to do more than just "run
it".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
G

Guest

Not to attempt to correct something one of the MVPs would write, just an
alternate approach...Within Access VBA, I've had better results substituting
single quotes rather than using double-doubles. When a literal appears in the
text, such as the back end of the email address, I use something like this:

strSQL = "SELECT IIf(InStr([FIRST NAME], ' ')=0,[FIRST NAME], " & _
"Left([FIRST NAME], InStr([FIRST NAME], ' ')-1)) AS FNAME, " & _
"morecells.[LAST NAME], morecells.[PHONE NUMBER], " & _
"IIf(InStr([FIRST NAME], ' ')=0,[FIRST NAME],Left([FIRST NAME], " & _
"InStr([FIRST NAME], ' ')-1)) & '" & "." & "' [LAST NAME] & '" & _
"@Mycompany.net" & "' AS Email FROM morecells " & _
"GROUP BY IIf(InStr([FIRST NAME], ' ')=0,[FIRST NAME]," & _
"Left([FIRST NAME],InStr([FIRST NAME], ' ')-1)), morecells.[LAST NAME], " & _
"morecells.[PHONE NUMBER], IIf(InStr([FIRST NAME], ' ')=0," & _
"[FIRST NAME],Left([FIRST NAME],InStr([FIRST NAME], ' ')-1)) & '" & "." & _
"' [LAST NAME] & '" & "@Mycompany.net" & "' ORDER BY morecells.[LAST NAME];"

See how that works for you.

Matt Rich
Humble Coder

Dirk Goldgar said:
Ray S. said:
OK, thanks for your help...
here's the SQL string:
strSQL = "SELECT IIf(InStr([FIRST NAME],"" "")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME],"" "")-1)) AS FNAME,
morecells.[LAST NAME], morecells.[PHONE NUMBER], IIf(InStr([FIRST
NAME],"" "")=0,[FIRST NAME],Left([FIRST NAME],InStr([FIRST NAME],""
"")-1)) & ""."" & [LAST NAME] & ""@Mycompany.net"" AS Email FROM
morecells GROUP BY IIf(InStr([FIRST NAME],"" "")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME],"" "")-1)),
morecells.[LAST NAME], morecells.[PHONE NUMBER], IIf(InStr([FIRST
NAME],"" "")=0,[FIRST NAME],Left([FIRST NAME],InStr([FIRST NAME],""
"")-1)) & ""."" & [LAST NAME] & ""@Mycompany.net"" ORDER BY
morecells.[LAST NAME];"

and here's the Debug.Print result from the Immediate window:

SELECT IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)) AS FNAME, morecells.[LAST NAME],
morecells.[PHONE NUMBER], IIf(InStr([FIRST NAME]," ")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST
NAME] & "@Mycompany.net" AS Email FROM morecells GROUP BY
IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)), morecells.[LAST NAME],
morecells.[PHONE NUMBER], IIf(InStr([FIRST NAME]," ")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST
NAME] & "@Mycompany.net" ORDER BY morecells.[LAST NAME];

Since the code doesn't fail, I'm pretty sure they're the same.

So, I can't understand why I get the error when I comment out the
Debut.Print and add DoCmd.RunSQL strSQL
Click to expand...

RunSQL only works to execute action queries (e.g., update, delete,
append, or make-table queries). This is a SELECT query -- one that
returns a set of records. The error message you get is misleading, but
that's what it means.

What do you want to do with the records returned by this query? If you
want to display them in a datasheet, you need to do more than just "run
it".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Douglas J Steele

Matt Rich said:
Not to attempt to correct something one of the MVPs would write, just an
alternate approach...Within Access VBA, I've had better results substituting
single quotes rather than using double-doubles. When a literal appears in the
text, such as the back end of the email address, I use something like
Click to expand...
this:
Matt Rich
Humble Coder
Click to expand...

Matt: while single quotes are fine, you seem to have ignored the true issue
that Dirk pointed out: you cannot use a SELECT query with DoCmd.RunSQL
 
G

Guest

Odd, since I use it every day. I populate a string variable, then:
DoCmd.RunSQL strVar
as a procedure used daily in an application my company is using.

Perhaps there is a difference in the versions of VBA?

 
D

Dirk Goldgar

Matt Rich said:
Odd, since I use it every day. I populate a string variable, then:
DoCmd.RunSQL strVar
as a procedure used daily in an application my company is using.

Perhaps there is a difference in the versions of VBA?
Click to expand...

I'll wager that when you use ...

DoCmd.RunSQL strVar

.... strVar contains an action query, not a SELECT query. I'm not aware
of any version of Access that supported the use of SELECT queries with
RunSQL.
 
G

Guest

Thanks everyone, I guess I can easily enough make the query an action query.
What's important for me is that the results of that query groups the
information as it does. That is, a simple report based on the query will give
me each employee along with the more than one phone numbers currentlly
assinged to them. The key is that I then want to somehow use the grouped
results to populate individualized emails to the employees.
 
G

Guest

Not sure what I was smoking. My apologies, I just realized I was using
recordsets for my select statements. But it sounded good, didn't it?

Matt R (as in "red-faced")
 
D

Dirk Goldgar

Ray S. said:
What I'm going to try to do with it is to use the results of the
grouped query to send an email to each of the employees showing them
the multiple phone numbers I show assigned to them and requesting a
confirmation or explanation of service.

Dirk Goldgar said:
Ray S. said:
OK, thanks for your help...
here's the SQL string:
strSQL = "SELECT IIf(InStr([FIRST NAME],"" "")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME],"" "")-1)) AS FNAME,
morecells.[LAST NAME], morecells.[PHONE NUMBER], IIf(InStr([FIRST
NAME],"" "")=0,[FIRST NAME],Left([FIRST NAME],InStr([FIRST NAME],""
"")-1)) & ""."" & [LAST NAME] & ""@Mycompany.net"" AS Email FROM
morecells GROUP BY IIf(InStr([FIRST NAME],"" "")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME],"" "")-1)),
morecells.[LAST NAME], morecells.[PHONE NUMBER], IIf(InStr([FIRST
NAME],"" "")=0,[FIRST NAME],Left([FIRST NAME],InStr([FIRST NAME],""
"")-1)) & ""."" & [LAST NAME] & ""@Mycompany.net"" ORDER BY
morecells.[LAST NAME];"

and here's the Debug.Print result from the Immediate window:

SELECT IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)) AS FNAME, morecells.[LAST NAME],
morecells.[PHONE NUMBER], IIf(InStr([FIRST NAME]," ")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST
NAME] & "@Mycompany.net" AS Email FROM morecells GROUP BY
IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)), morecells.[LAST NAME],
morecells.[PHONE NUMBER], IIf(InStr([FIRST NAME]," ")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST
NAME] & "@Mycompany.net" ORDER BY morecells.[LAST NAME];
Click to expand...
Click to expand...
Click to expand...

You're going to need to open a recordset on your query, and loop through
its records to send an e-mail to each address, picking up the necessary
data fields from the recordset. However, your query is going to
(potentially) return multiple records for each employee -- one record
for each phone number. If I understand you, you don't want to send an
e-mail for each record in the recordset, but only for each employee.
That means either some sort of inner loop (outer loop for each employee,
inner loop for each phone number of the current employee), or else use
some function such as the fConcatChild function posted at
http://www.mvps.org/access/modules/mdl0004.htm, to return a single
record per employee, with a calculated text field containing a
concatenated list of the phone numbers for each employee.

Before getting into the details of either approach, please tell us what
the relevant tables look like and how they are related. Do you have an
Employees table, with one record per employee? If so, is that table
related to the [morecells] table by some key such as EmployeeID? Or is
all the data only in this [morecells] table?
 

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

How to extract portion of text 5
Expresson Problem 2
Run time error in VBA Code 1
IIF Function 2
Passing results from Immediate Window 5
SQL Syntax in macro with & " 4
troubles with do while loop 2
Repeated call to InternetExplorer.Application seems to hang the PC 4

Top