Simple Record as Variable Ques.

  • Thread starter Thread starter Uncle Pete
  • Start date Start date
U

Uncle Pete

Trying to use a table value as a variable in a simple query. Access
2000 running on Win98SE.

(Disclaimer: I apologize if this is something exceptionally easy that
even a moron should know how to do or if it’s in the book [I should RTFM])

Here’s the situation:

Table 1 is a name /address list called “Sales”. Each entry has standard
name and address data and a “Terminal ID” field, which is unique and the
index.

Table 2, “Transactions” has the Terminal ID, transactions and a numeric
field called “Month” (i.e. 7 for July, 8 for August, etc.).

Table 3 is called “Current Month” and has a single numeric entry (i.e. 7
for July, 8 for August, etc.).

I’m trying to make a select query in design view* that will join the
name and address data and the transactions only for the month listed in
current month.

I tried using [Tbl Transactions]![Month] equal to [Tbl Current
Month]![Current Month] as criteria, but the query returns all
transactions regardless of the value in the Month field of the
Transactions table.

* I’ll try to work w/SQL if I have to but I’d rather not (I’m a hardware
guy).

Thanks in Advance

Uncle Pete
 
Why not just put a statement like this in the criteria
section of your query. [Input Current Month] Access will
not recognize the var as being defined and pop up a enter
parameter dialog asking user to input the value to use.
That way if you wanted a previous month or something you
could do that too. You don't need to keep a table to
just hold a single value.


Hope this makes sense to you!
Wally
 
I'm not sure why you're using Table 3. Couple of
solutions:

1. In the Month field of the query, use the function "Month
(Date())" as the Criteria. That will bring up the current
month's records.

2. Use a parameter query, [Select a Month] in the criteria
field for a little more flexibility. Doing September's
book on October 1, enter 9 when requested. The first
example only allows October records starting October 1.

I think Month is a reserved term that Access uses for
funtions. You can run into problems naming fields with
reserved terms. Suggest a name field name like
CurrentMonth or even aMonth.

Your example will work if Table 3 has only 1 record, the
number you want for current month. But changing the table
every month seems to be extra work.

Roxie Aho
roxiea at usinternet.com
 
In your query grid design under the field that is your transaction date you
can simply add in the criteria Format(Date(),"mm"). This will return the
current transactions for the current month. The previous post using a
perameter to select which month, works great if you are wanting to change
which month to view. I don't see the benefit of table 3 either.
 
1. In the Month field of the query, use the function "Month
(Date())" as the Criteria. That will bring up the current
month's records.
Click to expand...

Not necessarily. It will bring up all records for the month matching the
current month but for all years! You also have to match Year(Date) if you
have more than one year's worth of data in the table.

Tom Lake
 
This will work great too if the actual month that the
computer will return is always the month you want the
data of. If you run the query on Mon the 3rd and you
want last months data, it won't work for you. It is a
common thing to allow the user to input the criteria,
especially if it is only a 7 or 8 that's going to be
entered. It needs to be known what kind of data is
needed though. e.g. If the field needs an integer and you
enter Aug or July or a text representation of the month
then it will return nothing or produce an error.

Just some more input,
Wally


-----Original Message-----
In your query grid design under the field that is your transaction date you
can simply add in the criteria Format(Date(),"mm"). This will return the
current transactions for the current month. The previous post using a
perameter to select which month, works great if you are wanting to change
which month to view. I don't see the benefit of table 3 either.

Uncle Pete said:
Trying to use a table value as a variable in a simple query. Access
2000 running on Win98SE.

(Disclaimer: I apologize if this is something exceptionally easy that
even a moron should know how to do or if itâ?Ts in the book [I should RTFM])

Hereâ?Ts the situation:

Table 1 is a name /address list called â?oSalesâ?. Each entry has standard
name and address data and a â?oTerminal IDâ? field, which is unique and the
index.

Table 2, â?oTransactionsâ? has the Terminal ID, transactions and a numeric
field called â?oMonthâ? (i.e. 7 for July, 8 for August, etc.).

Table 3 is called â?oCurrent Monthâ? and has a single numeric entry (i.e. 7
for July, 8 for August, etc.).

Iâ?Tm trying to make a select query in design view* that will join the
name and address data and the transactions only for the month listed in
current month.

I tried using [Tbl Transactions]![Month] equal to [Tbl Current
Month]![Current Month] as criteria, but the query returns all
transactions regardless of the value in the Month field of the
Transactions table.

* Iâ?Tll try to work w/SQL if I have to but Iâ?Td rather not (Iâ?Tm a hardware
guy).

Thanks in Advance

Uncle Pete
Click to expand...
.
Click to expand...
 
Folks,

Thank you for your responses, but they don't get me where I want to go.

Here is some additional info:

At last count I have 27 different query/reports that need to be run from
this data every month. Some times I need to go back and get earlier
month's data. I would also like to macro some of these together.
That's why I want to *explicitly* state the month I'm looking for *one*
time and have it apply to all queries, hence table 3. I'd rather not
have to enter the month 27 times as the queries run and I don't want to
use anything involving the current month because that may not be the one
I'm looking for.

Tom Lake: I know, but if I can get the month selection right, I can fix
the year in a similar fashion.

Thanks Again

Uncle Pete
 
Uncle Pete,
I think the way for you to do this is to create a form
that will pop up each time you start running your queries,
that will allow you to input the month criteria on that
form and this form should stay open (perhaps hidden)until
all reports are run but each would refer to the same
textbox value on this form. It will work in the same
fashion as a var [Current Month] in your query criteria
statement except the value is refered to by all queries
across all reports. I don't have time right now to give
you an exact example but if you understand what I'm saying
this might get you going in the right direction.

Hope this helps,
Wally



-----Original Message-----
Folks,

Thank you for your responses, but they don't get me where I want to go.

Here is some additional info:

At last count I have 27 different query/reports that need to be run from
this data every month. Some times I need to go back and get earlier
month's data. I would also like to macro some of these together.
That's why I want to *explicitly* state the month I'm looking for *one*
time and have it apply to all queries, hence table 3. I'd rather not
have to enter the month 27 times as the queries run and I don't want to
use anything involving the current month because that may not be the one
I'm looking for.

Tom Lake: I know, but if I can get the month selection right, I can fix
the year in a similar fashion.

Thanks Again

Uncle Pete

Uncle said:
Trying to use a table value as a variable in a simple query. Access
2000 running on Win98SE.

(Disclaimer: I apologize if this is something exceptionally easy that
even a moron should know how to do or if it's in the book [I should RTFM])

Here's the situation:

Table 1 is a name /address list called "Sales". Each entry has standard
name and address data and a "Terminal ID" field, which is unique and the
index.

Table 2, "Transactions" has the Terminal ID, transactions and a numeric
field called "Month" (i.e. 7 for July, 8 for August, etc.).

Table 3 is called "Current Month" and has a single numeric entry (i.e. 7
for July, 8 for August, etc.).

I'm trying to make a select query in design view* that will join the
name and address data and the transactions only for the month listed in
current month.

I tried using [Tbl Transactions]![Month] equal to [Tbl Current
Month]![Current Month] as criteria, but the query returns all
transactions regardless of the value in the Month field of the
Transactions table.

* I'll try to work w/SQL if I have to but I'd rather not (I'm a hardware
guy).

Thanks in Advance

Uncle Pete
Click to expand...

.
Click to expand...
 
Back
Top