How do I catch an error if there is no such range

[jleval]

Okay, I did the following, but the problem occurs if there is no such date range. My report seems to have an error that says there is no such value to evaluate.

Is there a way to catch this error and have a message box explain that there is no such date?

Just add two controls to capture the start and end dates to the form and then you can filter the report in the click event on the command button to:

Dim strWHERE As String
 
strWHERE = "[DateFieldNameHere] Between #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"
 
DoCmd.OpenReport "ReportNameHere", acViewPreview, , strWHERE

 

[pbaldy]

You're trying to catch the user not entering a date or the report not returning records? Sounds like the latter, in which case you're have

Cancel = True

in the No Data event of the report and trap for the error in this code:

http://www.baldyweb.com/ErrorTrap.htm

 

[boblarson]

Something appears to be wrong with your dates. Because if there were no values which matched then it would just tell you that there was no data for the report or it would just open blank.

Where are you located? Your profile doesn't show. If you are used to using dates in the dd/mm/yyyy format, you need to change your input so it uses mm/dd/yyyy (you can thank Microsoft for that one). But you don't need to change how they are put into the text boxes but you can use

strWHERE = "[DateFieldNameHere] Between " & Format(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & " AND " & Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#")

 

[jleval]

I have just noticed that when there is not a proper date and it goes to the report that I get an error message that says, "You entered an expression that has no value. When I hit the debug, it takes me to the detail section of my report where I have various text boxes with the following code:

Me.FluteDia_1.Visible = Nz(Me.FluteDia_1, 0)
Me.FluteDia_2.Visible = Nz(Me.FluteDia_2, 0)
Me.FluteDia_3.Visible = Nz(Me.FluteDia_3, 0)
Me.FluteDia_4.Visible = Nz(Me.FluteDia_4, 0)

 

[jleval]

Okay, so I put the error code in the detail section, but I cant get the report to close without the message box running in an endless loop. I put the Docmd.Close in the exit handler, but the message box continues to pop up without stopping

 

[boblarson]

Okay, so I put the error code in the detail section, but I cant get the report to close without the message box running in an endless loop. I put the Docmd.Close in the exit handler, but the message box continues to pop up without stopping

That is due to the code which is being used on each record.

Also, are you sure that there are no empty strings in your Me.FluteDia_x fields? If there are, that could be why you are getting the error, as the NZ function doesn't help but setting Visible to an empty string isn't possible. Perhaps this would work better:

Me.FluteDia_1.Visible = Len(Me.FluteDia_1 & vbNullString) > 0
Me.FluteDia_2.Visible = Len(Me.FluteDia_2 & vbNullString) > 0
Me.FluteDia_3.Visible = Len(Me.FluteDia_3 & vbNullString) > 0
Me.FluteDia_4.Visible = Len(Me.FluteDia_4 & vbNullString) > 0

 

[jleval]

The values within the FluteDia... are integers

 

[boblarson]

The values within the FluteDia... are integers

Okay, comment out that code and see if it still errors out. If it does, it isn't that code. If it doesn't then it is and we can look into it further.

 

[jleval]

I commented the code out and there was no error

 

[boblarson]

Then make sure that the CONTROLS you are trying to set as visible or not visible are NOT named the same as the fields. You may need to change their names to:

txtFluteDia_1
txtFluteDia_2
txtFluteDia_3
txtFluteDia_4

 

[jleval]

Okay it works now, Thanks