Pass Through query with criteria on a form

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.
0
Utf
10/25/2007 6:46:01 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1656 Views

Similar Articles

[PageSpeed] 54

Hello "WildlyHarry".

"WildlyHarry" wrote:
>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

-- 
Regards,
Wolfgang 


0
Wolfgang
10/25/2007 7:19:35 PM
Hello "WildlyHarry".

"WildlyHarry" wrote:

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

-- 
Regards,
Wolfgang 


0
Wolfgang
10/26/2007 7:17:07 PM
Reply:

Similar Artilces:

Sql Query for Single & parent item Quantity
Hi All I would like to know if there Sql Query to convert the quantities of Parent item to be Single item , i would explain it with an example single item Itemlookupcode is 123456 and Qty 1 Parent item (outer pack of single item) like 6 cans of cola drink forms a pack which can be sold as single as well as pack item Itemlookupcode 123456-06 Qty 2 I need an sql query to produce result like 123456 QTY = 13 and 123456-06 Qty =0. Formula = Qty of Single item + Number of pieces in a single pack * Qty of parent item it would be great help that if it would be possible in sql Thanks ...

Is It Possible To Pass Parameters to A Pass Through Query
My assumption is that's it's not. At work I use ODBC to connect to our oracle database with Access 2003. There are cases where using a pass through query runs much much faster and I then use it in a make table query to make a local table. In access you can use brackets [] to have it ask for input. May I assume there is no way to do anything like that in a pass through query? Create a PassThrough query and use code to assign the SQL to this query filtering it with a parameter, and then run it e.g Dim MyVariable As Integer MyVariable = InputBox("Please select a Number&quo...

Data Validation Query:
Hi Guys, First Post. I need help with the following: I have a row of cells that is custom formatted as H:MM 1) Users enter their overtime in hours and minutes (H:MM) What I am trying to do is have data validation that they can only put their overtime H:MM 2) I try and validate their input by going to DATA | VALIDATION | ALLOW | CUSTOM | and custom = H:MM For some reason this will not work. Does anybody know how to get this working? Any Help would be much appreciated. Rgds, Bw --- Message posted from http://www.ExcelForum.com/ You can choose "Time" from the Allow list...

Count Query
Hi, I'm basing a query on a table,I need ,in one of the fileds of the query to count the filed Q2 if it's >=7 ,grouped by Caller ID and Language.. Here's the SQL i'm using that produces an error: SELECT Csat.Language, Csat.CallerId, Count(Csat.Q2Ans) AS CountOfQ2Ans FROM Csat GROUP BY Csat.Language, Csat.CallerId HAVING (((Count(Csat.Q2Ans))>7)); I don't get an error message,but the numbers counted are not corect if compared to the table from which it got the data,I don't know why.. I want to count How many times a certain Caller ID got more than 7... Than...

Web Query Help...
If I try and use a web query with the following site http://www.sportsline.com/mlb/stats/playersort/regularseason/yearly/MLB/P I get a message that says the web query returned no data. I click on the arrow right by player and it highlights all the players names and their stats. Anyone know why it doesn't import the data into excel? Is there too much data? Thanks Works fine for me, although one have to do some editing since it imports some trash as well (I would import it, then select the table itself from the import and copy and paste into a new sheet, then import the next 50 and...

Calander in Forms Mode
Hi everyone! I'm hoping someone can help me...I am trying to put together a spreadsheet someone will be entering data into. I would like this to be as easy as possible. Does any one know if in forms mode (from the Data menu) I can have a calender pop up for easy date entry? Any help will do. Thanks so much. -- Jules Jules Not from the Data>Form. See Ron de Bruin's site for adding a pop-up calendar. http://www.rondebruin.nl/calendar.htm Gord Dibben MS Excel MVP On Tue, 15 May 2007 11:06:03 -0700, Jules <Jules@discussions.microsoft.com> wrote: >Hi everyone! ...

Query #2
Is there any query for I can check on item options ACCEPT FOOD STAMP FOR THIS ITEM I need to ad this option on all the SODA & GROCERY department. -- Thank You Harjit Singh SherGill /DBA Altaville Market 324 South Main st po box 370 Altaville CA 95221 Tel : 209-736-1677 Fax; 209-498-3660 Altavillemarket1@att.net Hi Gill Backup the database first and if you can, run these after hours. First jot down the department ID values by connecting to your db and running SELECT * FROM DEPARTMENT -- jot down the ID values for soda and groceries Then check the items you are about to upda...

