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 19 Apr 2010 15:57
I need some help please. I want to build a query based on a form with two
combo boxes for Month 1 and Month 2 and then have a report that has two
columns for the LaborCost values for Month1 and Month2. I'll only be using
two fields in my table tblTrans_Mstr: [TransDate] and [LaborCost]. I think I
may also need two combo boxes for the year also since my data has multiple
years. How might you handle this?

Thanks for any suggestions.

From: KARL DEWEY on 19 Apr 2010 18:01
What will combo boxes for Month 1 and Month 2 supply as criteria? Number or
text?
Is [TransDate] a datatype DateTime field or text?

--
Build a little, test a little.


"AccessKay" wrote:

> I need some help please. I want to build a query based on a form with two
> combo boxes for Month 1 and Month 2 and then have a report that has two
> columns for the LaborCost values for Month1 and Month2. I'll only be using
> two fields in my table tblTrans_Mstr: [TransDate] and [LaborCost]. I think I
> may also need two combo boxes for the year also since my data has multiple
> years. How might you handle this?
>
> Thanks for any suggestions.
>
From: AccessKay on 19 Apr 2010 18:06
I'd say a text date for the combo box and TransDate is a date/time field


"KARL DEWEY" wrote:

> What will combo boxes for Month 1 and Month 2 supply as criteria? Number or
> text?
> Is [TransDate] a datatype DateTime field or text?
>
> --
> Build a little, test a little.
>
>
> "AccessKay" wrote:
>
> > I need some help please. I want to build a query based on a form with two
> > combo boxes for Month 1 and Month 2 and then have a report that has two
> > columns for the LaborCost values for Month1 and Month2. I'll only be using
> > two fields in my table tblTrans_Mstr: [TransDate] and [LaborCost]. I think I
> > may also need two combo boxes for the year also since my data has multiple
> > years. How might you handle this?
> >
> > Thanks for any suggestions.
> >
From: KARL DEWEY on 19 Apr 2010 19:10
Ok, that text date for the combo box must be in a format that Access can
recognize and the first of the month.

Try this --
SELECT Sum(IIF([TransDate] Between CVDate([Forms]![YourFormName]![Month1])
AND DateSerial(DateAdd("m", 1, CVDate([Forms]![YourFormName]![Month1]))-1),
[tblTrans_Mstr].[LaborCost], 0) AS [Month_1], Sum(IIF([TransDate] Between
CVDate([Forms]![YourFormName]![Month2]) AND DateSerial(DateAdd("m", 1,
CVDate([Forms]![YourFormName]![Month2]))-1), [tblTrans_Mstr].[LaborCost], 0)
AS [Month_2]
FROM [tblTrans_Mstr];

--
Build a little, test a little.


"AccessKay" wrote:

> I'd say a text date for the combo box and TransDate is a date/time field
>
>
> "KARL DEWEY" wrote:
>
> > What will combo boxes for Month 1 and Month 2 supply as criteria? Number or
> > text?
> > Is [TransDate] a datatype DateTime field or text?
> >
> > --
> > Build a little, test a little.
> >
> >
> > "AccessKay" wrote:
> >
> > > I need some help please. I want to build a query based on a form with two
> > > combo boxes for Month 1 and Month 2 and then have a report that has two
> > > columns for the LaborCost values for Month1 and Month2. I'll only be using
> > > two fields in my table tblTrans_Mstr: [TransDate] and [LaborCost]. I think I
> > > may also need two combo boxes for the year also since my data has multiple
> > > years. How might you handle this?
> > >
> > > Thanks for any suggestions.
> > >
From: AccessKay on 19 Apr 2010 19:56
Thanks for responding Karl. What is the CVDate? And will this take care of
the year also? I don't really understand. I'm a bit new at this. Please be
patient with me. I'm not the expert as you are. Would you mind explaining a
little more?

"KARL DEWEY" wrote:

> Ok, that text date for the combo box must be in a format that Access can
> recognize and the first of the month.
>
> Try this --
> SELECT Sum(IIF([TransDate] Between CVDate([Forms]![YourFormName]![Month1])
> AND DateSerial(DateAdd("m", 1, CVDate([Forms]![YourFormName]![Month1]))-1),
> [tblTrans_Mstr].[LaborCost], 0) AS [Month_1], Sum(IIF([TransDate] Between
> CVDate([Forms]![YourFormName]![Month2]) AND DateSerial(DateAdd("m", 1,
> CVDate([Forms]![YourFormName]![Month2]))-1), [tblTrans_Mstr].[LaborCost], 0)
> AS [Month_2]
> FROM [tblTrans_Mstr];
>
> --
> Build a little, test a little.
>
>
> "AccessKay" wrote:
>
> > I'd say a text date for the combo box and TransDate is a date/time field
> >
> >
> > "KARL DEWEY" wrote:
> >
> > > What will combo boxes for Month 1 and Month 2 supply as criteria? Number or
> > > text?
> > > Is [TransDate] a datatype DateTime field or text?
> > >
> > > --
> > > Build a little, test a little.
> > >
> > >
> > > "AccessKay" wrote:
> > >
> > > > I need some help please. I want to build a query based on a form with two
> > > > combo boxes for Month 1 and Month 2 and then have a report that has two
> > > > columns for the LaborCost values for Month1 and Month2. I'll only be using
> > > > two fields in my table tblTrans_Mstr: [TransDate] and [LaborCost]. I think I
> > > > may also need two combo boxes for the year also since my data has multiple
> > > > years. How might you handle this?
> > > >
> > > > Thanks for any suggestions.
> > > >
 |  Next  |  Last
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