By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,955 Members | 1,477 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,955 IT Pros & Developers. It's quick & easy.

Query Shows No Records

P: n/a
I have a problem that just showed up and I'm not sure why.

I have a query that is supposed to show all records that have an
Inspection Due Date any time in the past up until the end of the
current month.

Somehow I came up with something that worked, and has been fine for the
past year.
Now all of a sudden when I try to open the form, it doesn't have any
records, when I know it should have some.

No design changes have been made in a long time, and this form has
worked since then.

Playing with the query, I started removing items until the query showed

The part that is causing it to not find any records is the Inspection
Due criteria I mentioned above.

Here is the line:
Field: InspectionDue
Table: tblAssets
Criteria: <=CDate(CStr(DatePart("m",Date())+1) & "/1")-1

Can anyone out there help me with this criteria?

I can send the rest of the query if needed.

Thanks in advance,
Ken Mylar

Dec 21 '05 #1
Share this Question
Share on Google+
3 Replies

P: n/a
I don't have any more time to play around with this right now, but when
I created a table with dates and used your critera string I got no
results. After messing with it for a bit I ended up with:

<= CDate(DatePart("m",Now()+1) & "/1")-1

which returns the last day of last month. My statement uses Now()
instead of Date(), notice. However, they end up returning the same
thing when plugged into this formula. For now, until someone with more
experience on this comes along, you could change the "/1" to "/31" to
indicate the 31th of the current month as shown here:

CDate(DatePart("m",Date()+1) & "/31")-1

Setting the criteria date to the end of the month like this will work
fine as long as you are in a month that has 31 days. Otherwise, weird
things begin to occur.

Dec 21 '05 #2

P: n/a
Thanks for your help Steve.

I tried your statement and it seems to work fine. It returned results,
which is more than it was doing. I'll now if it returns the correct
records tomorrow morning when I can get my hands on the more accurate

It seemed strange to me that my version just stopped functioning
without any changes to the design or anything. So just for fun, I
changed my computer date back to November and it works fine. Changed it
ahead to January and it works fine, but changed back to December and it
returns no results.

Everything seems to be fine now with Steve's corrections I just just
thought it was strange that this query doesn't work in December.

Thanks again,

Dec 22 '05 #3

P: n/a
When I was playing around with the formula, I noticed that changing
some of the "1"s to anything else caused the returned result to be, for
instance, 1/12/1906. I can only assume this means that the CDATE
function is trying to convert the formula, but for whatever reason it
does not take Y2K into account and reverts back to 1900 + the last 2
digits of the proper year. So if you ran this formula in November, the
first of the coming month (December) would still be in the year 2005.
However, if you run it in December, the first of the coming month is
actually in 2006. I believe CDATE works accurately with adding years
after 2000, but I think the formula you have is confusing it, probably
since there's a CStr in there and you're manually adding the "1" for
the first of the month.

Solution? Well, outside of playing with the formula in a cartesian
manner and hoping for correct output, you could change the formula to
first check to see if the month is 12 and, if it is, eliminate the
dynamic month and date of the Now() function and use a static
end-of-month date (12/31) + the current year. You could do this with an
IIf function. I can't work with it right now, but I'll write out an
untested example of what I mean:

<= IIf(DatePart("m", Now())=12, CDate("12/31/" & DatePart("yyyy",
Now()), CDate(DatePart("m",Now()+1) & "/1")-1)

Dec 22 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.