Parameter??

Need help - have no idea on how to do this & am concerned that I should have
built table differently. On a form are fields where associates enter in
budget information - cy1 for current year january, cy2 for current year
february, etc; by1 for budget year january, by2 for budget year february;
by2-1 for next budget year january, by2-2 for next budget year february. I
just found out I need to add up the various months based on the current
month. For example, if doing reporting for March, need to add up cy1, cy2,
cy3, cy4... to by3; then a second total for by4 to by2-12. Is this making
sense??? I am not at all good at vba and so use macros. Anyone out there that
can help me with this?

Thanks! Jani

Jani wrote:
Need help - have no idea on how to do this & am concerned that I should have
built table differently. On a form are fields where associates enter in
budget information - cy1 for current year january, cy2 for current year
february, etc; by1 for budget year january, by2 for budget year february;
by2-1 for next budget year january, by2-2 for next budget year february. I
just found out I need to add up the various months based on the current
month. For example, if doing reporting for March, need to add up cy1, cy2,
cy3, cy4... to by3; then a second total for by4 to by2-12. Is this making
sense??? I am not at all good at vba and so use macros. Anyone out there that
can help me with this?

Thanks! Jani

Jani,

You should seriously rethink your table structure. Your current layout
resembles a spreadsheet--which Access is not. As you have found, it is a
terrible chore to add up columns of information.

But I confess I don't understand your business rules well enough to
recommend a better design. I'm sure there is a better design though.

Can you be more detailed about how you would report on March, for example?

--
Smartin

Smartin - Thanks for answering and assisting me. Reporting for March would be
done in April. One set of numbers needs to add cy1 (current year January)
through by3 (budget year March); another set would add numbers by4 (budget
year April) through by2-12 (budget year two December).Reporting for April
would be done in Mary. Add up cy1 through by4 and by5 through by2-12.
Associates fill in the fields of cy1, cy2, cy3, etc., by1, by2, by3, etc. on
a form and need to keep these up to date. I hope this is enough information
but if not please let me know!

"Smartin" wrote:

Jani wrote:
Need help - have no idea on how to do this & am concerned that I should have
built table differently. On a form are fields where associates enter in
budget information - cy1 for current year january, cy2 for current year
february, etc; by1 for budget year january, by2 for budget year february;
by2-1 for next budget year january, by2-2 for next budget year february. I
just found out I need to add up the various months based on the current
month. For example, if doing reporting for March, need to add up cy1, cy2,
cy3, cy4... to by3; then a second total for by4 to by2-12. Is this making
sense??? I am not at all good at vba and so use macros. Anyone out there that
can help me with this?

Thanks! Jani

Jani,

You should seriously rethink your table structure. Your current layout
resembles a spreadsheet--which Access is not. As you have found, it is a
terrible chore to add up columns of information.

But I confess I don't understand your business rules well enough to
recommend a better design. I'm sure there is a better design though.

Can you be more detailed about how you would report on March, for example?

--
Smartin

Hi Jani,

When you say "add cy1...through by3" I assume you mean "cy1 + cy2 + by3".

Here's something to consider: Next year your "current year" data is
really last year's, and "next year" data is really the current year. You
will forever be swapping fields or tables to keep apace with what year
it is.

I would suggest foremost, do away with the notion of "by" and "cy".
Instead, refer to Budget and Current (Actual?) month and year by the
/real/ month and year.

Start by creating a new table: "MyBudget". Within this table you have
three fields: "TheMonth", "BudgetType" and "Amount".

"TheMonth" (avoiding use of the reserved word "Month") will be part of
the the primary key, formatted as a Date/Time value. In it you enter a
month + year, such as "March 2007".

"BudgetType" is used to distinguish between Budget and Current. It is
the other part of the primary key. We can control the values entered in
this field using a form.

The two fields above, taken together, make the primary key. This ensures
there will only be one table row for any given month and budget type.

The "Amount" field is of course your Currency field.

Here is a first pass at getting March's report:

