Help with Query to find Month
in [Queries]
|
Prev: Using a Password With an SQL IN Clause
Next: creating a flag to identify first, second time etc someone has app
From: KARL DEWEY on 20 Apr 2010 02:20 >>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. > > > > >
From: AccessKay via AccessMonster.com on 20 Apr 2010 09:19 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/Forums.aspx/access-queries/201004/1
From: KARL DEWEY on 20 Apr 2010 11:47 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/Forums.aspx/access-queries/201004/1 > > . >
From: AccessKay on 20 Apr 2010 12:43 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/Forums.aspx/access-queries/201004/1 > > > > . > >
From: AccessKay on 20 Apr 2010 15:34 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/Forums.aspx/access-queries/201004/1 > > > > . > >
First
|
Prev
|
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 |