Custom data type

[Allan]

Hi

I would like to create a custom data type (or whatever the terminology is)
when I am designing my fields within design view. Can I create a data type
using the following scheme?

eg ABC|Date|autonumber

where "ABC" is a static value
where "date" is appended to ABC eg ABC20102005
where autonumber is an incrementing number that starts at "0" and increments
until the date is changed. After the date has changed the number resets to
"0" This number is appended to the previous combination of ABC|Date.

 

[Rick Brandt]

Hi

I would like to create a custom data type (or whatever the
terminology is) when I am designing my fields within design view. Can
I create a data type using the following scheme?

eg ABC|Date|autonumber

where "ABC" is a static value
where "date" is appended to ABC eg ABC20102005
where autonumber is an incrementing number that starts at "0" and
increments until the date is changed. After the date has changed the
number resets to "0" This number is appended to the previous
combination of ABC|Date.

You don't actually need a custom type. You can *store* the date and number in
two separate fields and then combine them with the "ABC" for *display*.

With the number and date in separate fields it is much easier to calculate what
the next number should be. In the Form's BeforeUpdate event...

If IsNull(Me.IDNum) = True Then
Me.IDNum = Nz(DMax("IDNum","TableName", "DateField = Date()"), 0) + 1
End If

 

[peregenem]

If IsNull(Me.IDNum) = True Then
Me.IDNum = Nz(DMax("IDNum","TableName", "DateField = Date()"), 0) + 1
End If

If this were the 'forms' group then that approach would be fine <g>.
Also, don't you agree that calculated values shouldn't normally be
stored in tables?

In the OP's spec, I read "until the date is changed" to refer to a
stored date value rather than the current *system* date. So, for the
tablesdbdesign group, here's my proposed design:

CREATE TABLE KeyDateTable (
key_date DATETIME
DEFAULT DATE()
NOT NULL UNIQUE)
;
CREATE TABLE TestTable (
key_int INTEGER IDENTITY(1,1) NOT NULL,
key_date DATETIME NOT NULL
REFERENCES KeyDateTable (key_date)
ON DELETE CASCADE
ON UPDATE CASCADE,
UNIQUE(key_date, key_int),
data_col VARCHAR(10) NOT NULL,
CONSTRAINT key_date_must_be_highest_available
CHECK(TestTable.key_date = (
SELECT MAX(KeyDateTable.key_date)
FROM KeyDateTable)
))
;

-- Test data:
INSERT INTO KeyDateTable VALUES (#2004-01-01#)
;
INSERT INTO TestTable (key_date, data_col)
VALUES (#2004-01-01#, 'One')
;
INSERT INTO TestTable (key_date, data_col)
VALUES (#2004-01-01#, 'Two')
;
-- the date is changed:
INSERT INTO KeyDateTable VALUES (#2005-01-01#)
;
-- try old date:
INSERT INTO TestTable (key_date, data_col)
VALUES (#2004-01-01#, 'Three')
;
-- 2004-01-01 fails to meet CHECK, try new date
INSERT INTO TestTable (key_date, data_col)
VALUES (#2005-01-01#, 'Three')
;
-- success!

Now the desired key values may be generated in a VIEW (a.k.a. Query
object), where calculated values normally belong:

CREATE VIEW Test (
key_value, data_value)
AS
SELECT 'ABC' &
FORMAT$(T1.key_date, 'ddmmyyyy')
& (
SELECT COUNT(*)
FROM TestTable
WHERE key_date = T1.key_date
AND key_int < T1.key_int
) AS key_value,
T1.data_col AS data_value
FROM TestTable AS T1
;

SELECT key_value, data_value FROM Test
;

 

[jahoobob]

No, and you shouldn't for various reasons. You should have thre
seperate fields, actually two (date and number.) You can (and should
add the ABC in your queries, forms, and reports and combine the thre
in your reports (via queries if you want.)

 

[peregenem]

I hit send to soon...

A procedure (a.k.a. parameterized Query object) could help generate new
rows by automatically picking up the highest date e.g.

CREATE PROCEDURE AddData data_value VARCHAR(20))
AS
INSERT INTO TestTable (key_date, data_col)
SELECT MAX(key_date), :data_value FROM KeyDateTable
;
EXEC AddData 'Four'
;
EXEC AddData 'Five'
;
INSERT INTO KeyDateTable (key_date) VALUES (#2006-01-01#)
;
EXEC AddData 'Six'
;
SELECT key_value, data_value FROM Test
;

 

[Rick Brandt]

If this were the 'forms' group then that approach would be fine <g>.

My reply was menat to imply that the palce to see what he wants is on a form
or report. Not in the table.

Also, don't you agree that calculated values shouldn't normally be
stored in tables?

I don't consider a code routine that assigns an incrementing value to a
record to be a "calculated value". While a calculation is required to
create it, it is not data that is calculated from other fields in the record
which is what is usually being referred to by "don't store that" advice.

 

[Allan]

Hello Again,

Thanks for your attention for this issue. I can at least "have a play" now.