Putting Query into SQL

W

Winger

As I can't write SQL I use the query builder to give me the SQL to define my
RecordSet.

My Query works fine:-

SELECT tblPeople.FirstName, tblPeople.LastName, tblPeople.UCLanEmail,
tblPeople.[IncludeInEmail?], tblPeople.[IncubationClient?],
tblPeople.[Archive?]
FROM tblPeople
WHERE (((tblPeople.UCLanEmail)<>"") AND ((tblPeople.[IncludeInEmail?])=Yes)
AND ((tblPeople.[IncubationClient?])="No") AND ((tblPeople.[Archive?])=No))
ORDER BY tblPeople.LastName;

but when I drop it into my code as follows:-

mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName,
tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?],
tblPeople.[IncubationClient?], tblPeople.[Archive?]"
mySQL = mySQL + "FROM tblPeople "
mySQL = mySQL + "WHERE (((tblPeople.UCLanEmail) <> "") And
((tblPeople.[IncludeInEmail?]) = Yes) And ((tblPeople.[IncubationClient?]) =
False) And ((tblPeople.[Archive?]) = No))"
mySQL = mySQL + "ORDER BY tblPeople.LastName;"

I get a syntax error.
This usually occurs with the brackets of the various "WHERE" clauses but I'm
still not sure on how the Chr$34$ should be used.

All the fields in the WHERE clauses are Yes/No fields.

Any help would be very much appreciated.

thanks

Winger
 
S

SteveM

If you check your SQL string in break mode, you will probably see the problem.
For one thing, when you concatenate the parts to your string, you are not
including a space...

Try this:
mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName,
tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?],
tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + "FROM tblPeople "
mySQL = mySQL + "WHERE tblPeople.UCLanEmail <> "" And
tblPeople.[IncludeInEmail?] = True And tblPeople.[IncubationClient?] =
False And tblPeople.[Archive?] = False "
mySQL = mySQL + "ORDER BY tblPeople.LastName;"
still not sure on how the Chr$34$ should be used.
Click to expand...
You sometimes use Chr$(34) in SQL when querying strings that may have
apostrophes, otherwise you don't need to. It's not a particularly bad habit
to use it all the time though just in case you forget - it will not hurt
anything...

strWhere = "WHERE MyStringField = " & Chr$(34) & "O'Hara" & Chr$(34)

Steve

Winger said:
As I can't write SQL I use the query builder to give me the SQL to define my
RecordSet.

My Query works fine:-

SELECT tblPeople.FirstName, tblPeople.LastName, tblPeople.UCLanEmail,
tblPeople.[IncludeInEmail?], tblPeople.[IncubationClient?],
tblPeople.[Archive?]
FROM tblPeople
WHERE (((tblPeople.UCLanEmail)<>"") AND ((tblPeople.[IncludeInEmail?])=Yes)
AND ((tblPeople.[IncubationClient?])="No") AND ((tblPeople.[Archive?])=No))
ORDER BY tblPeople.LastName;

but when I drop it into my code as follows:-

mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName,
tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?],
tblPeople.[IncubationClient?], tblPeople.[Archive?]"
mySQL = mySQL + "FROM tblPeople "
mySQL = mySQL + "WHERE (((tblPeople.UCLanEmail) <> "") And
((tblPeople.[IncludeInEmail?]) = Yes) And ((tblPeople.[IncubationClient?]) =
False) And ((tblPeople.[Archive?]) = No))"
mySQL = mySQL + "ORDER BY tblPeople.LastName;"

I get a syntax error.
This usually occurs with the brackets of the various "WHERE" clauses but I'm
still not sure on how the Chr$34$ should be used.

All the fields in the WHERE clauses are Yes/No fields.

Any help would be very much appreciated.

thanks

Winger
Click to expand...
 
P

pietlinden

As I can't write SQL I use the query builder to give me the SQL to define my
RecordSet.

My Query works fine:-

SELECT tblPeople.FirstName, tblPeople.LastName, tblPeople.UCLanEmail,
tblPeople.[IncludeInEmail?], tblPeople.[IncubationClient?],
tblPeople.[Archive?]
FROM tblPeople
WHERE (((tblPeople.UCLanEmail)<>"") AND ((tblPeople.[IncludeInEmail?])=Yes)
AND ((tblPeople.[IncubationClient?])="No") AND ((tblPeople.[Archive?])=No))
ORDER BY tblPeople.LastName;

but when I drop it into my code as follows:-

mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName,
tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?],
tblPeople.[IncubationClient?], tblPeople.[Archive?]"
mySQL = mySQL + "FROM tblPeople "
mySQL = mySQL + "WHERE (((tblPeople.UCLanEmail) <> "") And
((tblPeople.[IncludeInEmail?]) = Yes) And ((tblPeople.[IncubationClient?]) =
False) And ((tblPeople.[Archive?]) = No))"
mySQL = mySQL + "ORDER BY tblPeople.LastName;"

I get a syntax error.
This usually occurs with the brackets of the various "WHERE" clauses but I'm
still not sure on how the Chr$34$ should be used.

All the fields in the WHERE clauses are Yes/No fields.

Any help would be very much appreciated.

thanks

Winger
Click to expand...

looks like you're missing some spaces. like the one before "ORDER BY"
 
