Day, month, and current year

[Bruce]

My database contains expiration dates (of certificates) up
to four years in the future. If the expiration date is
during the current year, we need to request a new
certificate. In that case I have made a query that flags
everybody with a certificate that is due to expire in the
next thirty days. I run the query about weekly. If the
expiration date is not this year, we still need to obtain
confirmation that the certificate is still in effect. I
can construct (concatenate) an expression that combines
the month and day from the expiration date field with the
current year (if the certificate expires on 11/1/06, the
review date is 11/1/04, and I would send out a reminder on
10/1/04). It looks right when I run the query, but I
cannot filter it with the Between function:
Between Date() And DateAdd("m",1,Date())
This is a reworded reposting of an earlier question to
which I received no replies.

 

[Jeff Boyce]

Bruce

What happens in your query when you try using your "Between" statement?

Can you post the SQL statement behind the query?

What happens if you modify your query and use "hard-coded" values in your
"Between" statement?

Do you have a single field holding the ExpirationDate in your table
structure?

 

[Bruce]

Can you post the SQL statement behind the query?

Rather than posting the whole statement, I have included
here just the relevant code. ReviewDate is a calculated
field, and ExpDate is a Date/Time field in the underlying
table (and in the query).
ReviewDate: Format([ExpDate],"mm/dd/")+Format(Date(),"yy")

What happens in your query when you try using

your "Between" statement?

The statement (in the criteria for ReviewDate) is:
Between Date() And DateAdd("m",1,Date())
If the expiration date is 11/1/05, I see 11/1/04 in the
ReviewDate field (as I had hoped), but the filter is being
applied in some way I can't discover. When I use no
filtering I receive 54 records, while with the filter
above I receive 47 (all of the records for Oct. 31 through
Dec. 31 for whatever reason). If I change it to "m",2,Date
() I receive records only from Nov. 30 through Dec. 31.
If I use "m",3 etc. I receive records only for Dec. 31.
If I use "m",4 etc. I receive all dates from the present
through Dec. 31. Again, this attempt to filter is being
applied to a calculated field. The field itself clearly
is all wrong. If I try hard coded dates, such as Between
#10/1/04# And #11/1/04# I get a little closer in that I
see more of October, but I also receive records for
11/5/04.

By the way, if I use (in the calculation for ReviewDate):
ReviewDate: Format([ExpDate],"m/d/")+Format(Date(),"yy") I
receive a different record subset when the filters are
applied than when I use "mm/dd/".

Do you have a single field holding the ExpirationDate in

your table structure?

Yes. It is a date time field, formatted m/d/yy. The
criteria in the query is: DateSerial(Year(Date())+1,1,0)

What happens if you modify your query and use "hard-

coded" values in your "Between" statement?

Unpredictable. ReviewDate is not being handled as a
date. I don't know why.

 

[Jeff Boyce]

Bruce

Your last comment may be most telling -- your ReviewDate is NOT a date.
From your earlier statement, you've created a text field that consists of
the month and day of your ExpDate, concatenated with the year of the current
date. Just because it looks like a date doesn't mean Access treats it like
one.

If you want to use the month and day of your ExpDate and the year of Date(),
try using the Month(), Day(), and DateSerial() functions. This will
generate a date, which you can use in a "Between" expression.

 

[Bruce]

After quite a while of research and experimentation I have
finally come up with ReviewDate: DateSerial(Year(Date
()),Month([ExpDate]),Day([ExpDate])) for the calculated
field. Again, it looks right, and again I cannot apply
criteria. Attempts to use Between with actual dates tells
me again that I have not done it correctly. Attempts to
use Date(), DateSerial and other functions in the criteria
expression produce only the information that i have a type
mismatch.
Please remember that I asked the question in the first
place because I do not know how to do this. I have made
every attempt to research this on my own, but I am stuck.
I have put hours I do not really have to spend into trying
to solve what I thought was a simple problem. I do not
know how to use the functions, Help contains nothing
useful (in the case of DateSerial, nothing at all), and
newsgroup searches return thousands of hits and no real
help for my particular situation. Please do me the favor
of telling me the actual expressions I need to use.

 

[Jeff Boyce]

Bruce

I do not know why the Between expression is not working. I would use the
same DateSerial(xxx) expression you used.

I'll return to my earlier request -- please post the SQL statement. You can
do this by opening the query in design mode, changing to the SQL view,
copying the SQL statement and pasting it into your post back to this group.

 

[Guest]

I inherited the database from somebody who used
questionable naming practices. I should have started by
renaming objects and fields, but I did not, so until I can
take the time to clean it up I am stuck with it. That is
why field names have parentheses, apostrophes, and
question marks. If that is causing problems, I guess I
will have to redo the names, but I would rather not take
the time just now. I have shortened Master Supplier List
(Main Table) as you see, and in all cases I have used
lower case letters for field names, although they are
upper case in the actual database. Exp Date is a
Date/Time field, Approved? is Yes/No, and the rest are
text. The following error message shows up when I try to
see the query in datasheet view: "Data type mismatch in
criteria expression".
Thanks for taking the time to help with this.

SELECT [Master List (Main Table)].[Supplier Name], [Master
List (Main Table)].[Exp Date], [Master List (Main Table)].
[Type Form Ret'd], [Master List (Main Table)].Fax, [Master
List (Main Table)].[Purchase Type], DateSerial(Year(Date
()),Month([Exp Date]),Day([Exp Date])) AS ReviewDate
FROM [Master List (Main Table)]
WHERE ((([Master List (Main Table)].[Exp Date])>DateSerial
(Year(Date())+1,1,0)) AND ((DateSerial(Year(Date()),Month
([Exp Date]),Day([Exp Date]))) Between Date() And DateAdd
("m",1,Date())) AND (([Master List (Main Table)].
[Approved?])=Yes))
ORDER BY [Master List (Main Table)].[Supplier Name],
[Master List (Main Table)].[Exp Date];

 

[Jeff Boyce]

Bruce

I don't know if it's relevant, but I noticed that every single reference to
a table field in your query is fully qualified ([tablename].[fieldname])
EXCEPT in the Between ... And ... clause. Again, don't know if that's
important.

Have you already tried starting over from scratch, and building a new copy
of the query? Once or twice I've encountered a corrupted query that doesn't
show up and doesn't "compact/repair". In those cases, I was able to start
over, build a query that did what I wanted, and delete the old,
no-longer-functional query.

Another possibility would be to "chain" (nest) a pair of queries. In the
first, calculate the [ReviewDate] and return the other fields you want --
apply all criteria except the Between ... And .... In the second, apply the
Between ... And ... criteria to [ReviewDate].

 

[Bruce]

Thanks again. I will try starting from scratch. In the
current situation I started with a copy. Also I see how
that would work with nested queries, or basing a query on
another query.
The fully qualified references to table fields are what
happened when I built the query and then used SQL view.
It certainly is worth giving it a try for the Between
criteria, although it worked as is in other queries. That
too would seem to suggest a corrupted query, and point
toward starting over (which I should really do for the
whole database as soon as a dozen other project are
completed). I appreciate all of the time and thought you
have put into this.