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 15:36 >>I linked my query to the form that has my two combo boxes Does this mean you used the query as the source for the form? If so that is wrong. The form needs to be unbound. You would open the form, select dates, then run the report that uses the query as source. Test by opening the form, selecting dates, and then run the query by itself to view data. -- Build a little, test a little. "AccessKay" wrote: > 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 16:01 You're reply came after I sent you my reply...I knew you would ask me this. I did as you said...plugged the months in my UNBOUND form and ran the report based on the query. It still came up with zeros. "KARL DEWEY" wrote: > >>I linked my query to the form that has my two combo boxes > Does this mean you used the query as the source for the form? If so that is > wrong. > > The form needs to be unbound. You would open the form, select dates, then > run the report that uses the query as source. > > Test by opening the form, selecting dates, and then run the query by itself > to view data. > > -- > Build a little, test a little. > > > "AccessKay" wrote: > > > 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: KARL DEWEY on 20 Apr 2010 18:30 A couple of test to run -- -- Run query without the form being open and respond to the prompts. Make sure to answer the prompts exactly the same. -- Open form, select dates, and run query. BTW why do you need to use combo boxes instead of text box? Do you run the same dates so many time that you load them in a table? -- Build a little, test a little. "AccessKay" wrote: > You're reply came after I sent you my reply...I knew you would ask me this. > I did as you said...plugged the months in my UNBOUND form and ran the report > based on the query. It still came up with zeros. > > > "KARL DEWEY" wrote: > > > >>I linked my query to the form that has my two combo boxes > > Does this mean you used the query as the source for the form? If so that is > > wrong. > > > > The form needs to be unbound. You would open the form, select dates, then > > run the report that uses the query as source. > > > > Test by opening the form, selecting dates, and then run the query by itself > > to view data. > > > > -- > > Build a little, test a little. > > > > > > "AccessKay" wrote: > > > > > 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: KARL DEWEY on 20 Apr 2010 18:54 One other test. 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. "KARL DEWEY" wrote: > A couple of test to run -- > -- Run query without the form being open and respond to the prompts. Make > sure to answer the prompts exactly the same. > -- Open form, select dates, and run query. > > BTW why do you need to use combo boxes instead of text box? Do you run the > same dates so many time that you load them in a table? > > > -- > Build a little, test a little. > > > "AccessKay" wrote: > > > You're reply came after I sent you my reply...I knew you would ask me this. > > I did as you said...plugged the months in my UNBOUND form and ran the report > > based on the query. It still came up with zeros. > > > > > > "KARL DEWEY" wrote: > > > > > >>I linked my query to the form that has my two combo boxes > > > Does this mean you used the query as the source for the form? If so that is > > > wrong. > > > > > > The form needs to be unbound. You would open the form, select dates, then > > > run the report that uses the query as source. > > > > > > Test by opening the form, selecting dates, and then run the query by itself > > > to view data. > > > > > > -- > > > Build a little, test a little. > > > > > > > > > "AccessKay" wrote: > > > > > > > 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 21 Apr 2010 09:59 I ran the query without opening the form and was prompted to enter Month1 and Month2. I ignored them and it has the same result…two zero values in one row. My combo boxes are based on a table/query with one row for month number and another for month name (with month name visible). I could use text boxes but thought it would be easier for the user to select the month. 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. Just a little background…selecting date periods is key to this database. I learned how to set-up an unbound form to pull current month/year/qtr with different categories. I also learned how to use a date range. But now I need to produce reports for variances between periods such as month, year, qtr. I was able to set it up with the pop up parameters but it required creating four queries for one report and then the user had to answer six pop up parameters…too messy. "KARL DEWEY" wrote: > One other test. 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. > > > "KARL DEWEY" wrote: > > > A couple of test to run -- > > -- Run query without the form being open and respond to the prompts. Make > > sure to answer the prompts exactly the same. > > -- Open form, select dates, and run query. > > > > BTW why do you need to use combo boxes instead of text box? Do you run the > > same dates so many time that you load them in a table? > > > > > > -- > > Build a little, test a little. > > > > > > "AccessKay" wrote: > > > > > You're reply came after I sent you my reply...I knew you would ask me this. > > > I did as you said...plugged the months in my UNBOUND form and ran the report > > > based on the query. It still came up with zeros. > > > > > > > > > "KARL DEWEY" wrote: > > > > > > > >>I linked my query to the form that has my two combo boxes > > > > Does this mean you used the query as the source for the form? If so that is > > > > wrong. > > > > > > > > The form needs to be unbound. You would open the form, select dates, then > > > > run the report that uses the query as source. > > > > > > > > Test by opening the form, selecting dates, and then run the query by itself > > > > to view data. > > > > > > > > -- > > > > Build a little, test a little. > > > > > > > > > > > > "AccessKay" wrote: > > > > > > > > > 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 > > > > > > > > > > > > > > . > > > > > > >
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 |