Pass Through query with criteria on a form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

WildlyHarry - 25 Oct 2007 19:46 GMT

I have a Pass Through query that looks like this.

select

Location,City,State,Country,Pings

from

(select
sd_term_name_loc as 'Location',
sd_term_city as 'City',
sd_term_st as 'State',
sd_term_cntry as 'Country',
count(sd_key)as 'Pings'
from detail (nolock)
where

-- ***** The dates below refer to the date and time at which the
transaction was loaded to PRM.
-- *****
-- ***** The number of pings which have occurred BETWEEN those dates/times
will be counted.
-- *****
-- ***** Enter the date/time in the format '09/16/2007 14:00:00.000'
Be sure to use apostrophes.

dd_apdate between '10/23/2007 14:30:00.001' and
'10/24/2007 14:30:00.000'

and md_tran_amt1 between .01 and .99
and left(sd_msg_typ,3) in ('pin','sig')
and sd_mbr_num <> 'dep'
and sd_term_name_loc <> ' '
and sd_resp_cde <> 'PA'
group by sd_term_name_loc, sd_term_city, sd_term_st, sd_term_cntry ) temp_a

where
Pings > 5

order by Pings desc

I want to replace the dates and the amounts with variables from a form ie
[form]![form1]![date] etc. How can I do this? Thanks in advance for your
help.

Reply to this Message

Wolfgang Kais - 25 Oct 2007 20:19 GMT

Hello "WildlyHarry".

>I have a Pass Through query that looks like this.
> [...]
>
> I want to replace the dates and the amounts with variables from a
> form ie [form]![form1]![date] etc. How can I do this?

Maybe this helps: http://support.microsoft.com/kb/232493/en-us

Signature

Regards,
Wolfgang

Reply to this Message

WildlyHarry - 25 Oct 2007 20:29 GMT

Thanks for the help. However and this is probably just me, I do not see
anywhere to reference my values on the form or how I input the new function
into my existing Pass Through Query.

> Hello "WildlyHarry".
>
[quoted text clipped - 5 lines]
>
> Maybe this helps: http://support.microsoft.com/kb/232493/en-us

Reply to this Message

Wolfgang Kais - 26 Oct 2007 20:17 GMT

Hello "WildlyHarry".

>>> I have a Pass Through query that looks like this.
>>> [...]
>>>
>>> I want to replace the dates and the amounts with variables from a
>>> form ie [form]![form1]![date] etc. How can I do this?

>> Maybe this helps: http://support.microsoft.com/kb/232493/en-us

> Thanks for the help. However and this is probably just me, I do not
> see anywhere to reference my values on the form or how I input the
> new function into my existing Pass Through Query.

How are you going to use the passthrough query?
The mesage of the KB article is that you will have to manipulate the
SQL property of the pass though query before using it.

Fill a string variable with the complete sql string that you want to
send to the server (strSQL) and use dao code before using the query:

With CurrentDb.QueryDefs("YourPassThroughQuery")
.SQL = strSQL
.Close
End With

The strSQL variable should contain exactly the sql string that you
server has to execute, so it can't contain [Forms]![...]. Therefore
your code has to build the sql string using the values from the form.
I suggest to use date values in the ansi form '2007-10-26'.

Signature

Regards,
Wolfgang

Reply to this Message