Returning a value to a query

[k0r54]

Hi,

I have a query and in the field i have

Expr1: FieldName(Format(DateSerial(2005,Month(Date())-1,1),"mm"))

This will run the function below with the number 10 in (this month anyway)

Function FieldName(MMonth)
  FieldName = "[" & MMonth & "]"
End Function

The problem is, instead of then putting the value of field 10 (within the query) it actually puts in txt off [10].

Any ideas?

 

[KeithWilliams]

Hi,

I think its behaving correctly. You had better explain the context a bit more - what are you trying to achieve? Do you want Expr1 to return the value of the 10th column in the query? Why not just use the name of the 10th column, instead of the expression?

Keith.

 

[k0r54]

no,

In the table i have 6 7 8 9 10 representing months. I have got a make table query making the table every month but everything month the table will grow and have 7 8 9 10 11. For this puticular query. I only need the last 3 months, but every month it will change. I know i cant call a field by an expression. Like

Format(DateSerial(2005,Month(Date())-1,1),"mm")

Although it produces the number 10. It will not call the field name 10.

This is why i made the function, to hopefully get the value of field 10.

Hope this explains more so you can help

Thanks
k0r54

 

[KeithWilliams]

Hi,

I suggest you post the table structure (column names and data types) identifying the primary key. Post some sample data, and an example of the output you are trying to get. I understand your problem, and it does not sound too difficult from what you have described.

Keith.

 

[k0r54]

Ok the table and there data types are: -

Line Address: - number -PK
Site - Text
6 - double
7 - double
8 - double
9 - double
10 - double

Data would be

21
kilmarnock
97.64
28.64
25.84
59.46
89.40

Hope this helps

k0r54

 

[KeithWilliams]

Hi,

You will need to change the table to have values for all 12 months. You only need to populate the values for the months you want.

Then try Expr1: Choose(Month(Date()),[12],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11])

Keith.

 

[k0r54]

is choose a function, or a built in function?

 

[KeithWilliams]

Hi,

It is a built-in function.