Help with Query to find Month

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.

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.

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.

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.

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.

What is the CVDate?
CVDate converts text to a datetime datatype.

will this take care of the year also?
If I put it together correctly. If you get an error message post the exact
wording back and your SQL.

--
Build a little, test a little.


"AccessKay" wrote:

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.

That's neat to know about the CVDate.

I tried it and received an error message: Syntax error (missing operator) in
query expression 'Sum(IIF([TransDate.... It highlights the "AS" in the SQL.
Here is what I put in:
SELECT Sum(IIF([TransDate] Between CVDate([Forms]![frmDialogBox]![cboMo1])
AND DateSerial(DateAdd("m", 1, CVDate([Forms]![frmDialogBox]![cboMo1]))-1),
[tblTrans_Mstr].[Labor_Cost], 0) AS [Month_1], Sum(IIF([TransDate] Between
CVDate([Forms]![frmDialogBox]![cboMo2]) AND DateSerial(DateAdd("m", 1,
CVDate([Forms]![frmDialogBox]![cboMo2]))-1), [tblTrans_Mstr].[Labor_Cost], 0)

AS [Month_2]
FROM [tblTrans_Mstr];

Thanks.

KARL DEWEY wrote:
What is the CVDate?
CVDate converts text to a datetime datatype.

will this take care of the year also?
If I put it together correctly. If you get an error message post the exact
wording back and your SQL.

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
[quoted text clipped - 28 lines]

Thanks for any suggestions.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201004/1

I fixed some stuff but have not fully tested as I would need to build table
and populate it. Try this --
SELECT Sum(IIF([TransDate] Between CVDate([Forms]![frmDialogBox]![cboMo1])
AND DateAdd("m", 1, CVDate([Forms]![frmDialogBox]![cboMo1]) )-1,
[tblTrans_Mstr].[Labor_Cost], 0)) AS [Month_1], Sum(IIF([TransDate] Between
CVDate([Forms]![frmDialogBox]![cboMo2]) AND DateAdd("m", 1,
CVDate([Forms]![frmDialogBox]![cboMo2]))-1, [tblTrans_Mstr].[Labor_Cost], 0))
AS [Month_2]
FROM [tblTrans_Mstr];

--
Build a little, test a little.


"AccessKay via AccessMonster.com" wrote:

That's neat to know about the CVDate.

I tried it and received an error message: Syntax error (missing operator) in
query expression 'Sum(IIF([TransDate.... It highlights the "AS" in the SQL.
Here is what I put in:
SELECT Sum(IIF([TransDate] Between CVDate([Forms]![frmDialogBox]![cboMo1])
AND DateSerial(DateAdd("m", 1, CVDate([Forms]![frmDialogBox]![cboMo1]))-1),
[tblTrans_Mstr].[Labor_Cost], 0) AS [Month_1], Sum(IIF([TransDate] Between
CVDate([Forms]![frmDialogBox]![cboMo2]) AND DateSerial(DateAdd("m", 1,
CVDate([Forms]![frmDialogBox]![cboMo2]))-1), [tblTrans_Mstr].[Labor_Cost], 0)

AS [Month_2]
FROM [tblTrans_Mstr];

Thanks.

KARL DEWEY wrote:
What is the CVDate?
CVDate converts text to a datetime datatype.

will this take care of the year also?
If I put it together correctly. If you get an error message post the exact
wording back and your SQL.

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
[quoted text clipped - 28 lines]

Thanks for any suggestions.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201004/1

.

I don't know how you pull this out of your head...amazing!
The query works but where do I go from here. I linked my query to the form
that has my two combo boxes and then I created a report based on that query.
When I select the two months in my combo boxes, it's not pulling any amounts
for labor cost in the report. What should I do now?

"KARL DEWEY" wrote:

I fixed some stuff but have not fully tested as I would need to build table
and populate it. Try this --
SELECT Sum(IIF([TransDate] Between CVDate([Forms]![frmDialogBox]![cboMo1])
AND DateAdd("m", 1, CVDate([Forms]![frmDialogBox]![cboMo1]) )-1,
[tblTrans_Mstr].[Labor_Cost], 0)) AS [Month_1], Sum(IIF([TransDate] Between
CVDate([Forms]![frmDialogBox]![cboMo2]) AND DateAdd("m", 1,
CVDate([Forms]![frmDialogBox]![cboMo2]))-1, [tblTrans_Mstr].[Labor_Cost], 0))
AS [Month_2]
FROM [tblTrans_Mstr];

--
Build a little, test a little.


"AccessKay via AccessMonster.com" wrote:

That's neat to know about the CVDate.

I tried it and received an error message: Syntax error (missing operator) in
query expression 'Sum(IIF([TransDate.... It highlights the "AS" in the SQL.
Here is what I put in:
SELECT Sum(IIF([TransDate] Between CVDate([Forms]![frmDialogBox]![cboMo1])
AND DateSerial(DateAdd("m", 1, CVDate([Forms]![frmDialogBox]![cboMo1]))-1),
[tblTrans_Mstr].[Labor_Cost], 0) AS [Month_1], Sum(IIF([TransDate] Between
CVDate([Forms]![frmDialogBox]![cboMo2]) AND DateSerial(DateAdd("m", 1,
CVDate([Forms]![frmDialogBox]![cboMo2]))-1), [tblTrans_Mstr].[Labor_Cost], 0)

AS [Month_2]
FROM [tblTrans_Mstr];

Thanks.

KARL DEWEY wrote:
What is the CVDate?
CVDate converts text to a datetime datatype.

will this take care of the year also?
If I put it together correctly. If you get an error message post the exact
wording back and your SQL.

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
[quoted text clipped - 28 lines]

Thanks for any suggestions.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201004/1

.

To correct my last reply, I see that I shouldn't link the form to the query
because I think that's already built into the expression.

I thought maybe I needed a Nz because I checked my table and there were some
nulls. I didn't know how to insert to Nz so I deleted all of the rows
without amounts just to test to see if this was the problem. I guess not
because I still had no labor cost in my report.

"KARL DEWEY" wrote:

I fixed some stuff but have not fully tested as I would need to build table
and populate it. Try this --
SELECT Sum(IIF([TransDate] Between CVDate([Forms]![frmDialogBox]![cboMo1])
AND DateAdd("m", 1, CVDate([Forms]![frmDialogBox]![cboMo1]) )-1,
[tblTrans_Mstr].[Labor_Cost], 0)) AS [Month_1], Sum(IIF([TransDate] Between
CVDate([Forms]![frmDialogBox]![cboMo2]) AND DateAdd("m", 1,
CVDate([Forms]![frmDialogBox]![cboMo2]))-1, [tblTrans_Mstr].[Labor_Cost], 0))
AS [Month_2]
FROM [tblTrans_Mstr];

--
Build a little, test a little.


"AccessKay via AccessMonster.com" wrote:

That's neat to know about the CVDate.

I tried it and received an error message: Syntax error (missing operator) in
query expression 'Sum(IIF([TransDate.... It highlights the "AS" in the SQL.
Here is what I put in:
SELECT Sum(IIF([TransDate] Between CVDate([Forms]![frmDialogBox]![cboMo1])
AND DateSerial(DateAdd("m", 1, CVDate([Forms]![frmDialogBox]![cboMo1]))-1),
[tblTrans_Mstr].[Labor_Cost], 0) AS [Month_1], Sum(IIF([TransDate] Between
CVDate([Forms]![frmDialogBox]![cboMo2]) AND DateSerial(DateAdd("m", 1,
CVDate([Forms]![frmDialogBox]![cboMo2]))-1), [tblTrans_Mstr].[Labor_Cost], 0)

AS [Month_2]
FROM [tblTrans_Mstr];

Thanks.

KARL DEWEY wrote:
What is the CVDate?
CVDate converts text to a datetime datatype.

will this take care of the year also?
If I put it together correctly. If you get an error message post the exact
wording back and your SQL.

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
[quoted text clipped - 28 lines]

Thanks for any suggestions.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201004/1

.