Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

shaunacol

Programmer
Joined
Jan 29, 2001
Messages
226
Location
GB
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.
 
Why not base the crosstab on a query that returns the the last three months data?
 
That sounds great but what can I put in the criteria to make sure I get every date in the past 3 months?
 
These functions should help

Code:
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
 
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
 
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?
 
How stupid of me - I can use the code in the functions from MajP!! These work great. Thanks so much.
 
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
 
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.
 
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
 
That works fantastically - thanks for your quick response!!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top