W

Winger

Steve,

I tried your code but still got an Syntax error message.

I tried inserting the chr$(34) as follows:-


mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName,
tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?],
tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + " FROM tblPeople "
mySQL = mySQL + " WHERE tblPeople.UCLanEmail <> chr$(34)""chr$(34) And
tblPeople.[IncludeInEmail?] = True And tblPeople.[IncubationClient?] = False
And tblPeople.[Archive?] = False "
mySQL = mySQL + " ORDER BY tblPeople.LastName;"

but I'm not sure what I've done is correct.

I think the problem is in the "Where clause" and the "" 's.
If I finish the SQL at ...... "FROM tblPeople; " the code moves onto the
next bit, so I think this confirms the problem with th Where Clauses.

However, when it does progress, I get an "unknown receipenet" error from the
following code:

Set rst = db.OpenRecordset(mySQL, dbOpenDynaset)
If Not rst.EOF Then

rst.MoveFirst
mySTRING = ""

Do Until rst.EOF
mySTRING = mySTRING + rst!UCLanEmail & ";"
rst.MoveNext
Loop

lngLen = Len(mySTRING)
If lngLen > 0 Then
mySTRING = Left$(mySTRING, lngLen - 1)

DoCmd.SendObject , , , , , mySTRING, , , True


The sending code has previouly worked fine in other parts of the
application, so again I'm not sure what I'm missing.

thanks

Winger


SteveM said:
If you check your SQL string in break mode, you will probably see the problem.
For one thing, when you concatenate the parts to your string, you are not
including a space...

Try this:
mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName,
tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?],
tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + "FROM tblPeople "
mySQL = mySQL + "WHERE tblPeople.UCLanEmail <> "" And
tblPeople.[IncludeInEmail?] = True And tblPeople.[IncubationClient?] =
False And tblPeople.[Archive?] = False "
mySQL = mySQL + "ORDER BY tblPeople.LastName;"
still not sure on how the Chr$34$ should be used.
Click to expand...
You sometimes use Chr$(34) in SQL when querying strings that may have
apostrophes, otherwise you don't need to. It's not a particularly bad habit
to use it all the time though just in case you forget - it will not hurt
anything...

strWhere = "WHERE MyStringField = " & Chr$(34) & "O'Hara" & Chr$(34)

Steve

Winger said:
As I can't write SQL I use the query builder to give me the SQL to define my
RecordSet.

My Query works fine:-

SELECT tblPeople.FirstName, tblPeople.LastName, tblPeople.UCLanEmail,
tblPeople.[IncludeInEmail?], tblPeople.[IncubationClient?],
tblPeople.[Archive?]
FROM tblPeople
WHERE (((tblPeople.UCLanEmail)<>"") AND ((tblPeople.[IncludeInEmail?])=Yes)
AND ((tblPeople.[IncubationClient?])="No") AND ((tblPeople.[Archive?])=No))
ORDER BY tblPeople.LastName;

but when I drop it into my code as follows:-

mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName,
tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?],
tblPeople.[IncubationClient?], tblPeople.[Archive?]"
mySQL = mySQL + "FROM tblPeople "
mySQL = mySQL + "WHERE (((tblPeople.UCLanEmail) <> "") And
((tblPeople.[IncludeInEmail?]) = Yes) And ((tblPeople.[IncubationClient?]) =
False) And ((tblPeople.[Archive?]) = No))"
mySQL = mySQL + "ORDER BY tblPeople.LastName;"

I get a syntax error.
This usually occurs with the brackets of the various "WHERE" clauses but I'm
still not sure on how the Chr$34$ should be used.

All the fields in the WHERE clauses are Yes/No fields.

Any help would be very much appreciated.

thanks

Winger
Click to expand...
Click to expand...
 
J

John Spencer

I think what you want is something like the following

mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName, " & _
"tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?], " & _
"tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + " FROM tblPeople "
mySQL = mySQL + " WHERE tblPeople.UCLanEmail <> """" " & _
"And tblPeople.[IncludeInEmail?] = True " & _
" And tblPeople.[IncubationClient?] = False " & _
" And tblPeople.[Archive?] = False "
mySQL = mySQL + " ORDER BY tblPeople.LastName;"

Of course if you are attempting to eliminate null in UClanEmail that will
not work as you expect.and should read
mySQL = mySQL + " WHERE tblPeople.UCLanEmail is Not Null " & _

If you don't know if the field is Null or contains a zero length string, you
can use
mySQL = mySQL + " WHERE tblPeople.UCLanEmail & """" <> """" " & _

Hope this helps.

You can also use "single" quotes instead as in the following.
mySQL = mySQL + " WHERE tblPeople.UCLanEmail & '' <> '' " & _

Adding spaces for clarity (remove them) between the single quotes that looks
like
mySQL = mySQL + " WHERE tblPeople.UCLanEmail & ' ' <> ' ' " & _

And now that I think about it, you don't need to remove the space between
the single quotes in this case.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Winger said:
Steve,

I tried your code but still got an Syntax error message.

I tried inserting the chr$(34) as follows:-


mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName,
tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?],
tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + " FROM tblPeople "
mySQL = mySQL + " WHERE tblPeople.UCLanEmail <> chr$(34)""chr$(34) And
tblPeople.[IncludeInEmail?] = True And tblPeople.[IncubationClient?] =
False
And tblPeople.[Archive?] = False "
mySQL = mySQL + " ORDER BY tblPeople.LastName;"

but I'm not sure what I've done is correct.

I think the problem is in the "Where clause" and the "" 's.
If I finish the SQL at ...... "FROM tblPeople; " the code moves onto the
next bit, so I think this confirms the problem with th Where Clauses.

However, when it does progress, I get an "unknown receipenet" error from
the
following code:

Set rst = db.OpenRecordset(mySQL, dbOpenDynaset)
If Not rst.EOF Then

rst.MoveFirst
mySTRING = ""

Do Until rst.EOF
mySTRING = mySTRING + rst!UCLanEmail & ";"
rst.MoveNext
Loop

lngLen = Len(mySTRING)
If lngLen > 0 Then
mySTRING = Left$(mySTRING, lngLen - 1)

DoCmd.SendObject , , , , , mySTRING, , , True


The sending code has previouly worked fine in other parts of the
application, so again I'm not sure what I'm missing.

thanks

Winger


SteveM said:
If you check your SQL string in break mode, you will probably see the
problem.
For one thing, when you concatenate the parts to your string, you are not
including a space...

Try this:
mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName,
tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?],
tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + "FROM tblPeople "
mySQL = mySQL + "WHERE tblPeople.UCLanEmail <> "" And
tblPeople.[IncludeInEmail?] = True And tblPeople.[IncubationClient?] =
False And tblPeople.[Archive?] = False "
mySQL = mySQL + "ORDER BY tblPeople.LastName;"
still not sure on how the Chr$34$ should be used.
Click to expand...
You sometimes use Chr$(34) in SQL when querying strings that may have
apostrophes, otherwise you don't need to. It's not a particularly bad
habit
to use it all the time though just in case you forget - it will not hurt
anything...

strWhere = "WHERE MyStringField = " & Chr$(34) & "O'Hara" & Chr$(34)

Steve

Winger said:
As I can't write SQL I use the query builder to give me the SQL to
define my
RecordSet.

My Query works fine:-

SELECT tblPeople.FirstName, tblPeople.LastName, tblPeople.UCLanEmail,
tblPeople.[IncludeInEmail?], tblPeople.[IncubationClient?],
tblPeople.[Archive?]
FROM tblPeople
WHERE (((tblPeople.UCLanEmail)<>"") AND
((tblPeople.[IncludeInEmail?])=Yes)
AND ((tblPeople.[IncubationClient?])="No") AND
((tblPeople.[Archive?])=No))
ORDER BY tblPeople.LastName;

but when I drop it into my code as follows:-

mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName,
tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?],
tblPeople.[IncubationClient?], tblPeople.[Archive?]"
mySQL = mySQL + "FROM tblPeople "
mySQL = mySQL + "WHERE (((tblPeople.UCLanEmail) <> "") And
((tblPeople.[IncludeInEmail?]) = Yes) And
((tblPeople.[IncubationClient?]) =
False) And ((tblPeople.[Archive?]) = No))"
mySQL = mySQL + "ORDER BY tblPeople.LastName;"

I get a syntax error.
This usually occurs with the brackets of the various "WHERE" clauses
but I'm
still not sure on how the Chr$34$ should be used.

All the fields in the WHERE clauses are Yes/No fields.

Any help would be very much appreciated.

thanks

Winger
Click to expand...
Click to expand...
Click to expand...
 
W

Winger

John,

Thanks for this. I dropped the code straight in and didn't get an error
message. However when I clicked the comand button I didn't get anything!!?

The full code is as follows:

Dim db As Database
Dim rst As Recordset
Dim mySQL As String
Dim mySTRING As String

Set db = CurrentDb

mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName, " & _
"tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?], " & _
"tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + " FROM tblPeople "
mySQL = mySQL + " WHERE tblPeople.UCLanEmail & """" <> """" " & _
"And tblPeople.[IncludeInEmail?] = True " & _
" And tblPeople.[IncubationClient?] = False " & _
" And tblPeople.[Archive?] = False "
mySQL = mySQL + " ORDER BY tblPeople.LastName;"

Set rst = db.OpenRecordset(mySQL, dbOpenDynaset)

rst.MoveFirst


If Not rst.EOF Then

rst.MoveFirst

mySTRING = ""

Do Until rst.EOF
mySTRING = mySTRING + rst!UCLanEmail & ";"
rst.MoveNext
Loop

lngLen = Len(mySTRING)
If lngLen > 0 Then
mySTRING = Left$(mySTRING, lngLen - 1)

DoCmd.SendObject , , , , , mySTRING, , , True


End If

End If
End Sub


The DoCMDSend works fine, but if I end the SQL at "From tblPeople;" I get an
error message "unknown receipetnt", yet the Email facility hasn't been
invoked yet. I'm not very profiecnet at using the "break" and immediate
window, but with my first atempts, I think the SQL is returning an empty
file, yet I know that a number of people have an UClanEmail address.

I'm at a loss as to how to progress this?

Thankyou for your help with this.

Regards

Winger



John Spencer said:
I think what you want is something like the following

mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName, " & _
"tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?], " & _
"tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + " FROM tblPeople "
mySQL = mySQL + " WHERE tblPeople.UCLanEmail <> """" " & _
"And tblPeople.[IncludeInEmail?] = True " & _
" And tblPeople.[IncubationClient?] = False " & _
" And tblPeople.[Archive?] = False "
mySQL = mySQL + " ORDER BY tblPeople.LastName;"

