Calculated Field Error

[Pat Hartman]

No, if you create a calculated field directly in a table (using the "Calculated" data type), Access does not allow you to index it.

Then there is absolutely no reason to use them I guess.

 

[The_Doc_Man]

Now, Pat... given the abominations we have both seen on this forum in over 20 years, you know there is ALWAYS a reason. It just doesn't have to be a good one.

Past a certain point, I become amused by the logic some people offer for the crazy, inappropriate, or disastrous things they do. And then I think back to times when I didn't know better and, before I found this forum, had to guess what to do. Wrong is still wrong, but thinking back to when I was a raw beginner, I have learned to forgive others for failings I once shared (and still might).

 

[Cotswold]

I have been using Access since the last century and have never used a calculated field in a table.
Mainly because I've had the impression, rightly or wrongly, that I'm not totally in control of them.

 

[CJ_London]

Providing you understand the limitations I don’t see a problem with a calculated field-they can’t be indexed, but then the same calculation in a query can’t be either.

For me it’s more about how often it is required so for things like concatenating firstname lastname where it needs to be used in multiple locations I might use a calculated field. I wouldn’t bother if it is only used in one or two queries

 

[The_Doc_Man]

For me it’s more about how often it is required so for things like concatenating firstname lastname where it needs to be used in multiple locations I might use a calculated field. I wouldn’t bother if it is only used in one or two queries

As it happens, that question came up for one of my current projects - the genealogy DB I've built for my family, now with over 2000 people listed - because I'm doing my own family, my wife's family, and her first husband's family - so that my grandsons (technically, step-grands) will know about their roots and influencers.

The "concatenate names" thing came up so often that I built a special function for it, and it is used both in VBA generation of reports and in queries that are used on forms and reports. The basics are to remove excessive spaces in case the middle name is missing, because that led to too many spaces in some names, totally screwing up the appearance of the names in some VBA-generated Word documents that I produce.

 

[xavier.batlle]

Providing you understand the limitations I don’t see a problem with a calculated field-they can’t be indexed, but then the same calculation in a query can’t be either.

For me it’s more about how often it is required so for things like concatenating firstname lastname where it needs to be used in multiple locations I might use a calculated field. I wouldn’t bother if it is only used in one or two queries

I totaly agree with you. Sometimes, when I have needed a calculated field that should be indexed, or that it can't be set with the current operators, I use data macros to simulate it. It has been very useful in some projects.

 

[Ken Sheridan]

The "concatenate names" thing came up so often that I built a special function for it..............

I use the following generic function for names and any other sets of values which require concatenating. It was originally adapted from a CanShrinkLines function published by Microsoft many years ago for returning an address, suppressing Null lines:

Public Function ConcatValues(strSeparator As String, ParamArray arrVals())
 
    ' Pass this function a character or characters
    ' to be used as the separator followed by the values to be combined
    ' For example: strFullName =
    ' ConcatValues(" ",FirstName, MiddleName,LastName)
    
    Dim X As Integer, strLine As String
    
    For X = 0 To UBound(arrVals)
        If Not IsNull(arrVals(X)) And Trim(arrVals(X)) <> "" Then
          strLine = strLine & strSeparator & arrVals(X)
        End If
    Next
      
    ' remove leading separator character(s)
    ConcatValues = Mid(strLine, Len(strSeparator) + 1)
 
End Function

The following query is an example of its use, firstly to concatenate a contact's names, and secondly their address. The query is the RowSource property of a combo box in whose list the address is returned in a second column to differentiate two or more people of the same name:

SELECT
    Contacts.ContactID,
    ConcatValues (
        " ",
        FirstName,
        MiddleName,
        LastName
    ),
    ConcatValues (
        ", ",
        AddressLine1,
        AddressLine2,
        City,
        Region,
        PostCode
    )
FROM
    Regions
    INNER JOIN (
        Cities
        INNER JOIN Contacts ON Cities.CityID = Contacts.CityID
    ) ON Regions.RegionID = Cities.RegionID
ORDER BY
    Contacts.LastName,
    Contacts.FirstName;

 

[The_Doc_Man]

I use the following generic function for names and any other sets of values which require concatenating. It was originally adapted from a CanShrinkLines function published by Microsoft many years ago for returning an address, suppressing Null lines:

Public Function ConcatValues(strSeparator As String, ParamArray arrVals())
 
    ' Pass this function a character or characters
    ' to be used as the separator followed by the values to be combined
    ' For example: strFullName =
    ' ConcatValues(" ",FirstName, MiddleName,LastName)
   
    Dim X As Integer, strLine As String
   
    For X = 0 To UBound(arrVals)
        If Not IsNull(arrVals(X)) And Trim(arrVals(X)) <> "" Then
          strLine = strLine & strSeparator & arrVals(X)
        End If
    Next
     
    ' remove leading separator character(s)
    ConcatValues = Mid(strLine, Len(strSeparator) + 1)
 
End Function

Not that dissimilar to what I use. However, I tailored it because of knowing some specifics of the structure of the genealogy data files which are in GEDCOM format. It is essentially an entity-attribute-value style of data presentation with a quirky 5-part hierarchical definition for the attribute part of the EAV presentation.