Help with Query to find Month

[AccessKay]

I did...I guess it got lost in all the replies. See below...

I opened the form, selected dates, and then created the query you suggested.
I did get data to return in the query based on my month selections. The
query returns a column TransDate for all months and years, a column for
MONTH_1 that gives the month number per selection (e.g. “1†for Jan), a
column for Labor_Cost, and one more column for MONTH_2 with the month number.
I’m not sure if the Labor_Cost is for MONTH_1 or MONTH_2 column.

Did you run the test as I outlined?
Open form, select dates, create a new query like this --

SELECT [TransDate], [Forms]![frmDialogBox]![cboMo1] AS MONTH_1,
[tblTrans_Mstr].[Labor_Cost], [Forms]![frmDialogBox]![cboMo2] AS MONTH_2
FROM [tblTrans_Mstr];

--
Build a little, test a little.

I typed 2/1/2010, 1/1/2010, 02/01/2010, 2/1/2010, etc. in the text boxes...
same result. All my dates are on the first of month so didn't think I needed
to test any other day.

My complete entry also stated 'so as to enter a date that Access will
recognize such as 1/23/2010.'

In the text box DO NOT type 1 or January but 1/1/2010
like I said in my second post -- 'Ok, that text date for the combo box must
be in a format that Access can recognize and the first of the month.'

Humm...I was thinking that January is a text field in my combo box, so I
answered text. My bad...I should have told you that I wasn't 100% sure.
[quoted text clipped - 17 lines]

Thanks for any suggestions.

--



.

 

[KARL DEWEY]

a column for MONTH_1 that gives the month number per selection (e.g. “1â€
for Jan),
Your TransDate is a DateTime field and a '1' for January will not work as
criteria.

Why do you need to pick two months? Maybe there is an automated way instead
of picking dates. Is it you want data from the month prior and the one
before that every month?

What is your bussiness rule you need to apply?

--
Build a little, test a little.

I did...I guess it got lost in all the replies. See below...

I opened the form, selected dates, and then created the query you suggested.
I did get data to return in the query based on my month selections. The
query returns a column TransDate for all months and years, a column for
MONTH_1 that gives the month number per selection (e.g. “1†for Jan), a
column for Labor_Cost, and one more column for MONTH_2 with the month number.
I’m not sure if the Labor_Cost is for MONTH_1 or MONTH_2 column.

Did you run the test as I outlined?
Open form, select dates, create a new query like this --

SELECT [TransDate], [Forms]![frmDialogBox]![cboMo1] AS MONTH_1,
[tblTrans_Mstr].[Labor_Cost], [Forms]![frmDialogBox]![cboMo2] AS MONTH_2
FROM [tblTrans_Mstr];

--
Build a little, test a little.

I typed 2/1/2010, 1/1/2010, 02/01/2010, 2/1/2010, etc. in the text boxes...
same result. All my dates are on the first of month so didn't think I needed
to test any other day.

KARL DEWEY wrote:
My complete entry also stated 'so as to enter a date that Access will
recognize such as 1/23/2010.'

In the text box DO NOT type 1 or January but 1/1/2010
like I said in my second post -- 'Ok, that text date for the combo box must
be in a format that Access can recognize and the first of the month.'

Humm...I was thinking that January is a text field in my combo box, so I
answered text. My bad...I should have told you that I wasn't 100% sure.
[quoted text clipped - 17 lines]

Thanks for any suggestions.

--



.

 

[KARL DEWEY]

a column for MONTH_1 that gives the month number per selection (e.g. “1â€
for Jan),
Your TransDate is a DateTime field and a '1' for January will not work as
criteria.

Why do you need to pick two months? Maybe there is an automated way instead
of picking dates. Is it you want data from the month prior and the one
before that every month?

What is your bussiness rule you need to apply?

--
Build a little, test a little.

I did...I guess it got lost in all the replies. See below...

