Getting field name via a calculation

[k0r54]

Hi,

I have a cross tab query that has field names 6, 7, 8, 9, 10 representing months and a relevance date field with a date.

I have a form and from that form i need to get the value of the last three months from a relevance date field.

I.e relevance may be 01/10/2005 so i would need 3 labels, (labels are already made, but there source needs to be) 10, 9, 8. But the next row may have a relevance date of 01/08/2005 in which case it would be 8,7,6.

Any ideas on how i can do this.

Thanks Adam

 

[gromit]

Sounds like the best option would be to generate the query on the fly using Visual Basic. If you are familiar with VBA, then you could use a command button on a form to launch some code to determine the correct fields to include.

If you want to avoid VBA, then you might consider a make table query that generates a temporary table. The Table could have generic columns (Label1, Label2, Label3), some of which might be blank. Your crosstab query could read from the temporary table.

Just thinking out loud...

 

[k0r54]

mmm, ok if an admin could shift this over to vba section that would be great.

How can i do this with vba?

 

[gromit]

The key piece is to build the SQL statement using IF-THEN sort of syntax.

strSQL =  "TRANSFORM Sum(tblData.Sales) AS SumOfSales " & _
    "SELECT tblData.EmployeeID FROM tblData " & _
    "GROUP BY tblData.EmployeeID PIVOT tblData.Month " 

If (Date = #01/10/05") Then
    strSQL =  strSQL & " IN (10, 9, 8) "
elseif (Date = #01/08/2005#) Then
    strSQL = strSQL & " IN (8, 7, 6) "
end if

This SQL string can either be used for the RecordSource for a Form or as the basis for a stored Query.

- g