[AccessD] Feed variable to parameter query

If your variable is text, use Doris's solution (the single quotes are
important), for numbers mine should work.
Jim Hale 

-----Original Message-----
From: Hale, Jim [mailto:Jim.Hale at fleetpride.com]
Sent: Thursday, July 28, 2005 8:33 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Feed variable to parameter query


try

"WHERE [tbl Schedules].Pattern=" & PatternName & ";"
Regards,
Jim Hale

-----Original Message-----
From: Kaup, Chester [mailto:Chester_Kaup at kindermorgan.com]
Sent: Thursday, July 28, 2005 8:25 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Feed variable to parameter query


I am trying to feed a variable into a parameter query. I have done this
with dates but cannot get the syntax right (correct combination of
single, double quotes etc.) on pushing this text field into the query.
If someone has a better way to push a variable into a parameter query
feel free to jump in. Thanks all. Below is the code.

 

Option Compare Database

 

Function Table_Data_to_Parameter_Query()

 

    Dim MyDb As Database, MyQDef As QueryDef, myds As Recordset, myds1
As Recordset

    Dim strSQL As String, Table_Data As String

    

    Set MyDb = CurrentDb()

    Set myds = MyDb.OpenRecordset("tbl Patterns to Run", dbOpenTable)

    Set MyQDef2 = MyDb.QueryDefs("qry Pattern Start Date")

    

    PatternName = myds.Fields(0)

 

    strSQL = "SELECT Pattern, [CO2 Injection Start Date Schedule1]" & _

    "FROM [tbl Schedules]" & _

    "WHERE [tbl Schedules].Pattern=PatternName;"

 

    Set MyQDef = MyDb.QueryDefs("qry Pattern Start Date")

    MyQDef.SQL = strSQL

    Set myds1 = MyQDef.OpenRecordset()

    Table_Data = myds1.Fields(0)

    

    Set MyQDef = Nothing

 

 

Chester Kaup

Engineering Technician

Kinder Morgan CO2 Company, LLP

Office (432) 688-3797

 

No trees were killed in the sending of this message. However a large
number of electrons were terribly inconvenienced.

FAX (432) 688-3799

 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.