format to mmm-yy

[JimmyS]

This little bit of code is filling page header text boxes in a report with data from crosstab headers - its in the PageHeaderSection_Format section.

For intX = 1 To intColumnCount
        Me("Head" + Format$(intX)) = rstReport(intX - 1).Name
    Next intX

what I'm looking to do is format these text boxes as mmm-yy - they are dates in the format dd/mm/yy at the moment. I've added the code below ('for intx = 2' because the first column heading isn't a date) and it works but....

  For intX = 2 To intColumnCount
       Me("Head" + Format$(intX)) = Format(Me("Head" + Format$(intX)), "mmm-yy")
    Next intX

Ideally I would like the code to test if the value is a date and if it is set it to mmm-yy, also ideally in that same first bit of code where the headers are added to save a bit of time as the report creation is pretty slow already.

I want to be able to use this for other queries where the column headings may not be dates without having to alter the code.

jim

[namliam]

You could offcourse if its allways this way... do this:

Me("Head1" ) = rstReport(0).Name
For intX = 2 To intColumnCount
Me("Head" + Format$(intX)) = Format(Me("Head" + Format$(intX)), "mmm-yy")
Next intX

Or if you really really need to check for a date use the functions IsDate()

Regards

[JimmyS]

Thanks - that will definately work for this instance - and is tidier than my adding another for..next loop - however I was thinking that perhaps I might have a crosstab where 1 or 2 or even all the column headers aren't dates so that's why I was thinking there must be a way to do the test...

jim

[namliam]

Yep there is... as I also posted by using the IsDate([field]) function

[JimmyS]

doh ! sorry didn't see for looking - thanks for the suggestion

Now i'm going for the isdate option yet i'm struggling mixing if...thens with for...nexts.

 For intX = 1 To intColumnCount
   If IsDate(Me("Head" + Format$(intX)) = rstReport(intX - 1).Name) Then
   Me("Head" + Format$(intX)) = Format(rstReport(intX - 1).Name, "mmm-yy")        
  Else
      Me("Head" + Format$(intX)) = rstReport(intX - 1).Name
    End if
    Next intX

The dates don't change format

[namliam]

For intX = 1 To intColumnCount
If IsDate(Me("Head" + Format$(intX)) = rstReport(intX - 1).Name) Then
Me("Head" + Format$(intX)) = Format(rstReport(intX - 1).Name, "mmm-yy")
Else
Me("Head" + Format$(intX)) = rstReport(intX - 1).Name
End if
Next intX

Oh come on your kidding right??? What is supposed to be date??? The Head thing right??? So only isdate(headthing)

If IsDate(Me("Head" + Format$(intX))) = rstReport(intX - 1).Name Then

[JimmyS]

cheer - the penny just dropped literally seconds before i saw your post....I was just about to post that i'd got it and I'm a fool

Thanks namliam

- i'll remember to be less swift to post my problems when a little break can make things clearer

Its been a long day of staring at the same few lines of code - something I do very rarely..

i wrote this and it works to test each column header for a date:

For intX = 1 To intColumnCount
        If IsDate(rstReport(intX - 1).Name) Then
        Me("Head" + Format$(intX)) = Format(rstReport(intX - 1).Name, "mmm-yy")
        Else
        Me("Head" + Format$(intX)) = rstReport(intX - 1).Name
        End If
   Next intX

thanks again for taking the time to post.