Number Set 1:
SELECT
(SELECT SUM(MBC.Amount)
FROM MyBudget AS MBC
WHERE MBC.TheMonth #3/1/2007# AND
MBC.BudgetType = "Current")
+ Amount
FROM MyBudget
WHERE TheMonth = #3/1/2007#
AND BudgetType = "Budget";

Number Set 2:
SELECT SUM(Amount)
FROM MyBudget
WHERE TheMonth #3/1/2007# AND
BudgetType = "Budget";

Now these queries are not 100% done. Specifically we need to make sure
we are only summing "this year" in Number Set 1, and only summing
through "next year" in Number Set 2. This is not difficult. But you may
notice, we can make them generic to work for any month, regardless of
how many years' worth of data are in the table.

Hope this helps!

Jani wrote:
Smartin - Thanks for answering and assisting me. Reporting for March would be
done in April. One set of numbers needs to add cy1 (current year January)
through by3 (budget year March); another set would add numbers by4 (budget
year April) through by2-12 (budget year two December).Reporting for April
would be done in Mary. Add up cy1 through by4 and by5 through by2-12.
Associates fill in the fields of cy1, cy2, cy3, etc., by1, by2, by3, etc. on
a form and need to keep these up to date. I hope this is enough information
but if not please let me know!

--
Smartin

Hey there "Smartin"...

What you explained makes sense and I will begin by revising the table. My
other issue is going to be how to creating this type of form so it is easily
filled out for approximately 2-1/2 years out into the future! Suggestions
would be appreciated. Your help is so 'helpful'!!

Jani

"Smartin" wrote:

Hi Jani,

When you say "add cy1...through by3" I assume you mean "cy1 + cy2 + by3".

Here's something to consider: Next year your "current year" data is
really last year's, and "next year" data is really the current year. You
will forever be swapping fields or tables to keep apace with what year
it is.

I would suggest foremost, do away with the notion of "by" and "cy".
Instead, refer to Budget and Current (Actual?) month and year by the
/real/ month and year.

Start by creating a new table: "MyBudget". Within this table you have
three fields: "TheMonth", "BudgetType" and "Amount".

"TheMonth" (avoiding use of the reserved word "Month") will be part of
the the primary key, formatted as a Date/Time value. In it you enter a
month + year, such as "March 2007".

"BudgetType" is used to distinguish between Budget and Current. It is
the other part of the primary key. We can control the values entered in
this field using a form.

The two fields above, taken together, make the primary key. This ensures
there will only be one table row for any given month and budget type.

The "Amount" field is of course your Currency field.

Here is a first pass at getting March's report:

Number Set 1:
SELECT
(SELECT SUM(MBC.Amount)
FROM MyBudget AS MBC
WHERE MBC.TheMonth #3/1/2007# AND
MBC.BudgetType = "Current")
+ Amount
FROM MyBudget
WHERE TheMonth = #3/1/2007#
AND BudgetType = "Budget";

Number Set 2:
SELECT SUM(Amount)
FROM MyBudget
WHERE TheMonth #3/1/2007# AND
BudgetType = "Budget";

Now these queries are not 100% done. Specifically we need to make sure
we are only summing "this year" in Number Set 1, and only summing
through "next year" in Number Set 2. This is not difficult. But you may
notice, we can make them generic to work for any month, regardless of
how many years' worth of data are in the table.

Hope this helps!

Jani wrote:
Smartin - Thanks for answering and assisting me. Reporting for March would be
done in April. One set of numbers needs to add cy1 (current year January)
through by3 (budget year March); another set would add numbers by4 (budget
year April) through by2-12 (budget year two December).Reporting for April
would be done in Mary. Add up cy1 through by4 and by5 through by2-12.
Associates fill in the fields of cy1, cy2, cy3, etc., by1, by2, by3, etc. on
a form and need to keep these up to date. I hope this is enough information
but if not please let me know!

--
Smartin

Alas I'm not super "Smart" when it comes to form design. Also, your
workflow will dictate to a large extent what the best design is.

