Hi. I have a query named Query1. Let's say it looks like this:
Select * from Table1 where Field1=[Enter Value]
So whenever you run the query, it prompts for "Enter Value".
So now, I have Report1, whose RecordSource is Query1.
So when I run Report1, Access prompts "Enter Value". Everything's good so
far.
Well, the thing is, I need to do some code inside the report for some
purpose which I won't go into, and I need to know what value the user
entered for [Enter Value]. Is there some way for me to get that value?
I know it would seem like I could just query maybe any Detail record and see
what value Field1 has, and that would be it, but my case is actually a
little more complicated, because my query is actually more like:
Select * from Table1 where TheDate between [Start Date] and [End Date]
and I want to know what the Start Date and the End Date are inside the VBA
code because I need to do something with it.
TIA!
David 9 2865
Rewrite the query like this ...
Select *, [Enter Value] As UserEnterValue
from Table1 where Field1=[Enter Value]
or more accurately ...
Select *,[Start Date] As StartDate, [End Date] As EndDate
from Table1 where TheDate between [Start Date] and [End Date]
--
Danny J. Lesandrini dl*********@hotmail.com http://amazecreations.com/datafast/
"David Horowitz" <da***@soundsidesoftware.com> wrote ... Hi. I have a query named Query1. Let's say it looks like this:
Select * from Table1 where Field1=[Enter Value]
So whenever you run the query, it prompts for "Enter Value".
So now, I have Report1, whose RecordSource is Query1.
So when I run Report1, Access prompts "Enter Value". Everything's good so far.
Well, the thing is, I need to do some code inside the report for some purpose which I won't go into, and I need to know what value the user entered for [Enter Value]. Is there some way for me to get that value?
I know it would seem like I could just query maybe any Detail record and see what value Field1 has, and that would be it, but my case is actually a little more complicated, because my query is actually more like:
Select * from Table1 where TheDate between [Start Date] and [End Date]
and I want to know what the Start Date and the End Date are inside the VBA code because I need to do something with it.
TIA!
David
You should be able to put text boxes on your report bound to the parameters,
e.g.
=[Start Date]
You could then refer to the value of the text box in your code.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"David Horowitz" <da***@soundsidesoftware.com> wrote in message
news:Do*************@news-wrt-01.rdc-nyc.rr.com... Hi. I have a query named Query1. Let's say it looks like this:
Select * from Table1 where Field1=[Enter Value]
So whenever you run the query, it prompts for "Enter Value".
So now, I have Report1, whose RecordSource is Query1.
So when I run Report1, Access prompts "Enter Value". Everything's good so far.
Well, the thing is, I need to do some code inside the report for some purpose which I won't go into, and I need to know what value the user entered for [Enter Value]. Is there some way for me to get that value?
I know it would seem like I could just query maybe any Detail record and see what value Field1 has, and that would be it, but my case is actually a little more complicated, because my query is actually more like:
Select * from Table1 where TheDate between [Start Date] and [End Date]
and I want to know what the Start Date and the End Date are inside the VBA code because I need to do something with it.
Wow, thanks Danny! That looks like it may help, I think I'm missing one more
thing:
How would I then get the values while inside, say, GroupHeader0_Format?
Thanks!
Dave
"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:IL********************@comcast.com... Rewrite the query like this ... Select *, [Enter Value] As UserEnterValue from Table1 where Field1=[Enter Value]
or more accurately ...
Select *,[Start Date] As StartDate, [End Date] As EndDate from Table1 where TheDate between [Start Date] and [End Date] --
Danny J. Lesandrini dl*********@hotmail.com http://amazecreations.com/datafast/ "David Horowitz" <da***@soundsidesoftware.com> wrote ... Hi. I have a query named Query1. Let's say it looks like this:
Select * from Table1 where Field1=[Enter Value]
So whenever you run the query, it prompts for "Enter Value".
So now, I have Report1, whose RecordSource is Query1.
So when I run Report1, Access prompts "Enter Value". Everything's good so far.
Well, the thing is, I need to do some code inside the report for some purpose which I won't go into, and I need to know what value the user entered for [Enter Value]. Is there some way for me to get that value?
I know it would seem like I could just query maybe any Detail record and see what value Field1 has, and that would be it, but my case is actually a little more complicated, because my query is actually more like:
Select * from Table1 where TheDate between [Start Date] and [End Date]
and I want to know what the Start Date and the End Date are inside the VBA code because I need to do something with it.
TIA!
David
Yeah, I realize I forgot to mention that. You can treat these query
columns as a field. Set the control source of a text box to StartDate.
I see that someone posted an answer where you could set the expression,
[Start Date] as the control source. I wasn't aware that it would work
that way, but if so, that's even easier. Give them both a try.
--
Danny J. Lesandrini dl*********@hotmail.com http://amazecreations.com/datafast/
"David Horowitz" <da***@soundsidesoftware.com> wrote ... Wow, thanks Danny! That looks like it may help, I think I'm missing one more thing:
How would I then get the values while inside, say, GroupHeader0_Format?
Thanks!
Dave
"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message news:IL********************@comcast.com... Rewrite the query like this ... Select *, [Enter Value] As UserEnterValue from Table1 where Field1=[Enter Value]
or more accurately ...
Select *,[Start Date] As StartDate, [End Date] As EndDate from Table1 where TheDate between [Start Date] and [End Date] --
Danny J. Lesandrini dl*********@hotmail.com http://amazecreations.com/datafast/ "David Horowitz" <da***@soundsidesoftware.com> wrote ... Hi. I have a query named Query1. Let's say it looks like this:
Select * from Table1 where Field1=[Enter Value]
So whenever you run the query, it prompts for "Enter Value".
So now, I have Report1, whose RecordSource is Query1.
So when I run Report1, Access prompts "Enter Value". Everything's good so far.
Well, the thing is, I need to do some code inside the report for some purpose which I won't go into, and I need to know what value the user entered for [Enter Value]. Is there some way for me to get that value?
I know it would seem like I could just query maybe any Detail record and see what value Field1 has, and that would be it, but my case is actually a little more complicated, because my query is actually more like:
Select * from Table1 where TheDate between [Start Date] and [End Date]
and I want to know what the Start Date and the End Date are inside the VBA code because I need to do something with it.
TIA!
David
To reference the values from within code, you must bind a control to the
field/column.
--
Duane Hookom
MS Access MVP
--
"David Horowitz" <da***@soundsidesoftware.com> wrote in message
news:mN*************@news-wrt-01.rdc-nyc.rr.com... Wow, thanks Danny! That looks like it may help, I think I'm missing one more thing:
How would I then get the values while inside, say, GroupHeader0_Format?
Thanks!
Dave
"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message news:IL********************@comcast.com... Rewrite the query like this ... Select *, [Enter Value] As UserEnterValue from Table1 where Field1=[Enter Value]
or more accurately ...
Select *,[Start Date] As StartDate, [End Date] As EndDate from Table1 where TheDate between [Start Date] and [End Date] --
Danny J. Lesandrini dl*********@hotmail.com http://amazecreations.com/datafast/ "David Horowitz" <da***@soundsidesoftware.com> wrote ... Hi. I have a query named Query1. Let's say it looks like this:
Select * from Table1 where Field1=[Enter Value]
So whenever you run the query, it prompts for "Enter Value".
So now, I have Report1, whose RecordSource is Query1.
So when I run Report1, Access prompts "Enter Value". Everything's good so far.
Well, the thing is, I need to do some code inside the report for some purpose which I won't go into, and I need to know what value the user entered for [Enter Value]. Is there some way for me to get that value?
I know it would seem like I could just query maybe any Detail record and see what value Field1 has, and that would be it, but my case is actually a little more complicated, because my query is actually more like:
Select * from Table1 where TheDate between [Start Date] and [End Date]
and I want to know what the Start Date and the End Date are inside the VBA code because I need to do something with it.
TIA!
David
Hi Danny,
I figured out the answer to my last question -- put hidden textbox(es) on
the report and just do Me!StartDate and Me!EndDate -- thanks!
But, I realize I have this other problem. I need to use the same parameters
to requery the query again. I'm dealing with legacy code that I don't want
to completely re-write, otherwise I'm sure there are better ways to do this.
So now what I have is regular ADO code that runs a SQL query such as:
"Select * from Query1 where blah blah order by blah blah"
but I need to specify Query1's parameters (StartDate and EndDate).
Any help?
Thanks so much!
David
David
"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:IL********************@comcast.com... Rewrite the query like this ... Select *, [Enter Value] As UserEnterValue from Table1 where Field1=[Enter Value]
or more accurately ...
Select *,[Start Date] As StartDate, [End Date] As EndDate from Table1 where TheDate between [Start Date] and [End Date] --
Danny J. Lesandrini dl*********@hotmail.com http://amazecreations.com/datafast/ "David Horowitz" <da***@soundsidesoftware.com> wrote ... Hi. I have a query named Query1. Let's say it looks like this:
Select * from Table1 where Field1=[Enter Value]
So whenever you run the query, it prompts for "Enter Value".
So now, I have Report1, whose RecordSource is Query1.
So when I run Report1, Access prompts "Enter Value". Everything's good so far.
Well, the thing is, I need to do some code inside the report for some purpose which I won't go into, and I need to know what value the user entered for [Enter Value]. Is there some way for me to get that value?
I know it would seem like I could just query maybe any Detail record and see what value Field1 has, and that would be it, but my case is actually a little more complicated, because my query is actually more like:
Select * from Table1 where TheDate between [Start Date] and [End Date]
and I want to know what the Start Date and the End Date are inside the VBA code because I need to do something with it.
TIA!
David
"David Horowitz" <da***@soundsidesoftware.com> wrote So now what I have is regular ADO code that runs a SQL query such as:
"Select * from Query1 where blah blah order by blah blah"
but I need to specify Query1's parameters (StartDate and EndDate).
Dim cmd1 As ADODB.Command
Dim prm1 As ADODB.Parameter
Dim prm2 As ADODB.Parameter
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = CurrentProject.Connection
.CommandText = "MySavedPQuery"
.CommandType = adCmdStoredProc
Set prm1 = .CreateParameter("Start Date", adDate, adParamInput, ,
Me!txtStartDate.Value)
Set prm2 = .CreateParameter("End Date", adDate, adParamInput, ,
Me!txtEndDate.Value)
.Parameters.Append prm1
.Parameters.Append prm2
End With
rs.Open cmd1, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
--
Darryl Kerkeslager
Wordy But Works.
Are you saying that, after the user supplies the parameters and the query
has run, you'd like to reuse those same parameters again in another process?
If that's the case, then maybe you could use some functions to save the values
into global variables.
Select Table1 .*,
SaveStartDate([Start Date]) As StartDate,
SaveEndDate([End Date]) As EndDate
from Table1 where TheDate between [Start Date] and [End Date]
Create two function-pairs to save and get the values. For example, one pair
would look like this ...
Private m_dteStartDate As Date
Public Function SaveStartDate(ByVal varValue As Variant) As Date
' If varValue isn't a date, use a default start date of your choice.
If Not IsDate(varValue) Then
' If missing, set StartDate to Today -(7 Days)
m_dteStartDate = DateAdd("d",-7,Date())
Else
m_dteStartDate = varValue
End If
SaveStartDate = m_dteStartDate
End Function
Private Function GetStartDate() As Date
' If the value hasn't been set yet, it will be zero (0). Use default value
If m_dteStartDate = 0 Then m_dteStartDate = DateAdd("d",-7,Date())
GetStartDate = m_dteStartDate
End Function
Now, after the query has run, the value entered by the user will be saved
in the Private variable, m_dteStartDate, and will be accessible through the
function GetStartDate() anywhere you wish to use it.
Cool, huh?
--
Danny J. Lesandrini dl*********@hotmail.com http://amazecreations.com/datafast/
"David Horowitz" <da***@soundsidesoftware.com> wrote ... Hi Danny,
I figured out the answer to my last question -- put hidden textbox(es) on the report and just do Me!StartDate and Me!EndDate -- thanks!
But, I realize I have this other problem. I need to use the same parameters to requery the query again. I'm dealing with legacy code that I don't want to completely re-write, otherwise I'm sure there are better ways to do this.
So now what I have is regular ADO code that runs a SQL query such as:
"Select * from Query1 where blah blah order by blah blah"
but I need to specify Query1's parameters (StartDate and EndDate).
Any help?
Thanks so much!
David
Thanks everyone for your help. Really.
David
"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:e9********************@comcast.com... Yeah, I realize I forgot to mention that. You can treat these query columns as a field. Set the control source of a text box to StartDate.
I see that someone posted an answer where you could set the expression, [Start Date] as the control source. I wasn't aware that it would work that way, but if so, that's even easier. Give them both a try.
--
Danny J. Lesandrini dl*********@hotmail.com http://amazecreations.com/datafast/ "David Horowitz" <da***@soundsidesoftware.com> wrote ... Wow, thanks Danny! That looks like it may help, I think I'm missing one more thing:
How would I then get the values while inside, say, GroupHeader0_Format?
Thanks!
Dave
"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message news:IL********************@comcast.com... Rewrite the query like this ... Select *, [Enter Value] As UserEnterValue from Table1 where Field1=[Enter Value]
or more accurately ...
Select *,[Start Date] As StartDate, [End Date] As EndDate from Table1 where TheDate between [Start Date] and [End Date] --
Danny J. Lesandrini dl*********@hotmail.com http://amazecreations.com/datafast/ "David Horowitz" <da***@soundsidesoftware.com> wrote ... Hi. I have a query named Query1. Let's say it looks like this:
Select * from Table1 where Field1=[Enter Value]
So whenever you run the query, it prompts for "Enter Value".
So now, I have Report1, whose RecordSource is Query1.
So when I run Report1, Access prompts "Enter Value". Everything's good so far.
Well, the thing is, I need to do some code inside the report for some purpose which I won't go into, and I need to know what value the user entered for [Enter Value]. Is there some way for me to get that value?
I know it would seem like I could just query maybe any Detail record and see what value Field1 has, and that would be it, but my case is actually a little more complicated, because my query is actually more like:
Select * from Table1 where TheDate between [Start Date] and [End Date]
and I want to know what the Start Date and the End Date are inside the VBA code because I need to do something with it.
TIA!
David
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Simon Bailey |
last post by:
How do you created a query in VB?
I have a button on a form that signifies a certain computer in a
computer suite. On clicking on this button i would like to create a
query searching for all...
|
by: Mark Flippin |
last post by:
This is for a reporting problem using:
Access 2000
SQL Server 2000
Both at SP3
I've a stored procedure in SQL Server 2000 which builds a result set
from a disparate set of tables, utilizing...
|
by: Sreeneet |
last post by:
Hi all,
I want to call a stored procedure which is written in SQL Server from
an ms-access query. It is having some parameters also and the stored
procedure will return some records.
Is there...
|
by: Richard Hollenbeck |
last post by:
I tried to ask this question before on the 14th of January but I never got a
reply. I'm still struggling with the problem. I'll try to rephrase the
question:
I have a crosstab query with rows...
|
by: Lee |
last post by:
Hello All,
First of all I would like to say thank you for all of the help I have
received here. I have been teaching myself Access for about 4 years
now and I've always been able to find a...
|
by: scott.k.fraley |
last post by:
....and the SELECT thats trying to pull from said Query doesn't like it
one bit! ;)
I'm working on this project (in Access 2002) and there is a report
who's RecordSource is the following...
|
by: amorphous999 |
last post by:
I am running Access 2002. I have some reports I would like to be able to
run from VBA or interactively. The reports use queries with parameters.
When the user runs the report, the parameter...
|
by: dana1 |
last post by:
Hello Experts!
Does anyone know if there is a way to set the values of query parameters from VBA for a report's recordsource? (i.e., I want to set the values of the parameters and NOT have the...
|
by: Phil Stanton |
last post by:
I am running a query that calls a function used to format addresses
depending on the width of a control on a report that shows that address. The
same query is used as the RecordSource of lots of...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |