Format Settings?

[LucyColey]

I have a report that pulls data for the previous three months, so, when I run it this month, it would pull data for May, June and July. It gets more complicated. We have a field called TRANSMONTH. This is a six charachter string that represents the month of the transaction. So, this report is pulling data for TransMonths 200105, 200106 and 200107. This is a little tricky to figure out since I need to compare a string with the Current Date. Below (sorry it's so long), is how I have put together the selection formula. It works GREAT on my machine, but it brings back NO data on another machine. If I comment it out, it runs fine. Is there some setting on my machine that might not be on the other machine that would cause this? Any other ideas?

If Month(CurrentDate) = 1
Then {TableName.TransMonth} In ((CStr(Year(DateAdd("m", -3, CurrentDate)))) & (CStr(Month(DateAdd("m", -3, CurrentDate))))) To ((CStr(Year(DateAdd("m", -1, CurrentDate)))) & (CStr(Month(DateAdd("m", -1, CurrentDate)))))

Else If Month(CurrentDate) In [2, 3]
Then {TableName.TransMonth} In [(((CStr(Year(DateAdd("m", -3, CurrentDate)))) & (CStr(Month(DateAdd("m", -3, CurrentDate)))))), (((CStr(Year(DateAdd("m", -2, CurrentDate)))) & (CStr(Month(DateAdd("m", -2, CurrentDate)))))), (((CStr(Year(DateAdd("m", -1, CurrentDate)))) & '0' & (CStr(Month(DateAdd("m", -1, CurrentDate))))))]

Else If Month(CurrentDate) In 4 To 10
Then {TableName.TransMonth} In ((CStr(Year(DateAdd("m", -3, CurrentDate)))) & '0' & (CStr(Month(DateAdd("m", -3, CurrentDate))))) To ((CStr(Year(DateAdd("m", -1, CurrentDate)))) & '0' & (CStr(Month(DateAdd("m", -1, CurrentDate)))))

Else If Month(CurrentDate) In [11, 12]
Then {TableName.TransMonth} In ((CStr(Year(DateAdd("m", -3, CurrentDate)))) & '0' & (CStr(Month(DateAdd("m", -3, CurrentDate))))) To ((CStr(Year(DateAdd("m", -1, CurrentDate)))) & (CStr(Month(DateAdd("m", -1, CurrentDate)))))

 

[ScottGS]

Lucy,

Two ideas popped into my head...
One, and this sounds obvious but I've overlooked it before, is the PC clock. Make sure both are set correctly.

Two; Date display settings in Windows. I'm not sure if they affect the behaviour of these date and time functions, but you may get some insight if you create a couple formulas to display on the report which contain intermediate results from your selection formula. For example, "CStr(Month(DateAdd("m", -3, CurrentDate)))." Preview it on each machine and make sure it displays the same thing.

One other idea would be to convert {TableName.TransMonth} to a date and do the comparison in that domain. Use "Date (ToNumber (left({TableName.TransMonth},4)),ToNumber (right({TableName.TransMonth},2)) , 1)" to get a date value of the first day of that month. I think that would simplify the formulas and make them somewhat easier to follow. You might even code this into its own formula and then refer to it in the selection formula.

Scott