Error in open report function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi !
I created a function in a form that opens a report with a filter or not,
depending on what the user decides. The user enters the hydranttID in a
textbox in the form if he wants to print a report with the information about
that hydrant only. If he leaves the textbox blank, it prints the whole report
(for all hydrants).

On my computer I have Access 2000 in english and Word and excel 2003 in
french.
The function works well with the filter. But when I leave the text box
blank, an error message in french (every other messages are in english except
this one) tells me (translated for you...):

) in excess in the expression '([HydrantID] = )'

Here is the code I written: ->There are no () !!!!

Function OpenReports(stDocName As String) As Integer
On Error GoTo Err_OpenReports

Dim condition As String
stDocName = "registre hydrant"
If Me!HydrantNumber = Null Then
'HydrantNumber is the name of the textbox
DoCmd.OpenReport stDocName, acViewNormal
MsgBox "This will print the whole report. Continue?", vbOKCancel

Else
DoCmd.OpenReport stDocName, acViewPreview, , "[HydrantID] = " &
Me!HydrantNumber
End If
Exit_OpenReports:
Exit Function

Err_OpenReports:
MsgBox Err.Description
Resume Exit_OpenReports

End Function
 
Because you are entering the hydrant ID in a textbox, it is not "null" and
that is why your IF statement keeps going to the ELSE portion and running the
SQL statement giving you ) in excess in the expression '([HydrantID] = )'.

Try...

If Me!HydrantNumber = "" Then ....etc

Hope this helps!
 
Try changing

If Me!HydrantNumber = Null Then

to

If Len(Trim(Nz(Me!HydrantNumber,""))) = 0 Then

In your code it could be possible that Me!HydrantNumber could = "" or " "
neither of which is Null sor your If would be false and your Else block
would execute.

You might alco consider wrapping your OpenFrom expression in a Nz() too.
Might look Like:

"[HydrantID] = " & Nz(Me!HydrantNumber,-1)


Ron W
 
Back
Top