Can I set the 'column headings' field within query properties?

[shaunacol]

I would like to be able to set the 'column headings' field within query properties WIHTOUT accessing the query itself. The main reason for this is that I have a crosstab query which I want to see only the last 3 months. The only way I can figure to do this is to add the last 3 months headings into the column headings field of the query property. The problem with this is that the customer will have to update this every month. If there is a better way to get the last 3 months of info that is great, else I need a way for the customer to change the info in that field preferably without accessing the query itself as they may change something they shouldn't! Thanks for any help.

 

[MajP]

Why not base the crosstab on a query that returns the the last three months data?

 

[shaunacol]

That sounds great but what can I put in the criteria to make sure I get every date in the past 3 months?

 

[MajP]

These functions should help

Public Function LastDateM(dtmDate)
    'Return the Last date of provided month
    '
    LastDateM = DateSerial(Year(dtmDate), Month(dtmDate) + 1, 0)
End Function
Public Function firstDayM(dtmDate)
    'Return the First day of provided month
    '
    firstDayM = DateSerial(Year(dtmDate), Month(dtmDate), 1)
End Function
Public Function EndDateRange()
   LastDateM (Date)
End Function
Public Function startDateRange(MonthsPrevious As Integer)
  '
  startDateRange = firstDayM(LastDateM(Date) - (MonthsPrevious) * 31)
End Function

where yourDate >= startDateRange(3) and yourDate<= EndDateRange

 

[MajP]

you probably really want

startDateRange(2) not startDateRange(3)

MonthsPrevious
0 returns this month
1 this month and previous
2 this month and 2 prior months
3 this month and 3 prior months

 

[dhookom]

I would use the DateDiff() function as in the solution at faq703-5466

Duane
Hook'D on Access
MS Access MVP

 

[shaunacol]

Thanks so much for all the responses - I did get the datediff working! However, I had 2 problems. The row heading 'total quotes' is not showing the total quotes for the 3 months I am showing on the crosstab table -it shows the total quotes for the entire query. That made me think that maybe it is easier if I just create a query to show the last 3 months of data and base my crosstab on that?!?! The problem with this solution is that I dont know what to use to get the last 3 months data. I tried 'between date() and date()-90' which works but I cant guareentee that every month has only 30 days....

Any ideas?

 

[shaunacol]

How stupid of me - I can use the code in the functions from MajP!! These work great. Thanks so much.

 

[shaunacol]

MajP or anyone....I have just noticed that the module with the code in from above actually brings back all dates in the last 3 months regardless of the year. How can I make sure it includes only the last 3 months of data and not previous years? Thanks in advance

 

[MajP]

You will probably have to show where you are using it. The functions only return a start and end date based on a provided date. The code does not "bring back all dates", but the queries you are using might.

 

[MajP]

Sorry I posted incorrectly.

Public Function EndDateRange()
LastDateM (Date)
End Function

The above function is not returning any value. It should read

Public Function EndDateRange()
EndDateRange = LastDateM (Date)
End Function

 

[shaunacol]

That works fantastically - thanks for your quick response!!!