How to make a chosen field from table a value for query criteria?

[Mike Watson]

Hello,

I created a table with only two columns "Month" and "FY". The table has only one row. The purpose is to change two cells content:

- once a month ("Month") and
- once a year ("FY")...

... as an admin. My idea was to take these values and use them as criteria in a query that could show me only records for the month chosen by me in "Month" column.

How make the query recognize the value of the "Month"?
Usuall "directory" like:

[tbl_CurrentMonth]![Month]

simply does not work...

I would really appreciate some suggestions!

Thanks !

[nico5038]

There are two options:
1) JOIN the table with the table you want to "filter" and change the ON option into a comparison with the Month and Year field.
2) Create a startdate and enddate function to get the year and month and use a "BETWEEN fncStart() and fncEnd()" in the query.

Getting the idea ?

Nic;o)

[Jimmy Jones]

Hi,

Is there a simpler option? I mean is there a way to show directly which cell in my table I want to use as a variable? Following my "logic" is there a way to show Access directly the "coordinates" of the value?

For example something like this:

=[tbl_CurrentMonth]![Month]![ROW1] ???

Thanks.

[nico5038]

For a "direct" comparison there's the JOIN.
The only other option I see is to create a query with a Month and FYear column and to JOIN the single row table by that.
The "cost" is in general the fact that the result won't be editable.

Nic;o)

PS: Change the name of the Month field, as it's a reserved word (and also an Access function...)

[Jimmy Jones]

Ok,

And what if I want to use a value from table in a form so that when a user opens a form such a value is already fetched as "default" ? How to fetch the value to a form?

Thanks.

[nico5038]

Use the query as the form's recordsource.
(That's why I warned about the query in my previous comment)

Nic;o)

[Jimmy Jones]

Ok, I'm getting the idea, one more beginner's question:

how to:

"...create a query with a Month and FYear column and to JOIN the single row table by that."

?

[nico5038]

The easy way is to place both tables in the graphical query editor and to JOIN on some field.
Next switch to SQL-text mode and change the field1 = field2 into e.g.
Month(Datefield) = F_Month and Year(Datefield) = FMonth

The cost is the fact that Access can't represent this in the grahical query editor and will issue warning messages !
Thus I prefer the function approach :-)

Nic;o)