Re: Parameters in queries

I believe that is exactly what I am saying, but someone else might be
able to come up with a way. I can't think of any way you could do
what you are saying without code.



On Jan 21, 2:58 am, colmkav <colmj...@xxxxxxxxxxx> wrote:
No. I am not doing it from VBA code (or other code). In that case it
would be very simple.

What I want needs to be in SQL.

Are you saying that its not possible?

regards
Colm

On 18 Jan, 17:35, n00b <adv...@xxxxxxxxx> wrote:



Where are you running the query?  If you execute it from code, you
could specify the parameter so you could have the 2 queries you need
with only one querydef.  It's going to take that to avoid creating the
second query.

In code you could do something like this:

Dim qd As QueryDef

Set qd = CurrentDb.QueryDefs("Query1")   ' Your INSERT query

qd.Parameters("#addDate") = Date

qd.Execute

-OR-

qd.Parameters("#addDate") = txtAddDate   ' User's input

qd.Execute

Then wrapper this in some type of conditional statement to determine
what it fills the parameter with.

On Jan 18, 10:07 am, colmkav <colmj...@xxxxxxxxxxx> wrote:

Well the first query should use the date given by the user as a
parameter
the 2nd query should use todays date instead of the parameter. ie
using the date() function.

But Id like not to have to duplicate the first query exactly if
possible. ie both queries should use the same subquery not 2 separate
subqueries

Hope that makes sense

On 18 Jan, 16:15, n00b <adv...@xxxxxxxxx> wrote:

I think I have an idea of what you are saying, but please be more
specific about "uses today as the date".

What are you really wanting the first query to do?  Be verbose.

On Jan 18, 3:22 am, colmkav <colmj...@xxxxxxxxxxx> wrote:

Im not 100% sure what you mean.

here are my exact queries:

INSERT INTO tblBBIDRiskMeasureLink ( BBIdentifier, BBRiskMeasure )
SELECT BBIdentifier, "CREDITDELTA"
FROM QryBitMapRiskMeasuresCreditSEL
WHERE BBIdentifier Not In (SELECT BBIdentifier
FROM tblBBIDRiskMeasureLink
WHERE BBRiskMeasure="CREDITDELTA"  );

QryBitMapRiskMeasuresCreditSEL=
SELECT tblSecurityInfo.BBIdentifier, [#addDate] as addDate
FROM (tblProduct INNER JOIN tblDomain ON tblProduct.ProductName =
tblDomain.Code) INNER JOIN tblSecurityInfo ON tblDomain.DomainId =
tblSecurityInfo.ProductCodeId
WHERE (tblSecurityInfo.AddDate>[#addDate] AND
tblProduct.VaRSplitCreditSpreadLong<>"NA");

how do I change these queries so that I can have one query that does
what it does at the moment ie select on date according to that given
by the user and a second query that uses today as the date?

in my example
On 17 Jan, 20:56, n00b <n...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Wow, here I go preaching what a subquery is and that wasn't your question at
all, LOL.  Sorry.

After re-reading your post, I have an answer.

If you use a parameter in a query, like [addDate] in your example, you can
use the parameter in as many different places within the query as you would
like (you only get one prompt, not multiple prompts.)  So, you could have a
query like this:

SELECT field, [addDate] As ParameterEntered where fldDate = [addDate]

This will allow your topmost query "know" the parameter value of qrySUB by
selecting it:

SELECT field, ParameterEntered FROM qrySUB WHERE ....

"colmkav" wrote:
Hi,

how can I write a query that specifies the value of a parameter that
exists in a subquery.

eg

SELECT field FROM qrySUB
WHERE ....

qrySUB:

SELECT field where fldDate = [addDate]

Basically the reason I want to do this is that I want two similar
queries one that specifically uses todays date and the other where you
can choose the date and I dont want to duplicate all my subqueries for
each.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

.