VBA input, add to query

I want to use an inputbox in the Form_Load event, and then use that input as 
a parameter in several queries.  Is that  possible?  How would I put the 
input values in the queries?  (My form uses the data from several queries.)

TIA 


0
zSplash
3/21/2007 10:52:35 PM
access.queries 6343 articles. 1 followers. Follow

11 Replies
866 Views

Similar Articles

[PageSpeed] 20

You need to use an unbound textbox to enter the parameter.   

In the query use this as criteria, replace form & textbox names with yours ---
    [Forms]![YourFormName]![YourTextbox]

The above is for exact match criteria. For start of field use ---
    Like [Forms]![YourFormName]![YourTextbox] & "*"

For match any part of field use ---
    Like "*" & [Forms]![YourFormName]![YourTextbox] & "*"


-- 
KARL DEWEY
Build a little - Test a little


"zSplash" wrote:

> I want to use an inputbox in the Form_Load event, and then use that input as 
> a parameter in several queries.  Is that  possible?  How would I put the 
> input values in the queries?  (My form uses the data from several queries.)
> 
> TIA 
> 
> 
> 
0
Utf
3/21/2007 11:34:10 PM
Thank for the guidance, Karl.
So, I have made 2 global variables in the Form_Load event -- begDate and 
endDate.  In the Form_Load event, from an inputbox, I get the value for the 
year, which I then make into a date (begDate = ">#1/1/" & theYear & "#"). 
In the parameter of the query (from which the form is based), I put this 
code:
    >[Forms]![myFormName]![begDate] And <[Forms]![myFormName]![endDate]

On my form, I get the value 0 instead of 73[on the form, I am doing a 
Count(*) of a textbox].  Do I have to use an SQL statement in my VBA code, 
somehow, instead of use a variable in my query parameter, or what?


"KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message 
news:E3BEE1F3-E493-4164-824D-7C91FC6B0CAD@microsoft.com...
> You need to use an unbound textbox to enter the parameter.
>
> In the query use this as criteria, replace form & textbox names with 
> yours ---
>    [Forms]![YourFormName]![YourTextbox]
>
> The above is for exact match criteria. For start of field use ---
>    Like [Forms]![YourFormName]![YourTextbox] & "*"
>
> For match any part of field use ---
>    Like "*" & [Forms]![YourFormName]![YourTextbox] & "*"
>
>
> -- 
> KARL DEWEY
> Build a little - Test a little
>
>
> "zSplash" wrote:
>
>> I want to use an inputbox in the Form_Load event, and then use that input 
>> as
>> a parameter in several queries.  Is that  possible?  How would I put the
>> input values in the queries?  (My form uses the data from several 
>> queries.)
>>
>> TIA
>>
>>
>> 


0
zSplash
3/22/2007 4:30:20 PM
I would not use 'Form_Load event' but just create two unbound textbox to 
enter your dates.
In the criteria of the query use ---
   Between   [Forms]![YourFormName]![YourTextbox1] And   
[Forms]![YourFormName]![YourTextbox1]

Like I said replace replace form & textbox names with your names.  

Open the form, enter dates, then run the query. 
-- 
KARL DEWEY
Build a little - Test a little


"zSplash" wrote:

> Thank for the guidance, Karl.
> So, I have made 2 global variables in the Form_Load event -- begDate and 
> endDate.  In the Form_Load event, from an inputbox, I get the value for the 
> year, which I then make into a date (begDate = ">#1/1/" & theYear & "#"). 
> In the parameter of the query (from which the form is based), I put this 
> code:
>     >[Forms]![myFormName]![begDate] And <[Forms]![myFormName]![endDate]
> 
> On my form, I get the value 0 instead of 73[on the form, I am doing a 
> Count(*) of a textbox].  Do I have to use an SQL statement in my VBA code, 
> somehow, instead of use a variable in my query parameter, or what?
> 
> 
> "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message 
> news:E3BEE1F3-E493-4164-824D-7C91FC6B0CAD@microsoft.com...
> > You need to use an unbound textbox to enter the parameter.
> >
> > In the query use this as criteria, replace form & textbox names with 
> > yours ---
> >    [Forms]![YourFormName]![YourTextbox]
> >
> > The above is for exact match criteria. For start of field use ---
> >    Like [Forms]![YourFormName]![YourTextbox] & "*"
> >
> > For match any part of field use ---
> >    Like "*" & [Forms]![YourFormName]![YourTextbox] & "*"
> >
> >
> > -- 
> > KARL DEWEY
> > Build a little - Test a little
> >
> >
> > "zSplash" wrote:
> >
> >> I want to use an inputbox in the Form_Load event, and then use that input 
> >> as
> >> a parameter in several queries.  Is that  possible?  How would I put the
> >> input values in the queries?  (My form uses the data from several 
> >> queries.)
> >>
> >> TIA
> >>
> >>
> >> 
> 
> 
> 
0
Utf
3/23/2007 12:39:03 AM
Thanks, Karl.  I have created a new form ("fGetStats").  After I get the 
input, how do I put that into the query?  I understand you say I'll be 
putting "Between [Forms]![fGetStats]![tbxBegDate] And 
[Forms]![fGetStats]![tbxEndDate]" into the criteria for the various queries, 
but I don't get how to do that.  Do I do that with VBA?

"KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message 
news:B68FBE0E-FCF0-436A-B6C3-54AECB99765D@microsoft.com...
>I would not use 'Form_Load event' but just create two unbound textbox to
> enter your dates.
> In the criteria of the query use ---
>   Between   [Forms]![YourFormName]![YourTextbox1] And
> [Forms]![YourFormName]![YourTextbox1]
>
> Like I said replace replace form & textbox names with your names.
>
> Open the form, enter dates, then run the query.
> -- 
> KARL DEWEY
> Build a little - Test a little
>
>
> "zSplash" wrote:
>
>> Thank for the guidance, Karl.
>> So, I have made 2 global variables in the Form_Load event -- begDate and
>> endDate.  In the Form_Load event, from an inputbox, I get the value for 
>> the
>> year, which I then make into a date (begDate = ">#1/1/" & theYear & "#").
>> In the parameter of the query (from which the form is based), I put this
>> code:
>>     >[Forms]![myFormName]![begDate] And <[Forms]![myFormName]![endDate]
>>
>> On my form, I get the value 0 instead of 73[on the form, I am doing a
>> Count(*) of a textbox].  Do I have to use an SQL statement in my VBA 
>> code,
>> somehow, instead of use a variable in my query parameter, or what?
>>
>>
>> "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message
>> news:E3BEE1F3-E493-4164-824D-7C91FC6B0CAD@microsoft.com...
>> > You need to use an unbound textbox to enter the parameter.
>> >
>> > In the query use this as criteria, replace form & textbox names with
>> > yours ---
>> >    [Forms]![YourFormName]![YourTextbox]
>> >
>> > The above is for exact match criteria. For start of field use ---
>> >    Like [Forms]![YourFormName]![YourTextbox] & "*"
>> >
>> > For match any part of field use ---
>> >    Like "*" & [Forms]![YourFormName]![YourTextbox] & "*"
>> >
>> >
>> > -- 
>> > KARL DEWEY
>> > Build a little - Test a little
>> >
>> >
>> > "zSplash" wrote:
>> >
>> >> I want to use an inputbox in the Form_Load event, and then use that 
>> >> input
>> >> as
>> >> a parameter in several queries.  Is that  possible?  How would I put 
>> >> the
>> >> input values in the queries?  (My form uses the data from several
>> >> queries.)
>> >>
>> >> TIA
>> >>
>> >>
>> >>
>>
>>
>> 


0
zSplash
3/27/2007 9:35:13 PM
OK.  I think I figured out what I'm supposed to do.  In the query itself, I 
put "Between [Forms]![fGetStats]![tbxBegDate] And 
[Forms]![fGetStats]![tbxEndDate]" (no quotes) as a parameter.  Then, when I 
open the "parameterForm" ("fGetStats"), I'll input the desired dates and 
click the cmdButton.  On the click_event of the cmdButton, I open the 
underlying form, which is based on the query.  The trouble is, if I manually 
put the dates in the query, I get a count of 6.  If I use this method, my 
count is 0.  So, I think I'm still goofed up.

"zSplash" <zsplash@nospamPlease.com> wrote in message 
news:%23J2w1fLcHHA.2300@TK2MSFTNGP06.phx.gbl...
> Thanks, Karl.  I have created a new form ("fGetStats").  After I get the 
> input, how do I put that into the query?  I understand you say I'll be 
> putting "Between [Forms]![fGetStats]![tbxBegDate] And 
> [Forms]![fGetStats]![tbxEndDate]" into the criteria for the various 
> queries, but I don't get how to do that.  Do I do that with VBA?
>
> "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message 
> news:B68FBE0E-FCF0-436A-B6C3-54AECB99765D@microsoft.com...
>>I would not use 'Form_Load event' but just create two unbound textbox to
>> enter your dates.
>> In the criteria of the query use ---
>>   Between   [Forms]![YourFormName]![YourTextbox1] And
>> [Forms]![YourFormName]![YourTextbox1]
>>
>> Like I said replace replace form & textbox names with your names.
>>
>> Open the form, enter dates, then run the query.
>> -- 
>> KARL DEWEY
>> Build a little - Test a little
>>
>>
>> "zSplash" wrote:
>>
>>> Thank for the guidance, Karl.
>>> So, I have made 2 global variables in the Form_Load event -- begDate and
>>> endDate.  In the Form_Load event, from an inputbox, I get the value for 
>>> the
>>> year, which I then make into a date (begDate = ">#1/1/" & theYear & 
>>> "#").
>>> In the parameter of the query (from which the form is based), I put this
>>> code:
>>>     >[Forms]![myFormName]![begDate] And <[Forms]![myFormName]![endDate]
>>>
>>> On my form, I get the value 0 instead of 73[on the form, I am doing a
>>> Count(*) of a textbox].  Do I have to use an SQL statement in my VBA 
>>> code,
>>> somehow, instead of use a variable in my query parameter, or what?
>>>
>>>
>>> "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message
>>> news:E3BEE1F3-E493-4164-824D-7C91FC6B0CAD@microsoft.com...
>>> > You need to use an unbound textbox to enter the parameter.
>>> >
>>> > In the query use this as criteria, replace form & textbox names with
>>> > yours ---
>>> >    [Forms]![YourFormName]![YourTextbox]
>>> >
>>> > The above is for exact match criteria. For start of field use ---
>>> >    Like [Forms]![YourFormName]![YourTextbox] & "*"
>>> >
>>> > For match any part of field use ---
>>> >    Like "*" & [Forms]![YourFormName]![YourTextbox] & "*"
>>> >
>>> >
>>> > -- 
>>> > KARL DEWEY
>>> > Build a little - Test a little
>>> >
>>> >
>>> > "zSplash" wrote:
>>> >
>>> >> I want to use an inputbox in the Form_Load event, and then use that 
>>> >> input
>>> >> as
>>> >> a parameter in several queries.  Is that  possible?  How would I put 
>>> >> the
>>> >> input values in the queries?  (My form uses the data from several
>>> >> queries.)
>>> >>
>>> >> TIA
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
>
> 


