I have a form that, without going into too much detail, deletes a query and
then creates a new query “TempNewQuery” The SQL is
SELECT Member.*, [MemFirstName] & " " & [MemSurName] AS MemName,
Trophies.TrophyName, TrophyYear.Year, Winners.WinnerYearID,
TrophyTypes.TrophyType, TrophyTypes.TrophyTypeSelected,
Trophies.TrophyTypeID, LstAddress.MemAddLF, Winners.RaceDate,
Winners.SignedOut, Winners.DateOut, Trophies.PresentedFor,
Winners.JointWinner,
DuplicateWinnerNames([Winners].[WinnerYearID],[Winners].[TrophyID],[Member].[MemSurName],[Member].[MemFirstName])
AS WinnerNames, Boat.BoatName FROM TrophyYear INNER JOIN (Boat RIGHT JOIN
(TrophyTypes INNER JOIN (Trophies INNER JOIN ((Member LEFT JOIN LstAddress ON
Member.[MemAddID] = LstAddress.[MemAddID]) INNER JOIN Winners ON
Member.[MemberID] = Winners.[WinnerMemberID]) ON Trophies.[TrophyID] =
Winners.[TrophyID]) ON TrophyTypes.[TrophyTypeID] = Trophies.[TrophyTypeID])
ON Boat.BoatID = Winners.WinnerBoatID) ON TrophyYear.YearID =
Winners.WinnerYearID WHERE
(((TrophyYear.Year)>=IIf(Forms.ReportSelector.From Year=0,[Year],forms.ReportSelector.FromYear)
And
(TrophyYear.Year)<=IIf(Forms.ReportSelector.ToYear =0,[Year],forms.ReportSelector.ToYear))
And ((TrophyTypes.TrophyTypeSelected)=True)) ;

The form “ReportSelector is open.

If I run the query, all is OK

If I run it in code
Private Sub CheckQuery_Click()
Dim MyDb As Database
Dim SampleSet As Recordset
Dim Msg As String

On Error GoTo CheckQuery_Err
NoRecords = Null

Set MyDb = CurrentDb

Set Set SampleSet = MyDb.OpenRecordset("TempNewQuery") 'New QueryDef just
created

….
I get error 3061 Application-defined or object-defined error.

MyDb.QueryDefs("TempNewQuery").OpenRecordset gives the same error

Any bright ideas
Thanks
Phil

On Mon, 9 Apr 2012 15:11:02 +0100, "Phil" <phil (AT) stantonfamily (DOT) co.uk>
wrote:

Why delete and recreate the query? Create the query without the Where
clause.

Then pass the where clause as part of a string to the Recordset
statement.

Air Code

strSQL = "SELECT * from [query name] WHERE WHERE
(((TrophyYear.Year)>=" &
IIf(Forms.ReportSelector.FromYear=0,[Year],forms.ReportSelector.FromYear)
& " And (TrophyYear.Year)<=" &
IIf(Forms.ReportSelector.ToYear=0,[Year],forms.ReportSelector.ToYear))
& " And ((TrophyTypes.TrophyTypeSelected)=True)) ;"

Assuming that IIF works in VBA code. I can't recall if it does or
not.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

On 12/04/2012 21:32:32, Tony Toews wrote:
Ah... it appears that I have to go into some detail.
I have a form that lists every form, query and report in the database. From
any of these, I can create a new query and add additional parameters. I
delete the old TempNewQuery whatever that might be from the last time I
selected an item, then create a new TempNewQuery based on the selected form/
Report/Query and the additional parameters. This subsequently gets used for
Emails or mail merge

Phil

On Apr 13, 9:45*am, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
Not much help to your question. I sometimes simply exchange/update
the SQL statement. Ex:

Sub SetQSql()
Dim d As Database
Dim q As QueryDef

Set d = CurrentDb
Set q = d.QueryDefs("Query1")
q.SQL = "Select * from Query2" 'change query1's SQL

d.Close

End Sub

Are you stepping thru your code?

I sometimes will, in debug mode, get the SQL I just updated and do a
debug.print strSQL
and then grab that SQL and create a new query, go into SQL view, and
paste the SQL from the debug into it and then see if it runs.

On 13/04/2012 20:01:14, Patrick Finucane wrote:

Wish life were that simple. Query1 in your example almost certainly will
contain WHERE clauses and ORDER BY and possibly HAVING etc.

I have a form which allows the user to basically add additional WHERES. There
is a combo box that you pick for the different types of WHERE condition like
=, >=, like, between etc and then the fields in the query are listed . So for
example if you had a query based on Members and there was already something
like "WHERE Age < 40 ORDER BY Surname", useing this form you could add
something like Surname like "S*" and EMail > "".
Obviously pretty complex to regenerate the SQL statement to read
WHERE Age < 40 AND Surname like "S*" AND EMail > "" ORDER BY Surname
Anyway that all works. That is the new TempNewQuery that I create for
subsequent use in Emails or Mail merge

Going back to the original posting this is the bit that fails

WHERE
(((TrophyYear.Year)>=IIf(Forms.ReportSelector.From Year=0,[Year],forms.ReportSelector.FromYear)
AND
(TrophyYear.Year)<=IIf(Forms.ReportSelector.ToYear =0,[Year],forms.ReportSelector.ToYear))
AND ((TrophyTypes.TrophyTypeSelected)=True)) ;

If I paste the SQL from the Debug Window into a new query it works fine, It's
just using VBA that I get error 3061 Application-defined or object-defined
error.

As I said, I have got over the problem by replacing the above with

WHERE (((TrophyTypes.TrophyTypeSelected)=True) AND
((TrophyYear.Year)>=GetFromYear() And (TrophyYear.Year)<=GetToYear()))

and used a function and similar Function GetToYear() As Long

Function GetFromYear() As Long

If Not IsLoaded("ReportSelector") Then
GetFromYear = Year(Date)
Else
If Forms!ReportSelector!FromYear = 0 Then
GetFromYear = Year(Date)
Else
GetFromYear = Forms!ReportSelector!FromYear
End If
End If

End Function

Phil

On Apr 14, 3:51*am, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
I was simply providing an example of modifiying any SQL statement.
The SQL statement can contain orderby's, groupby;s, having's, where's,
or any valid SQL statement. No need to kill and recreate. Simply
stuff the new SQL statement string into the query to modify it.

Heck, I'd see if putting #'s around your date would work.

On Sat, 14 Apr 2012 09:51:56 +0100, "Phil" <phil (AT) stantonfamily (DOT) co.uk>
wrote:

Whereas very few of my queries contain Where clauses other than
Inactive = false or similar. I will add the WHERE clauses in VBA
code or filters.

Right. You got bit by the fact that executing a query via VBA code
doesn't parse out the Forms!<formname>!<FieldName> separately whereas
running those querys in the query, form or reports works just fine.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

On 15/04/2012 03:20:52, Tony Toews wrote:
Tony, I'll eat my hat. I would have sworn that I had done it many times
before, but on checking I hadn't

Live & learn

Looks as if I am going to have to review a hell of a lot of queries if I am
going to use them as a source of VBA code.

Many thanks

Phil

On Sun, 15 Apr 2012 09:26:36 +0100, "Phil" <phil (AT) stantonfamily (DOT) co.uk>
wrote:

Looking forward to the Youtube URL later today. <smile>

You're welcome. I'm glad I posted the explanation of the problem.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/