469,128 Members | 1,661 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,128 developers. It's quick & easy.

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

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 !
Oct 18 '10 #1
7 862
nico5038
3,080 Expert 2GB
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)
Oct 18 '10 #2
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.
Oct 18 '10 #3
nico5038
3,080 Expert 2GB
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...)
Oct 18 '10 #4
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.
Oct 18 '10 #5
nico5038
3,080 Expert 2GB
Use the query as the form's recordsource.
(That's why I warned about the query in my previous comment)

Nic;o)
Oct 18 '10 #6
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."

?
Oct 18 '10 #7
nico5038
3,080 Expert 2GB
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)
Oct 18 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Ray Holtz | last post: by
5 posts views Thread by epost2 | last post: by
1 post views Thread by Coll | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.