Thanks to everyone who helped out with my issue. After
incorporating practically all of the suggested changes
(except for the person whose email I accidentally
deleted before I had a chance to try your changes --
my apologies, since you were nice enough to test your
solution for me) my query is now running. Here's the
edited code:
<!--- Initialize variables --->
<CFPARAM NAME="FORM.LastName" DEFAULT="0">
<CFPARAM NAME="FORM.Zip" DEFAULT="0">
<CFPARAM NAME="FORM.SpaceNumber" DEFAULT="0">
<CFPARAM NAME="FORM.TagNumber" DEFAULT="0">
<CFPARAM NAME="FORM.DecalNumber" DEFAULT="0">
<CFPARAM NAME="FORM.PermitNumber" DEFAULT="0">
<!--- Get list of permits from database--->
<cfquery name="PermitSearch"
datasource="CMISPersonnel">
Select Personnel.FirstName, Personnel.MiddleInitial,
Personnel.LastName, Personnel.Zip,
Personnel.SpaceNumber, Vehicles.TagNumber,
Vehicles.DecalNumber, Vehicles.PermitNumber
from Personnel, Vehicles
where Personnel.PersonnelKey = Vehicles.PersonnelKey
<!--- Search by last name --->
<cfif form.LastName is not "">
and LastName like '%#form.LastName#%'
</cfif>
<!--- Search by zip code --->
<cfif form.Zip is not "">
and Zip = '#form.Zip#'
</cfif>
<!--- Search by permit number --->
<cfif form.PermitNumber is not "">
and PermitNumber = '#form.PermitNumber#'
</cfif>
<!--- Search by assigned parking space number --->
<cfif form.SpaceNumber is not "">
and SpaceNumber = '#form.SpaceNumber#'
</cfif>
<!--- Search by car license plate --->
<cfif form.TagNumber is not "">
and TagNumber = '#form.TagNumber#'
</cfif>
<!--- Search by decal number --->
<cfif form.DecalNumber is not "">
and DODDecalNumber = '#form.DODDecalNumber#'
</cfif>
order by LastName
</cfquery>
>
> -----Original Message-----
> From: Laura Schlee [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October 29, 2003 11:15 AM
> To: CF-Talk
> Subject: CF/Access/SQL query
>
>
> Hi all,
>
> I'm having a problem with a query and I think it
> might
> be Access-related. Here's the text:
>
> <cfquery name="PermitSearch"
> datasource="CMISPersonnel">
> Select FirstName, MiddleInitial, LastName, Zip,
> SpaceNumber, TagNumber, DecalNumber, PermitNumber
> from Personnel, Vehicles
> where Personnel.PersonnelKey = Vehicles.PersonnelKey
> <!-- Search by last name -->
> <cfif form.LastNameSearch is not "">
> and LastName like '%#form.LastNameSearch#%'
> </cfif>
> <!-- Search by zip code -->
> <cfif form.ZipCodeSearch is not "">
> and Zip = #form.ZipCodeSearch#
> </cfif>
> <!-- Search by permit number -->
> <cfif form.PermitNbrSearch is not "">
> and PermitNumber = #form.PermitNbrSearch#
> </cfif>
> <!-- Search by assigned parking space number -->
> <cfif form.SpaceNbrSearch is not "">
> and SpaceNumber = #form.SpaceNbrSearch#
> </cfif>
> <!-- Search by car license plate -->
> <cfif form.TagSearch is not "">
> and TagNumber = #form.TagSearch#
> </cfif>
> <!-- Search by decal number -->
> <cfif form.DecalSearch is not "">
> and DecalNumber = #form.DecalSearch#
> </cfif>
> order by LastName
> </cfquery>
>
> Here's the error message:
>
> Invalid use of '.', '!', or '()'. in query
> _expression_
> 'Personnel.PersonnelKey = Vehicles.PersonnelKey '.
__________________________________
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
http://launch.yahoo.com/promos/britneyspears/
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
