Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-01-13
6
Medium Priority
?
420 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
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…

636 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