resolving parameters in a query

[Guest]

Dear All


I apologise in advance that this is a long post I am just trying to give you
all the info you will need.

I have posted this before but have been unable to get my code working.

I want to email a copy of a report (converted to PDF) to all the email
addresses resolved from a query,

the parameter comes from the form control Forms![Events1]![EventID] and in
the query it is [em] the query is called Emailteachers

the error I get when running the code is:

Run Time Error 94 Invalid use of NUll: The following line is highlighted

strEmail = .Fields("email")

Here is the code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Dim strEmail As String
'Dim sParam As DAO.Parameter
Dim sParam As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Emailteacher")
sParam = Nz(Forms![Events1]![EventID], "")
qdf.Parameters![Em] = sParam

Set rst = qdf.OpenRecordset

With rst
If (Not .BOF) And (Not .EOF) Then
.MoveFirst
strEmail = .Fields("email")
.MoveNext
End If

If (Not .BOF) And (Not .EOF) Then
Do Until .EOF
strEmail = strEmail & ", " & .Fields("email")
.MoveNext
Loop
End If
.Close
End With

EMailAsPDF "Timetable", strEmail, Me.[EventName] & " " & "Cohort " &
Me.[Cohort] & " Timetable", "Enter Your Text Here"

I do hope someone can help, I always seem to fall down when I am using
expressions with null

thanks

Phil

 

[Baz]

strEmail = Nz(.Fields("email"))

 

[Guest]

Hi Baz

that has sorted that problem thanks, but has generated more problems I will
try to resolve them and start a new post if I cannot thanks

Phil

strEmail = Nz(.Fields("email"))

Dear All


I apologise in advance that this is a long post I am just trying to give you
all the info you will need.

I have posted this before but have been unable to get my code working.

I want to email a copy of a report (converted to PDF) to all the email
addresses resolved from a query,

the parameter comes from the form control Forms![Events1]![EventID] and in
the query it is [em] the query is called Emailteachers

the error I get when running the code is:

Run Time Error 94 Invalid use of NUll: The following line is highlighted

strEmail = .Fields("email")

Here is the code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Dim strEmail As String
'Dim sParam As DAO.Parameter
Dim sParam As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Emailteacher")
sParam = Nz(Forms![Events1]![EventID], "")
qdf.Parameters![Em] = sParam

Set rst = qdf.OpenRecordset

With rst
If (Not .BOF) And (Not .EOF) Then
.MoveFirst
strEmail = .Fields("email")
.MoveNext
End If

If (Not .BOF) And (Not .EOF) Then
Do Until .EOF
strEmail = strEmail & ", " & .Fields("email")
.MoveNext
Loop
End If
.Close
End With

EMailAsPDF "Timetable", strEmail, Me.[EventName] & " " & "Cohort " &
Me.[Cohort] & " Timetable", "Enter Your Text Here"

I do hope someone can help, I always seem to fall down when I am using
expressions with null

thanks

Phil

 

[Guest]

Phil,

The recordset automatically opens to the first record, so you don't really
need to test for BOF/EOF and do a move first. Just create a loop to
determine whether you are at the EOF marker.

Basically, the problem you are having is that you have some email addresses
that are NULL, when you try to concatenate them to your strEmail, you are
getting an error. I disagree with the way the BAZ handled this, just
converting the null values to a string will not solve your email address
problems as you will have a bunch of blanks that your email program may not
be able to resolve. You actually should just ignore these values altogether.
You might also need to use a semi-colon as the separater between your
values.

The first thing I would do is change strEmail to varEmail. This way, you
can add a NULL value to it without causing an error.

By wrapping the reference to rst.Fields("email") in the parenthesis below,
and using the "+" to concatenate the comma seperator to it, you will
actually add a NULL to a string, resulting in a NULL. This ensures that if
the email address is null, you don't add extraneous commas to your string
(variant) as well.

Note, you should test the length of varEmail prior to calling your
EmailAsPDF code. If the LEN(NZ(varEmail, "")) = 0 then there were no valid
email addresses.

Replace your With ... End With code with:

Do While not rst.EOF

varEmail = varEmail & (", " + rst.Fields("email"))
rst.movenext

Loop
rst.close
Set rst = nothing

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Dear All


I apologise in advance that this is a long post I am just trying to give you
all the info you will need.

I have posted this before but have been unable to get my code working.

I want to email a copy of a report (converted to PDF) to all the email
addresses resolved from a query,

the parameter comes from the form control Forms![Events1]![EventID] and in
the query it is [em] the query is called Emailteachers

the error I get when running the code is:

Run Time Error 94 Invalid use of NUll: The following line is highlighted

strEmail = .Fields("email")

Here is the code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Dim strEmail As String
'Dim sParam As DAO.Parameter
Dim sParam As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Emailteacher")
sParam = Nz(Forms![Events1]![EventID], "")
qdf.Parameters![Em] = sParam

Set rst = qdf.OpenRecordset

With rst
If (Not .BOF) And (Not .EOF) Then
.MoveFirst
strEmail = .Fields("email")
.MoveNext
End If

If (Not .BOF) And (Not .EOF) Then
Do Until .EOF
strEmail = strEmail & ", " & .Fields("email")
.MoveNext
Loop
End If
.Close
End With

EMailAsPDF "Timetable", strEmail, Me.[EventName] & " " & "Cohort " &
Me.[Cohort] & " Timetable", "Enter Your Text Here"

I do hope someone can help, I always seem to fall down when I am using
expressions with null

thanks

Phil

 

[Baz]

The OP indicated that he was quite happy to attempt to take it further
himself, hopefully he is learning a lot in the process.

