Concatenation as a default value rather than in a query

[Marlene McCall]

Hi all:

I'm designing a table that has three numeric fields for
the dimensions of shipping boxes: depth, width, and
height. It's important that these be numeric and not text
fields for purposes of sorting, numerical calculations,
etc. I want to create a fourth field -- a text field --
with a default value that automatically concatenates the
value in those three fields with some other text elements,
as follows:

[box length]& "x" & [box width]& "x" [box height]& "
inches"

I can get this to work properly when I define it as a
calculated field in a query:

boxdimensions: [box length] & "x" & [box width] & "x" &
[box height] & " inches"

but I can't get it to work as a default value in the table
itself. I get a message that says:

"The database engine does not recognize either the
field 'box length' in a validation expression, or the
default value in the table 'Unsold Works'.

I thought it might be a problem with the spaces in the
field names (although these field names work fine in the
query) but I get the same message when I remove the spaces
from the field names.

Am I doing something wrong (and if so, what?) Or is this
type of calculation simply not allowed as a default value
in the table?

Thanks in advance for any help.

Marlene

 

[John Vinson]

Or is this
type of calculation simply not allowed as a default value
in the table?

Exactly. A Default value is assigned at the instant a new record is
created - when the other three fields do not yet have any value
assigned.

Unless you want to allow the combined three fields to be edited so
that they no longer reflect the values in the three base fields, I'd
suggest not storing the concatenation in your table AT ALL. It would
be all too easy to have a record with Length = 4, Width = 8, Height =
2 and this new field equal to "8 x 8 x 8 inches", since any of the
four fields could be edited after the record is created!

If you *do* want to have it as a true default (editable, that is),
then you'll need to concatenate the values in VBA code on a Form, or
by running an Update query. There's no way to do it in table
properties.

 

[Dennis Schmidt]

Hi Marlene,

My name is Dennis Schmidt. Thank you for using the Microsoft Newsgroups.

You are correct, you cannot use functions as the basis for default values
of a record in a table. Your approach of creating the field in a query and
then perhaps using an update query would be a possible workaround. Or if
you base your data entry for that table on a form, you can create a simple
piece of code to update that field on the BeforeUpdate event of the form.

I hope this helps! If you have additional questions on this topic, please
reply to this posting.

Need quick answers to questions like these? The Microsoft Knowledge Base
provides a wealth of information that you can use to troubleshoot a problem
or answer a question! It's located at
http://support.microsoft.com/support/c.asp?M=F>.

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.

Regards,
Dennis Schmidt
Microsoft Support