Solved

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

Posted on 2014-01-13
6
411 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

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.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

895 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now