I disagree with YOUR solution of using a variant. IMO variants are a
necessary evil to be avoided whenever there is an alternative e.g. use Nz as
I did, and then test for a zero-length string. Also, we have no idea
whether the field in the underlying table allows zero-length values, and
your suggested approach does not address that possibility.

Phil,

The recordset automatically opens to the first record, so you don't really
need to test for BOF/EOF and do a move first. Just create a loop to
determine whether you are at the EOF marker.

Basically, the problem you are having is that you have some email addresses
that are NULL, when you try to concatenate them to your strEmail, you are
getting an error. I disagree with the way the BAZ handled this, just
converting the null values to a string will not solve your email address
problems as you will have a bunch of blanks that your email program may not
be able to resolve. You actually should just ignore these values altogether.
You might also need to use a semi-colon as the separater between your
values.

The first thing I would do is change strEmail to varEmail. This way, you
can add a NULL value to it without causing an error.

By wrapping the reference to rst.Fields("email") in the parenthesis below,
and using the "+" to concatenate the comma seperator to it, you will
actually add a NULL to a string, resulting in a NULL. This ensures that if
the email address is null, you don't add extraneous commas to your string
(variant) as well.

Note, you should test the length of varEmail prior to calling your
EmailAsPDF code. If the LEN(NZ(varEmail, "")) = 0 then there were no valid
email addresses.

Replace your With ... End With code with:

Do While not rst.EOF

varEmail = varEmail & (", " + rst.Fields("email"))
rst.movenext

Loop
rst.close
Set rst = nothing

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Dear All


I apologise in advance that this is a long post I am just trying to give you
all the info you will need.

I have posted this before but have been unable to get my code working.

I want to email a copy of a report (converted to PDF) to all the email
addresses resolved from a query,

the parameter comes from the form control Forms![Events1]![EventID] and in
the query it is [em] the query is called Emailteachers

the error I get when running the code is:

Run Time Error 94 Invalid use of NUll: The following line is highlighted

strEmail = .Fields("email")

Here is the code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Dim strEmail As String
'Dim sParam As DAO.Parameter
Dim sParam As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Emailteacher")
sParam = Nz(Forms![Events1]![EventID], "")
qdf.Parameters![Em] = sParam

Set rst = qdf.OpenRecordset

With rst
If (Not .BOF) And (Not .EOF) Then
.MoveFirst
strEmail = .Fields("email")
.MoveNext
End If

If (Not .BOF) And (Not .EOF) Then
Do Until .EOF
strEmail = strEmail & ", " & .Fields("email")
.MoveNext
Loop
End If
.Close
End With

EMailAsPDF "Timetable", strEmail, Me.[EventName] & " " & "Cohort " &
Me.[Cohort] & " Timetable", "Enter Your Text Here"

I do hope someone can help, I always seem to fall down when I am using
expressions with null

thanks

Phil

 

[Guest]

Hi Baz

The further problems I alluded to were of my own making, as I stated in a
later post I had actually unknowingly changed the query that generated the
timetable, The Nz function resolved all my issues, I have actually tested it
with null vaues for email addresses and all null values, I do not get any
errors or problems. thanks for your post Dale i will keep it in mind.

thanks

Phil

The OP indicated that he was quite happy to attempt to take it further
himself, hopefully he is learning a lot in the process.

I disagree with YOUR solution of using a variant. IMO variants are a
necessary evil to be avoided whenever there is an alternative e.g. use Nz as
I did, and then test for a zero-length string. Also, we have no idea
whether the field in the underlying table allows zero-length values, and
your suggested approach does not address that possibility.

Phil,

The recordset automatically opens to the first record, so you don't really
need to test for BOF/EOF and do a move first. Just create a loop to
determine whether you are at the EOF marker.

Basically, the problem you are having is that you have some email addresses
that are NULL, when you try to concatenate them to your strEmail, you are
getting an error. I disagree with the way the BAZ handled this, just
converting the null values to a string will not solve your email address
problems as you will have a bunch of blanks that your email program may not
be able to resolve. You actually should just ignore these values altogether.
You might also need to use a semi-colon as the separater between your
values.

The first thing I would do is change strEmail to varEmail. This way, you
can add a NULL value to it without causing an error.

By wrapping the reference to rst.Fields("email") in the parenthesis below,
and using the "+" to concatenate the comma seperator to it, you will
actually add a NULL to a string, resulting in a NULL. This ensures that if
the email address is null, you don't add extraneous commas to your string
(variant) as well.

Note, you should test the length of varEmail prior to calling your
EmailAsPDF code. If the LEN(NZ(varEmail, "")) = 0 then there were no valid
email addresses.

Replace your With ... End With code with:

Do While not rst.EOF

varEmail = varEmail & (", " + rst.Fields("email"))
rst.movenext

Loop
rst.close
Set rst = nothing

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Dear All


I apologise in advance that this is a long post I am just trying to give you
all the info you will need.

I have posted this before but have been unable to get my code working.

I want to email a copy of a report (converted to PDF) to all the email
addresses resolved from a query,

the parameter comes from the form control Forms![Events1]![EventID] and in
the query it is [em] the query is called Emailteachers

the error I get when running the code is:

Run Time Error 94 Invalid use of NUll: The following line is highlighted

strEmail = .Fields("email")

Here is the code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Dim strEmail As String
'Dim sParam As DAO.Parameter
Dim sParam As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Emailteacher")
sParam = Nz(Forms![Events1]![EventID], "")
qdf.Parameters![Em] = sParam

Set rst = qdf.OpenRecordset

With rst
If (Not .BOF) And (Not .EOF) Then
.MoveFirst
strEmail = .Fields("email")
.MoveNext
End If

