ACCESS FEED: RE: [MS_AccessPros] help with query structure

Minggu, 25 Agustus 2013

RE: [MS_AccessPros] help with query structure

Rod-

SQL is very picky about the sequence of the clauses. WHERE must appear
before ORDER BY. One solution would be to find the ORDER BY using InStr,
strip it off the end, add your WHERE clause, then put the ORDER BY back.

You don't say why you're wanting to do this. If the query is a Record
Source for a Form or Report, you can have Access apply the filter implied by
the WHERE clause for you. You can do that by using the WhereCondition
parameter of an OpenForm or OpenReport command. Supply your WHERE clause in
that parameter - without the WHERE keyword.

Like this:

DoCmd.OpenForm "MyForm", _
WhereCondition:="(((Qry_Auditor2Project.strResearchDate)>#4/1/2013# " & _
"And (Qry_Auditor2Project.strResearchDate)<#8/24/2013#) AND " & _
"((Qry_Auditor2Project.lngUserID)=4) AND
((Qry_Auditor2Project.lngProjectTypeID)=1))"

The form will open showing only the records allowed by the filter.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of rodbevill
Sent: Saturday, August 24, 2013 10:57 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] help with query structure

Good Afternoon All,
I am trying to add a where clause to an named query. The initial query is :

SELECT [tblDBUsers]![strLastName] & ", " & [tblDBUsers]![strFirstName]
ASAuditor, " _
& "tblProjectType.strProjectType, Qry_Auditor2Project.lngTPTID,
Qry_Auditor2Taxpayer.strTaxPayerName, " _
& "Qry_Auditor2Taxpayer.strTPT,
Qry_Auditor2Project.strResearchDate, Qry_Auditor2Project.lngUserID,
Qry_Auditor2Project.lngProjectTypeID" _
& " FROM ((Qry_Auditor2Project INNER JOIN Qry_Auditor2Taxpayer
ON Qry_Auditor2Project.lngTPTID = Qry_Auditor2Taxpayer.lngTPTID) " _
& " INNER JOIN tblDBUsers ON Qry_Auditor2Project.lngUserID =
tblDBUsers.lngUserID) INNER JOIN tblProjectType ON
Qry_Auditor2Project.lngProjectTypeID = tblProjectType.lngProjectTypeID " _
& " ORDER BY [tblDBUsers]![strLastName] & ", " &
[tblDBUsers]![strFirstName]; "

Want to add the following WHERE clause:

& " WHERE (((Qry_Auditor2Project.strResearchDate)>#4/1/2013# And
(Qry_Auditor2Project.strResearchDate)<#8/24/2013#) AND
((Qry_Auditor2Project.lngUserID)=4) AND
((Qry_Auditor2Project.lngProjectTypeID)=1)); "

when the ";" is deleted from the original sql then the WHERE clause is
appended an error message is generated. Error 3075 missing operator.
Everything seems to work without the WHERE clause except I don't get the
filtered results. This filtered query is being used to export data to
EXCEL.

Thank for any help
Rod

------------------------------------

Yahoo! Groups Links