This suggestion seems more than a little clumsy but might get you on
your way... hopefully you will find a more efficient way.

Create two forms based on "MyBudget".

On one, set the Filter property of the form to BudgetType='Budget' and
the Order By property to TheMonth. In the form's On Open event write
this single line of code: Me.FilterOn = True. Set the Default property
of the BudgetType text box to "Budget" (include quotes) and its Locked
property to Yes.

On the other form do everything the same except the Filter property of
the form is BudgetType='Current' and the default value for the text box
is "Current".

Now you have two forms, one in which you can enter Budget and the other
for Current, for each month. You will also need to create a validation
rule or constraint of some kind to ensure the only dates that can be
entered are the first of a given month.

I hope I have given you some workable ideas.


Jani wrote:
Hey there "Smartin"...

What you explained makes sense and I will begin by revising the table. My
other issue is going to be how to creating this type of form so it is easily
filled out for approximately 2-1/2 years out into the future! Suggestions
would be appreciated. Your help is so 'helpful'!!

Jani

"Smartin" wrote:

Hi Jani,

When you say "add cy1...through by3" I assume you mean "cy1 + cy2 + by3".

Here's something to consider: Next year your "current year" data is
really last year's, and "next year" data is really the current year. You
will forever be swapping fields or tables to keep apace with what year
it is.

I would suggest foremost, do away with the notion of "by" and "cy".
Instead, refer to Budget and Current (Actual?) month and year by the
/real/ month and year.

Start by creating a new table: "MyBudget". Within this table you have
three fields: "TheMonth", "BudgetType" and "Amount".

"TheMonth" (avoiding use of the reserved word "Month") will be part of
the the primary key, formatted as a Date/Time value. In it you enter a
month + year, such as "March 2007".

"BudgetType" is used to distinguish between Budget and Current. It is
the other part of the primary key. We can control the values entered in
this field using a form.

The two fields above, taken together, make the primary key. This ensures
there will only be one table row for any given month and budget type.

The "Amount" field is of course your Currency field.

Here is a first pass at getting March's report:

Number Set 1:
SELECT
(SELECT SUM(MBC.Amount)
FROM MyBudget AS MBC
WHERE MBC.TheMonth #3/1/2007# AND
MBC.BudgetType = "Current")
+ Amount
FROM MyBudget
WHERE TheMonth = #3/1/2007#
AND BudgetType = "Budget";

Number Set 2:
SELECT SUM(Amount)
FROM MyBudget
WHERE TheMonth #3/1/2007# AND
BudgetType = "Budget";

Now these queries are not 100% done. Specifically we need to make sure
we are only summing "this year" in Number Set 1, and only summing
through "next year" in Number Set 2. This is not difficult. But you may
notice, we can make them generic to work for any month, regardless of
how many years' worth of data are in the table.

Hope this helps!

Jani wrote:
Smartin - Thanks for answering and assisting me. Reporting for March would be
done in April. One set of numbers needs to add cy1 (current year January)
through by3 (budget year March); another set would add numbers by4 (budget
year April) through by2-12 (budget year two December).Reporting for April
would be done in Mary. Add up cy1 through by4 and by5 through by2-12.
Associates fill in the fields of cy1, cy2, cy3, etc., by1, by2, by3, etc. on
a form and need to keep these up to date. I hope this is enough information
but if not please let me know!
--
Smartin



--
Smartin

Yes, you have given me some workable ideas and it is much appreciated...
Thank you!

"Smartin" wrote:

Alas I'm not super "Smart" when it comes to form design. Also, your
workflow will dictate to a large extent what the best design is.

This suggestion seems more than a little clumsy but might get you on
your way... hopefully you will find a more efficient way.

Create two forms based on "MyBudget".

On one, set the Filter property of the form to BudgetType='Budget' and
the Order By property to TheMonth. In the form's On Open event write
this single line of code: Me.FilterOn = True. Set the Default property
of the BudgetType text box to "Budget" (include quotes) and its Locked
property to Yes.