0
zSplash
3/27/2007 10:12:14 PM
You do not need VBA.

Open the query in design view.  In the criteria row of the grid place    
Between [Forms]![fGetStats]![tbxBegDate] And [Forms]![fGetStats]![tbxEndDate] 
   below your date field that you want to use in selecting the records.


-- 
KARL DEWEY
Build a little - Test a little


"zSplash" wrote:

> Thanks, Karl.  I have created a new form ("fGetStats").  After I get the 
> input, how do I put that into the query?  I understand you say I'll be 
> putting "Between [Forms]![fGetStats]![tbxBegDate] And 
> [Forms]![fGetStats]![tbxEndDate]" into the criteria for the various queries, 
> but I don't get how to do that.  Do I do that with VBA?
> 
> "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message 
> news:B68FBE0E-FCF0-436A-B6C3-54AECB99765D@microsoft.com...
> >I would not use 'Form_Load event' but just create two unbound textbox to
> > enter your dates.
> > In the criteria of the query use ---
> >   Between   [Forms]![YourFormName]![YourTextbox1] And
> > [Forms]![YourFormName]![YourTextbox1]
> >
> > Like I said replace replace form & textbox names with your names.
> >
> > Open the form, enter dates, then run the query.
> > -- 
> > KARL DEWEY
> > Build a little - Test a little
> >
> >
> > "zSplash" wrote:
> >
> >> Thank for the guidance, Karl.
> >> So, I have made 2 global variables in the Form_Load event -- begDate and
> >> endDate.  In the Form_Load event, from an inputbox, I get the value for 
> >> the
> >> year, which I then make into a date (begDate = ">#1/1/" & theYear & "#").
> >> In the parameter of the query (from which the form is based), I put this
> >> code:
> >>     >[Forms]![myFormName]![begDate] And <[Forms]![myFormName]![endDate]
> >>
> >> On my form, I get the value 0 instead of 73[on the form, I am doing a
> >> Count(*) of a textbox].  Do I have to use an SQL statement in my VBA 
> >> code,
> >> somehow, instead of use a variable in my query parameter, or what?
> >>
> >>
> >> "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message
> >> news:E3BEE1F3-E493-4164-824D-7C91FC6B0CAD@microsoft.com...
> >> > You need to use an unbound textbox to enter the parameter.
> >> >
> >> > In the query use this as criteria, replace form & textbox names with
> >> > yours ---
> >> >    [Forms]![YourFormName]![YourTextbox]
> >> >
> >> > The above is for exact match criteria. For start of field use ---
> >> >    Like [Forms]![YourFormName]![YourTextbox] & "*"
> >> >
> >> > For match any part of field use ---
> >> >    Like "*" & [Forms]![YourFormName]![YourTextbox] & "*"
> >> >
> >> >
> >> > -- 
> >> > KARL DEWEY
> >> > Build a little - Test a little
> >> >
> >> >
> >> > "zSplash" wrote:
> >> >
> >> >> I want to use an inputbox in the Form_Load event, and then use that 
> >> >> input
> >> >> as
> >> >> a parameter in several queries.  Is that  possible?  How would I put 
> >> >> the
> >> >> input values in the queries?  (My form uses the data from several
> >> >> queries.)
> >> >>
> >> >> TIA
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
Utf
3/27/2007 11:01:17 PM
Post your query SQL statement.   Open the query in design view.  Click on 
menu VIEW - SQL View.   Highlight all, copy, paste in a post.

-- 
KARL DEWEY
Build a little - Test a little


"zSplash" wrote:

