?
Solved

For the DoCmd.OpenQuery do you have to have a query saved when using VB?

Posted on 2014-01-13
6
Medium Priority
?
416 Views
Last Modified: 2014-01-14
I have a query in Access VB as follows:

            strCurrentYear = "[Dues '" & Format(Now(), "yy") & "]"
           
            strSQL = NormalSelect & " FROM Table1 " & _
                     "WHERE " & strCurrentYear & " <> 0 ORDER BY Last"
            CurrentDb.QueryDefs("Membership").SQL = strSQL
            DoCmd.OpenQuery "Membership"

It worked fine until 2014 hit.  Then it still displayed last year's information.  I tracked it down to the query that was saved as "Membership".  In there was still the "Dues '13 <>0".  Once I deleted that from the saved query the output was correct.

Seems as though I shouldn't have to do that.  Is there a way to do this such that the advancing years will not have an impact?

For those that are wondering, NormalSelect is defined as "SELECT [First], [Last], Street, City, State, Zip, [Phone #] " just so I don't have to keep typing it over all the time.

Thanks,
Ric
0
Comment
Question by:Joppa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 58
ID: 39777826
Ric,

  Use a querydef object:

  Dim dbCur as DAO.Database
  Dim qryDef as DAO.QueryDef

 Set dbCur = CurrentDB()  
 strCurrentYear = "[Dues '" & Format(Now(), "yy") & "]"
 strSQL = NormalSelect & " FROM Table1 " & _
                     "WHERE " & strCurrentYear & " <> 0 ORDER BY Last"
 Set qryDef = OpenQueryDef("Membership")
 qryDef.Properties("SQL") = strSQL
 qryDef.Close          
 set qrydef = nothing
 set dbCur = nothing

 DoCmd.OpenQuery "Membership"

Jim.
0
 

Author Comment

by:Joppa
ID: 39777898
Jim,
  I get a "Sub or Function not defined" error when I go to compile for OpenQueryDef.  I looked around and found that it may be the DAO library was not selected but it was.  Any other ideas on what the problem is?

Thanks,
Ric
0
 
LVL 58
ID: 39778026
Sorry, should be:

Set qryDef = dbCur.OpenQueryDef("Membership")

Jim.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Joppa
ID: 39778247
Jim,

  Can't seem to get ahead of the curve here.  Now I get "Function or Interface Marked as Restricted, or the Function Uses an Automation Type Not Supported in Visual Basic"  I tried to find information on this and it said I have to provide a path to Dao350.dll but I don't have that dll.  I have a reference to DAO 3.6 selected.

Thanks for the help in advance.

Ric
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 39779050
My apologies; old habits die so hard.

You want:

Set qryDef = dbCur.QueryDefs("Membership")

OpenQueryDef() is the old call (but then I'm a old programmer as well<g>).

Jim.
0
 

Author Comment

by:Joppa
ID: 39779171
Jim,


  Know the feeling.

  That fixed my problem.  Thanks for the help.

Ric
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question