If (Not .BOF) And (Not .EOF) Then
Do Until .EOF
strEmail = strEmail & ", " & .Fields("email")
.MoveNext
Loop
End If
.Close
End With

EMailAsPDF "Timetable", strEmail, Me.[EventName] & " " & "Cohort " &
Me.[Cohort] & " Timetable", "Enter Your Text Here"

I do hope someone can help, I always seem to fall down when I am using
expressions with null

thanks

Phil

 

[Baz]

Great, do come back if more problems.

Hi Baz

The further problems I alluded to were of my own making, as I stated in a
later post I had actually unknowingly changed the query that generated the
timetable, The Nz function resolved all my issues, I have actually tested it
with null vaues for email addresses and all null values, I do not get any
errors or problems. thanks for your post Dale i will keep it in mind.

thanks

Phil

The OP indicated that he was quite happy to attempt to take it further
himself, hopefully he is learning a lot in the process.

I disagree with YOUR solution of using a variant. IMO variants are a
necessary evil to be avoided whenever there is an alternative e.g. use Nz as
I did, and then test for a zero-length string. Also, we have no idea
whether the field in the underlying table allows zero-length values, and
your suggested approach does not address that possibility.

Phil,

The recordset automatically opens to the first record, so you don't really
need to test for BOF/EOF and do a move first. Just create a loop to
determine whether you are at the EOF marker.

Basically, the problem you are having is that you have some email addresses
that are NULL, when you try to concatenate them to your strEmail, you are
getting an error. I disagree with the way the BAZ handled this, just
converting the null values to a string will not solve your email address
problems as you will have a bunch of blanks that your email program

may
not

be able to resolve. You actually should just ignore these values altogether.
You might also need to use a semi-colon as the separater between your
values.

The first thing I would do is change strEmail to varEmail. This way, you
can add a NULL value to it without causing an error.

By wrapping the reference to rst.Fields("email") in the parenthesis below,
and using the "+" to concatenate the comma seperator to it, you will
actually add a NULL to a string, resulting in a NULL. This ensures

that
if

the email address is null, you don't add extraneous commas to your string
(variant) as well.

Note, you should test the length of varEmail prior to calling your
EmailAsPDF code. If the LEN(NZ(varEmail, "")) = 0 then there were no valid
email addresses.

Replace your With ... End With code with:

Do While not rst.EOF

varEmail = varEmail & (", " + rst.Fields("email"))
rst.movenext

Loop
rst.close
Set rst = nothing

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

Dear All


I apologise in advance that this is a long post I am just trying to

give
you

all the info you will need.

I have posted this before but have been unable to get my code working.

I want to email a copy of a report (converted to PDF) to all the email
addresses resolved from a query,

the parameter comes from the form control Forms![Events1]![EventID]

and
in

the query it is [em] the query is called Emailteachers

the error I get when running the code is:

Run Time Error 94 Invalid use of NUll: The following line is highlighted

strEmail = .Fields("email")

Here is the code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Dim strEmail As String
'Dim sParam As DAO.Parameter
Dim sParam As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Emailteacher")
sParam = Nz(Forms![Events1]![EventID], "")
qdf.Parameters![Em] = sParam

Set rst = qdf.OpenRecordset

With rst
If (Not .BOF) And (Not .EOF) Then
.MoveFirst
strEmail = .Fields("email")
.MoveNext
End If

If (Not .BOF) And (Not .EOF) Then
Do Until .EOF
strEmail = strEmail & ", " & .Fields("email")
.MoveNext
Loop
End If
.Close
End With

EMailAsPDF "Timetable", strEmail, Me.[EventName] & " " & "Cohort " &
Me.[Cohort] & " Timetable", "Enter Your Text Here"

I do hope someone can help, I always seem to fall down when I am using
expressions with null

thanks

Phil

 

[Guest]

Baz,

I don't have a problem with the NZ function, I just disagreed with using it
the way you did. Where in your original post did you mention anything about
testing or dealing with zero length strings? If you are averse to using
variants (they are very handy in some instances), then another way to skin
the cat would have been the following. Either way avoids appending a zero
length string to the list of email addresses.
IF LEN(NZ(rs.Fields("Email"), "") <> 0 then
strEmail = strEmail & ", " & rst.Fields("email"))
EndIF
Dale
--
Email address is not valid.
Please reply to newsgroup only.

The OP indicated that he was quite happy to attempt to take it further
himself, hopefully he is learning a lot in the process.

I disagree with YOUR solution of using a variant. IMO variants are a
necessary evil to be avoided whenever there is an alternative e.g. use Nz as
I did, and then test for a zero-length string. Also, we have no idea
whether the field in the underlying table allows zero-length values, and
your suggested approach does not address that possibility.

Phil,

The recordset automatically opens to the first record, so you don't really
need to test for BOF/EOF and do a move first. Just create a loop to
determine whether you are at the EOF marker.

Basically, the problem you are having is that you have some email addresses
that are NULL, when you try to concatenate them to your strEmail, you are
getting an error. I disagree with the way the BAZ handled this, just
converting the null values to a string will not solve your email address
problems as you will have a bunch of blanks that your email program may not
be able to resolve. You actually should just ignore these values altogether.
You might also need to use a semi-colon as the separater between your
values.

The first thing I would do is change strEmail to varEmail. This way, you
can add a NULL value to it without causing an error.

By wrapping the reference to rst.Fields("email") in the parenthesis below,
and using the "+" to concatenate the comma seperator to it, you will
actually add a NULL to a string, resulting in a NULL. This ensures that if
the email address is null, you don't add extraneous commas to your string
(variant) as well.

