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 said:
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 said:
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.
:
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.
:
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.
:
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?
:
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.
:
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
.
