[AccessD] Need Some help

>>a table with the dates would be eaiser to pull from and write to.

A fiscal calendar table is the way to go.

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

Got what I needed from  a dlookup. Syntax is a problem. One comma or quote
can mess up the whole thing. What I'm doing is fixing some code where we
have to know the Month Start Date and Month End Date to run reports. Like
March of this year started in February and ended in April.
Our time is by the week and ends on Fridays. The guy that wrote the original
code had all the dates in the code and each year we had to go change the
code and add the next year. I though a table with the dates would be eaiser
to pull from and write to. I can pull the dates from the accounting system
with code and write to the table each year. I was fixing some other code for
monthly dates and wanted to fix this also.
Hopefully what I got will do the trick now.

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: Monday, May 09, 2011 12:23 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Need Some help

Steve, have you resolved? It's been awhile.

I think your problem is two-fold. You must change the SQL assignment as I
showed you to be syntactically correct... and this line:

	  DoCmd.OpenQuery strSql

Should be 

	  DoCmd.OpenQuery cstrQryName

But ... I am not really sure what you are trying to accomplish, it seems
pretty convoluted even for my taste, and I have an advanced degree in
Convolution.


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

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