Return the curent month problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Arlend Floyd - 01 Feb 2008 04:04 GMT

In the critera i am using "Month(Now())" the field is date/time with no
format or input mask.

But it returns no records???

thanks Arlend

Reply to this Message

Allen Browne - 01 Feb 2008 04:47 GMT

You placed this in a query, in the Criteria row, under what type of field?

If it is under a Date/Time field, you are limiting the records to only
January 1 1900. Month will return 2, and that's the date that is represented
internally by the number 2.

What did you intend?
Only the current month of the current year?
Only the current month, regardless of year?

Or is this under a Text field with values like "Feb"?
Or under a Number field with numbers like 2?

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> In the critera i am using "Month(Now())" the field is date/time with no
> format or input mask.
>
> But it returns no records???
>
> thanks Arlend

Reply to this Message

Arlend Floyd - 01 Feb 2008 05:26 GMT

It is under date/Time field like "1/1/08" and im looking for current month
and year. I also created an expression "Pmt Date Month: [PmtDate] and put in
the critera "Month(Now())" and I also created an expression "Pmt Date Year:
[PmtDate] and put in the critera "Year(Now())"

But no records?

Could I be a missing library?Because this has worked for me in past DB's.

Thanks Arlend

> You placed this in a query, in the Criteria row, under what type of field?
>
[quoted text clipped - 15 lines]
> >
> > thanks Arlend

Reply to this Message

Allen Browne - 01 Feb 2008 06:09 GMT

I guess you didn't understand the explanation given.

You did tell us the field type, thanks.

You did not answer the question about what you intend, so I can't advise on
what expression you need.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> It is under date/Time field like "1/1/08" and im looking for current month
> and year. I also created an expression "Pmt Date Month: [PmtDate] and put
[quoted text clipped - 28 lines]
>> >
>> > But it returns no records???

Reply to this Message

John W. Vinson - 01 Feb 2008 05:15 GMT

>In the critera i am using "Month(Now())" the field is date/time with no
>format or input mask.
>
>But it returns no records???
>
>thanks Arlend

Well, it wouldn't.

The Month(Now()) call returns the number of the current month, an integer 1
today, 2 tomorrow, 12 next December.

A date/time field is a Double Float, a count of days and fractions of a day
since midnight, December 30, 1899. I doubt that your date field contains
integer values between 1 and 12, so they won't match!

To get records during the current month, use a criterion of

>= DateSerial(Year(Date()), Month(Date()), 1) AND < DateSerial(Year(Date()), Month(Date()) - 1, 1)

John W. Vinson [MVP]

Reply to this Message

Arlend Floyd - 01 Feb 2008 05:43 GMT

But that dosent explain why the same criteria "Month(Now())" works for me in
different DB on a different computer.

Could I be a missing library?

Thanks

Arlend

> >In the critera i am using "Month(Now())" the field is date/time with no
> >format or input mask.
[quoted text clipped - 17 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 01 Feb 2008 05:55 GMT

>But that dosent explain why the same criteria "Month(Now())" works for me in
>different DB on a different computer.

As described - IT DOESN'T work on your other computer.

I suspect that the criterion on the other computer is being applied, not to a
Date/Time field, but to a calculated field such as Month([datefield]).

Here's a plain, unvarnished fact: the function Month(Now()) will return an
integer number 1 for me for the next hour or so. Then it will be February, and
the function will return an integer number 2. This is not a library error,
this is not a mistake, this is not a bug - this is precisely what the Month()
function is documented to do, and precisely what it is doing on your computer.
Try it. Type Ctrl-G to open the VBA editor window, and type

?Month(Now())

in the Immediate window. See what you get. Try it on the other database. See
what you get there.

John W. Vinson [MVP]

Reply to this Message

Arlend Floyd - 01 Feb 2008 06:36 GMT

Yes it was a calculated field as I discribed in my reply to Allen Browne I
thought you understood that. Anyways thanks it works now.

Sorry for the confusion.

Arlend

> >But that dosent explain why the same criteria "Month(Now())" works for me in
> >different DB on a different computer.
[quoted text clipped - 17 lines]
>
> John W. Vinson [MVP]

Reply to this Message