Of course if you are attempting to eliminate null in UClanEmail that will
not work as you expect.and should read
mySQL = mySQL + " WHERE tblPeople.UCLanEmail is Not Null " & _

If you don't know if the field is Null or contains a zero length string, you
can use
mySQL = mySQL + " WHERE tblPeople.UCLanEmail & """" <> """" " & _

Hope this helps.

You can also use "single" quotes instead as in the following.
mySQL = mySQL + " WHERE tblPeople.UCLanEmail & '' <> '' " & _

Adding spaces for clarity (remove them) between the single quotes that looks
like
mySQL = mySQL + " WHERE tblPeople.UCLanEmail & ' ' <> ' ' " & _

And now that I think about it, you don't need to remove the space between
the single quotes in this case.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Winger said:
Steve,

I tried your code but still got an Syntax error message.

I tried inserting the chr$(34) as follows:-


mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName,
tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?],
tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + " FROM tblPeople "
mySQL = mySQL + " WHERE tblPeople.UCLanEmail <> chr$(34)""chr$(34) And
tblPeople.[IncludeInEmail?] = True And tblPeople.[IncubationClient?] =
False
And tblPeople.[Archive?] = False "
mySQL = mySQL + " ORDER BY tblPeople.LastName;"

but I'm not sure what I've done is correct.

I think the problem is in the "Where clause" and the "" 's.
If I finish the SQL at ...... "FROM tblPeople; " the code moves onto the
next bit, so I think this confirms the problem with th Where Clauses.

However, when it does progress, I get an "unknown receipenet" error from
the
following code:

Set rst = db.OpenRecordset(mySQL, dbOpenDynaset)
If Not rst.EOF Then

rst.MoveFirst
mySTRING = ""

Do Until rst.EOF
mySTRING = mySTRING + rst!UCLanEmail & ";"
rst.MoveNext
Loop

lngLen = Len(mySTRING)
If lngLen > 0 Then
mySTRING = Left$(mySTRING, lngLen - 1)

DoCmd.SendObject , , , , , mySTRING, , , True


The sending code has previouly worked fine in other parts of the
application, so again I'm not sure what I'm missing.

thanks

Winger


SteveM said:
If you check your SQL string in break mode, you will probably see the
problem.
For one thing, when you concatenate the parts to your string, you are not
including a space...

Try this:
mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName,
tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?],
tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + "FROM tblPeople "
mySQL = mySQL + "WHERE tblPeople.UCLanEmail <> "" And
tblPeople.[IncludeInEmail?] = True And tblPeople.[IncubationClient?] =
False And tblPeople.[Archive?] = False "
mySQL = mySQL + "ORDER BY tblPeople.LastName;"

still not sure on how the Chr$34$ should be used.
You sometimes use Chr$(34) in SQL when querying strings that may have
apostrophes, otherwise you don't need to. It's not a particularly bad
habit
to use it all the time though just in case you forget - it will not hurt
anything...

strWhere = "WHERE MyStringField = " & Chr$(34) & "O'Hara" & Chr$(34)

Steve

:

As I can't write SQL I use the query builder to give me the SQL to
define my
RecordSet.

My Query works fine:-

SELECT tblPeople.FirstName, tblPeople.LastName, tblPeople.UCLanEmail,
tblPeople.[IncludeInEmail?], tblPeople.[IncubationClient?],
tblPeople.[Archive?]
FROM tblPeople
WHERE (((tblPeople.UCLanEmail)<>"") AND
((tblPeople.[IncludeInEmail?])=Yes)
AND ((tblPeople.[IncubationClient?])="No") AND
((tblPeople.[Archive?])=No))
ORDER BY tblPeople.LastName;

but when I drop it into my code as follows:-

mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName,
tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?],
tblPeople.[IncubationClient?], tblPeople.[Archive?]"
mySQL = mySQL + "FROM tblPeople "
mySQL = mySQL + "WHERE (((tblPeople.UCLanEmail) <> "") And
((tblPeople.[IncludeInEmail?]) = Yes) And
((tblPeople.[IncubationClient?]) =
False) And ((tblPeople.[Archive?]) = No))"
mySQL = mySQL + "ORDER BY tblPeople.LastName;"

I get a syntax error.
This usually occurs with the brackets of the various "WHERE" clauses
but I'm
still not sure on how the Chr$34$ should be used.

All the fields in the WHERE clauses are Yes/No fields.

Any help would be very much appreciated.

thanks

Winger
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Try Adding a

debug.print MySQL

into your code right after the MySQL varaiable is populated and then
checking the SQL that is generated correctly. You should be able to copy
the SQL into a new query and run the query. If you get no records back then
you know you need to work on the SQL string. If you do get records back,
then check them to see what values you are getting. And if that looks good
start stepping through your code to find where the error occurs.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
W

Winger

John,

Thanks for the tips - debug.print is very useful along with pasting back
into query.

The error is in my part. I'd forgotton that I'd changed
tblPeople.[IncubationClient?] into a TEXT field with a lookup of YES/NO.

So the code is producing no reocrds.

I need to change the Where clause - I've had a go at:

And tblPeople.[IncubationClient?] <> &chr$(34)& 'No' & chr$(34)& " &

but get a syntax error, as does

And tblPeople.[IncubationClient?] <> "No" " & _

How do I reference the value of a TEXT field and get all the "" "" "" correct?

thanks

Winger





John Spencer said:
Try Adding a

debug.print MySQL

into your code right after the MySQL varaiable is populated and then
checking the SQL that is generated correctly. You should be able to copy
the SQL into a new query and run the query. If you get no records back then
you know you need to work on the SQL string. If you do get records back,
then check them to see what values you are getting. And if that looks good
start stepping through your code to find where the error occurs.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Winger said:
John,

Thanks for this. I dropped the code straight in and didn't get an error
message. However when I clicked the comand button I didn't get anything!!?

The full code is as follows:

Dim db As Database
Dim rst As Recordset
Dim mySQL As String
Dim mySTRING As String

Set db = CurrentDb

mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName, " & _
"tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?], " & _
"tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + " FROM tblPeople "
mySQL = mySQL + " WHERE tblPeople.UCLanEmail & """" <> """" " & _
"And tblPeople.[IncludeInEmail?] = True " & _
" And tblPeople.[IncubationClient?] = False " & _
" And tblPeople.[Archive?] = False "
mySQL = mySQL + " ORDER BY tblPeople.LastName;"

Set rst = db.OpenRecordset(mySQL, dbOpenDynaset)

rst.MoveFirst


If Not rst.EOF Then

rst.MoveFirst

mySTRING = ""

Do Until rst.EOF
mySTRING = mySTRING + rst!UCLanEmail & ";"
rst.MoveNext
Loop

lngLen = Len(mySTRING)
If lngLen > 0 Then
mySTRING = Left$(mySTRING, lngLen - 1)

DoCmd.SendObject , , , , , mySTRING, , , True


End If

End If
End Sub


The DoCMDSend works fine, but if I end the SQL at "From tblPeople;" I get
an
error message "unknown receipetnt", yet the Email facility hasn't been
invoked yet. I'm not very profiecnet at using the "break" and immediate
window, but with my first atempts, I think the SQL is returning an empty
file, yet I know that a number of people have an UClanEmail address.

I'm at a loss as to how to progress this?

Thankyou for your help with this.

Regards

Winger
Click to expand...
Click to expand...
 
J

John Spencer

Leave out the Chr(34) calls. They are not needed in situation.

" AND tblPeople.[IncubationClient"]<> 'No' " & _

If you wish to use quotes then double up on them.


" AND tblPeople.[IncubationClient"]<> ""No"" " & _

When building a string putting in two quotes in a row will return one
quote in the string that is built.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,

Thanks for the tips - debug.print is very useful along with pasting back
into query.

The error is in my part. I'd forgotton that I'd changed
tblPeople.[IncubationClient?] into a TEXT field with a lookup of YES/NO.

So the code is producing no reocrds.

I need to change the Where clause - I've had a go at:

And tblPeople.[IncubationClient?] <> &chr$(34)& 'No' & chr$(34)& " &

but get a syntax error, as does

And tblPeople.[IncubationClient?] <> "No" " & _

How do I reference the value of a TEXT field and get all the "" "" "" correct?

thanks

Winger





John Spencer said:
Try Adding a

debug.print MySQL

into your code right after the MySQL varaiable is populated and then
checking the SQL that is generated correctly. You should be able to copy
the SQL into a new query and run the query. If you get no records back then
you know you need to work on the SQL string. If you do get records back,
then check them to see what values you are getting. And if that looks good
start stepping through your code to find where the error occurs.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Winger said:
John,

Thanks for this. I dropped the code straight in and didn't get an error
message. However when I clicked the comand button I didn't get anything!!?

The full code is as follows:

Dim db As Database
Dim rst As Recordset
Dim mySQL As String
Dim mySTRING As String

Set db = CurrentDb

mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName, " & _
"tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?], " & _
"tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + " FROM tblPeople "
mySQL = mySQL + " WHERE tblPeople.UCLanEmail & """" <> """" " & _
"And tblPeople.[IncludeInEmail?] = True " & _
" And tblPeople.[IncubationClient?] = False " & _
" And tblPeople.[Archive?] = False "
mySQL = mySQL + " ORDER BY tblPeople.LastName;"

Set rst = db.OpenRecordset(mySQL, dbOpenDynaset)

rst.MoveFirst


If Not rst.EOF Then

rst.MoveFirst

mySTRING = ""

Do Until rst.EOF
mySTRING = mySTRING + rst!UCLanEmail & ";"
rst.MoveNext
Loop

lngLen = Len(mySTRING)
If lngLen > 0 Then
mySTRING = Left$(mySTRING, lngLen - 1)

DoCmd.SendObject , , , , , mySTRING, , , True


End If

End If
End Sub