I opened the form, selected dates, and then created the query you suggested.
I did get data to return in the query based on my month selections. The
query returns a column TransDate for all months and years, a column for
MONTH_1 that gives the month number per selection (e.g. “1†for Jan), a
column for Labor_Cost, and one more column for MONTH_2 with the month number.
I’m not sure if the Labor_Cost is for MONTH_1 or MONTH_2 column.

Did you run the test as I outlined?
Open form, select dates, create a new query like this --

SELECT [TransDate], [Forms]![frmDialogBox]![cboMo1] AS MONTH_1,
[tblTrans_Mstr].[Labor_Cost], [Forms]![frmDialogBox]![cboMo2] AS MONTH_2
FROM [tblTrans_Mstr];

--
Build a little, test a little.

I typed 2/1/2010, 1/1/2010, 02/01/2010, 2/1/2010, etc. in the text boxes...
same result. All my dates are on the first of month so didn't think I needed
to test any other day.

KARL DEWEY wrote:
My complete entry also stated 'so as to enter a date that Access will
recognize such as 1/23/2010.'

In the text box DO NOT type 1 or January but 1/1/2010
like I said in my second post -- 'Ok, that text date for the combo box must
be in a format that Access can recognize and the first of the month.'

Humm...I was thinking that January is a text field in my combo box, so I
answered text. My bad...I should have told you that I wasn't 100% sure.
[quoted text clipped - 17 lines]

Thanks for any suggestions.

--



.

 

[AccessKay via AccessMonster.com]

I'm trying to create a report with two columns for MONTH_1 and MONTH_2 and
then the variance. MONTH_1 is not the current month but usually close to it.
It may be mid April before I pull the report to retrieve Feb and March or I
may want to go back and see Dec and Jan. My query and report works for the
pop-up parameters but I have no idea how make it into a user friendly unbound
form. I'm dealing with multiple months and multiple years.
I appreciate all of your help and maybe if I look at what we were doing here,
it will spark something.

for Jan),
Your TransDate is a DateTime field and a '1' for January will not work as
criteria.

Why do you need to pick two months? Maybe there is an automated way instead
of picking dates. Is it you want data from the month prior and the one
before that every month?

What is your bussiness rule you need to apply?

I did...I guess it got lost in all the replies. See below...

[quoted text clipped - 28 lines]

 

[AccessKay via AccessMonster.com]

I'm trying to create a report with two columns for MONTH_1 and MONTH_2 and
then the variance. MONTH_1 is not the current month but usually close to it.
It may be mid April before I pull the report to retrieve Feb and March or I
may want to go back and see Dec and Jan. My query and report works for the
pop-up parameters but I have no idea how make it into a user friendly unbound
form. I'm dealing with multiple months and multiple years.
I appreciate all of your help and maybe if I look at what we were doing here,
it will spark something.

for Jan),
Your TransDate is a DateTime field and a '1' for January will not work as
criteria.

Why do you need to pick two months? Maybe there is an automated way instead
of picking dates. Is it you want data from the month prior and the one
before that every month?

What is your bussiness rule you need to apply?

I did...I guess it got lost in all the replies. See below...

[quoted text clipped - 28 lines]

 

[AccessKay via AccessMonster.com]

I'm trying to create a report with two columns for MONTH_1 and MONTH_2 and
then the variance. MONTH_1 is not the current month but usually close to it.
It may be mid April before I pull the report to retrieve Feb and March or I
may want to go back and see Dec and Jan. My query and report works for the
pop-up parameters but I have no idea how make it into a user friendly unbound
form. I'm dealing with multiple months and multiple years.
I appreciate all of your help and maybe if I look at what we were doing here,
it will spark something.

for Jan),
Your TransDate is a DateTime field and a '1' for January will not work as
criteria.

Why do you need to pick two months? Maybe there is an automated way instead
of picking dates. Is it you want data from the month prior and the one
before that every month?

What is your bussiness rule you need to apply?

I did...I guess it got lost in all the replies. See below...

[quoted text clipped - 28 lines]