Solved

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

Posted on 2014-01-13
6
414 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 57
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 57
ID: 39778026
Sorry, should be:

Set qryDef = dbCur.OpenQueryDef("Membership")

Jim.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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

Increase your protection from Zero Day threats!

Running two Antivirus' is never a good idea.
Taking advantage of Multiple Security layers on the other hand can often save your hide.
See which top notch security software brands have been proven to happily coexist together.
Reduce your chances of becoming a statistic.

Question has a verified solution.

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

742 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