Re: Report parameters and text options

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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?

.

Quantcast