[AccessD] Need Some help

Steve,

A query def simply represents a stored SQL statement.  So you can do this:

 'how to read query for MSD and MED???
  Set rstEnd = db.OpenRecordset(cstrQryName)

  If rstEnd.EOF then
     MSD = Null
     MED = Null
  Else
    MED = rstEnd![MonthStartDate]
    MED = rstEnd![MonthEndDate]
  End If

  rstEnd.Close
  Set rstEnd = Nothing

Or this:

  strSQL = "SELECT * FROM <some table>;"

  Set rstEnd = db.OpenRecordset(strSQL)

  If rstEnd.EOF then
     MSD = Null
     MED = Null
  Else
    MED = rstEnd![MonthStartDate]
    MED = rstEnd![MonthEndDate]
  End If

  rstEnd.Close
  Set rstEnd = Nothing

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Turner
Sent: Monday, May 09, 2011 10:55 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Need Some help

Still returns can't find the object. 

Steve A. Turner
Controller
Mid-South Engineering Co. Inc
E-Mail: sturner at mseco.com and saturner at mseco.com



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William
Benson (VBACreations.Com)
Sent: Friday, May 06, 2011 5:54 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Need Some help


strSql ="Select * from [" & cstrQryName & "]"

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Turner
Sent: Friday, May 06, 2011 6:01 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Need Some help

Hey Guys,

 Need some help here. I've tried everything I can find but I don't
understand some of it. I have a table with project year, project Month
and
the Month Start Date and Month End Date's for a number of years. I am
trying
to read this table MonthlDates for the ProjYear And ProjMonth to get the
Month Start and End Days. I have a query built that pulls this data and
a
query table appears in Access 2010 with the data in it as a one line
record.
How do I read this data into a variable MSD and MED so I can Update
another
table called Variables we use to run reports from.? Code works till I
get to
the how to read query for MSD and MED.

 

Dim rst As Recordset

    Dim strSql As String, strParm As String

    Dim strMessage As String

    Dim intCommand As Integer

    Dim qdf As QueryDef

    Const cstrQryName As String = "Project Month And Year"

   

'sets the project year and month

   

    PY = ProjYear

    PM = projMonth

    

 'Update the variables table

 

    Set rstVariables = db.OpenRecordset("Variables")

    With rstVariables

        .Edit

        !PY = PY 'ProjYear

        !PM = PM 'projMonth

        .Update

    End With

    

'Query MonthlyDates table for Month Start and End

 

     

  Set qdf = db.QueryDefs(cstrQryName)

   With DoCmd

       .SetWarnings False

       .OpenQuery (cstrQryName), , acEdit

       .SetWarnings True

    End With

    

 'how to read query for MSD and MED???

 

  Set rstEnd = db.OpenRecordset(cstrQryName)

   strSql = ("select *,(cstrQryName)") '<--MS ERROR can't find
(cstrQryName)

  DoCmd.OpenQuery strSql

  

 ' want to fill in these variables to update table Variables

   

  MSD = MonthStartDate

  MED = MonthEndDate

  

 

Steve A. Turner
Controller
Mid-South Engineering Co. Inc
P.O. Box 1399
Hot Springs, AR 71902
E-Mail: sturner at mseco.com and saturner at mseco.com
Phone: (501)321-2276
Fax:     (501)321-4750
Cell:     (501)282-7751

 

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

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

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