If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Help with Query to find Month
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. |
#3
|
|||
|
|||
Help with Query to find Month
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. |
#4
|
|||
|
|||
Help with Query to find Month
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. |
#5
|
|||
|
|||
Help with Query to find Month
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. |
#6
|
|||
|
|||
Help with Query to find Month
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. |
#7
|
|||
|
|||
Help with Query to find Month
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 |
#8
|
|||
|
|||
Help with Query to find Month
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 . |
#9
|
|||
|
|||
Help with Query to find Month
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 . |
#10
|
|||
|
|||
Help with Query to find Month
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 . |
Thread Tools | |
Display Modes | |
|
|