Help with Query to find Month [Queries]

Prev: Using a Password With an SQL IN Clause
Next: creating a flag to identify first, second time etc someone has app

From: AccessKay on 22 Apr 2010 14:39

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.

"KARL DEWEY" wrote:

> 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.
>
>
> "AccessKay via AccessMonster.com" wrote:
>
> > 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.
> >
> > --
> > Message posted via AccessMonster.com
> > http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201004/1
> >
> > .
> >

From: KARL DEWEY on 22 Apr 2010 16:38

>> 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.

"AccessKay" wrote:

> 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.
>
>
>
> "KARL DEWEY" wrote:
>
> > 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.
> >
> >
> > "AccessKay via AccessMonster.com" wrote:
> >
> > > 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.
> > >
> > > --
> > > Message posted via AccessMonster.com
> > > http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201004/1
> > >
> > > .
> > >

From: AccessKay via AccessMonster.com on 22 Apr 2010 17:37

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.

KARL DEWEY wrote:
>>> 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?
>
>> I did...I guess it got lost in all the replies. See below...
>>
>[quoted text clipped - 28 lines]
>> > > >> >> > > > > > > > >> > > > >
>> > > >> >> > > > > > > > >> > > > > Thanks for any suggestions.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201004/1

From: AccessKay via AccessMonster.com on 22 Apr 2010 17:38

First | Prev |
Pages: 1 2 3 4 5
Prev: Using a Password With an SQL IN Clause
Next: creating a flag to identify first, second time etc someone has app