Joppa
asked on
For the DoCmd.OpenQuery do you have to have a query saved when using VB?
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("Membe rship").SQ L = 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
strCurrentYear = "[Dues '" & Format(Now(), "yy") & "]"
strSQL = NormalSelect & " FROM Table1 " & _
"WHERE " & strCurrentYear & " <> 0 ORDER BY Last"
CurrentDb.QueryDefs("Membe
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
ASKER
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
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
Sorry, should be:
Set qryDef = dbCur.OpenQueryDef("Member ship")
Jim.
Set qryDef = dbCur.OpenQueryDef("Member
Jim.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jim,
Know the feeling.
That fixed my problem. Thanks for the help.
Ric
Know the feeling.
That fixed my problem. Thanks for the help.
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.