Note, you should test the length of varEmail prior to calling your
EmailAsPDF code. If the LEN(NZ(varEmail, "")) = 0 then there were no valid
email addresses.

Replace your With ... End With code with:

Do While not rst.EOF

varEmail = varEmail & (", " + rst.Fields("email"))
rst.movenext

Loop
rst.close
Set rst = nothing

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Dear All


I apologise in advance that this is a long post I am just trying to give you
all the info you will need.

I have posted this before but have been unable to get my code working.

I want to email a copy of a report (converted to PDF) to all the email
addresses resolved from a query,

the parameter comes from the form control Forms![Events1]![EventID] and in
the query it is [em] the query is called Emailteachers

the error I get when running the code is:

Run Time Error 94 Invalid use of NUll: The following line is highlighted

strEmail = .Fields("email")

Here is the code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Dim strEmail As String
'Dim sParam As DAO.Parameter
Dim sParam As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Emailteacher")
sParam = Nz(Forms![Events1]![EventID], "")
qdf.Parameters![Em] = sParam

Set rst = qdf.OpenRecordset

With rst
If (Not .BOF) And (Not .EOF) Then
.MoveFirst
strEmail = .Fields("email")
.MoveNext
End If

If (Not .BOF) And (Not .EOF) Then
Do Until .EOF
strEmail = strEmail & ", " & .Fields("email")
.MoveNext
Loop
End If
.Close
End With

EMailAsPDF "Timetable", strEmail, Me.[EventName] & " " & "Cohort " &
Me.[Cohort] & " Timetable", "Enter Your Text Here"

I do hope someone can help, I always seem to fall down when I am using
expressions with null

thanks

Phil

 

[Baz]

I didn't mention anything about testing for zero length strings. I credited
the OP with sufficient intelligence to finish the job himself having solved
his immediate problem, and it seems I was right. AFAIK helping a poster
with a specific problem carries no obligation to finish the rest of his
application for him.

If you are determined to try to teach your grandmother to suck eggs, allow
me to return the compliment:

Dim strEmail As String
Dim strComma As String

Do Until rst.EOF
strEmail = Nz(rst!Email)
If strEmail <> "" Then
strEmail = strComma & strEmail
strComma = ","
End If
rst.MoveNext
Loop

A bit snappier than your somewhat tortured offering, apart from having to
deal with the leading comma which you have overlooked.

Baz,

I don't have a problem with the NZ function, I just disagreed with using it
the way you did. Where in your original post did you mention anything about
testing or dealing with zero length strings? If you are averse to using
variants (they are very handy in some instances), then another way to skin
the cat would have been the following. Either way avoids appending a zero
length string to the list of email addresses.
IF LEN(NZ(rs.Fields("Email"), "") <> 0 then
strEmail = strEmail & ", " & rst.Fields("email"))
EndIF
Dale
--
Email address is not valid.
Please reply to newsgroup only.

The OP indicated that he was quite happy to attempt to take it further
himself, hopefully he is learning a lot in the process.

I disagree with YOUR solution of using a variant. IMO variants are a
necessary evil to be avoided whenever there is an alternative e.g. use Nz as
I did, and then test for a zero-length string. Also, we have no idea
whether the field in the underlying table allows zero-length values, and
your suggested approach does not address that possibility.

Phil,

The recordset automatically opens to the first record, so you don't really
need to test for BOF/EOF and do a move first. Just create a loop to
determine whether you are at the EOF marker.

Basically, the problem you are having is that you have some email addresses
that are NULL, when you try to concatenate them to your strEmail, you are
getting an error. I disagree with the way the BAZ handled this, just
converting the null values to a string will not solve your email address
problems as you will have a bunch of blanks that your email program

may
not

be able to resolve. You actually should just ignore these values altogether.
You might also need to use a semi-colon as the separater between your
values.

The first thing I would do is change strEmail to varEmail. This way, you
can add a NULL value to it without causing an error.

By wrapping the reference to rst.Fields("email") in the parenthesis below,
and using the "+" to concatenate the comma seperator to it, you will
actually add a NULL to a string, resulting in a NULL. This ensures

that
if

the email address is null, you don't add extraneous commas to your string
(variant) as well.

Note, you should test the length of varEmail prior to calling your
EmailAsPDF code. If the LEN(NZ(varEmail, "")) = 0 then there were no valid
email addresses.

Replace your With ... End With code with:

Do While not rst.EOF

varEmail = varEmail & (", " + rst.Fields("email"))
rst.movenext

Loop
rst.close
Set rst = nothing

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

Dear All


I apologise in advance that this is a long post I am just trying to

give
you

all the info you will need.

I have posted this before but have been unable to get my code working.

I want to email a copy of a report (converted to PDF) to all the email
addresses resolved from a query,

the parameter comes from the form control Forms![Events1]![EventID]

and
in

the query it is [em] the query is called Emailteachers

the error I get when running the code is:

Run Time Error 94 Invalid use of NUll: The following line is highlighted

strEmail = .Fields("email")

Here is the code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Dim strEmail As String
'Dim sParam As DAO.Parameter
Dim sParam As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Emailteacher")
sParam = Nz(Forms![Events1]![EventID], "")
qdf.Parameters![Em] = sParam

Set rst = qdf.OpenRecordset

With rst
If (Not .BOF) And (Not .EOF) Then
.MoveFirst
strEmail = .Fields("email")
.MoveNext
End If

If (Not .BOF) And (Not .EOF) Then
Do Until .EOF
strEmail = strEmail & ", " & .Fields("email")
.MoveNext
Loop
End If
.Close
End With

EMailAsPDF "Timetable", strEmail, Me.[EventName] & " " & "Cohort " &
Me.[Cohort] & " Timetable", "Enter Your Text Here"

