Re: Report parameters and text options

In response to your reply in another part of this thread, I meant to say
that a command button on the form will open the report, not that it will
open the form.

CertType is a text field. Other than alphanumeric characters the data may
contain a colon, a hyphen, or a slash. That wasn't the problem. More
shortly.

I removed the CertExp = True oddity (an oversight), and still received the
syntax error message. I stripped the code (and the function) down to
checking for CertReturned, but it said I had an extra parentheses (or maybe
that I was missing one - I don't recall). That clearly was not the case, so
I created a new text box and put the function in its control source. This
time it worked.

I open the report from a command button. The command button code contains:

Dim strCertSQL as String

strCertSQL = "[CertReturned] = True and [CertExp] < Date()"

The strCertSQL is actually more complex than that, but it's the general
idea. strCertSQL is used as the Where part (after the last comma) of
DoCmd.OpenReport. The idea is that I will only include vendors whose
certificates need attention. If a vendor's cert expires in three months, I
don't need to know anything about it now, so the strCertSQL excludes that
vendor from the recordset. If there is a better way than the command
button's click event to filter the recordset I would be glad to hear about
it.

In any case, thanks for pointing me in the right direction. I would have
responded sooner, but I have been experimenting for a while. There is more
to this than I needed to explain, but some of the complexities left me
puzzled for a while. I thought I would need to ask for assistance, but I
eventually solved the difficulties (for now, at least).

"Steve Schapel" <schapel@xxxxxxxxxxx> wrote in message
news:OL$ONiJoGHA.1052@xxxxxxxxxxxxxxxxxxxxxxx
Bruce,

I'm sorry, I can't immediately see what is causing this problem.

There is an oddity in your code...
CertExp = True
... where CertExp is a date - but I don't imagine this would lead to the
error you are receiving.

What is the actual data in the CertType field? Does it ever have an
apostrophe or parentheses or whatnot in there?

--
Steve Schapel, Microsoft Access MVP


BruceM wrote:
I have had a chance to try out the code, but have not been able to make
any real progress. Here is what I have so far in a public function in a
code module:

Public Function FaxText(CertExp As Date, CertType As String, _
CertReturned As Boolean) As String

Dim TextType As Integer

' CertReturned = True until a cert has been requested. CertReturned
= True
' for certs that are current.

If CertExp = True And CertReturned = True Then
If CertExp < DateAdd("m", 1, Date) And CertExp >=
DateAdd("ww", -2, Date) Then
TextType = 3
End If
Else
If CertExp > DateAdd("ww", -2, Date) _
And CertExp <= DateAdd("m", -2, Date) Then
TextType = 2
Else
If CertExp > DateAdd("m", -2, Date) Then
TextType = 1
End If
End If
End If

Select Case TextType
Case 1
FaxText = "Your " & CertType & " is going to expire on " & _
Format(CertExp, "mmmm dd, yyyy") & _
". Please send an updated certificate."
Case 2
FaxText = "Did you get the request?"
Case 3
FaxText = "Your cert is seriously expired."
End Select

End Function

I used CertReturned instead of CertRequested, and DateAdd rather than
Date + or Date -, but other than that it seems to be what you suggested.
The code compiles OK, but when I attempt to use it as the control source
for an unbound text box on the report:
=FaxText([CertExp],[CertType],[CertRequested]) I receive the following
error message when I attempt to open the report:
Syntax error (comma) in query expression '[
=FaxText([CertExp],[CertType],[CertRequested])]'
If I attempt to open the report from a command button, I receive the same
error message, but because of error handling I can identify it as Error
3075.

Can you tell what I'm missing here?



.