> OK.  I think I figured out what I'm supposed to do.  In the query itself, I 
> put "Between [Forms]![fGetStats]![tbxBegDate] And 
> [Forms]![fGetStats]![tbxEndDate]" (no quotes) as a parameter.  Then, when I 
> open the "parameterForm" ("fGetStats"), I'll input the desired dates and 
> click the cmdButton.  On the click_event of the cmdButton, I open the 
> underlying form, which is based on the query.  The trouble is, if I manually 
> put the dates in the query, I get a count of 6.  If I use this method, my 
> count is 0.  So, I think I'm still goofed up.
> 
> "zSplash" <zsplash@nospamPlease.com> wrote in message 
> news:%23J2w1fLcHHA.2300@TK2MSFTNGP06.phx.gbl...
> > Thanks, Karl.  I have created a new form ("fGetStats").  After I get the 
> > input, how do I put that into the query?  I understand you say I'll be 
> > putting "Between [Forms]![fGetStats]![tbxBegDate] And 
> > [Forms]![fGetStats]![tbxEndDate]" into the criteria for the various 
> > queries, but I don't get how to do that.  Do I do that with VBA?
> >
> > "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message 
> > news:B68FBE0E-FCF0-436A-B6C3-54AECB99765D@microsoft.com...
> >>I would not use 'Form_Load event' but just create two unbound textbox to
> >> enter your dates.
> >> In the criteria of the query use ---
> >>   Between   [Forms]![YourFormName]![YourTextbox1] And
> >> [Forms]![YourFormName]![YourTextbox1]
> >>
> >> Like I said replace replace form & textbox names with your names.
> >>
> >> Open the form, enter dates, then run the query.
> >> -- 
> >> KARL DEWEY
> >> Build a little - Test a little
> >>
> >>
> >> "zSplash" wrote:
> >>
> >>> Thank for the guidance, Karl.
> >>> So, I have made 2 global variables in the Form_Load event -- begDate and
> >>> endDate.  In the Form_Load event, from an inputbox, I get the value for 
> >>> the
> >>> year, which I then make into a date (begDate = ">#1/1/" & theYear & 
> >>> "#").
> >>> In the parameter of the query (from which the form is based), I put this
> >>> code:
> >>>     >[Forms]![myFormName]![begDate] And <[Forms]![myFormName]![endDate]
> >>>
> >>> On my form, I get the value 0 instead of 73[on the form, I am doing a
> >>> Count(*) of a textbox].  Do I have to use an SQL statement in my VBA 
> >>> code,
> >>> somehow, instead of use a variable in my query parameter, or what?
> >>>
> >>>
> >>> "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message
> >>> news:E3BEE1F3-E493-4164-824D-7C91FC6B0CAD@microsoft.com...
> >>> > You need to use an unbound textbox to enter the parameter.
> >>> >
> >>> > In the query use this as criteria, replace form & textbox names with
> >>> > yours ---
> >>> >    [Forms]![YourFormName]![YourTextbox]
> >>> >
> >>> > The above is for exact match criteria. For start of field use ---
> >>> >    Like [Forms]![YourFormName]![YourTextbox] & "*"
> >>> >
> >>> > For match any part of field use ---
> >>> >    Like "*" & [Forms]![YourFormName]![YourTextbox] & "*"
> >>> >
> >>> >
> >>> > -- 
> >>> > KARL DEWEY
> >>> > Build a little - Test a little
> >>> >
> >>> >
> >>> > "zSplash" wrote:
> >>> >
> >>> >> I want to use an inputbox in the Form_Load event, and then use that 
> >>> >> input
> >>> >> as
> >>> >> a parameter in several queries.  Is that  possible?  How would I put 
> >>> >> the
> >>> >> input values in the queries?  (My form uses the data from several
> >>> >> queries.)
> >>> >>
> >>> >> TIA
> >>> >>
> >>> >>
> >>> >>
> >>>
> >>>
> >>>
> >
> > 
> 
> 
> 
0
Utf
3/27/2007 11:52:21 PM
Here is my SQL statement:
    SELECT q1Main.DRef, q1Main.Type
    FROM q1Main
    WHERE (((q1Main.DRef) Between [Forms]![fgetStats]![begDate] And 
([q1Main].[DRef])<[Forms]![fgetStats]![endDate]));

I think the trouble I'm having now is trying to open the report, which is 
based on a form, which is based on queries -- and having the queries and 
form have the parameter value (from fgetStats form) before I run them. 
(Uffdah!)  I've been trying it just by opening the "parameterForm" 
("fgetStats"), but now I'm wondering if that's not good...

"KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message 
news:0B9F1D87-8E65-406A-AB2B-5DD8C79EA151@microsoft.com...
> Post your query SQL statement.   Open the query in design view.  Click on
> menu VIEW - SQL View.   Highlight all, copy, paste in a post.
>
> -- 
> KARL DEWEY
> Build a little - Test a little
>
>
> "zSplash" wrote:
>
>> OK.  I think I figured out what I'm supposed to do.  In the query itself, 
>> I
>> put "Between [Forms]![fGetStats]![tbxBegDate] And
>> [Forms]![fGetStats]![tbxEndDate]" (no quotes) as a parameter.  Then, when 
>> I
>> open the "parameterForm" ("fGetStats"), I'll input the desired dates and
>> click the cmdButton.  On the click_event of the cmdButton, I open the
>> underlying form, which is based on the query.  The trouble is, if I 
>> manually
>> put the dates in the query, I get a count of 6.  If I use this method, my
>> count is 0.  So, I think I'm still goofed up.
>>
>> "zSplash" <zsplash@nospamPlease.com> wrote in message
>> news:%23J2w1fLcHHA.2300@TK2MSFTNGP06.phx.gbl...
>> > Thanks, Karl.  I have created a new form ("fGetStats").  After I get 
>> > the
>> > input, how do I put that into the query?  I understand you say I'll be
>> > putting "Between [Forms]![fGetStats]![tbxBegDate] And
>> > [Forms]![fGetStats]![tbxEndDate]" into the criteria for the various
>> > queries, but I don't get how to do that.  Do I do that with VBA?
>> >
>> > "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message
>> > news:B68FBE0E-FCF0-436A-B6C3-54AECB99765D@microsoft.com...
>> >>I would not use 'Form_Load event' but just create two unbound textbox 
>> >>to
>> >> enter your dates.
>> >> In the criteria of the query use ---
>> >>   Between   [Forms]![YourFormName]![YourTextbox1] And
>> >> [Forms]![YourFormName]![YourTextbox1]
>> >>
>> >> Like I said replace replace form & textbox names with your names.
>> >>
>> >> Open the form, enter dates, then run the query.
>> >> -- 
>> >> KARL DEWEY
>> >> Build a little - Test a little
>> >>
>> >>
>> >> "zSplash" wrote:
>> >>
>> >>> Thank for the guidance, Karl.
>> >>> So, I have made 2 global variables in the Form_Load event -- begDate 
>> >>> and
>> >>> endDate.  In the Form_Load event, from an inputbox, I get the value 
>> >>> for
>> >>> the
>> >>> year, which I then make into a date (begDate = ">#1/1/" & theYear &
>> >>> "#").
>> >>> In the parameter of the query (from which the form is based), I put 
>> >>> this
>> >>> code:
>> >>>     >[Forms]![myFormName]![begDate] And 
>> >>> <[Forms]![myFormName]![endDate]
>> >>>
>> >>> On my form, I get the value 0 instead of 73[on the form, I am doing a
>> >>> Count(*) of a textbox].  Do I have to use an SQL statement in my VBA
>> >>> code,
>> >>> somehow, instead of use a variable in my query parameter, or what?
>> >>>
>> >>>
>> >>> "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message
>> >>> news:E3BEE1F3-E493-4164-824D-7C91FC6B0CAD@microsoft.com...
>> >>> > You need to use an unbound textbox to enter the parameter.
>> >>> >
>> >>> > In the query use this as criteria, replace form & textbox names 
>> >>> > with
>> >>> > yours ---
>> >>> >    [Forms]![YourFormName]![YourTextbox]
>> >>> >
>> >>> > The above is for exact match criteria. For start of field use ---
>> >>> >    Like [Forms]![YourFormName]![YourTextbox] & "*"
>> >>> >
>> >>> > For match any part of field use ---
>> >>> >    Like "*" & [Forms]![YourFormName]![YourTextbox] & "*"
>> >>> >
>> >>> >
>> >>> > -- 
>> >>> > KARL DEWEY
>> >>> > Build a little - Test a little
>> >>> >
>> >>> >
>> >>> > "zSplash" wrote:
>> >>> >
>> >>> >> I want to use an inputbox in the Form_Load event, and then use 
>> >>> >> that
>> >>> >> input
>> >>> >> as
>> >>> >> a parameter in several queries.  Is that  possible?  How would I 
>> >>> >> put
>> >>> >> the
>> >>> >> input values in the queries?  (My form uses the data from several
>> >>> >> queries.)
>> >>> >>
>> >>> >> TIA
>> >>> >>
>> >>> >>
>> >>> >>
>> >>>
>> >>>
>> >>>
>> >
>> >
>>
>>
>> 


0
zSplash
3/28/2007 7:30:03 PM
You would open   fgetStats    form and enter the dates.
The report would use a query with the textboxes as criteria.
You can have a command button on     fgetStats   form to open the report.
-- 
KARL DEWEY
Build a little - Test a little


"zSplash" wrote:

> Here is my SQL statement:
>     SELECT q1Main.DRef, q1Main.Type
>     FROM q1Main
>     WHERE (((q1Main.DRef) Between [Forms]![fgetStats]![begDate] And 
> ([q1Main].[DRef])<[Forms]![fgetStats]![endDate]));
> 
> I think the trouble I'm having now is trying to open the report, which is 
> based on a form, which is based on queries -- and having the queries and 
> form have the parameter value (from fgetStats form) before I run them. 
> (Uffdah!)  I've been trying it just by opening the "parameterForm" 
> ("fgetStats"), but now I'm wondering if that's not good...
> 
> "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message 
> news:0B9F1D87-8E65-406A-AB2B-5DD8C79EA151@microsoft.com...
> > Post your query SQL statement.   Open the query in design view.  Click on
> > menu VIEW - SQL View.   Highlight all, copy, paste in a post.
> >
> > -- 
> > KARL DEWEY
> > Build a little - Test a little
> >
> >
> > "zSplash" wrote:
> >
> >> OK.  I think I figured out what I'm supposed to do.  In the query itself, 
> >> I
> >> put "Between [Forms]![fGetStats]![tbxBegDate] And
> >> [Forms]![fGetStats]![tbxEndDate]" (no quotes) as a parameter.  Then, when 
> >> I
> >> open the "parameterForm" ("fGetStats"), I'll input the desired dates and
> >> click the cmdButton.  On the click_event of the cmdButton, I open the
> >> underlying form, which is based on the query.  The trouble is, if I 
> >> manually
> >> put the dates in the query, I get a count of 6.  If I use this method, my
> >> count is 0.  So, I think I'm still goofed up.
> >>
> >> "zSplash" <zsplash@nospamPlease.com> wrote in message
> >> news:%23J2w1fLcHHA.2300@TK2MSFTNGP06.phx.gbl...
> >> > Thanks, Karl.  I have created a new form ("fGetStats").  After I get 
> >> > the
> >> > input, how do I put that into the query?  I understand you say I'll be
> >> > putting "Between [Forms]![fGetStats]![tbxBegDate] And
> >> > [Forms]![fGetStats]![tbxEndDate]" into the criteria for the various
> >> > queries, but I don't get how to do that.  Do I do that with VBA?
> >> >
> >> > "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message
> >> > news:B68FBE0E-FCF0-436A-B6C3-54AECB99765D@microsoft.com...
> >> >>I would not use 'Form_Load event' but just create two unbound textbox 
> >> >>to
> >> >> enter your dates.
> >> >> In the criteria of the query use ---
> >> >>   Between   [Forms]![YourFormName]![YourTextbox1] And
> >> >> [Forms]![YourFormName]![YourTextbox1]
> >> >>
> >> >> Like I said replace replace form & textbox names with your names.
> >> >>
> >> >> Open the form, enter dates, then run the query.
> >> >> -- 
> >> >> KARL DEWEY
> >> >> Build a little - Test a little
> >> >>
> >> >>
> >> >> "zSplash" wrote:
> >> >>
> >> >>> Thank for the guidance, Karl.
> >> >>> So, I have made 2 global variables in the Form_Load event -- begDate 
> >> >>> and
> >> >>> endDate.  In the Form_Load event, from an inputbox, I get the value 
> >> >>> for
> >> >>> the
> >> >>> year, which I then make into a date (begDate = ">#1/1/" & theYear &
> >> >>> "#").
> >> >>> In the parameter of the query (from which the form is based), I put 
> >> >>> this
> >> >>> code:
> >> >>>     >[Forms]![myFormName]![begDate] And 
> >> >>> <[Forms]![myFormName]![endDate]
> >> >>>
> >> >>> On my form, I get the value 0 instead of 73[on the form, I am doing a
> >> >>> Count(*) of a textbox].  Do I have to use an SQL statement in my VBA
> >> >>> code,
> >> >>> somehow, instead of use a variable in my query parameter, or what?
> >> >>>
> >> >>>
> >> >>> "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message
> >> >>> news:E3BEE1F3-E493-4164-824D-7C91FC6B0CAD@microsoft.com...
> >> >>> > You need to use an unbound textbox to enter the parameter.
> >> >>> >
> >> >>> > In the query use this as criteria, replace form & textbox names 
> >> >>> > with
> >> >>> > yours ---
> >> >>> >    [Forms]![YourFormName]![YourTextbox]
> >> >>> >
> >> >>> > The above is for exact match criteria. For start of field use ---
> >> >>> >    Like [Forms]![YourFormName]![YourTextbox] & "*"
> >> >>> >
> >> >>> > For match any part of field use ---
> >> >>> >    Like "*" & [Forms]![YourFormName]![YourTextbox] & "*"
> >> >>> >
> >> >>> >
> >> >>> > -- 
> >> >>> > KARL DEWEY
> >> >>> > Build a little - Test a little
> >> >>> >
> >> >>> >
> >> >>> > "zSplash" wrote:
> >> >>> >
> >> >>> >> I want to use an inputbox in the Form_Load event, and then use 
> >> >>> >> that
> >> >>> >> input
> >> >>> >> as
> >> >>> >> a parameter in several queries.  Is that  possible?  How would I 
> >> >>> >> put
> >> >>> >> the
> >> >>> >> input values in the queries?  (My form uses the data from several
> >> >>> >> queries.)
> >> >>> >>
> >> >>> >> TIA
> >> >>> >>
> >> >>> >>
> >> >>> >>
> >> >>>
> >> >>>
> >> >>>
> >> >
> >> >
> >>
> >>
> >> 
> 
> 
> 
0
Utf
3/28/2007 8:02:05 PM
On Wed, 28 Mar 2007 11:30:03 -0800, "zSplash" <zsplash@nospamPlease.com>
wrote:

>    WHERE (((q1Main.DRef) Between [Forms]![fgetStats]![begDate] And 
>([q1Main].[DRef])<[Forms]![fgetStats]![endDate]));
>

You're mixing things up: the BETWEEN operator in a query expects two values;
you're giving it one value and one expression.

Change the BETWEEN to >= and you should be a step closer.

             John W. Vinson [MVP]
0
John
3/28/2007 8:07:01 PM
Thanks, guys.  I have modified the SQL code to be:
    >=[Forms]![fgetStats]![tbxBegDate] And 
<=[Forms]![fgetStats]![tbxEndDate]

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:klil03lk540ieuobbj3a07jf0bm4h0bgc0@4ax.com...
> On Wed, 28 Mar 2007 11:30:03 -0800, "zSplash" <zsplash@nospamPlease.com>
> wrote:
>
>>    WHERE (((q1Main.DRef) Between [Forms]![fgetStats]![begDate] And
>>([q1Main].[DRef])<[Forms]![fgetStats]![endDate]));
>>
>
> You're mixing things up: the BETWEEN operator in a query expects two 
> values;
> you're giving it one value and one expression.
>
> Change the BETWEEN to >= and you should be a step closer.
>
>             John W. Vinson [MVP] 


0
zSplash
3/29/2007 6:58:49 PM
Reply:

Similar Artilces:

Is there a way for a client to add contacts to Global Address List?
We are running Exchange 2007 at my company and since I am the admin I have to constantly update our Global Address List and create Universal Distribution groups using Exchange Management Console. Is there a way to allow a user to create these contacts using just Microsoft Outlook 2007 client? And same for the distribution groups. (Right now I create the distribution groups and give users permissions to modify them). If someone has an answer out there I would greatly appreciate it. Thanks, Nick ...

Re: How to lock input?
Is there any way to lock input in a textbox or cell once is entere once... so it wont be altered??? -- Message posted from http://www.ExcelForum.com May not be quite what you want, but following protects a worksheet with a random password (i.e. you can't unprotect it without a password cracker). Set any cells you want to be still amendable to unprotected first. ActiveSheet.Protect Password:="A" & Int(Rnd() * 10000000000#) >-----Original Message----- >Is there any way to lock input in a textbox or cell once is entered >once... so it wont be altered???? >...

forms not listed in database contents, yet can add data to them
Hi - there are no forms listed when I open up the database in design view. All the tables, queries, and macros are there. When I open up the database to add data, all the forms are there and can be used. The data is stored. So, it seems that the forms are still there, but just cannot be seen in design view. The database, when I open it up in design view, says Access 2000 file object even though I am using Access 2003 - could that be the problem? Thanks! You cannot open an entire database in Design View. Try opening it normally, selecting a form, and choosing Design View from the V...

Macro/VBA to loop through list of values and output row- Help Need
I am new to VBA/Macros and really don't know how to get this to work. I have a spreadsheet which calculates distances between two postcodes for a list of suppliers. Each supplier can have one or more branches at different locations (postcodes). I need to return only the closest branch of each supplier (i.e. copy the row of the closest supplier to a new sheet. The sheet has 6 columns. Only 2 of these columns are needed to work out this problem. Column A holds the Supplier Name and Column F holds the distance from us. I have worked out the steps needed to complete this...

group policy
I've looked in the group policy template from the Office Resource Kit, and can't seem to find any way to add our RPC over HTTP settings. I'd rather add this through group policy, instead of a login script to run maintwiz. Is it possible? Thanks Mike Account-specific settings cannot be added with Group Policy Objects. You = need to use a .prf file, as generated by CIW or CMW.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administ...

Query won't total...
Microsoft Access 2003 I have a query which is pulling from several numerical fields. Each field individually shows up fine, but when I try to get a total of several of the fields together the total field ends up blank. Here is the formula I have in the query: Total: ([0111LICN]+[0110OTHER]+[0112CLAS]+[0113ADMN]+[0114MNGR]) I have tried adding up just 2 of the fields and that works for 50% of them, but the other 50% are still blank. I have also tried entering the table name for each field within the formula but nothing seems to be working. Has anyone run into this issue before, or can a...

VBA Macro change column contents
I have a column in a worksheet which has a list of 3 digit numbers. I want to change the 3 digits to become 4 digits, the new fourth digit has to be a leading zero, i.e 355 to become 0355. I prefer the format of the digits to be a number but a text format would not be a problem. The length of the digits in the column are not static. i.e one month there may be 200 rows of data the next might be 300 and I would want the macro to cater for this variation. Thanks -- James James, A macro simply isn't necessary, select the column and apply a custom format of 0000 that...

VBA Macro Error
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Hello, I�m running in a pretty bad problem: If I write in MSOffice 2008 a number followed by a letter like "5a" on a new line I get an error "This command is not available in this Version of Microsoft Word" It seems that word want�s to make a lookup for a VBA script - which is not supported anymore, so in panic they write the message on the screen. But instead I really want to write only 5a. Is there a button to switch of those interpretations? It was not an issue with 2004 and not on windows. Any...

Query Builder Error: Unexpected Error
I am working with 1.2. I tried to print all cases for an account. When I leave the bullet on 'all pages', and then click the 'print' icon, I receive an error. ...

Add value to a list box
How do I add stuff to a listbox, if it's not already in there? Just like the "Not in List" event of the combo box. ...

Excel add-ins - "xla could not be found"
Recently I started using Excel add-ins (xla files) -- which I have found very handy. For example, I have an add-in that can add a contents page to my workbook, or the list the file contents of a folder, etc. The trouble is, if someone, who doesn't have same xla files on their computer opens my spreadsheet they get a message: "...xla could not be found. Check the spelling of the file name" etc. After that they can open file fine. And apart from the messages, there seems to be no other problem; but I don't see why it is necessary for this message to occur. The add-ins helped me...

Search and replace query
I need to be able to specify my search criteria including leading and/or trailing spaces, and similarly with the replace text. I could not get Excel to accept/handle spaces as part of the search mask. How would I do this? For example if I want to replace all uses of the word he in a database with she how would I specify space he space for the search, and space she space for the replace? TIA Brian Tozer Brian Edit>Replace What: <space>he<space> With: <space>she<space> Works for me. Gord Dibben Excel MVP On Sun, 19 Sep 2004 16:31:09 +1200, "KiwiBrian&...

Jewellery Add-on
Does anyone know of an RMS add-on specifically for use in a jewellery store? Hi Gaz, Would you be interested for custom solution for Jewellery store?. If so please drop a email. Regards, Manick manick.m@hotmail.com "Gaz" wrote: > Does anyone know of an RMS add-on specifically for use in a jewellery store? Gaz, What are you looking for specifically? We have done some work with Jewelry stores in the past. -- Thank you, Ryan Sakry Program Manager Retail Information Technology Enterprises (RITE) rsakry@rite.us http://www.rite.us 320-230-2282 ext. 4002 (Office) 320-230-1796...

Need to get result of a select query in code
I have a select statement that I need to run in code. The select statement needs to reference 2 variables which are in the code. I know that the usual way to run a select in code is with DLookup. This if fine because I only need 1 column of 1 record even though the select can return more than 1 record. But, the query needs to reference those 2 variables in the code. How can I run a select in code which references 2 fields in the code and get the result of 1 record (only)? If necessary I could put those 2 values in textboxes on forms as I have several queries which reference textbox...

ComboBox related query
On a form I have a ComboBox (Combo8). Is there a way to base a query on the value of what is selected in the ComboBox? Here is the SQL statement I've created, that is not working: SELECT [Combo8.value].DRAWING, [Combo8.value].[EQUIPMENT/CIRCUITS], [Combo8.value].[CABLE TYPE], [Combo8.value].[FND/SEQ], [Combo8.value].COMPT, [Combo8.value].LEX, [Combo8.value].ZONE, [Combo8.value].BILL, [23Tag and TM Data].[To Eq], [23Tag and TM Data].[To Desc], [23Tag and TM Data].[From Eq], [23Tag and TM Data].[From Desc] FROM Combo8 LEFT JOIN [23Tag and TM Data] ON Combo8.value.[EQUIPMEN...

Delete Query 03-27-10
Please Help - I have searched the posts and have found some ideas, but nothing seems to work. Here is the sql statement Error 3086 resolutions do not apply as far as i can determine DELETE DISTINCTROW DistributedAnalyst.*, DistributedByMaterial.Material FROM DistributedAnalyst LEFT JOIN DistributedByMaterial ON DistributedAnalyst.Material = DistributedByMaterial.Material WHERE (((DistributedByMaterial.Material) Is Null)); Thanks in advance On Sat, 27 Mar 2010 15:55:01 -0700, dmoney <dmoney@discussions.microsoft.com> wrote: >Please Help - I have searched the posts...

VBA code for "Save as PDF"
I have developed a reporting tool in excel, where I need series of worksheets to be saved as PDF one after another through a click of button (VBA macro-enabled). I need VBA code to save my print-area as PDF where it only once asks "where to save" and then saves current series of PDFs. It should take file name from a fixed cell too. Regards, Pritesh Check out this link... http://www.excelguru.ca/node/21 -- HTH... Jim Thomlinson "Pritesh" wrote: > I have developed a reporting tool in excel, where I need series of worksheets > to be sa...

Add Text Together for Report
I tried to search for this one but not sure how to word it. What I am trying to figure out is how to make a report that adds all the text from a query together and displays it in one field. For example: I have a table that gets me the following data. deconid logdate log 14 12/06/2007 This is just a test 14 12/07/2007 yes.. This is a log 14 12/08/2007 Yet a log number three 14 12/09/2007 This is number 4 I would like it to display this data in a report like this: With DeconID 14, "12/09/2007- This is number 4 ~ 12/08/2007- yes.. Yet a log number three ~ 12/07/2007-...

Including Null values in query design
I am using a form to enter the parameters for a report. The parameters are drawn from a number of combo boxes on the form. The report is based on a query. Several of the query fields have expressions such as: Like IIf(IsNull([forms]![frmReports]![cboIssuedBy]),"*",([forms]! [frmReports]![cboIssuedBy])). This works fine as long as the fields that are being queried have no null values - if they do, then in the situation where I want the "*" to apply, the query only returns the records which have a value. Really, I want to be able to replace the "*" pa...

two sets of parameters..one query...but how?
I have a query to track when I must renew State Police Clearances AND Child Abuse Clearances. I want to use the "Between [date] and [date]" function on BOTH of the fields in order to bring up BOTH sets of dates for BOTH clearances.The problem I am running into is that when the query results come up, if one date doesn't meet the criteria NONE of the information comes up (even if the other date DOES).Any suggestions!??!?! Thanks in Advance....

Database query won't open
I am trying to create a new database query from within Excel 2000. I was able to do this successfully yesterday from the same PC. Today, when I go to Data, Get External data, new database query, I get the hour glass and Excel then locks up. Anyone have any ideas? -- maryj ...

Odd Add-in behaviour
HI all, We have created an Excel application for a client which consists of an Add-In (xla) and a workbook. We tested it thoroughly under Win2K+Office2K (client set-up) and WinXP-OfficeXP (our setup). All seemed fine. One of our test users now has a problem in that when the Add-In is enabled in Excel Tools>AddIns, his machine slows down tremendously (most noticably using Outlook over a network), disenabling the Addin speeds things up. The Add-In itself is simple - just a user settings management routine and adds a control to the Excel Menu Bar - with no complex references etc. And perh...

Need Help Filtering a Form with VBA
I have a form whose underlying data comes primarily from "Table1" (for this example). On the form is a drop-down box that displays a field named "Primary OBS" for each record. "Primary OBS" is stored in "Table1". It is an index into another table "Table2" which contains a field named "Team Code". "Team Code" can look like "JD130000" or "JG320000", etc. What I need to do is to filter the records in the form by the first two letters of the "Team Code". In other words, the user ca...

How to add newsreader to Outlook 2003 with SP1?
I'm trying to follow the instructions of Start the newsgroup reader. But I cannot find the News command in the command list of the Go menu. I'm using Outlook 2003 with SP1. Thanks. To get the News command back, go back to the Programs tab in Control Panel | Internet Options and change the default newsreader to Outlook Express. If OE prompts you to make it your default newsreader the next time you launch it, clear the "Always perform this check..." box and then click the No button. -- Russ Valentine [MVP-Outlook] "Peter" <Peter@discussions.microsoft.com&...

VBA using an image as a toggle switch
Hi hope you can help. I know next to nothing about VBA and have adapted code below from stuff I had. I simply want to show hide portions of my page to cut down on screen clutter/information overload. I have assigned my macro to a screen image but was wondering if I could have a hide and unhide under the same button. Any help would be much appreciated Call Unl Application.ScreenUpdating = False Rows("25:75").EntireRow.Hidden = True Rows("98:148").EntireRow.Hidden = True Rows("171:221").EntireRow.Hidden = True Rows("244:294").EntireRow.Hidden = T...