Return the curent month problem

[Arlend Floyd]

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

 

[Allen Browne]

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?

 

[John W. Vinson]

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]

 

[Arlend Floyd]

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

 

[Arlend Floyd]

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

 

[John W. Vinson]

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]

 

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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
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?

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?

 

[Arlend Floyd]

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.

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]