query to overcome field type problem?
I have a contacts table... let's say 2 fields- ID and email. I have a groups table... it has a number of fields that are linked back to the contacts field already (contact 1 thru nn), so that the groups table just has a contact id number and I look up their email from the other table. One field is also the "primary contact" (similarly linked)... for the person who "owns" that group. Works nicely so far, (thanks, techrat!). Until I try to send email to that primary contact to have them verify the details.... Their email id doesn't show up in the wiz...

Setting a value on a main form from a subform
All, Am I correct in assuming the way to set the value of a control on the main form from a subform is: Me.Parent.ControlName.Value = "XXX" Obviously it isn't because I continue to receive errors when I try to access the control. Thanks in advance. - CES CES wrote: > All, > Am I correct in assuming the way to set the value of a control on the > main form from a subform is: > > Me.Parent.ControlName.Value = "XXX" > > Obviously it isn't because I continue to receive errors when I try to > access the control. > > Thanks in advance...

How to pass an object from C++
Hello! We have a C# asp.net web application that is using a COM dll We use the tlbimp to be able to use the COM dll from C# asp.net web application The method InitRules shown below is located in the COM dll. It can be seen below the text Original. Method InitRules below is called from C# but we want to pass an object of type Handle_DS in addition to all the other parameters. You can see the modified InitRules below marked Modified Note the object of type Handle_DS is a C++ object that is created from C# asp.net application and pass into the InitRules I have also copied the whole idl fil...

Adding filter to query
I'm trying to export to excel the filtered results of an underlying query on a split form. In the on click event of a command button I create a querydef and then use transfer spreadsheet to create an Excel workbook. The code creates the spreadsheet, but it includes all the records. I need to make it create only the filtered records. I've tried several methods, but can't seem to figure this out. Any help will be greatly appreciated. TIA, Ken I just answered a question just like this yesterday. I got a green check for it! Maybe I will get another today... ...