The DoCMDSend works fine, but if I end the SQL at "From tblPeople;" I get
an
error message "unknown receipetnt", yet the Email facility hasn't been
invoked yet. I'm not very profiecnet at using the "break" and immediate
window, but with my first atempts, I think the SQL is returning an empty
file, yet I know that a number of people have an UClanEmail address.

I'm at a loss as to how to progress this?

Thankyou for your help with this.

Regards

Winger
Click to expand...
Click to expand...
Click to expand...
 
W

Winger

John,

I'm still getting syntax errors.

I'm guesing that ....client"]<> in your code is just a typo, becuase
the " is quickly highlighted by the compiler as a error.
However, even with the two versions of your code, it compiles but still runs
and gives a syntax error.

Version1(Gives syntax error)
mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName, " & _
"tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?], " & _
"tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + " FROM tblPeople "
mySQL = mySQL + " WHERE tblPeople.UCLanEmail isNot NUll " & _
"And tblPeople.[IncludeInEmail?] = True " & _
" AND tblPeople.[IncubationClient]<> ""No"" " & _
" And tblPeople.[Archive?] = False "
mySQL = mySQL + " ORDER BY tblPeople.LastName;"

Version2 (Gives error -too few parameteres)
mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName, " & _
"tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?], " & _
"tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + " FROM tblPeople "
mySQL = mySQL + " WHERE tblPeople.UCLanEmail is Not NUll " & _
"And tblPeople.[IncludeInEmail?] = True " & _
" AND tblPeople.[IncubationClient]<> 'No' " & _
" And tblPeople.[Archive?] = False "
mySQL = mySQL + " ORDER BY tblPeople.LastName;"


thanks

Peter




John Spencer said:
Leave out the Chr(34) calls. They are not needed in situation.

" AND tblPeople.[IncubationClient"]<> 'No' " & _

If you wish to use quotes then double up on them.


" AND tblPeople.[IncubationClient"]<> ""No"" " & _

When building a string putting in two quotes in a row will return one
quote in the string that is built.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,

Thanks for the tips - debug.print is very useful along with pasting back
into query.

The error is in my part. I'd forgotton that I'd changed
tblPeople.[IncubationClient?] into a TEXT field with a lookup of YES/NO.

So the code is producing no reocrds.

I need to change the Where clause - I've had a go at:

And tblPeople.[IncubationClient?] <> &chr$(34)& 'No' & chr$(34)& " &

but get a syntax error, as does

And tblPeople.[IncubationClient?] <> "No" " & _

How do I reference the value of a TEXT field and get all the "" "" "" correct?

thanks

Winger





John Spencer said:
Try Adding a

debug.print MySQL

into your code right after the MySQL varaiable is populated and then
checking the SQL that is generated correctly. You should be able to copy
the SQL into a new query and run the query. If you get no records back then
you know you need to work on the SQL string. If you do get records back,
then check them to see what values you are getting. And if that looks good
start stepping through your code to find where the error occurs.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,

Thanks for this. I dropped the code straight in and didn't get an error
message. However when I clicked the comand button I didn't get anything!!?

The full code is as follows:

Dim db As Database
Dim rst As Recordset
Dim mySQL As String
Dim mySTRING As String

Set db = CurrentDb

mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName, " & _
"tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?], " & _
"tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + " FROM tblPeople "
mySQL = mySQL + " WHERE tblPeople.UCLanEmail & """" <> """" " & _
"And tblPeople.[IncludeInEmail?] = True " & _
" And tblPeople.[IncubationClient?] = False " & _
" And tblPeople.[Archive?] = False "
mySQL = mySQL + " ORDER BY tblPeople.LastName;"

Set rst = db.OpenRecordset(mySQL, dbOpenDynaset)

rst.MoveFirst


If Not rst.EOF Then

rst.MoveFirst

mySTRING = ""

Do Until rst.EOF
mySTRING = mySTRING + rst!UCLanEmail & ";"
rst.MoveNext
Loop

lngLen = Len(mySTRING)
If lngLen > 0 Then
mySTRING = Left$(mySTRING, lngLen - 1)

DoCmd.SendObject , , , , , mySTRING, , , True


End If

End If
End Sub


The DoCMDSend works fine, but if I end the SQL at "From tblPeople;" I get
an
error message "unknown receipetnt", yet the Email facility hasn't been
invoked yet. I'm not very profiecnet at using the "break" and immediate
window, but with my first atempts, I think the SQL is returning an empty
file, yet I know that a number of people have an UClanEmail address.

I'm at a loss as to how to progress this?

Thankyou for your help with this.

Regards

Winger
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Version one (as posted) has
IsNot Null
instead of
Is Not Null

Version two:
Too few Parameters almost always means that there is a typo in a field name
or a tablename.

As I said before, use Debug.Print MySQL to get the SQL string in the VBA
immediate window.
Copy the string and paste it into a new query.
You can then try to run the query.
If there are misspelled table or field names, you will get a parameter
prompt for the misspelled name. Fix the misspelling in your code and try
again.

If there is a syntax error, you will get a better description of the exact
error and the place where the error is occuring will probably be
highlighted.

When I build a query in VBA I almost always add
Debug.Print mySQL : STOP
as a line in the code before I try to use the SQL statement that I have
built. This stops the code and allows me to examine what I've built. If I
have any doubts about the SQL string I can then stop the process that is
running, copy the sql to a new query and check it there.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Winger said:
John,