I do hope someone can help, I always seem to fall down when I am using
expressions with null

thanks

Phil

 

[Douglas J. Steele]

Why would you reset strComma repeatedly?

Dim strEmail As String
Dim strComma As String

strComma = ","

Do Until rst.EOF
strEmail = Nz(rst!Email)
If strEmail <> "" Then
strEmail = strComma & strEmail
End If
rst.MoveNext
Loop

Actually I think Dale's suggestion of varEmail = varEmail & (", " +
rst.Fields("email")) was a good one, although there really was no reason
for him to suggest changing strEmail to varEmail. Even though (", " +
rst.Fields("email")) may evaluate to a Null, using & to concatenate a string
to a Null results in the original string. that means that the following
should work fine:

Dim strEmail As String
Dim strComma As String

strComma = ","

Do Until rst.EOF
strEmail = strEmail & (strComma + rst!Email)
rst.MoveNext
Loop

' Get rid of first comma (if there is one)
If Len(strEmail) > 0 Then
strEmail = Mid(strEmail, 2)
End If


-
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I didn't mention anything about testing for zero length strings. I
credited
the OP with sufficient intelligence to finish the job himself having
solved
his immediate problem, and it seems I was right. AFAIK helping a poster
with a specific problem carries no obligation to finish the rest of his
application for him.

If you are determined to try to teach your grandmother to suck eggs, allow
me to return the compliment:

Dim strEmail As String
Dim strComma As String

Do Until rst.EOF
strEmail = Nz(rst!Email)
If strEmail <> "" Then
strEmail = strComma & strEmail
strComma = ","
End If
rst.MoveNext
Loop

A bit snappier than your somewhat tortured offering, apart from having to
deal with the leading comma which you have overlooked.

Baz,

I don't have a problem with the NZ function, I just disagreed with using it
the way you did. Where in your original post did you mention anything about
testing or dealing with zero length strings? If you are averse to using
variants (they are very handy in some instances), then another way to
skin
the cat would have been the following. Either way avoids appending a
zero
length string to the list of email addresses.

Do While not rst.EOF

IF LEN(NZ(rs.Fields("Email"), "") <> 0 then
strEmail = strEmail & ", " & rst.Fields("email"))
EndIF

rst.movenext

Loop

Dale
--
Email address is not valid.
Please reply to newsgroup only.

The OP indicated that he was quite happy to attempt to take it further
himself, hopefully he is learning a lot in the process.

I disagree with YOUR solution of using a variant. IMO variants are a
necessary evil to be avoided whenever there is an alternative e.g. use Nz as
I did, and then test for a zero-length string. Also, we have no idea
whether the field in the underlying table allows zero-length values,
and
your suggested approach does not address that possibility.

Phil,

The recordset automatically opens to the first record, so you don't really
need to test for BOF/EOF and do a move first. Just create a loop to
determine whether you are at the EOF marker.

Basically, the problem you are having is that you have some email
addresses
that are NULL, when you try to concatenate them to your strEmail, you are
getting an error. I disagree with the way the BAZ handled this, just
converting the null values to a string will not solve your email address
problems as you will have a bunch of blanks that your email program may
not
be able to resolve. You actually should just ignore these values
altogether.
You might also need to use a semi-colon as the separater between
your
values.

The first thing I would do is change strEmail to varEmail. This way, you
can add a NULL value to it without causing an error.

By wrapping the reference to rst.Fields("email") in the parenthesis below,
and using the "+" to concatenate the comma seperator to it, you will
actually add a NULL to a string, resulting in a NULL. This ensures that
if
the email address is null, you don't add extraneous commas to your string
(variant) as well.

Note, you should test the length of varEmail prior to calling your
EmailAsPDF code. If the LEN(NZ(varEmail, "")) = 0 then there were no
valid
email addresses.

Replace your With ... End With code with:

Do While not rst.EOF

varEmail = varEmail & (", " + rst.Fields("email"))
rst.movenext

Loop
rst.close
Set rst = nothing

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

Dear All


I apologise in advance that this is a long post I am just trying to give
you
all the info you will need.

I have posted this before but have been unable to get my code working.

I want to email a copy of a report (converted to PDF) to all the email
addresses resolved from a query,

the parameter comes from the form control Forms![Events1]![EventID] and
in
the query it is [em] the query is called Emailteachers

the error I get when running the code is:

Run Time Error 94 Invalid use of NUll: The following line is highlighted

strEmail = .Fields("email")

Here is the code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Dim strEmail As String
'Dim sParam As DAO.Parameter
Dim sParam As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Emailteacher")
sParam = Nz(Forms![Events1]![EventID], "")
qdf.Parameters![Em] = sParam

Set rst = qdf.OpenRecordset

With rst
If (Not .BOF) And (Not .EOF) Then
.MoveFirst
strEmail = .Fields("email")
.MoveNext
End If

If (Not .BOF) And (Not .EOF) Then
Do Until .EOF
strEmail = strEmail & ", " & .Fields("email")
.MoveNext
Loop
End If
.Close
End With

EMailAsPDF "Timetable", strEmail, Me.[EventName] & " " & "Cohort "
&
Me.[Cohort] & " Timetable", "Enter Your Text Here"

I do hope someone can help, I always seem to fall down when I am using
expressions with null

thanks

Phil

 

[Baz]

Because you have just achieved exactly what I was seeking to avoid, which is
a string with a leading comma. OK, I note in your second example that you
subsequently strip it off, but for some reason that approach always really
grates on me, I prefer not to have the darn thing there in the first place.