MS Query
I have the following MS Query inside my spreadsheet: SELECT `HS_Incident$`.`Plant Name`, `HS_Incident$`.`Incident Desc`, Format(`HS_Incident$`.`Incident Date`,'dd-mmm-yyyy'), Format(`HS_Incident$`.`Report Date`,'dd-mmm-yyyy'), `HS_Incident$`.`Lost Time Case`, `HS_Incident$`.`Incident Status` FROM `C:\Reports\HS_Incident`.`HS_Incident$` `HS_Incident$` WHERE (`HS_Incident$`.`Plant Name`<>'MARKHAM ') AND (`HS_Incident$`.`Incident Status` Not Like 'SUBM%' And `HS_Incident$`.`Incident Status` Not Like 'SAVE%' And `HS_Incident$`.`Incident Status`...

Form to query
Hello everybody, I have a question concerning the "Enter Parameter Value" window that opens when I run a query. I have asked this in the access.queries newsgroup but I couldn't get the answer. Is it possible to have that as a combobox so that a list of possibilities is shown? Another question has to do with the same query. This asks a date from and to (in the query criteria >[from] And [to]. I would like the selected period to be shown in the report. Is this possible (and if so... how can I achieve that)? Thanks for all the help you can give -- Lisa Save the Dogs Onlus...

How do I set the print range on a form. Regards
I've created a Timesheet on a form in Excel, but when I print, it only prints a third of the form Enter this into Excel help: Define or clear a print area on a worksheet This should give you a solution "Dave K" wrote: > I've created a Timesheet on a form in Excel, but when I print, it only prints > a third of the form In Excel 200, you would select the are to print, go to "File", "Print area", "Set print area". To make it easier and quicker to print in future, you can record a macro as you print the print area and att...

Keypad/Number Pad forms?
Anyone aware of "canned" popup keypads and/or number pads? Something I can import to a access 2003 DB? Thanks in advance D How's this Dave? An ActiveX Control here: http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=65113&lngWId=1 Another sample here: http://www.utteraccess.com/forums/showflat.php?Cat=&Board=83&Number=1033080 MVP Roger Carlson's sample here: http://www.rogersaccesslibrary.com/download3.asp?SampleName=NumberPad.mdb Sample file here: http://www.askdoctoraccess.com:80/DownloadPage.htm -- Jeff Conrad - Access Junkie - MVP ...

Need help with changing query
First of all, thanks for reading this. Here's my problem. I have 1 field called [sex] and another called [race]. At one time the word sex was a text field Male or Female. I've changed the field properties to either yes/no true false. Yes = Female and No = Male. The code in the sql/query below is using the words Male or Female {list5}. The [race] seems to be ok. I need to change the sex part to either true or false even though on the screen it will show Male/Female SELECT qryEmpMaster.Lname, qryEmpMaster.Fname, qryEmpMaster.Sex, qryEmpMaster. Race FROM qryEmpMaster WHER...

Pass multiple selected values from list box to table
How do I pass multiple selected values from a list box to a table? I've found code to use the values as criteria in a query but would now like to store the values in a table (at least temporarily) thanks in advance. jack Pass selected values from a list box to a table? I don't really think you want to do this. Can you describe more about what you are trying to accomplish? You just store raw data in a table. You don't store manipulated data in a table. Maybe you need a query to do what you want to do... -- Ryan--- If this information was helpful, please indi...

Date query "Excel VBA"
Is there anyway i can use vba code to create a user input box on a sale worksheet that will filter the sales orders and only display the order that equal the date entered into the input box by the end user. I am already using the autofilter feature but would like to take it on step further and make it easier for the admin clerk. Thank-Yo -- Message posted from http://www.ExcelForum.com You didn't like yesterday's suggestion???? http://groups.google.com/groups?threadm=4110273A.B3C20CDD%40msn.com "avilla <" wrote: > > Is there anyway i can use vba code to cr...

100% Passing Guaranteed in All I.T. Exams/Certifications at 1st Attempt Hi Friends, To pass all types of I.T Exams/Certifications with mind blowing results in 1st attempt just visit http://www.itreal
100% Passing Guaranteed in All I.T. Exams/Certifications at 1st Attempt Hi Friends, To pass all types of I.T Exams/Certifications with mind blowing results in 1st attempt just visit http://www.itrealexams.com/ and get real time Q&As, Brain Dumps, Real Exams, Study material, E-Books, Video Exams and Labs for your all type of I.T Certification Exams. ...

date diff expression in query
I am trying to calculate the difference between two dates 'End Date' and 'Date of Visit' using a query. This is the expression I used: TimeFrame:[Date of Visit] - [End Date]. However, 'End Date' is present in both tables I have used to create this query 'tbl_NewInfo' and 'tbl_Foloowup'. I want the 'End Date' from 'tbl_NewInfo'. How do i specify this in the expression and thereby prevent the error msg I am getting? Thankyou You must fully qualify the field name like: [Table Name].[Field Name] I use a naming convention that resu...

Making form using Excell Help ASAP!
I've made a form. This needs to be filled out online. I can freeze cells so the user cannot mess with them, but how do I form lines.? Say, a line above 'Customer Name' in order to answer that question. This is really getting to me b/c it seems like it should be so easy... Hi Amanda not really sure what you're after but if you click in a cell and choose format / cells and click on the border tab and then the top line icon does this give you what you want? Cheers JulieD "~Amanda~" <~Amanda~@discussions.microsoft.com> wrote in message news:26819D...

Help with Advanced Filter criteria for customized email inbox view
Using MSOutlook2003 I customized my Outlook Inbox view with an advanced filter. The criteria were Field Condition Value -------- ----------- ------ Due By does not exist Due By on or before Today() The result was that I see all of my email messages that either don't have a reminder set (flag and due date) or have a reminder set for any time after 12:00:00 AM of the current day. This worked very well. Everything that is due on a future day was hidden from view so I didn't have to look at it. I then decided that I also wan...

Form feeding a table problem
I am a newer user of Access and inherited a database from a person no longer with the company. I am using a form to input invoice amounts into an invoice table. The form uses a parameter query to ask for invoice number. When the form opens, I have it pulling customer info from a customer table, which it displays on the form. I then enter more invoice info. When I look at the invoice table after I close the form, the info I typed (invoice update) is there, but the customer info that is automatically pulled doesn't load from the customer table to the invoice table. How can I g...

Help Query.
Hi, I have 2 tables, one it has customers who are past due and another table that has customers who have paid and not past due after a certain period. My objective is to find the customers who hasn't paid yet. I did a query, joined by account #, this query will return customers who has paid after they were past due. But I am looking for account IDs of customers who are still past due. Anyway to do this? Thansk for the help! Try the query wizard about finding unmatched rows. It seems you need rows from the first table that are NOT in the second table. Hoping it may help, Vander...

Update query help
Thanks to Jon I have almost solved my problem. When I run the following cade a box pops up that says"yourtable.Datefield" to which I have to enter a date. I would like the code to run autonatically when the database is opened based on the current system time.I would also like to change waiting to pending UPDATE yourtable SET yourtable.Status = "Cleared" WHERE (((yourtable.datefield)<Date()-10) AND ((yourtable.Status)="Waiting")); This should run and update the status of any records that have a date over 10 days old and a status of waiting. TIA yourtable....