Access truncates memo fields in reports — sometimes?!

Interesting problem.  I’m loading up some data in Access that includes a memo field that could potentially contain many K of information.  I created a report and eventually noticed that not all of the memo field was being outputted!  Very odd.

A quick Google search found a post by Allen Browne that described the issue and the solution.  Simply put, I had noticed that Access had added an @ format for the field in question when I pasted to create the table from Excel.  The @ format copied down to the report level.  That code, and several other conditions as described by Allen, will cause Access to truncate the memo output to 255 characters to save CPU effort.


I guess it makes sense, 20 years ago, when CPUs were considerably weaker.  Now?  I’m not so sure that it’s needed.


T-SQL += started in 2008?!

A few days ago, The Impatient DBA wrote that in Microsoft SQL Server 2008, MS added an old C operator: +=.  Simply put, add a value to a variable.  You can run the following code that he provided:

DECLARE @shamwow int = 0;
SET @shamwow += 1;
SET @shamwow += 1;
SELECT @shamwow AS ShamWow;

And @shamwow is now 2.  Without using the += operator, we’d be doing @shamwow = @shamwow +1.  Is += better?  It is certainly shorter, but I think that’s mainly a matter of personal choice.  If you code in other languages that support it, I think it would probably be a good thing.

But there’s more than just adding: there’s also operators for the other basic math functions: -=, *=, /=.  Let’s continue playing with @shamwow with the following code:

DECLARE @shamwow FLOAT = 3;

SET @shamwow *= 3;

SELECT @shamwow;

SET @shamwow /= 2;

SELECT @shamwow;

SET @shamwow -= 3;

SELECT @shamwow;

SSMS will return 9, 4.5, and 1.5.

Pretty cool, eh?

But there’s two problems.  First, this is supported in T-SQL, but not in Access 2013 VBA.  The other is that there’s another pair of C operators, ++ and — (that’s minus minus, not an em dash).  Add or subtract one from the preceding variable, so you’d do set @shamwow++ if it were supported.  And these latter two are not supported in either T-SQL or VBA.  I’m sure they’re alive and well in C# and probably several other .Net languages.

I’m frankly amazed that this happened EIGHT YEARS AGO and I haven’t seen it in common usage.  It’s been in C for probably as long as there has been C, and now we have it in T-SQL.  Something about new tricks and old dogs comes to mind.

A Momentary Lapse of Reason

I’ve been working on code to cross-reference data sources, tables or queries, against queries, tables, and forms.  And I finally have a workable solution.  I will publish it here, but it needs a little cleanup first.

I recently generated such a report and found a surprising number of reports that weren’t using a query for their recordsource, they had SQL statements instead.  There’s nothing wrong with that, but all of a sudden I had to use a memo field for the cross-reference, which you can’t sort on.

The biggest offender?  A select statement that named 22 fields, complete with the full name of the query, which was upwards of 40 characters long.  And after all of those field names?  A *.  And there’s only the one table for the report.

I don’t know what I was thinking.  Shouldn’t be too difficult to clean up.

Viewing Table Dependencies in Access for Linked Tables

I thought of a way to improve data security in my current project, but to implement it safely, I needed to review what linked tables were being used for what forms and reports.  It’s kind of a daunting task, and if anyone knows of a free tool that could provide this for me, I’d be greatly appreciative.  I’m working on some code, but it’s a slow path.

Under the Database Tools tab in later editions of Access is the Object Dependencies tool.  Unfortunately, if you click on a linked table, the button goes gray and is unavailable.  Fortunately, I found a workaround.

Click on a local table first, then open the tool.  So far, so good.  Now click on the table that you actually want to look at dependencies for, and click the Refresh link in the top right of the Object Dependencies pane that opened when you clicked on the tool’s button.

Easy peasy.

My thanks to Avon4026 on (a web site that I highly recommend) for pointing out this workaround.

One form, two data sources

As a part of the system that I’m developing, I needed to copy a form.  The two copies were identical, and the data set was the same, the difference was in filtering.  The RecordSource for each was a different query, one connected to a view on my server, the other to a query in Access.  I thought this was a ridiculous thing to have two copies with such a trivial difference, so I went after it and came up with a pretty cool solution.

First, I added a hidden text box called txtRecordSource to the menu form.  I made it visible for testing.  Then I changed the buttons that the user clicked from macros to open the form to the following:

Private Sub cmdEditStudents_Click()
    txtRecordSource = "dbo_vwStudentsFiltered"
    DoCmd.OpenForm "frmStudents"