However, I do agree that simply concatenating rst!Email is better than
shoving it into a variable, string or otherwise. You failed to notice,
though, that I was assigning Nz(rst!Email) to the same variable that I was
concatenating the entire result into, thereby overwriting the concatenated
string with every iteration!

Why would you reset strComma repeatedly?

Dim strEmail As String
Dim strComma As String

strComma = ","

Do Until rst.EOF
strEmail = Nz(rst!Email)
If strEmail <> "" Then
strEmail = strComma & strEmail
End If
rst.MoveNext
Loop

Actually I think Dale's suggestion of varEmail = varEmail & (", " +
rst.Fields("email")) was a good one, although there really was no reason
for him to suggest changing strEmail to varEmail. Even though (", " +
rst.Fields("email")) may evaluate to a Null, using & to concatenate a string
to a Null results in the original string. that means that the following
should work fine:

Dim strEmail As String
Dim strComma As String

strComma = ","

Do Until rst.EOF
strEmail = strEmail & (strComma + rst!Email)
rst.MoveNext
Loop

' Get rid of first comma (if there is one)
If Len(strEmail) > 0 Then
strEmail = Mid(strEmail, 2)
End If


-
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I didn't mention anything about testing for zero length strings. I
credited
the OP with sufficient intelligence to finish the job himself having
solved
his immediate problem, and it seems I was right. AFAIK helping a poster
with a specific problem carries no obligation to finish the rest of his
application for him.

If you are determined to try to teach your grandmother to suck eggs, allow
me to return the compliment:

Dim strEmail As String
Dim strComma As String

Do Until rst.EOF
strEmail = Nz(rst!Email)
If strEmail <> "" Then
strEmail = strComma & strEmail
strComma = ","
End If
rst.MoveNext
Loop

A bit snappier than your somewhat tortured offering, apart from having to
deal with the leading comma which you have overlooked.

Baz,

I don't have a problem with the NZ function, I just disagreed with

using
it

the way you did. Where in your original post did you mention anything about
testing or dealing with zero length strings? If you are averse to using
variants (they are very handy in some instances), then another way to
skin
the cat would have been the following. Either way avoids appending a
zero
length string to the list of email addresses.

Do While not rst.EOF

IF LEN(NZ(rs.Fields("Email"), "") <> 0 then
strEmail = strEmail & ", " & rst.Fields("email"))
EndIF
rst.movenext

Loop

Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

The OP indicated that he was quite happy to attempt to take it further
himself, hopefully he is learning a lot in the process.

I disagree with YOUR solution of using a variant. IMO variants are a
necessary evil to be avoided whenever there is an alternative e.g.

use
Nz as

I did, and then test for a zero-length string. Also, we have no idea
whether the field in the underlying table allows zero-length values,
and
your suggested approach does not address that possibility.

Phil,

The recordset automatically opens to the first record, so you don't really
need to test for BOF/EOF and do a move first. Just create a loop to
determine whether you are at the EOF marker.

Basically, the problem you are having is that you have some email
addresses
that are NULL, when you try to concatenate them to your strEmail,

you
are

getting an error. I disagree with the way the BAZ handled this, just
converting the null values to a string will not solve your email address
problems as you will have a bunch of blanks that your email program may
not
be able to resolve. You actually should just ignore these values
altogether.
You might also need to use a semi-colon as the separater between
your
values.

The first thing I would do is change strEmail to varEmail. This

way,
you

can add a NULL value to it without causing an error.

By wrapping the reference to rst.Fields("email") in the parenthesis below,
and using the "+" to concatenate the comma seperator to it, you will
actually add a NULL to a string, resulting in a NULL. This ensures that
if
the email address is null, you don't add extraneous commas to your string
(variant) as well.

Note, you should test the length of varEmail prior to calling your
EmailAsPDF code. If the LEN(NZ(varEmail, "")) = 0 then there were no
valid
email addresses.

Replace your With ... End With code with:

Do While not rst.EOF

varEmail = varEmail & (", " + rst.Fields("email"))
rst.movenext

Loop
rst.close
Set rst = nothing

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

Dear All


I apologise in advance that this is a long post I am just trying

to
give

you
all the info you will need.

I have posted this before but have been unable to get my code working.

I want to email a copy of a report (converted to PDF) to all the email
addresses resolved from a query,

the parameter comes from the form control

Forms![Events1]![EventID]
and

in
the query it is [em] the query is called Emailteachers

the error I get when running the code is:

Run Time Error 94 Invalid use of NUll: The following line is highlighted

strEmail = .Fields("email")

Here is the code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Dim strEmail As String
'Dim sParam As DAO.Parameter
Dim sParam As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Emailteacher")
sParam = Nz(Forms![Events1]![EventID], "")
qdf.Parameters![Em] = sParam

Set rst = qdf.OpenRecordset

With rst
If (Not .BOF) And (Not .EOF) Then
.MoveFirst
strEmail = .Fields("email")
.MoveNext
End If

If (Not .BOF) And (Not .EOF) Then
Do Until .EOF
strEmail = strEmail & ", " & .Fields("email")
.MoveNext
Loop
End If
.Close
End With

EMailAsPDF "Timetable", strEmail, Me.[EventName] & " " & "Cohort "
&
Me.[Cohort] & " Timetable", "Enter Your Text Here"

I do hope someone can help, I always seem to fall down when I am using
expressions with null

thanks

Phil

 

[Douglas J. Steele]

I believe it's far more efficient to simply remove the extraneous comma than
to go through hoops to avoid it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Because you have just achieved exactly what I was seeking to avoid, which
is
a string with a leading comma. OK, I note in your second example that you
subsequently strip it off, but for some reason that approach always really
grates on me, I prefer not to have the darn thing there in the first
place.

