Matching dates by month and year

[monplankton]

Hi , Looking to match to date fields within a query by using the month and year only, is it possible and how.

Thanks and I'm using Access 2003

 

[bob fitz]

Hi , Looking to match to date fields within a query by using the month and year only, is it possible and how.

I don't understand what you are trying to do.
Can you explain a little more about what you are trying to do.

 

[Brianwarnock]

To answer with the best solution we would need more info regarding how the selection is being asked for but a simple but possibly least efficient method is to use the Month and Year functions on your date field and apply your criteria to those, or maybe look at the format function.

Another would be to use the between ... And.. and use Dateserial with your criteria to search for dates between the start and end of the month.

Brian

 

[monplankton]

I have 2 date fields within my query, one is date received and the other is date of reject. If the date reject is in the same month as the received date I want to show it as 1 and any rejects from following months as zero.

 

[Brianwarnock]

Use Format on the date fields to do the comparison of mm/yyyy

Brian

 

[monplankton]

Tried that it didn't work.

 

[CazB]

try

=iif((Format([date received],"mm/yyyy"))=(Format([date of reject],"mm/yyyy")),1,0)

 

[CJ_London]

Tried that it didn't work.

It would be helpful if you can explain why it didn't work - for all we know you might have keyed it in wrong or it is producing unexpected results - for example if one of the dates is null.

another suggestion - change names to suit:

Abs(year(datereceived)=year(daterejected) and month(datereceived)=month(daterejected))

 

[monplankton]

Even though the date was formatted it still returned zero for everything with both date fields populated.
I've just tried CazB suggestion of
=iif((Format([date received],"mm/yyyy"))=(Format([date of reject],"mm/yyyy")),1,0)
and it works a treat.

 

[Brianwarnock]

Since cazb's code is exactly what I was telling you to do I haven't a clue what you did after my post that did not work, or why cazb was telling you something different.

Brian

 

[CazB]

Brian - I wasn't saying anything different, I just posted the code you suggested, in case the op hadn't understood what you meant... cos I couldn't understand why that wouldn't have worked

 

[Brianwarnock]

Ah! Ok cazb. I understand, thanks for putting me straight.

Brian

 

[CazB]

PS - your sig = one of my favourite poems... W H Davies if I remember correctly

 

[Brianwarnock]

Yes , his poem Leisure, I loved it as soon as I read it at school and remains the only poem I can still quote almost 60 years later. As a walker it suits my leisure time.

Brian