Help with Query to find Month


A

AccessKay

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.
 
Ad

Advertisements

K

KARL DEWEY

What will combo boxes for Month 1 and Month 2 supply as criteria? Number or
text?
Is [TransDate] a datatype DateTime field or text?
 
A

AccessKay

I'd say a text date for the combo box and TransDate is a date/time field


KARL DEWEY said:
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 said:
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.
 
K

KARL DEWEY

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 said:
I'd say a text date for the combo box and TransDate is a date/time field


KARL DEWEY said:
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 said:
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.
 
A

AccessKay

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 said:
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 said:
I'd say a text date for the combo box and TransDate is a date/time field


KARL DEWEY said:
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.


:

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.
 
K

KARL DEWEY

What is the CVDate?
CVDate converts text to a datetime datatype.
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 said:
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 said:
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 said:
I'd say a text date for the combo box and TransDate is a date/time field


:

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.


:

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.
 
Ad

Advertisements

K

KARL DEWEY

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


.
 
A

AccessKay

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


.
 
A

AccessKay

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


.
 
K

KARL DEWEY

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


.
 
A

AccessKay

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


.
 
Ad

Advertisements

K

KARL DEWEY

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


.
 
K

KARL DEWEY

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.


AccessKay said:
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 said:
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


.
 
A

AccessKay

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.


AccessKay said:
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


.
 
K

KARL DEWEY

Whoa! Way back I asked what the combo supplied and you said 'I'd say a text
date for the combo box.' I went with that assumption.

You can not ignore query prompts for criteria and expect to have any results.

Change from combo to text boxes so as to enter a date that Access will
recognize such as 1/23/2010. You can name the text boxes the same as you had
for the combos.


--
Build a little, test a little.


AccessKay said:
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


.
 
K

KARL DEWEY

My complete entry also stated 'so as to enter a date that Access will
recognize such as 1/23/2010.'

In the text box DO NOT type 1 or January but 1/1/2010
like I said in my second post -- 'Ok, that text date for the combo box must
be in a format that Access can recognize and the first of the month.'

--
Build a little, test a little.


AccessKay via AccessMonster.com said:
Humm...I was thinking that January is a text field in my combo box, so I
answered text. My bad...I should have told you that I wasn't 100% sure.
Anyways, I changed the combos to text boxes. I still get the same results.
Oh...with the query test before, after I ignored them, I did put the months
in the parameters but got an error about it being too complex or somthing
like that. Sorry to be so much trouble. I appreciate you trying.

KARL said:
Whoa! Way back I asked what the combo supplied and you said 'I'd say a text
date for the combo box.' I went with that assumption.

You can not ignore query prompts for criteria and expect to have any results.

Change from combo to text boxes so as to enter a date that Access will
recognize such as 1/23/2010. You can name the text boxes the same as you had
for the combos.
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
[quoted text clipped - 89 lines]
Thanks for any suggestions.
 
Ad

Advertisements

K

KARL DEWEY

Did you run the test as I outlined?
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.


AccessKay via AccessMonster.com said:
I typed 2/1/2010, 1/1/2010, 02/01/2010, 2/1/2010, etc. in the text boxes...
same result. All my dates are on the first of month so didn't think I needed
to test any other day.

KARL said:
My complete entry also stated 'so as to enter a date that Access will
recognize such as 1/23/2010.'

In the text box DO NOT type 1 or January but 1/1/2010
like I said in my second post -- 'Ok, that text date for the combo box must
be in a format that Access can recognize and the first of the month.'
Humm...I was thinking that January is a text field in my combo box, so I
answered text. My bad...I should have told you that I wasn't 100% sure.
[quoted text clipped - 17 lines]
Thanks for any suggestions.

--
Message posted via AccessMonster.com


.
 
A

AccessKay

I did...I guess it got lost in all the replies. See below...

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.



KARL DEWEY said:
Did you run the test as I outlined?
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.


AccessKay via AccessMonster.com said:
I typed 2/1/2010, 1/1/2010, 02/01/2010, 2/1/2010, etc. in the text boxes...
same result. All my dates are on the first of month so didn't think I needed
to test any other day.

KARL said:
My complete entry also stated 'so as to enter a date that Access will
recognize such as 1/23/2010.'

In the text box DO NOT type 1 or January but 1/1/2010
like I said in my second post -- 'Ok, that text date for the combo box must
be in a format that Access can recognize and the first of the month.'

Humm...I was thinking that January is a text field in my combo box, so I
answered text. My bad...I should have told you that I wasn't 100% sure.
[quoted text clipped - 17 lines]

Thanks for any suggestions.

--
Message posted via AccessMonster.com


.
 
K

KARL DEWEY

a column for MONTH_1 that gives the month number per selection (e.g. “1â€
for Jan),
Your TransDate is a DateTime field and a '1' for January will not work as
criteria.

Why do you need to pick two months? Maybe there is an automated way instead
of picking dates. Is it you want data from the month prior and the one
before that every month?

What is your bussiness rule you need to apply?

--
Build a little, test a little.


AccessKay said:
I did...I guess it got lost in all the replies. See below...

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.



KARL DEWEY said:
Did you run the test as I outlined?
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.


AccessKay via AccessMonster.com said:
I typed 2/1/2010, 1/1/2010, 02/01/2010, 2/1/2010, etc. in the text boxes...
same result. All my dates are on the first of month so didn't think I needed
to test any other day.

KARL DEWEY wrote:
My complete entry also stated 'so as to enter a date that Access will
recognize such as 1/23/2010.'

In the text box DO NOT type 1 or January but 1/1/2010
like I said in my second post -- 'Ok, that text date for the combo box must
be in a format that Access can recognize and the first of the month.'

Humm...I was thinking that January is a text field in my combo box, so I
answered text. My bad...I should have told you that I wasn't 100% sure.
[quoted text clipped - 17 lines]

Thanks for any suggestions.

--
Message posted via AccessMonster.com


.
 
Ad

Advertisements

K

KARL DEWEY

a column for MONTH_1 that gives the month number per selection (e.g. “1â€
for Jan),
Your TransDate is a DateTime field and a '1' for January will not work as
criteria.

Why do you need to pick two months? Maybe there is an automated way instead
of picking dates. Is it you want data from the month prior and the one
before that every month?

What is your bussiness rule you need to apply?

--
Build a little, test a little.


AccessKay said:
I did...I guess it got lost in all the replies. See below...

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.



KARL DEWEY said:
Did you run the test as I outlined?
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.


AccessKay via AccessMonster.com said:
I typed 2/1/2010, 1/1/2010, 02/01/2010, 2/1/2010, etc. in the text boxes...
same result. All my dates are on the first of month so didn't think I needed
to test any other day.

KARL DEWEY wrote:
My complete entry also stated 'so as to enter a date that Access will
recognize such as 1/23/2010.'

In the text box DO NOT type 1 or January but 1/1/2010
like I said in my second post -- 'Ok, that text date for the combo box must
be in a format that Access can recognize and the first of the month.'

Humm...I was thinking that January is a text field in my combo box, so I
answered text. My bad...I should have told you that I wasn't 100% sure.
[quoted text clipped - 17 lines]

Thanks for any suggestions.

--
Message posted via AccessMonster.com


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top