date and month Parameter

  • Thread starter Thread starter Laura K
  • Start date Start date
L

Laura K

Trying to do a parameter query. Should be so simple. I can so far only
retrieve the year but I need to query by month and year.

Here is my year clause. Can someone help me make it a month and year.

Where year([jobTransactionDate]) = [ ];

Thanks

I know this should be so much easier but I just can not get it.

Laura K
 
....
WHERE (Year([JobTransDate]) = [Enter Year:])
AND (Month([JobTransDate]) = [Enter Month:])

The above is fairly inefficient, though. For efficiency, you should use:

WHERE [JobTransDate]
BETWEEN DateSerial([Enter Year:], [Enter Month:], 1)
AND DateSerial([Enter Year:], [Enter Month:] + 1, 0)

HTH
Van T. Dinh
MVP (Access)
 
Van said:
you should use:

WHERE [JobTransDate]
BETWEEN DateSerial([Enter Year:], [Enter Month:], 1)
AND DateSerial([Enter Year:], [Enter Month:] + 1, 0)
Click to expand...

You are close but not quite there. For example:

CREATE TABLE TestDates (
key_col INTEGER NOT NULL PRIMARY KEY,
data_col DATETIME NOT NULL
)
;
INSERT INTO TestDates VALUES (1, #2005-03-31#);
INSERT INTO TestDates VALUES (2, #2005-03-31 06:00:00#);
INSERT INTO TestDates VALUES (3, #2005-03-31 18:00:00#);
INSERT INTO TestDates VALUES (4, #2005-04-01#);
INSERT INTO TestDates VALUES (5, #2005-04-01 06:00:00#);
INSERT INTO TestDates VALUES (6, #2005-04-01 18:00:00#);
INSERT INTO TestDates VALUES (7, #2005-04-30#);
INSERT INTO TestDates VALUES (8, #2005-04-30 06:00:00#);
INSERT INTO TestDates VALUES (9, #2005-04-30 18:00:00#);
INSERT INTO TestDates VALUES (10, #2005-05-01#);
INSERT INTO TestDates VALUES (11, #2005-05-01 06:00:00#);
INSERT INTO TestDates VALUES (12, #2005-05-01 18:00:00#);

I think it's clear the intention is to SELECT all rows for April 2005,
being rows 4 to 9 inclusive.

Your approach:

SELECT key_col, data_col
FROM TestDates
WHERE data_col BETWEEN DateSerial(2005, 04, 1)
AND DateSerial(2005, 04 + 1, 0);

will only SELECT rows 4 to 7.

For a full solution, I prefer to use Jet's DATEDIFF function:

SELECT key_col, data_col
FROM TestDates
WHERE DATEDIFF('d', DateSerial(2005, 4, 1), data_col) >= 0
AND DATEDIFF('d', DateSerial(2005, 4 + 1, 0), data_col) <= 0;

Jamie.

--
 
I assumed the O.P. doesn't have the time component since it wasn't mentioned
in the post. However, if the time component is required, it is easily fixed
using >= and <.

The main thing for the second method is to avoid repeated execution(s) of
VBA function(s). In my second suggestion, the DateSerial() function has to
be executed twice only for the whole query while in your suggestion, the
DateDiff() function has to be executed twice for _each_ Record in the Table.
That's why I posted the 2 SQLs and I would suggest that most experienced
Access users would know why I wrote that the second SQL is more efficient.

Surely you wouldn't want to introduce unnecessary processing for no gains
....

BTW, AFAIK, DateDiff() is a VBA function, _not_ a Jet's function.

--
HTH
Van T. Dinh
MVP (Access)


Jamie Collins said:
you should use:

WHERE [JobTransDate]
BETWEEN DateSerial([Enter Year:], [Enter Month:], 1)
AND DateSerial([Enter Year:], [Enter Month:] + 1, 0)
Click to expand...

You are close but not quite there. For example:

CREATE TABLE TestDates (
key_col INTEGER NOT NULL PRIMARY KEY,
data_col DATETIME NOT NULL
)
;
INSERT INTO TestDates VALUES (1, #2005-03-31#);
INSERT INTO TestDates VALUES (2, #2005-03-31 06:00:00#);
INSERT INTO TestDates VALUES (3, #2005-03-31 18:00:00#);
INSERT INTO TestDates VALUES (4, #2005-04-01#);
INSERT INTO TestDates VALUES (5, #2005-04-01 06:00:00#);
INSERT INTO TestDates VALUES (6, #2005-04-01 18:00:00#);
INSERT INTO TestDates VALUES (7, #2005-04-30#);
INSERT INTO TestDates VALUES (8, #2005-04-30 06:00:00#);
INSERT INTO TestDates VALUES (9, #2005-04-30 18:00:00#);
INSERT INTO TestDates VALUES (10, #2005-05-01#);
INSERT INTO TestDates VALUES (11, #2005-05-01 06:00:00#);
INSERT INTO TestDates VALUES (12, #2005-05-01 18:00:00#);

I think it's clear the intention is to SELECT all rows for April 2005,
being rows 4 to 9 inclusive.

Your approach:

SELECT key_col, data_col
FROM TestDates
WHERE data_col BETWEEN DateSerial(2005, 04, 1)
AND DateSerial(2005, 04 + 1, 0);

will only SELECT rows 4 to 7.

For a full solution, I prefer to use Jet's DATEDIFF function:

SELECT key_col, data_col
FROM TestDates
WHERE DATEDIFF('d', DateSerial(2005, 4, 1), data_col) >= 0
AND DATEDIFF('d', DateSerial(2005, 4 + 1, 0), data_col) <= 0;

Jamie.
Click to expand...
 
Van said:
The main thing for the second method is to avoid repeated execution(s) of
VBA function(s).
Surely you wouldn't want to introduce unnecessary processing for no
Click to expand...
gains

You are right, of course, my construct isn't very fast, and the
experienced Jet user wouldn't have to use MS Access to pick up on that
<g>.

My point was, you have to be cautious when using BETWEEN with DATETIME
values because it's very easy to end up with values in the wrong
tranche (e.g. #2005-04-30 12:00:00# ends up in May) or, worse, values
get omitted (e.g. #2005-04-30 12:00:00# is missed by both BETWEEN
#2005-04-01# AND #2005-04-30# and BETWEEN #2005-05-01# AND
#2005-05-31#).

You are right, of course, my construct isn't very fast, and the
experienced Jet user wouldn't have to use MS Access to pick up on that
<g>.

My point was, you have to be cautious when using BETWEEN with DATETIME
values because it's very easy to end up with values in the wrong
tranche (e.g. #2005-04-30 12:00:00# ends up in May) or, worse, values
get omitted (e.g. #2005-04-30 12:00:00# is missed by both BETWEEN
#2005-04-01# AND #2005-04-30# and BETWEEN #2005-05-01# AND
#2005-05-31#).
I assumed the O.P. doesn't have the time component since it wasn't mentioned
in the post.
Click to expand...

Can you please post the CHECK constraint or Validation rule you
normally use to prevent time elements in your DATETIME columns <g>? My
point is, it is very rare for DATETIME columns to be explicitly
constrained for time elements and, if you are not disallowing them, you
have to expect them to turn up sooner or later and they should not be
allowed to break your app.
However, if the time component is required, it is easily fixed
using >= and <.
Click to expand...

Agreed, this is preferable to BETWEEN and not significantly less
efficient.
BTW, AFAIK, DateDiff() is a VBA function, _not_ a Jet's function.
Click to expand...

AFAIK Jet does not require VBA to be installed. When using Jet outside
of the MS Access UI, the jetvba32.dll component is used (I know that
jetvba32.dll only supports VBA5 expressions e.g. a simple test is to
use InStrRev in a query). To test that Jet's vba dll is being used, I
just wrote a simple C#.NET OleDb project and it successfully ran my Jet
query including VBA expressions even when I had renamed the VBA folder
i.e. to make the regular VBA dlls unavailable.

As regards the MS Access UI, I've no idea which vba dlls are used by
Jet and in which circumstances because I don't use MS Access (I only
seem to install it on virtual machines in relation to newsgroup posts
<g>). If you do, please post some details here.

The great thing is, with a component named 'jetvba', we're both right
<g>.

Jamie.

--
 
(Copy & paste problems on your PC?)

Validation rule: TimeValue([Fieldname]) = #00:00:00#
Validation Text: {whatever}

You can enter the above in the Validation Property of of the Field in the
DesignView of the Table in Access. (I guess you don't know because you don't
use Access much.)

Users don't normally enter the time component unless they are forced to do
so. Just a quick check in one of my old databases where the DateTime Field
is used for date only and the user manually enter the date values. Not a
single value has non-zero time component out of 40K+ Records even though I
didn't even bother setting up validation in the Table or on the Forms!

I guess in theory, we should check these things. However, in practice and
with experience (excuse for laziness, just like the users'), we know which
validations are required and which one can be assumed. Another point is
that I can't charge my client for a perfect database. I can only charge for
a practical database that is useful to the client.

Access 2002 uses VBE6.DLL. I can't find jetvba32.dll on the workstation I
am on now but there were some entries on Google about security holes in
jetvba32.dll so it may be a good thing not to have it on my work-station.
Still, the name "JETVBA" indicates that it is the VBA extension that
designed for use with JET. So, I still think DateDiff() is a VBA function
and not JET's. Find me an "official" list of JET keywords that includes
"DateDiff" then you convince me that it is a JET's function.

--
HTH
Van T. Dinh
MVP (Access)
 
Van said:
I guess in theory, we should check these things. However, in practice and
with experience (excuse for laziness, just like the users'), we know which
validations are required and which one can be assumed.
Click to expand...

Now *that* is an "official" list I'd like to see <g>.

I guess I've seen too much bad data to subscribe to that view. Perhaps
it's also my Excel background showing through: now that I've got
constraints I'm going to use them. Funny, when you think about it, that
to me assumptions mean constraints, whereas to you assumptions mean no
constraints <g>.

Thanks for sharing your views.

Jamie.

--
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

parameter query 7
retrieving previous year and month 1
Access 2000: Data Type Mismatch in query 2
Query - Date - Month Plus One? 3
multiple parameter query 10
Query to Complex with Criteria 1
Query criteria to select first day of current month 1
Years and months between 2 dates 3

Back
Top