On the other form do everything the same except the Filter property of
the form is BudgetType='Current' and the default value for the text box
is "Current".

Now you have two forms, one in which you can enter Budget and the other
for Current, for each month. You will also need to create a validation
rule or constraint of some kind to ensure the only dates that can be
entered are the first of a given month.

I hope I have given you some workable ideas.


Jani wrote:
Hey there "Smartin"...

What you explained makes sense and I will begin by revising the table. My
other issue is going to be how to creating this type of form so it is easily
filled out for approximately 2-1/2 years out into the future! Suggestions
would be appreciated. Your help is so 'helpful'!!

Jani

"Smartin" wrote:

Hi Jani,

When you say "add cy1...through by3" I assume you mean "cy1 + cy2 + by3".

Here's something to consider: Next year your "current year" data is
really last year's, and "next year" data is really the current year. You
will forever be swapping fields or tables to keep apace with what year
it is.

I would suggest foremost, do away with the notion of "by" and "cy".
Instead, refer to Budget and Current (Actual?) month and year by the
/real/ month and year.

Start by creating a new table: "MyBudget". Within this table you have
three fields: "TheMonth", "BudgetType" and "Amount".

"TheMonth" (avoiding use of the reserved word "Month") will be part of
the the primary key, formatted as a Date/Time value. In it you enter a
month + year, such as "March 2007".

"BudgetType" is used to distinguish between Budget and Current. It is
the other part of the primary key. We can control the values entered in
this field using a form.

The two fields above, taken together, make the primary key. This ensures
there will only be one table row for any given month and budget type.

The "Amount" field is of course your Currency field.

Here is a first pass at getting March's report:

Number Set 1:
SELECT
(SELECT SUM(MBC.Amount)
FROM MyBudget AS MBC
WHERE MBC.TheMonth #3/1/2007# AND
MBC.BudgetType = "Current")
+ Amount
FROM MyBudget
WHERE TheMonth = #3/1/2007#
AND BudgetType = "Budget";

Number Set 2:
SELECT SUM(Amount)
FROM MyBudget
WHERE TheMonth #3/1/2007# AND
BudgetType = "Budget";

Now these queries are not 100% done. Specifically we need to make sure
we are only summing "this year" in Number Set 1, and only summing
through "next year" in Number Set 2. This is not difficult. But you may
notice, we can make them generic to work for any month, regardless of
how many years' worth of data are in the table.

Hope this helps!

Jani wrote:
Smartin - Thanks for answering and assisting me. Reporting for March would be
done in April. One set of numbers needs to add cy1 (current year January)
through by3 (budget year March); another set would add numbers by4 (budget
year April) through by2-12 (budget year two December).Reporting for April
would be done in Mary. Add up cy1 through by4 and by5 through by2-12.
Associates fill in the fields of cy1, cy2, cy3, etc., by1, by2, by3, etc. on
a form and need to keep these up to date. I hope this is enough information
but if not please let me know!
--
Smartin



--
Smartin

On Jan 8, 1:03 am, Smartin wrote:
Now you have two forms, one in which you can enter Budget and the other
for Current, for each month. You will also need to create a validation
rule or constraint of some kind to ensure the only dates that can be
entered are the first of a given month.

Good point.

Note that Access/Jet, in common with most (all?) available SQL
products, has only one temporal data type, being DATETIME (equivalent
to TIMESTAMP in the SQL-92 standard). A month is a period and periods
are modelled in SQL using a start date and an end date pair, both in
the same row because they are two elements of the same atomic fact.

Using the closed-closed representation, the current period's end date
will be one time granule before the immediately following period's end
date. Many people omit the end date, arguing that a subquery (expensive
in Access/Jet) can be used to reconstruct the periods. I counter that
if you must use a query is to reconstruct your data then it is
incorrectly modelled in the first place. Most common the closed-open
representation but I find closed-closed suits BETWEEN constructs i.e.

X BETWEEN Y AND Z

X=Y AND X=Z

the above are both equivalent but the first is IMO is more human
readable and the second requires two conditions to state one fact.

