Finding difference in Month Values

[Margaret]

I'm trying to extract differences in month values - eg Values >1 (january)
and <4(april) return 11,12 ,2 & 3. How can I get results 2 & 3 or february
and march?

Thanks to any one that can help with this.

Margaret

 

[Ken Snell \(MVP\)]

You need to give us more infomation.... post the SQL statement of the query
that you're trying to run. I'm guessing, based on the values being selected,
that you're Values field/variable is a text data type, which will cause the
problem you're seeing. In text data, 11 and 12 are less than 4 because the
first "digit" (actually a text character, not a number) is used to make the
comparison, and 1 is definitely less than 4 when comparing alphanumeric
characters.

 

[Margaret]

Yes, I realise that it is taking text values, even though I have formatted
the field as 'Fixed'. - SQL statement is:
-- SELECT Month([DOA]) AS mth
FROM PolicyManager
WHERE (((Month([DOA]))>=[StartMonth?] And (Month([DOA]))<=[EndMonth?]));

Thanks
Margaret

 

[John W. Vinson]

Yes, I realise that it is taking text values, even though I have formatted
the field as 'Fixed'. - SQL statement is:
-- SELECT Month([DOA]) AS mth
FROM PolicyManager
WHERE (((Month([DOA]))>=[StartMonth?] And (Month([DOA]))<=[EndMonth?]));

Do you want 1 to return January 2002, January 2008, January 2005? Or just
January of this year?

I'd change the logic a bit to allow searching of the DOA field directly:
something like

WHERE [DOA] >= DateSerial(Year(Date()), Cint([StartMonth?], 1) AND [DOA] <
DateSerial(Year(Date()), Cint([EndMonth?])+ 1, 1)

You might need some other logic to look in last year's dates for months prior
to the current month... will you ever want to find data from November through
February, say?

John W. Vinson [MVP]

 

[Margaret]

John I sometimes need to work with months from say November to February. Do
you have a solution for this? - Thanks som much for your help
--
Margaret

Yes, I realise that it is taking text values, even though I have formatted
the field as 'Fixed'. - SQL statement is:
-- SELECT Month([DOA]) AS mth
FROM PolicyManager
WHERE (((Month([DOA]))>=[StartMonth?] And (Month([DOA]))<=[EndMonth?]));

Do you want 1 to return January 2002, January 2008, January 2005? Or just
January of this year?

I'd change the logic a bit to allow searching of the DOA field directly:
something like

WHERE [DOA] >= DateSerial(Year(Date()), Cint([StartMonth?], 1) AND [DOA] <
DateSerial(Year(Date()), Cint([EndMonth?])+ 1, 1)

You might need some other logic to look in last year's dates for months prior
to the current month... will you ever want to find data from November through
February, say?

John W. Vinson [MVP]

 

[Margaret]

I forgot to mention that I only want to return the month - eg just January
--
Margaret

Yes, I realise that it is taking text values, even though I have formatted
the field as 'Fixed'. - SQL statement is:
-- SELECT Month([DOA]) AS mth
FROM PolicyManager
WHERE (((Month([DOA]))>=[StartMonth?] And (Month([DOA]))<=[EndMonth?]));

Do you want 1 to return January 2002, January 2008, January 2005? Or just
January of this year?

I'd change the logic a bit to allow searching of the DOA field directly:
something like

WHERE [DOA] >= DateSerial(Year(Date()), Cint([StartMonth?], 1) AND [DOA] <
DateSerial(Year(Date()), Cint([EndMonth?])+ 1, 1)

You might need some other logic to look in last year's dates for months prior
to the current month... will you ever want to find data from November through
February, say?

John W. Vinson [MVP]

 

[John W. Vinson]

I forgot to mention that I only want to return the month - eg just January

and you also say

John I sometimes need to work with months from say November to February. Do
you have a solution for this? - Thanks som much for your help

Don't you see that those two requirements are DIRECTLY IN CONTRADICTION?

January *of which year*?

November *of which year*?

If you have data in your table from December 2006, and from December 2007, and
from December 2008, and you ask for all records from November through January,
which records do you want to see?


Stop. Step back a bit. Describe the nature of the data you're storing, how you
want to retrieve it, and what you want to do with it. I *suspect* you'll want
to retrieve a range of complete dates - year, month, and day to year, month,
and day - but since I don't know your problem space it's hard to say!

John W. Vinson [MVP]