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 21 Apr 2010 10:32
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" wrote:

> 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
> > > > > > > >
> > > > > > > > .
> > > > > > > >
From: AccessKay via AccessMonster.com on 21 Apr 2010 11:04
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 DEWEY wrote:
>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.

--
Message posted via http://www.accessmonster.com

From: KARL DEWEY on 21 Apr 2010 15:03
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" wrote:

> 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 DEWEY wrote:
> >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.
>
> --
> Message posted via http://www.accessmonster.com
>
> .
>
From: AccessKay via AccessMonster.com on 21 Apr 2010 15:37
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
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201004/1

From: KARL DEWEY on 22 Apr 2010 11:20
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" wrote:

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