Out of interest, how do you propose implementing such a 'first of a
given month' Validation Rule or constraint ?

Let's consider a 'first of a given day' rule i.e. there are more days
than months, giving us more values to test performance!

I tested the following by loading my Calendar table, which holds one
row for each day between 1980-01-01 and 2010-12-31 inclusive (11323
rows):

CREATE TABLE Table1 (
date_col DATETIME NOT NULL,
CHECK(
date_col =
DATEADD('D',
DATEDIFF('D', #1990-01-01 00:00:00#, date_col),
#1990-01-01 00:00:00#)
)
);

The above logic is a little obscure for SQL DDL but I use the same
constructs in SQL DML, where it works well, and consistency in code is
a good thing, IMO. The logic for 'first of month' is similar i.e.
change granule type from 'D' to 'M'. Approx 270 milliseconds.

CREATE TABLE Table2 (
date_col DATETIME NOT NULL,
CHECK(
HOUR(date_col) = 0
AND MINUTE(date_col) = 0
AND SECOND(date_col) = 0
)
);

The above again uses DATETIME functions which commonly have equivalents
in other products and the logic is crystal clear but only if your are
aware that the smallest DATETIME granularity in Jet is one second.
Approx 230 milliseconds.

CREATE TABLE Table3 (
date_col DATETIME NOT NULL,
CHECK(
date_col = DATEVALUE(date_col)
)
);

The above uses a DATETIME function but one that is perhaps more
peculiar to Jet, hence has good portability is not as good. Approx 220
milliseconds.

CREATE TABLE Table4 (
date_col DATETIME NOT NULL,
CHECK(
date_col = INT(date_col)
)
);

The above relies on the Jet implementation to coerce a DATETIME to
DOUBLE FLOAT for a INTEGER comparison, for which you need to be very
familiar with Jet under the hood to follow the logic, plus it will not
port well. Approx 120 milliseconds.

Not surprisingly, the more you rely on the unique and physical
characteristics of the Jet implementation, the more obscure is the
logic and the more compromised is the portability but you will get
better performance in *relative* terms, noting that none of the above
examples perform badly in absolute terms.

Personally, I prefer clear logic in code with good portability so it
can be understood by a wide range of SQL coders (rather than, say,
Access power users only), which aids maintenance and means the code can
be moved to SQL platform (or to the same SQL platform in the unlikely
scenario where the Jet implementation moves away from epoch dates) with
minimum effort.

I cringe every time I read, "Dates are stored as floating point decimal
where the integer part represents the date and the decimal part
represents the time etc" (yes, I cringe many times each day g) as
someone explains the logic behind their proposed solution, usually
involving integer arithmetic on DATETIME values e.g. date_value + 1.
Surely logic such as DATEADD('D', 1, date_value) needs no explaining,
considering DATEADD can be found in the help?

Jamie.

--

Jamie Collins wrote:
On Jan 8, 1:03 am, Smartin wrote:
Now you have two forms, one in which you can enter Budget and the other
for Current, for each month. You will also need to create a validation
rule or constraint of some kind to ensure the only dates that can be
entered are the first of a given month.

Good point.

Note that Access/Jet, in common with most (all?) available SQL
products, has only one temporal data type, being DATETIME (equivalent
to TIMESTAMP in the SQL-92 standard). A month is a period and periods
are modelled in SQL using a start date and an end date pair, both in
the same row because they are two elements of the same atomic fact.

Using the closed-closed representation, the current period's end date
will be one time granule before the immediately following period's end
date. Many people omit the end date, arguing that a subquery (expensive
in Access/Jet) can be used to reconstruct the periods. I counter that
if you must use a query is to reconstruct your data then it is
incorrectly modelled in the first place. Most common the closed-open
representation but I find closed-closed suits BETWEEN constructs i.e.

X BETWEEN Y AND Z

X=Y AND X=Z

the above are both equivalent but the first is IMO is more human
readable and the second requires two conditions to state one fact.

Out of interest, how do you propose implementing such a 'first of a
given month' Validation Rule or constraint ?

Let's consider a 'first of a given day' rule i.e. there are more days
than months, giving us more values to test performance!

I tested the following by loading my Calendar table, which holds one
row for each day between 1980-01-01 and 2010-12-31 inclusive (11323
rows):

CREATE TABLE Table1 (
date_col DATETIME NOT NULL,
CHECK(
date_col =
DATEADD('D',
DATEDIFF('D', #1990-01-01 00:00:00#, date_col),
#1990-01-01 00:00:00#)
)
);

The above logic is a little obscure for SQL DDL but I use the same
constructs in SQL DML, where it works well, and consistency in code is
a good thing, IMO. The logic for 'first of month' is similar i.e.
change granule type from 'D' to 'M'. Approx 270 milliseconds.

CREATE TABLE Table2 (
date_col DATETIME NOT NULL,
CHECK(
HOUR(date_col) = 0
AND MINUTE(date_col) = 0
AND SECOND(date_col) = 0
)
);

The above again uses DATETIME functions which commonly have equivalents
in other products and the logic is crystal clear but only if your are
aware that the smallest DATETIME granularity in Jet is one second.
Approx 230 milliseconds.

CREATE TABLE Table3 (
date_col DATETIME NOT NULL,
CHECK(
date_col = DATEVALUE(date_col)
)
);

The above uses a DATETIME function but one that is perhaps more
peculiar to Jet, hence has good portability is not as good. Approx 220
milliseconds.

CREATE TABLE Table4 (
date_col DATETIME NOT NULL,
CHECK(
date_col = INT(date_col)
)
);

The above relies on the Jet implementation to coerce a DATETIME to
DOUBLE FLOAT for a INTEGER comparison, for which you need to be very
familiar with Jet under the hood to follow the logic, plus it will not
port well. Approx 120 milliseconds.

Not surprisingly, the more you rely on the unique and physical
characteristics of the Jet implementation, the more obscure is the
logic and the more compromised is the portability but you will get
better performance in *relative* terms, noting that none of the above
examples perform badly in absolute terms.

Personally, I prefer clear logic in code with good portability so it
can be understood by a wide range of SQL coders (rather than, say,
Access power users only), which aids maintenance and means the code can
be moved to SQL platform (or to the same SQL platform in the unlikely
scenario where the Jet implementation moves away from epoch dates) with
minimum effort.

I cringe every time I read, "Dates are stored as floating point decimal
where the integer part represents the date and the decimal part
represents the time etc" (yes, I cringe many times each day g) as
someone explains the logic behind their proposed solution, usually
involving integer arithmetic on DATETIME values e.g. date_value + 1.
Surely logic such as DATEADD('D', 1, date_value) needs no explaining,
considering DATEADD can be found in the help?

Jamie.

--


Hi Jamie,

I hope my use of the word "constraint" was not taken out of context.
Between the lines I assumed the OP could construct the UI in such a way
to limit date input to month/year values only, which would greatly
simplify the queries involved. The apparent requirements are such that
discrete date values are involved. Perhaps a calendar table of permitted
valued would be a better solution here.

Thanks again!

--
Smartin

"Smartin" wrote:

Jamie Collins wrote:
On Jan 8, 1:03 am, Smartin wrote:
Now you have two forms, one in which you can enter Budget and the other
for Current, for each month. You will also need to create a validation
rule or constraint of some kind to ensure the only dates that can be
entered are the first of a given month.

Good point.

Note that Access/Jet, in common with most (all?) available SQL
products, has only one temporal data type, being DATETIME (equivalent
to TIMESTAMP in the SQL-92 standard). A month is a period and periods
are modelled in SQL using a start date and an end date pair, both in
the same row because they are two elements of the same atomic fact.

Using the closed-closed representation, the current period's end date
will be one time granule before the immediately following period's end
date. Many people omit the end date, arguing that a subquery (expensive
in Access/Jet) can be used to reconstruct the periods. I counter that
if you must use a query is to reconstruct your data then it is
incorrectly modelled in the first place. Most common the closed-open
representation but I find closed-closed suits BETWEEN constructs i.e.

X BETWEEN Y AND Z

X=Y AND X=Z

the above are both equivalent but the first is IMO is more human
readable and the second requires two conditions to state one fact.

Out of interest, how do you propose implementing such a 'first of a
given month' Validation Rule or constraint ?

Let's consider a 'first of a given day' rule i.e. there are more days
than months, giving us more values to test performance!

I tested the following by loading my Calendar table, which holds one
row for each day between 1980-01-01 and 2010-12-31 inclusive (11323
rows):

CREATE TABLE Table1 (
date_col DATETIME NOT NULL,
CHECK(
date_col =
DATEADD('D',
DATEDIFF('D', #1990-01-01 00:00:00#, date_col),
#1990-01-01 00:00:00#)
)
);

The above logic is a little obscure for SQL DDL but I use the same
constructs in SQL DML, where it works well, and consistency in code is
a good thing, IMO. The logic for 'first of month' is similar i.e.
change granule type from 'D' to 'M'. Approx 270 milliseconds.

CREATE TABLE Table2 (
date_col DATETIME NOT NULL,
CHECK(
HOUR(date_col) = 0
AND MINUTE(date_col) = 0
AND SECOND(date_col) = 0
)
);

The above again uses DATETIME functions which commonly have equivalents
in other products and the logic is crystal clear but only if your are
aware that the smallest DATETIME granularity in Jet is one second.
Approx 230 milliseconds.

CREATE TABLE Table3 (
date_col DATETIME NOT NULL,
CHECK(
date_col = DATEVALUE(date_col)
)
);

The above uses a DATETIME function but one that is perhaps more
peculiar to Jet, hence has good portability is not as good. Approx 220
milliseconds.

CREATE TABLE Table4 (
date_col DATETIME NOT NULL,
CHECK(
date_col = INT(date_col)
)
);

The above relies on the Jet implementation to coerce a DATETIME to
DOUBLE FLOAT for a INTEGER comparison, for which you need to be very
familiar with Jet under the hood to follow the logic, plus it will not
port well. Approx 120 milliseconds.

Not surprisingly, the more you rely on the unique and physical
characteristics of the Jet implementation, the more obscure is the
logic and the more compromised is the portability but you will get
better performance in *relative* terms, noting that none of the above
examples perform badly in absolute terms.

Personally, I prefer clear logic in code with good portability so it
can be understood by a wide range of SQL coders (rather than, say,
Access power users only), which aids maintenance and means the code can
be moved to SQL platform (or to the same SQL platform in the unlikely
scenario where the Jet implementation moves away from epoch dates) with
minimum effort.

I cringe every time I read, "Dates are stored as floating point decimal
where the integer part represents the date and the decimal part
represents the time etc" (yes, I cringe many times each day g) as
someone explains the logic behind their proposed solution, usually
involving integer arithmetic on DATETIME values e.g. date_value + 1.
Surely logic such as DATEADD('D', 1, date_value) needs no explaining,
considering DATEADD can be found in the help?

Jamie.

--


I hope my use of the word "constraint" was not taken out of context.
Between the lines I assumed the OP could construct the UI in such a way
to limit date input to month/year values only

I agree it is important to use a 'constraint' to ensure the values in the
database match the data model (BTW I've used CHECK constraints but they same
can be implemented via column-level Validation Rules). However, I do not
agree validation rules as simple as the ones above should be implemented in a
control on a form in one front end application. For an overview of the
issues, see:

Mop the Floor and Fix the Leak
by Joe Celko

http://www.dbazine.com/ofinterest/oi-articles/celko25/

"The poster was asked about some basic data integrity issue and in the
discussion he replied that all the validation would be done in the
front end application program, so we did not have to bother with
constraints on the [database] side. Golly gee whiz, do you suppose that
might be a bad idea? Let's just list some of the assumptions and
situations required for this approach to work..."

Jamie.

--