I'm still getting syntax errors.

I'm guesing that ....client"]<> in your code is just a typo,
becuase
the " is quickly highlighted by the compiler as a error.
However, even with the two versions of your code, it compiles but still
runs
and gives a syntax error.

Version1(Gives syntax error)
mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName, " & _
"tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?], " & _
"tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + " FROM tblPeople "
mySQL = mySQL + " WHERE tblPeople.UCLanEmail isNot NUll " & _
"And tblPeople.[IncludeInEmail?] = True " & _
" AND tblPeople.[IncubationClient]<> ""No"" " & _
" And tblPeople.[Archive?] = False "
mySQL = mySQL + " ORDER BY tblPeople.LastName;"

Version2 (Gives error -too few parameteres)
mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName, " & _
"tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?], " & _
"tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + " FROM tblPeople "
mySQL = mySQL + " WHERE tblPeople.UCLanEmail is Not NUll " & _
"And tblPeople.[IncludeInEmail?] = True " & _
" AND tblPeople.[IncubationClient]<> 'No' " & _
" And tblPeople.[Archive?] = False "
mySQL = mySQL + " ORDER BY tblPeople.LastName;"


thanks

Peter




John Spencer said:
Leave out the Chr(34) calls. They are not needed in situation.

" AND tblPeople.[IncubationClient"]<> 'No' " & _

If you wish to use quotes then double up on them.


" AND tblPeople.[IncubationClient"]<> ""No"" " & _

When building a string putting in two quotes in a row will return one
quote in the string that is built.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,

Thanks for the tips - debug.print is very useful along with pasting
back
into query.

The error is in my part. I'd forgotton that I'd changed
tblPeople.[IncubationClient?] into a TEXT field with a lookup of
YES/NO.

So the code is producing no reocrds.

I need to change the Where clause - I've had a go at:

And tblPeople.[IncubationClient?] <> &chr$(34)& 'No' & chr$(34)& " &

but get a syntax error, as does

And tblPeople.[IncubationClient?] <> "No" " & _

How do I reference the value of a TEXT field and get all the "" "" ""
correct?

thanks

Winger





:

Try Adding a

debug.print MySQL

into your code right after the MySQL varaiable is populated and then
checking the SQL that is generated correctly. You should be able to
copy
the SQL into a new query and run the query. If you get no records
back then
you know you need to work on the SQL string. If you do get records
back,
then check them to see what values you are getting. And if that looks
good
start stepping through your code to find where the error occurs.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,

Thanks for this. I dropped the code straight in and didn't get an
error
message. However when I clicked the comand button I didn't get
anything!!?

The full code is as follows:

Dim db As Database
Dim rst As Recordset
Dim mySQL As String
Dim mySTRING As String

Set db = CurrentDb

mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName, " & _
"tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?], " & _
"tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + " FROM tblPeople "
mySQL = mySQL + " WHERE tblPeople.UCLanEmail & """" <> """" " & _
"And tblPeople.[IncludeInEmail?] = True " & _
" And tblPeople.[IncubationClient?] = False " & _
" And tblPeople.[Archive?] = False "
mySQL = mySQL + " ORDER BY tblPeople.LastName;"

Set rst = db.OpenRecordset(mySQL, dbOpenDynaset)

rst.MoveFirst


If Not rst.EOF Then

rst.MoveFirst

mySTRING = ""

Do Until rst.EOF
mySTRING = mySTRING + rst!UCLanEmail & ";"
rst.MoveNext
Loop

lngLen = Len(mySTRING)
If lngLen > 0 Then
mySTRING = Left$(mySTRING, lngLen - 1)

DoCmd.SendObject , , , , , mySTRING, , , True


End If

End If
End Sub


The DoCMDSend works fine, but if I end the SQL at "From tblPeople;" I
get
an
error message "unknown receipetnt", yet the Email facility hasn't
been
invoked yet. I'm not very profiecnet at using the "break" and
immediate
window, but with my first atempts, I think the SQL is returning an
empty
file, yet I know that a number of people have an UClanEmail address.

I'm at a loss as to how to progress this?

Thankyou for your help with this.

Regards

Winger
Click to expand...
Click to expand...
Click to expand...
 
W

Winger

John,

I had to go for a work-around to get moving on this. I've now amended the
TEXT field to a yes/no field so that I don't need to use " " in the SQL. I
used some tips you gave in one of the earlier posts to get this working, so
have marked it as solving the problem.

Thank you very much for your patience on this, it really was appreciated.
I've really learnt some great tips and my progamming has definatly moved on a
stage. I'm using debug.print / dropping the generated sql back into a query
and getting much slicker at it.

I'm not sure if I'll ever get my head round use of " " 's in SQl query. But
at least the solution we've hit apon saves my poor administrator a lot of
time in sending out our emails over the next few weeks.

Many thanks for you help.

Regards

Winger



John Spencer said:
Version one (as posted) has
IsNot Null
instead of
Is Not Null

Version two:
Too few Parameters almost always means that there is a typo in a field name
or a tablename.

