Troubleshooting MS-Access Error 31532 – Unable to export data.

Microsoft Access can sometimes seem to have its own obstinate personality, throwing errors that persist no matter what you try. I wanted to share a recent troubleshooting experience to show some of the steps that you can take when a function is just not working as expected and the decision process involved in fixing it.

The Problem

The other day, I received a bug report from one of my clients over e-mail. They were trying to run a report I’d designed for them awhile back and getting a rather vague error.

At least the VBA error handler worked as designed; it passed along the error number and description that VBA coughed up and allowed the user to exit from the screen and continue using the program. It wouldn’t hurt if I had included the name of the subroutine for my own convenience but this client is good about providing information on where they found it. In this case, it was a form that enables them to preview the results of a query and then export it to Excel.

The Process

Investigating the procedure behind the form, I found the DoCmd.TransferSpreadsheet command where it was failing and the query it was trying to export. Running the query, I got another error when trying to preview the data instead of exporting it:

The “too complex to be evaluated” error in an Access query is a bit like the Check Engine light in your car. It’s not going to tell you what’s wrong but there’s definitely something Access SQL doesn’t like and it won’t cooperate until you figure it out.

The next step was to look at the elements in the query to see what might have been causing the problem. Removing all but a couple of basic fields enabled the query to run and this meant that at least the query itself with its multiple table joins was working.

I finally narrowed it down to this field:

This client uses a shipment reference number that combines a date code with a sequence number (-1, -2, -3, etc..). On the first line above, the query is using Left() and InStr() to strip off the dash and sequence number so it can search the records just by the date code. The criteria line at the bottom shows that the resulting value is being matched against a value selected from a combo box on the form. In this way, the client can pull all the records for that date code.

Obviously, there was something in that combination of functions and criteria that was giving Access indigestion but I needed it to work. I was also a little puzzled why it was failing now since I know I tested it before releasing the changes to them.

Since Access wasn’t providing any more information, my next step was to break the Left() function down into a custom VBA function of my own and use that in the query instead. In retrospect, I should have first looked more closely at the values that the Left() function was producing through the query but there are a lot of things that seem obvious after they slap us in the face.

Public Function GetDateCodeFromShipment(ShipmentID As String) As String
   'Get the position of the dash in the NonReturnID.
   intDash = InStr([ShipmentID], "-") - 1
   'Return the relevant portion.
   GetDateCodeFromShipment = Left([ShipmentID], intDash)
...

I could then use this function in the query and it would return the date code needed to compare against the one selected on the form.

… and it failed!!

The Cause

The routine was failing in the Left() function from last line of the code above That’s when I decided to look at the values that the InStr() function was returning and, sure enough, I found that on one record, it was returning a value of -1 which the Left() function didn’t like.

This was because, for the first time in tens of thousands of records, the client had accidentally entered a ShipmentID without the dash and sequence number. InStr() returned a 0 since it found no dash and then Left() tried to select -1 characters from the ShipmentID, resulting in the illegal function call. Since the entire function had been previously contained within the query, there was no error handling to explain the message until I broke it out into my own function.

I also found that the query needed to be filtered on another field that would further limit the records returned. With the extra filter, the bad data didn’t even affect it and it ran faster.

The Solution

The extra query filter was the first step. Since I’d already written the new function, I decided to leave it in place and added a couple lines to handle values that did not include the dash.

'Get the position of the dash in the ShipmentID.
intDash = InStr([ShipmentID], "-") - 1

'Return the relevant portion.
If intDash > 0 Then
    GetDateCodeFromShipment = Left([ShipmentID], intDash)
Else
    'No dash, return the whole thing.
    GetDateCodeFromShipment = ShipmentID
End If

Then, I actually made this moot as I decided to try to prevent further data entry errors on the form where the data had originally come from.

Private Sub ShipmentID_BeforeUpdate(Cancel As Integer)
...
'Verify there's a sequence number at the end.
If InStr(Me.ShipmentID, "-") = 0 Then
    MsgBox "Shipment codes should end with a dash followed by a sequence number. (-1, -2, -3, etc..). Please correct this if possible.", _
        vbOKOnly, "Missing sequence number ..."
End If
...

The BeforeUpdate event on a form field can be used to run checks on the data and to cancel the update if necessary. On the event declaration above, you will notice the Cancel argument. Setting this argument to True (even though it’s an integer) will cancel the update and keep the focus on that field until the user corrects the problem. In this case, I decided just to show a warning and not restrict the client’s data entry any more than absolutely necessary.