date and month Parameter

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
 
V

Van T. Dinh

....
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)
 
V

Van T. Dinh

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...
 
V

Van T. Dinh

(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)
 
Top