However, I do agree that simply concatenating rst!Email is better than
shoving it into a variable, string or otherwise. You failed to notice,
though, that I was assigning Nz(rst!Email) to the same variable that I
was
concatenating the entire result into, thereby overwriting the concatenated
string with every iteration!

Why would you reset strComma repeatedly?

Dim strEmail As String
Dim strComma As String

strComma = ","

Do Until rst.EOF
strEmail = Nz(rst!Email)
If strEmail <> "" Then
strEmail = strComma & strEmail
End If
rst.MoveNext
Loop

Actually I think Dale's suggestion of varEmail = varEmail & (", " +
rst.Fields("email")) was a good one, although there really was no reason
for him to suggest changing strEmail to varEmail. Even though (", " +
rst.Fields("email")) may evaluate to a Null, using & to concatenate a string
to a Null results in the original string. that means that the following
should work fine:

Dim strEmail As String
Dim strComma As String

strComma = ","

Do Until rst.EOF
strEmail = strEmail & (strComma + rst!Email)
rst.MoveNext
Loop

' Get rid of first comma (if there is one)
If Len(strEmail) > 0 Then
strEmail = Mid(strEmail, 2)
End If


-
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I didn't mention anything about testing for zero length strings. I
credited
the OP with sufficient intelligence to finish the job himself having
solved
his immediate problem, and it seems I was right. AFAIK helping a
poster
with a specific problem carries no obligation to finish the rest of his
application for him.

If you are determined to try to teach your grandmother to suck eggs, allow
me to return the compliment:

Dim strEmail As String
Dim strComma As String

Do Until rst.EOF
strEmail = Nz(rst!Email)
If strEmail <> "" Then
strEmail = strComma & strEmail
strComma = ","
End If
rst.MoveNext
Loop

A bit snappier than your somewhat tortured offering, apart from having to
deal with the leading comma which you have overlooked.


Baz,

I don't have a problem with the NZ function, I just disagreed with using
it
the way you did. Where in your original post did you mention anything
about
testing or dealing with zero length strings? If you are averse to using
variants (they are very handy in some instances), then another way to
skin
the cat would have been the following. Either way avoids appending a
zero
length string to the list of email addresses.

Do While not rst.EOF

IF LEN(NZ(rs.Fields("Email"), "") <> 0 then
strEmail = strEmail & ", " & rst.Fields("email"))
EndIF
rst.movenext

Loop

Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

The OP indicated that he was quite happy to attempt to take it further
himself, hopefully he is learning a lot in the process.

I disagree with YOUR solution of using a variant. IMO variants are
a
necessary evil to be avoided whenever there is an alternative e.g. use
Nz as
I did, and then test for a zero-length string. Also, we have no
idea
whether the field in the underlying table allows zero-length values,
and
your suggested approach does not address that possibility.

Phil,

The recordset automatically opens to the first record, so you
don't
really
need to test for BOF/EOF and do a move first. Just create a loop to
determine whether you are at the EOF marker.

Basically, the problem you are having is that you have some email
addresses
that are NULL, when you try to concatenate them to your strEmail, you
are
getting an error. I disagree with the way the BAZ handled this,
just
converting the null values to a string will not solve your email
address
problems as you will have a bunch of blanks that your email
program
may
not
be able to resolve. You actually should just ignore these values
altogether.
You might also need to use a semi-colon as the separater between
your
values.

The first thing I would do is change strEmail to varEmail. This way,
you
can add a NULL value to it without causing an error.

By wrapping the reference to rst.Fields("email") in the
parenthesis
below,
and using the "+" to concatenate the comma seperator to it, you will
actually add a NULL to a string, resulting in a NULL. This
ensures
that
if
the email address is null, you don't add extraneous commas to your
string
(variant) as well.

Note, you should test the length of varEmail prior to calling your
EmailAsPDF code. If the LEN(NZ(varEmail, "")) = 0 then there were no
valid
email addresses.

Replace your With ... End With code with:

Do While not rst.EOF

varEmail = varEmail & (", " + rst.Fields("email"))
rst.movenext

Loop
rst.close
Set rst = nothing

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

Dear All


I apologise in advance that this is a long post I am just trying to
give
you
all the info you will need.

I have posted this before but have been unable to get my code
working.

I want to email a copy of a report (converted to PDF) to all the
email
addresses resolved from a query,

the parameter comes from the form control Forms![Events1]![EventID]
and
in
the query it is [em] the query is called Emailteachers

the error I get when running the code is:

Run Time Error 94 Invalid use of NUll: The following line is
highlighted

strEmail = .Fields("email")

Here is the code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Dim strEmail As String
'Dim sParam As DAO.Parameter
Dim sParam As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Emailteacher")
sParam = Nz(Forms![Events1]![EventID], "")
qdf.Parameters![Em] = sParam

Set rst = qdf.OpenRecordset

With rst
If (Not .BOF) And (Not .EOF) Then
.MoveFirst
strEmail = .Fields("email")
.MoveNext
End If

If (Not .BOF) And (Not .EOF) Then
Do Until .EOF
strEmail = strEmail & ", " & .Fields("email")
.MoveNext
Loop
End If
.Close
End With

EMailAsPDF "Timetable", strEmail, Me.[EventName] & " " & "Cohort "
&
Me.[Cohort] & " Timetable", "Enter Your Text Here"

I do hope someone can help, I always seem to fall down when I am
using
expressions with null

thanks

Phil

 

[Guest]

BAZ: Don't have a cow. I was just suggesting another technique that Phil
might want to consider or put in his kit bag.

Doug: what I meant to do was:

varEmail = (varEmail + ",") & rst!Email

