expression problems

Earthling

I want to enter an expression into my Default Value under my field
properties
within the design view of my table.

In English:

If the previous record has the same "product ID" number then by default
make the result FALSE, otherwise TRUE for the given field name value.

What this does is compare the previous Product ID (data type is Number) -
foreign key
to the Product Table ID (data type is AutoNumber) of the previous record.

Thus if the Product ID is the same number for the previous record "Product
Table ID minus 1"
of Product ID, then this means that our Product ID is not the first new
record entered.

Problem:

The Field Names are made up of multiple text so how can I enter an
expression
and also subtract 1 from the value the field name represents ?

The following didn't work:

If [Product Table ID -1] = [Product ID] Then False

Please help me write the expression for the default value.

Thanks,
Martians

Nov 12 '05 #1

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Why are you trying to do this? Try expressions like "I want to limit
the number of ...," "There can only be ...," "I want the user to
know ...," etc.

In SQL tables there isn't any order to records; therefore "previous
record" has no meaning (unless you're looking at a recordset in a
datasheet or form). If you want to prevent duplicate ProductIDs being
entered you can set the ProductID column as the Primary Key (or a
unique index) - or a combination of columns as PK or unique index.

Example: You have an order system. You do not want to allow
customers to order more than 1 product per order:

Orders table
CustomerID
OrderID
OrderDate
SalesRep
... etc. ...

Order Details table
OrderID
ProductID
Quantity - you could restrict this value to 1

OR - you could have a PK on the OrderID and ProductID without the
Quantity column, which would mean that there could only be one product
(ProductID) per order (OrderID).

If you used the Quantity column you could have a Check Constraint
(Validation Rule in .mdb tables) that could make sure the value is
within a specified range, e.g.:

Between 1 and 4

This would only allow 4 items to be requested in one order.

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQB2rGIechKqOuFEgEQIKQwCgm86tpYyGIduKqm37d/++qROUxFEAn0qj
ozYTgLumJ3oG/S0jPBGaSNng
=gZo/
-----END PGP SIGNATURE-----
Earthling wrote:

I want to enter an expression into my Default Value under my field
properties
within the design view of my table.

In English:

If the previous record has the same "product ID" number then by default
make the result FALSE, otherwise TRUE for the given field name value.

Nov 12 '05 #2