As I said before, use Debug.Print MySQL to get the SQL string in the VBA
immediate window.
Copy the string and paste it into a new query.
You can then try to run the query.
If there are misspelled table or field names, you will get a parameter
prompt for the misspelled name. Fix the misspelling in your code and try
again.

If there is a syntax error, you will get a better description of the exact
error and the place where the error is occuring will probably be
highlighted.

When I build a query in VBA I almost always add
Debug.Print mySQL : STOP
as a line in the code before I try to use the SQL statement that I have
built. This stops the code and allows me to examine what I've built. If I
have any doubts about the SQL string I can then stop the process that is
running, copy the sql to a new query and check it there.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Winger said:
John,

I'm still getting syntax errors.

I'm guesing that ....client"]<> in your code is just a typo,
becuase
the " is quickly highlighted by the compiler as a error.
However, even with the two versions of your code, it compiles but still
runs
and gives a syntax error.

Version1(Gives syntax error)
mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName, " & _
"tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?], " & _
"tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + " FROM tblPeople "
mySQL = mySQL + " WHERE tblPeople.UCLanEmail isNot NUll " & _
"And tblPeople.[IncludeInEmail?] = True " & _
" AND tblPeople.[IncubationClient]<> ""No"" " & _
" And tblPeople.[Archive?] = False "
mySQL = mySQL + " ORDER BY tblPeople.LastName;"

Version2 (Gives error -too few parameteres)
mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName, " & _
"tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?], " & _
"tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + " FROM tblPeople "
mySQL = mySQL + " WHERE tblPeople.UCLanEmail is Not NUll " & _
"And tblPeople.[IncludeInEmail?] = True " & _
" AND tblPeople.[IncubationClient]<> 'No' " & _
" And tblPeople.[Archive?] = False "
mySQL = mySQL + " ORDER BY tblPeople.LastName;"


thanks

Peter




John Spencer said:
Leave out the Chr(34) calls. They are not needed in situation.

" AND tblPeople.[IncubationClient"]<> 'No' " & _

If you wish to use quotes then double up on them.


" AND tblPeople.[IncubationClient"]<> ""No"" " & _

When building a string putting in two quotes in a row will return one
quote in the string that is built.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Winger wrote:
John,

Thanks for the tips - debug.print is very useful along with pasting
back
into query.

The error is in my part. I'd forgotton that I'd changed
tblPeople.[IncubationClient?] into a TEXT field with a lookup of
YES/NO.

So the code is producing no reocrds.

I need to change the Where clause - I've had a go at:

And tblPeople.[IncubationClient?] <> &chr$(34)& 'No' & chr$(34)& " &

but get a syntax error, as does

And tblPeople.[IncubationClient?] <> "No" " & _

How do I reference the value of a TEXT field and get all the "" "" ""
correct?

thanks

Winger





:

Try Adding a

debug.print MySQL

into your code right after the MySQL varaiable is populated and then
checking the SQL that is generated correctly. You should be able to
copy
the SQL into a new query and run the query. If you get no records
back then
you know you need to work on the SQL string. If you do get records
back,
then check them to see what values you are getting. And if that looks
good
start stepping through your code to find where the error occurs.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,

Thanks for this. I dropped the code straight in and didn't get an
error
message. However when I clicked the comand button I didn't get
anything!!?

The full code is as follows:

Dim db As Database
Dim rst As Recordset
Dim mySQL As String
Dim mySTRING As String

Set db = CurrentDb

mySQL = "SELECT tblPeople.FirstName, tblPeople.LastName, " & _
"tblPeople.UCLanEmail, tblPeople.[IncludeInEmail?], " & _
"tblPeople.[IncubationClient?], tblPeople.[Archive?] "
mySQL = mySQL + " FROM tblPeople "
mySQL = mySQL + " WHERE tblPeople.UCLanEmail & """" <> """" " & _
"And tblPeople.[IncludeInEmail?] = True " & _
" And tblPeople.[IncubationClient?] = False " & _
" And tblPeople.[Archive?] = False "
mySQL = mySQL + " ORDER BY tblPeople.LastName;"

Set rst = db.OpenRecordset(mySQL, dbOpenDynaset)

rst.MoveFirst


If Not rst.EOF Then

rst.MoveFirst

mySTRING = ""

Do Until rst.EOF
mySTRING = mySTRING + rst!UCLanEmail & ";"
rst.MoveNext
Loop

lngLen = Len(mySTRING)
If lngLen > 0 Then
mySTRING = Left$(mySTRING, lngLen - 1)

DoCmd.SendObject , , , , , mySTRING, , , True


End If

End If
End Sub


The DoCMDSend works fine, but if I end the SQL at "From tblPeople;" I
get
an
error message "unknown receipetnt", yet the Email facility hasn't
been
invoked yet. I'm not very profiecnet at using the "break" and
immediate
window, but with my first atempts, I think the SQL is returning an
empty
file, yet I know that a number of people have an UClanEmail address.

I'm at a loss as to how to progress this?

Thankyou for your help with this.

Regards

Winger
Click to expand...
Click to expand...
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

checkbox doesn't filter query 7
Return records from one table for both parent and children? 4
Updateable max query? 3
How do I FindNext in Recordset.clone 0
Design Updateable Query 2
SQL for specific pattern of output? 2
Combo Box Source from Same Table 4
Empty Recordset 1

Top