Cross tab with calculated column headers

[JaneC]

Hi,

I have a really strange problem that I hope someone can help with. I have a cross tab query that uses a function to calculate the column headers, the function returns "1 - 14", "15-16" etc depending on the gap between certain dates. I have Column headers set to "1 - 14","15-16" etc to match. This works perfectly on my PC and shows the expected values on the query rows, however it doesn't work on a colleagues laptop on another site where it returns 1 in the first column for each row and nothing else. As far as I can see the references are the same so I'm at a bit of a loss.


Any help or suggestions gratefully received !

Jane

 

[dhookom]

I try to avoid any derived column headings that begin with numbers. What happens if you concatenate a "D" to the beginning of your column headings?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JaneC]

I'd wondered if it was the numeric headers - will change it and see what happens !

Thanks

 

[JonFer]

Can you show the function?

Could the laptop be looking at a different table(s) - e.g. if the table is linked? A different table with only one record or something might explain it.


John

http://www.interautomateconsulting.com

 

[JaneC]

This is the function

Function CalcdayBand(NumDays)
Select Case NumDays


Case Is < 15
CalcdayBand = "1 - 14"
Case 15 To 16
CalcdayBand = "15 - 16"
Case 17 To 21
CalcdayBand = "17 - 21"
Case 22 To 28
CalcdayBand = "22 - 28"
Case Else
CalcdayBand = "After 28"

End Select
End Function


It's exactly the same database - but I do have my doubts as to how the laptop is set up. Just don't know enough about what the problem could be !

 

[dhookom]

I would declare some data types in your function:

Function CalcdayBand(NumDays as Integer) as String
Select Case NumDays
    Case Is < 15
        CalcdayBand = "From 1 - 14"
    Case 15 To 16
        CalcdayBand = "From 15 - 16"
    Case 17 To 21
        CalcdayBand = "From 17 - 21"
    Case 22 To 28
        CalcdayBand = "From 22 - 28"
    Case Else
        CalcdayBand = "After 28"
End Select
End Function

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JonFer]

Can you post a few records of the good query results and a few of the bad results?

John

http://www.interautomateconsulting.com

 

[JaneC]

Thanks for all your help. I've declared data types (oops) and changed the headers to start with alpha characters and all is working fine now !

Jane