Since the variant instantiates as NULL, you will never get a leading comma
with this method. I use this general technique alot when building complex
criteria in code (when I have multiple combo boxes or text boxes a user can
use to filter data). Instead of the comma, I put something like:

If NOT ISNULL(me.cboWhatever) then
varCriteria = (varCriteria + " AND ") & ......
END IF

At the end of that process, I can do:

strSQL = strSQL & (" WHERE " + varCriteria)

so that if none of the combo or text boxes have any values, varCriteria will
still be null and strSQL will not contain any WHERE clause.

Dale

--
Email address is not valid.
Please reply to newsgroup only.

Why would you reset strComma repeatedly?

Dim strEmail As String
Dim strComma As String

strComma = ","

Do Until rst.EOF
strEmail = Nz(rst!Email)
If strEmail <> "" Then
strEmail = strComma & strEmail
End If
rst.MoveNext
Loop

Actually I think Dale's suggestion of varEmail = varEmail & (", " +
rst.Fields("email")) was a good one, although there really was no reason
for him to suggest changing strEmail to varEmail. Even though (", " +
rst.Fields("email")) may evaluate to a Null, using & to concatenate a string
to a Null results in the original string. that means that the following
should work fine:

Dim strEmail As String
Dim strComma As String

strComma = ","

Do Until rst.EOF
strEmail = strEmail & (strComma + rst!Email)
rst.MoveNext
Loop

' Get rid of first comma (if there is one)
If Len(strEmail) > 0 Then
strEmail = Mid(strEmail, 2)
End If


-
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I didn't mention anything about testing for zero length strings. I
credited
the OP with sufficient intelligence to finish the job himself having
solved
his immediate problem, and it seems I was right. AFAIK helping a poster
with a specific problem carries no obligation to finish the rest of his
application for him.

If you are determined to try to teach your grandmother to suck eggs, allow
me to return the compliment:

Dim strEmail As String
Dim strComma As String

Do Until rst.EOF
strEmail = Nz(rst!Email)
If strEmail <> "" Then
strEmail = strComma & strEmail
strComma = ","
End If
rst.MoveNext
Loop

A bit snappier than your somewhat tortured offering, apart from having to
deal with the leading comma which you have overlooked.

Baz,

I don't have a problem with the NZ function, I just disagreed with using it
the way you did. Where in your original post did you mention anything about
testing or dealing with zero length strings? If you are averse to using
variants (they are very handy in some instances), then another way to
skin
the cat would have been the following. Either way avoids appending a
zero
length string to the list of email addresses.

Do While not rst.EOF

IF LEN(NZ(rs.Fields("Email"), "") <> 0 then
strEmail = strEmail & ", " & rst.Fields("email"))
EndIF
rst.movenext

Loop

Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

The OP indicated that he was quite happy to attempt to take it further
himself, hopefully he is learning a lot in the process.

I disagree with YOUR solution of using a variant. IMO variants are a
necessary evil to be avoided whenever there is an alternative e.g. use Nz as
I did, and then test for a zero-length string. Also, we have no idea
whether the field in the underlying table allows zero-length values,
and
your suggested approach does not address that possibility.

Phil,

The recordset automatically opens to the first record, so you don't really
need to test for BOF/EOF and do a move first. Just create a loop to
determine whether you are at the EOF marker.

Basically, the problem you are having is that you have some email
addresses
that are NULL, when you try to concatenate them to your strEmail, you are
getting an error. I disagree with the way the BAZ handled this, just
converting the null values to a string will not solve your email address
problems as you will have a bunch of blanks that your email program may
not
be able to resolve. You actually should just ignore these values
altogether.
You might also need to use a semi-colon as the separater between
your
values.

The first thing I would do is change strEmail to varEmail. This way, you
can add a NULL value to it without causing an error.

By wrapping the reference to rst.Fields("email") in the parenthesis below,
and using the "+" to concatenate the comma seperator to it, you will
actually add a NULL to a string, resulting in a NULL. This ensures that
if
the email address is null, you don't add extraneous commas to your string
(variant) as well.

Note, you should test the length of varEmail prior to calling your
EmailAsPDF code. If the LEN(NZ(varEmail, "")) = 0 then there were no
valid
email addresses.

Replace your With ... End With code with:

Do While not rst.EOF

varEmail = varEmail & (", " + rst.Fields("email"))
rst.movenext

Loop
rst.close
Set rst = nothing

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

Dear All


I apologise in advance that this is a long post I am just trying to give
you
all the info you will need.

I have posted this before but have been unable to get my code working.

I want to email a copy of a report (converted to PDF) to all the email
addresses resolved from a query,

the parameter comes from the form control Forms![Events1]![EventID] and
in
the query it is [em] the query is called Emailteachers

the error I get when running the code is:

Run Time Error 94 Invalid use of NUll: The following line is highlighted

strEmail = .Fields("email")

Here is the code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Dim strEmail As String
'Dim sParam As DAO.Parameter
Dim sParam As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Emailteacher")
sParam = Nz(Forms![Events1]![EventID], "")
qdf.Parameters![Em] = sParam

Set rst = qdf.OpenRecordset

With rst
If (Not .BOF) And (Not .EOF) Then
.MoveFirst
strEmail = .Fields("email")
.MoveNext
End If

If (Not .BOF) And (Not .EOF) Then
Do Until .EOF
strEmail = strEmail & ", " & .Fields("email")
.MoveNext
Loop
End If
.Close
End With

EMailAsPDF "Timetable", strEmail, Me.[EventName] & " " & "Cohort "
&
Me.[Cohort] & " Timetable", "Enter Your Text Here"

I do hope someone can help, I always seem to fall down when I am using
expressions with null

thanks

Phil