SQL query

Chris Thu, 30 Oct 2003 01:47:02 -0800

Laura,

Try this :
<cfquery name="PermitSearch" datasource="CMISPersonnel">

SELECT

    Personnel.PersonnelKey ,
    Personnel.FirstName,
    Personnel.MiddleInitial,
    Personnel.LastName,
    Personnel.Zip,

    Vehicles.PersonnelKey ,
    Vehicles.SpaceNumber,
    Vehicles.TagNumber,
    Vehicles.DecalNumber,
    Vehicles.PermitNumber

FROM

    Personnel

INNER JOIN

    Vehicles

ON

    Personnel.PersonellKey = Vehicles.PersonnelKey

WHERE

    0=0

    
        <cfif form.LastNameSearch is not "">AND Personnel.LastName like  '%#form.LastNameSearch#%'</cfif>
    
        <cfif form.ZipCodeSearch is not "">AND Personnel.Zip = #form.ZipCodeSearch#</cfif>
    
        <cfif form.PermitNbrSearch is not "">AND Vehicles.PermitNumber =  #form.PermitNbrSearch#</cfif>
    
        <cfif form.SpaceNbrSearch is not "">AND Vehicles.SpaceNumber = #form.SpaceNbrSearch#</cfif>
    
        <cfif form.TagSearch is not "">AND Vehicles.TagNumber = #form.TagSearch#</cfif>
    
        <cfif form.DecalSearch is not "">AND Vehicles.DecalNumber =#form.DecalSearch#</cfif>

ORDER BY
LastName
</cfquery>

Just check from which table all variables come as I don't know wether Spcaenumber comes from vehicles or personnel and adapt the code consequently.
Works fine in my test environment.

Best regards,
Chris
  ----- Original Message -----
  From: Tangorre, Michael
  To: CF-Talk
  Sent: Wednesday, October 29, 2003 8:26 PM
  Subject: RE: CF/Access/SQL query

  Also, which fields are coming from which table in your select statement?

  <cfquery name="PermitSearch" datasource="CMISPersonnel">
  SELECT
  FirstName,
  MiddleInitial,
  LastName,
  Zip,
  SpaceNumber,
  TagNumber,
  DecalNumber,
  PermitNumber
  FROM
    Personnel, Vehicles
  WHERE
  Personnel.PersonnelKey = Vehicles.PersonnelKey
  
  <cfif form.LastNameSearch is not "">AND LastName like
  '%#form.LastNameSearch#%'</cfif>
  
  <cfif form.ZipCodeSearch is not "">AND Zip = #form.ZipCodeSearch#</cfif>
  
  <cfif form.PermitNbrSearch is not "">AND PermitNumber =
  #form.PermitNbrSearch#</cfif>
  
  <cfif form.SpaceNbrSearch is not "">AND SpaceNumber =
  #form.SpaceNbrSearch#</cfif>
  
  <cfif form.TagSearch is not "">AND TagNumber = #form.TagSearch#</cfif>
  
  <cfif form.DecalSearch is not "">AND DecalNumber =
  #form.DecalSearch#</cfif>
  ORDER BY
  LastName
  </cfquery>

  -----Original Message-----
  From: Laura Schlee [mailto:[EMAIL PROTECTED]
  Sent: Wednesday, October 29, 2003 2:15 PM
  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
  
    <cfif form.LastNameSearch is not "">
    and LastName like '%#form.LastNameSearch#%'
    </cfif>
    
    <cfif form.ZipCodeSearch is not "">
    and Zip = #form.ZipCodeSearch#
    </cfif>
    
    <cfif form.PermitNbrSearch is not "">
    and PermitNumber = #form.PermitNbrSearch#
    </cfif>
    
    <cfif form.SpaceNbrSearch is not "">
    and SpaceNumber = #form.SpaceNbrSearch#
    </cfif>
    
    <cfif form.TagSearch is not "">
    and TagNumber = #form.TagSearch#
    </cfif>
    
    <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 '.

  I've Googled the error message and all the results
  were from Access users, so I'm going to assume that
  it's some Access weirdness. Any Access experts out
  there? I'm using Access 2000 and CFMX. I've tried
  replacing the periods in 'Personnel.PersonnelKey =
  Vehicles.PersonnelKey' with exclamation points but it
  didn't make a difference. Another suggestion was to
  change one of the field names so that the name
  wouldn't appear twice, i.e., change one of the
  "PersonnelKey" field names in one of the tables, but I
  won't be allowed to make that kind of change to the
  database.

  Any ideas about what I could do?

  Thanks,

  Laura
  ===========================
  Laura Schlee
  DML Associates
  work 202 685 1360
  cell 703 622 0363

  __________________________________
  Do you Yahoo!?
  Exclusive Video Premiere - Britney Spears
  http://launch.yahoo.com/promos/britneyspears/
  <http://launch.yahoo.com/promos/britneyspears/>
    _____

[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

Re: CF/Access/SQL query

Reply via email to