I have a form, that when I open it, it shows all items that are due for
inspection in the current month and that is overdue. This is shown in
continuous view and it has a column that shows overdue months.
So if it was November it would show all the assets due in November with
a "0" in the Overdue column. And if there happen to be some October
left they would be shown with a "1" in the overdue column.
This worked perfect all year until I hit December and no results were
shown. After a quick check of the assets, I seen it should show a bunch
of results for December and November.
What I found was if I change my computer date back to November it works
fine, and if I move it January it works fine, but not December.
My query used this criteria on the InspectionDue field to get the
results:
<=CDate(CStr(DatePart("m",Date())+1) & "/1")-1
Someone posted this criteria for me to try:
<= CDate(DatePart("m",Now()+1) & "/1")-1
This seemed to work but when I looked closer it still didn't show
December's records, but it did show November's...which was better then
the one I used. From what I understand about the statement it is taking the current
month, adding a month, going back to the 1st of the month, and
subtracting a day. I think its having a problem with the new year, but
not sure if the fix. The month is almost over and I'm sure all will be
well, but I looking out for next year.
If anyone has some better date functions that would work I would
appreciate it.
Thanks,
Ken 10 1666
"Ken" <km****@elch.org> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com... I have a form, that when I open it, it shows all items that are due for inspection in the current month and that is overdue. This is shown in continuous view and it has a column that shows overdue months.
So if it was November it would show all the assets due in November with a "0" in the Overdue column. And if there happen to be some October left they would be shown with a "1" in the overdue column.
This worked perfect all year until I hit December and no results were shown. After a quick check of the assets, I seen it should show a bunch of results for December and November.
What I found was if I change my computer date back to November it works fine, and if I move it January it works fine, but not December.
My query used this criteria on the InspectionDue field to get the results:
<=CDate(CStr(DatePart("m",Date())+1) & "/1")-1
Someone posted this criteria for me to try:
<= CDate(DatePart("m",Now()+1) & "/1")-1
This seemed to work but when I looked closer it still didn't show December's records, but it did show November's...which was better then the one I used.
From what I understand about the statement it is taking the current month, adding a month, going back to the 1st of the month, and subtracting a day. I think its having a problem with the new year, but not sure if the fix. The month is almost over and I'm sure all will be well, but I looking out for next year.
If anyone has some better date functions that would work I would appreciate it.
Thanks, Ken
The first day of the current month is:
DateSerial(Year(Date),Month(Date),1)
so to get the first day of the next month we need to add a month:
DateAdd("m",1,DateSerial(Year(Date),Month(Date),1) )
All matching dates must be less than the first of next month, so your
criteria is:
<DateAdd("m",1,DateSerial(Year(Date),Month(Date),1 ))
I think...
Well, the only difference between using Date() and Now() is that Now()
includes both the current date and time, Date() gives just the current date.
This should make NO difference in the code you've displayed since you're
only using the month.
The problem you're running into is that "DatePart("m",Date())+1" returns 13
if the month is December. You than concatenate "/1" to it, resulting in
"13/1". Next, you use CDate() to convert this to a date. If the year is
missing, the current year is assumed, so this becomes "13/1/2005". However,
since there aren't 13 months, VBA assumes this to be dd/mm/yyyy format and
converts it to 13 Jan 2005. Next, you subtract 1, which leaves you with 12
Jan 2005.
Now, to do what you're wanting. I will give this a try, but I need to know
what value is in the field that this criteria is for. Is it a full date or
just a month and year, or what? Do you want to handle anything in the month
the same or should it change part way through the month as the due date
passes (i.e. is it not over due until Dec 1, even if it was due 15 Nov)?
Try:
<=DateSerial(Year(Date()), Month(Date()) + 1, 0)
This will return the last day of the current month. In other words, for
December, this will return 31 December of the current year. The 0 at the end
is actually 1 - 1 (subtract 1 day from the first day of the next month).
--
Wayne Morgan
MS Access MVP
"Ken" <km****@elch.org> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com... I have a form, that when I open it, it shows all items that are due for inspection in the current month and that is overdue. This is shown in continuous view and it has a column that shows overdue months.
So if it was November it would show all the assets due in November with a "0" in the Overdue column. And if there happen to be some October left they would be shown with a "1" in the overdue column.
This worked perfect all year until I hit December and no results were shown. After a quick check of the assets, I seen it should show a bunch of results for December and November.
What I found was if I change my computer date back to November it works fine, and if I move it January it works fine, but not December.
My query used this criteria on the InspectionDue field to get the results:
<=CDate(CStr(DatePart("m",Date())+1) & "/1")-1
Someone posted this criteria for me to try:
<= CDate(DatePart("m",Now()+1) & "/1")-1
This seemed to work but when I looked closer it still didn't show December's records, but it did show November's...which was better then the one I used.
From what I understand about the statement it is taking the current month, adding a month, going back to the 1st of the month, and subtracting a day. I think its having a problem with the new year, but not sure if the fix. The month is almost over and I'm sure all will be well, but I looking out for next year.
If anyone has some better date functions that would work I would appreciate it.
Thanks, Ken
First of all, thanks for the great explanation, I never knew how it
processed this information and now it makes complete sense as to why it
doesn;t work in December.
My overdue column was also based on this same criteria, so it also
explains why it would mess up when the inpsection due was late in the
month. It would show that it was 11 months overdue if it was due
somewhere around the last day of the month.
To answer your question, the field is a date field, using short date
format, mm/dd/yy.
The way it should be handled is if it's December and it's due anytime
in December, it should be current (not overdue). So if the due date was
12/01/05 and the date was today, it should still be good until 01/01/06
then it should say 1 month overdue.
Thanks,
Ken
Your welcome. One thing to be careful of though. You have just a date in
your field, so what I posted should work ok. When there is just a date in
the field, the time is assumed to be midnight (i.e. zero). If the field
should have a date and time, you can't then just check for <= the date,
because you would be checking for <= midnight on that date. In this case,
you would actually check for < the next day instead of <= today.
--
Wayne Morgan
MS Access MVP
"Ken" <km****@elch.org> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com... First of all, thanks for the great explanation, I never knew how it processed this information and now it makes complete sense as to why it doesn;t work in December.
My overdue column was also based on this same criteria, so it also explains why it would mess up when the inpsection due was late in the month. It would show that it was 11 months overdue if it was due somewhere around the last day of the month.
To answer your question, the field is a date field, using short date format, mm/dd/yy.
The way it should be handled is if it's December and it's due anytime in December, it should be current (not overdue). So if the due date was 12/01/05 and the date was today, it should still be good until 01/01/06 then it should say 1 month overdue.
Thanks, Ken
Thanks again for you help. That looks to have fixed part of it, but I
was wondering if you could look at my overdue criteria?
I put this field in my query (it used to have the same criteria and I
used before, minus the <=)
EndofMonth: DateSerial(Year(Date()),Month(Date())+1,0)
Then I have this one-
Ovd: Format(DateDiff("d",[InspectionDue],[EndofMonth]),"mm")
My overdue column is controlled by this Ovd field, but it is a month
off. Current inspections, like for December, show 1 in the overdue
column instead of a zero. I previously created another field called
Ovd2: Ovd-1 and then it looked good, but that's probably not the
correct method.
Thanks for your help,
Ken
Anthony England wrote: All matching dates must be less than the first of next month, so your criteria is: <DateAdd("m",1,DateSerial(Year(Date),Month(Date),1 ))
I think...
8) 8) I couldn't resist this one...
If you're asking for confirmation of what you've written, it's difficult
to tell when it is written in a "code of a sufficiently poor standard".
Tee hee hee.
Just joking, merry christmas to you. 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Anthony England wrote: The first day of the current month is: DateSerial(Year(Date),Month(Date),1)
so to get the first day of the next month we need to add a month: DateAdd("m",1,DateSerial(Year(Date),Month(Date),1) )
All matching dates must be less than the first of next month, so your criteria is: <DateAdd("m",1,DateSerial(Year(Date),Month(Date),1 ))
I think...
Although what you show is correct, DateSerial should automatically
convert a month 13 into 1 so:
< DateSerial(Year(Date), Month(Date) + 1, 1)
would be a little simpler.
James A. Fortune CM********@FortuneJames.com
"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:do**********@coranto.ucs.mun.ca... Anthony England wrote:
All matching dates must be less than the first of next month, so your criteria is: <DateAdd("m",1,DateSerial(Year(Date),Month(Date),1 ))
I think...
8) 8) I couldn't resist this one...
If you're asking for confirmation of what you've written, it's difficult to tell when it is written in a "code of a sufficiently poor standard".
Tee hee hee.
Just joking, merry christmas to you. 8) -- Tim http://www.ucs.mun.ca/~tmarshal/ ^o< /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake /^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Indeed. Not only did no-one want to steal it, but the OP didn't even want
it handed to him for free!
Merry Christmas,
<CD********@FortuneJames.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com... Anthony England wrote:
The first day of the current month is: DateSerial(Year(Date),Month(Date),1)
so to get the first day of the next month we need to add a month: DateAdd("m",1,DateSerial(Year(Date),Month(Date),1) )
All matching dates must be less than the first of next month, so your criteria is: <DateAdd("m",1,DateSerial(Year(Date),Month(Date),1 ))
I think...
Although what you show is correct, DateSerial should automatically convert a month 13 into 1 so:
< DateSerial(Year(Date), Month(Date) + 1, 1)
would be a little simpler.
James A. Fortune CM********@FortuneJames.com
.... should automatically convert a month ... sounds a little less assertive
than it needs to. Without sounding too sure of yourself, I think you could
write ... will automatically convert ...
Merry Christmas
Anthony England wrote: ... should automatically convert a month ... sounds a little less assertive than it needs to. Without sounding too sure of yourself, I think you could write ... will automatically convert ... Merry Christmas
Thanks for the feedback. You're absolutely correct. But then again
you can't always count on Access doing something the same way
everytime. I shall be more assertive in the future! I think :-).
James A. Fortune CD********@FortuneJames.com This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: PMBragg |
last post by:
Thank you in advance. I'm trying to pull all inventory items from December
of the previous year back to 4 years for my accountant. I know this can be
done, but I'm drawing a blank. I've tried;
...
|
by: PMBragg |
last post by:
ORINGINAL Post
>Thank you in advance. I'm trying to pull all inventory items from December
>of the previous year back to 4 years for my accountant. I know this can be
>done, but I'm drawing a...
|
by: Colm O'Brien |
last post by:
I
have a field called year end month where records hold the month financial
year end for accounts is stored.
i need to query the data base and return all records where year end month is
less...
|
by: Kevin |
last post by:
Hi
I have the code below that allows me to test the result of a query
The query is a crosstab and pivots on months
what i want to do is check if the field december is different to blank
and if so...
|
by: Ken Mylar |
last post by:
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...
|
by: Tina |
last post by:
My employer tracks productivity/performance of clinicians (how much
they
bill) each week, its averages for the month, and the 6 months.
These averages are compared to their expected productivity....
|
by: patrickkellogg |
last post by:
I have this code when you click the buttom is suppose to add a job
history. it works with firefox, opera, but not ie. (please note -
new entries don't have all the elements in them yet, but...
|
by: Stan |
last post by:
I am working with Access 2003 on a computer running XP. I am new at
using Access. I have a Db with a date field stored as mm/dd/yyyy. I
need a Query that will prompt for the month, ie. 6 for...
|
by: craigfr |
last post by:
I am making a graph comparing last year's defect data with YTD defect data. Our fiscal year starts Nov.1 and ends Oct.31.
To get the YTD, I started used a simple date serial criteria:
Between...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |