VBA input, add to query

[zSplash]

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

 

[Guest]

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] & "*"

 

[zSplash]

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?

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

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

 

[Guest]

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

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?

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

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

 

[zSplash]

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?

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

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?

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


:

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

 

[zSplash]

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.

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?

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

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?


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


:

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

 

[Guest]

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

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?

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

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?


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


:

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

 

[Guest]

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

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.

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?

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


:

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?


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


:

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

 

[zSplash]

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

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

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.

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?

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


:

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?


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


:

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

 

[Guest]

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

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

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

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.

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?

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


:

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?


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


:

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

 

[John W. Vinson]

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]

 

[zSplash]

Thanks, guys. I have modified the SQL code to be:

=[Forms]![fgetStats]![tbxBegDate] And

<=[Forms]![fgetStats]![tbxEndDate]

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]