End Sub
Private Sub cmdCaseloadEditStudents_Click()
    txtRecordSource = "qryCaseloadStudents"
    DoCmd.OpenForm "frmStudents"
End Sub

I could have used the FilterName or WhereCondition parameters but decided not to.  I wanted the student filtering to happen at a lower level so that filter could be used in other places and changed in only one.

Within frmStudents, I added the following code:

Private Sub Form_Open(Cancel As Integer)
    Me.RecordSource = Forms!frmMenu!txtRecordSource.Value
End Sub

That’s all there is to it.  Whenever you change the RecordSource of a form or report, it automatically requeries the underlying data.  I’m considering applying the technique in some other places throughout my system, it will require a little more work but might be worth it for a long-term maintenance perspective.

If you’re in the United States, I hope you enjoy your Thanksgiving holiday.  If you’re not in the USA, have a good remainder of the week and a wonderful weekend!

Invalid character value for cast specification (#0)

I ran in to this rather odious SQL Server Native Client 11.0 error last week, getting a message box saying “ODBC–update on a linked table failed.”  Actually it was an insert, but that’s OK, I never expect software to have particularly good error messages.  (but why, oh why, can’t Microsoft make their error messages so that we can copy the error message out with our mouse to make it easier to look up?)  I’ve since been banging my head against the wall trying to solve it.  It didn’t seem like anything complicated going on: I had a table that I was accessing directly through a subform and adding a record.  The insert was consistently blowing up.

Well, I found my problem.  Your mileage may vary.

First, the SQL Server table:

CREATE TABLE [dbo].[NotesStudents](
    [NoteID] [int] IDENTITY(1,1) NOT NULL,
    [StudentID] [int] NOT NULL,
    [NoteDate] [date] NOT NULL CONSTRAINT [DF_NotesStudents_NoteDate] DEFAULT (getdate()),
    [NoteTime] [time](3) NOT NULL CONSTRAINT [DF_NotesStudents_NoteTime] DEFAULT (getdate()),
    [UserID] [int] NOT NULL,
    [NoteText] [varchar](1000) NOT NULL,
    [RowVer] [timestamp] NOT NULL);

I have the date and time as two separate fields as I want my form to display the records sorted by date descending, time ascending.  I needed to populate the date, time, student ID, and user ID, and the following code on the BeforeInsert did that:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.txtNoteLen.Visible = True
    Forms!frmStudentsSPEDs!fsubStudentsNotes.Form!SeekerID = Forms!frmStudentsSPEDs.StudentID
    Forms!frmStudentsSPEDs!fsubStudentsNotes.Form!UserID = Forms!frmMenuSPEDs.UserID
    Forms!frmStudentsSPEDs!fsubStudentsNotes.Form!NoteDate = Date
    Forms!frmStudentsSPEDs!fsubStudentsNotes.Form!NoteTime = CStr(Time)
End Sub

(the txtNotLen field counts how many characters are in the note since they’re limited to 1,000)

So the above code produced the #0 error message.  I could add records through SSMS, so I knew the table was functional.  Today I get back from lunch and decide to manually add a record to the table via Access, using Ctrl-; to add the date and Ctrl-: to add the time.

And it blew up.

Turns out that when you use Ctrl-: to enter the time, you get 12:57:41 PM.  The PM was the problem.  SQL Server was expecting just a time value, so I changed the NoteTime line to say Format(Time, “h:m:s”): the Format “h:m:s” converts the current time to 24 hour format, so 4:49pm becomes 16:49.

So if you’re getting this error and you’re dealing the VBA and a Time(3) field on SQL Server, this might be the cause.

A clean way to handle a report’s NODATA event

This was annoying me.  I wanted to display a report showing all of the notes for a student, but it was entirely possible that there would be no notes.  Seems simple: put a MSGBOX in the VBA behind the NODATA event of the report.  Except the report then tries to display.  So I came up with this code:

Private Sub cmdNotesReportPreview_Click()
    Dim snum As Integer   'student ID
    Dim sncnt As Integer  'num of notes for this student
    snum = Forms!frmStudentsSPEDs.StudentID
    sncnt = DCount("[NoteID]", "dbo_NotesStudents", "StudentID=" & snum)
    If sncnt > 0 Then
        DoCmd.OpenReport "rptStudentsNotes", acViewPreview, , "StudentID=" & snum
        MsgBox "This student has no notes to display."
    End If
 End Sub

Does the trick for me quite nicely.