Query too complex - Is access query builder messing up the sql?

Question

I am having trouble with a query I have been building in query builder.

I thought query builder was the solution because my search formula has a lot of options to chose from to do the search.

I have been searching to find others with a similar problem and has discovered that Access 2013 is more touchy when it comes to reserved words (so I have been through my entire database and removed any words that could be problematic) and that query builder should be able to handle 255 columns of data - my query is only 75 columns long when the problem shows. And I need the query to have at least 2 columns more for the query to be complete.

The query is an append query which gather my data in a single table before I export it to excel.

The query builder's SQL is quite long and I think maybe too complex for its own good

Here are the code from query builders SQL view:

INSERT INTO
   tblResultsGeneral ( ReGRecordNumber, ReGUVNumber, ReGDate, RefNoInformer, ReGFullName, ReGOfficielObserver, ReGCountry, ReGProvince, ReGMuncipality, ReGNearestTown, ReGAreaName, ReGPathNumber, ReGSCALP, ReGValidateYes, ReGFieldTripYes, ReGScatYes, ReGPreyYes, ReGOtherDNAYes, ReGSightingYes, ReGTrackYes, ReGHowlYes, ReGUrinBloodYes, ReGCameraYes, ReGDenYes, ReGInsideWolfZone, ReGSampleType, ReGSampleEvidence, ReGDogPresent, ReGGELat, ReGGELong, ReGCoordinates, RefNoCoorSys, ReGUTMQuadrant, ReGUTMCoorX, ReGUTMCoorY, ReGPrecision, RefNoValidate, ReGSenckLabID, ReGSenckType_mtDNA, ReGSenckHaploType, ReGSenckInfo_mtDNA, ReGSenckType_NucleusDNA, ReGSenckInfo_NucleusDNA, ReGNumberOfAnimalsCam, ReGNumberOfAnimalsSight, ReGFullNameWriterCam, ReGFullNameWriterSight, ReGFullNameWriterHowl, ReGFullNameWriterOther, ReGFullNameWriterPrey, ReGFullNameWriterScat, ReGFullNameWriterTrack, ReGFullNameWriterUrinBlood, ReGFullNameWriterDen, RefNoValidateSpecies, ReGValidateGenus, ReGValidateSpecies, ReGValidateDanishName, ReGPackName, ReGIndividual, ReGIndiSex, ReGIndiBornYear, ReGIndiBornPlace, ReGIndiDead, ReGIndiDeadCause, ReGProbIndiGuess, ReGIndiAgeAtSampleTime, ReGTerritoryName, ReGMonitoringYear, ReGPhotoNotes, ReGPermissionToUsePhotoText, ReGPhotoFiles ) 
   SELECT
      tblRecord.RecRecordNumber,
      tblUVNumber.UVNo,
      tblRecord.RecDate,
      tblRecord.RefNoInformer,
      tblPersons.PerFullName,
      tblPersons.PerOfficielObserver,
      tblLocation.LocCountry,
      tblLocation.LocProvince,
      tblLocation.LocMuncipality,
      tblLocation.LocNearestTown,
      tblLocation.LocAreaName,
      tblFieldTrip.FiTPathNumber,
      tblValidate.ValSCALP,
      tblRecord.RecValidateYes,
      tblRecord.RecFieldTripYes,
      tblRecord.RecScatYes,
      tblRecord.RecPreyYes,
      tblRecord.RecOtherDNAYes,
      tblRecord.RecSightingYes,
      tblRecord.RecTrackYes,
      tblRecord.RecHowlYes,
      tblRecord.RecUrinBloodYes,
      tblRecord.RecCameraYes,
      tblRecord.RecDenYes,
      tblValidate.ValInsideWolfZone,
      tblRecord.RecSampleType,
      tblRecord.RecSampleEvidence,
      tblRecord.RecDogPresent,
      tblRecord.RecGELat,
      tblRecord.RecGELong,
      tblRecord.RecCoordinates,
      tblRecord.RefNoCoorSys,
      tblRecord.RecUTMQuadrant,
      tblRecord.RecUTMCoorX,
      tblRecord.RecUTMCoorY,
      tblLocPrecision.LocationPrecision,
      tblRecord.RefNoValidate,
      tblValidate.ValSenckLabID,
      tblValidate.ValSenckType_mtDNA,
      tblValidate.ValSenckHaploType,
      tblValidate.ValSenckInfo_mtDNA,
      tblValidate.ValSenckType_KernDNA,
      tblValidate.ValSenckInfo_KernDNA,
      tblAnimal_1.AniNumberOfAnimals,
      tblAnimal.AniNumberOfAnimals,
      tblPersons_1.PerFullName,
      tblPersons_2.PerFullName,
      tblPersons_3.PerFullName,
      tblPersons_4.PerFullName,
      tblPersons_5.PerFullName,
      tblPersons_6.PerFullName,
      tblPersons_7.PerFullName,
      tblPersons_8.PerFullName,
      tblPersons_9.PerFullName,
      tblValidate.RefNoValSpecies,
      tblValidateSpecies.ValSpeGenus,
      tblValidateSpecies.ValSpeSpecies,
      tblValidateSpecies.ValSpeDanishName,
      tblPack.PackName,
      tblIndividual.Individual,
      tblIndividual.IndiSex,
      tblIndividual.IndiBornYear,
      tblIndividual.IndiBornPlace,
      tblIndividual.IndiDead,
      tblIndividual.IndiDeadCause,
      tblValidate.RefNoProbIndiGuess,
      tblValidate.ValIndiAgeAtSampleTime,
      tblTerritoryName.TerritoryName,
      tblMonitoringYear.MonYear,
      tblPhotoDoc.PhDPhotoNotes,
      tblPhotoDoc.PhDPermissionToUsePhotoText,
      tblPhotoDoc.PhDPhotoFiles 
   FROM
      (
         tblDen 
         INNER JOIN
            (
               tblIndividual 
               INNER JOIN
                  (
(tblValidateSpecies 
                     INNER JOIN
                        (
                           tblPack 
                           INNER JOIN
                              tblValidate 
                              ON tblPack.KeyPack = tblValidate.RefNoPack
                        )
                        ON tblValidateSpecies.KeyValidateSpecies = tblValidate.RefNoValSpecies) 
                     INNER JOIN
                        (
                           tblUVNumber 
                           INNER JOIN
                              (
(tblPersons AS tblPersons_8 
                                 INNER JOIN
                                    tblUrinBlood 
                                    ON tblPersons_8.KeyPersons = tblUrinBlood.RefNoWriterUrinBlood) 
                                 INNER JOIN
                                    (
(tblPersons AS tblPersons_7 
                                       INNER JOIN
                                          tblTrack 
                                          ON tblPersons_7.KeyPersons = tblTrack.RefNoWriterTrack) 
                                       INNER JOIN
                                          (
                                             tblTerritoryName 
                                             INNER JOIN
                                                (
(tblAnimal 
                                                   INNER JOIN
                                                      (
                                                         tblPersons AS tblPersons_2 
                                                         INNER JOIN
                                                            tblSighting 
                                                            ON tblPersons_2.KeyPersons = tblSighting.RefNoWriterSight
                                                      )
                                                      ON tblAnimal.KeyAnimal = tblSighting.RefNoAnimal) 
                                                   INNER JOIN
                                                      (
(tblPersons AS tblPersons_6 
                                                         INNER JOIN
                                                            tblScat 
                                                            ON tblPersons_6.KeyPersons = tblScat.RefNoWriterScat) 
                                                         INNER JOIN
                                                            (
(tblPersons AS tblPersons_5 
                                                               INNER JOIN
                                                                  tblPrey 
                                                                  ON tblPersons_5.KeyPersons = tblPrey.RefNoWriterPrey) 
                                                               INNER JOIN
                                                                  (
                                                                     tblPhotoDoc 
                                                                     INNER JOIN
                                                                        (
                                                                           tblPersons 
                                                                           INNER JOIN
                                                                              (
(tblPersons AS tblPersons_4 
                                                                                 INNER JOIN
                                                                                    tblOtherDNA 
                                                                                    ON tblPersons_4.KeyPersons = tblOtherDNA.RefNoWriterOther) 
                                                                                 INNER JOIN
                                                                                    (
                                                                                       tblMonitoringYear 
                                                                                       INNER JOIN
                                                                                          (
                                                                                             tblLocPrecision 
                                                                                             INNER JOIN
                                                                                                (
                                                                                                   tblLocation 
                                                                                                   INNER JOIN
                                                                                                      (
(tblPersons AS tblPersons_3 
                                                                                                         INNER JOIN
                                                                                                            tblHowl 
                                                                                                            ON tblPersons_3.KeyPersons = tblHowl.RefNoWriterHowl) 
                                                                                                         INNER JOIN
                                                                                                            (
                                                                                                               tblFieldTrip 
                                                                                                               INNER JOIN
                                                                                                                  (
(tblAnimal AS tblAnimal_1 
                                                                                                                     INNER JOIN
                                                                                                                        (
                                                                                                                           tblPersons AS tblPersons_1 
                                                                                                                           INNER JOIN
                                                                                                                              tblCamera 
                                                                                                                              ON tblPersons_1.KeyPersons = tblCamera.RefNoWriterCam
                                                                                                                        )
                                                                                                                        ON tblAnimal_1.KeyAnimal = tblCamera.RefNoAnimal) 
                                                                                                                     INNER JOIN
                                                                                                                        tblRecord 
                                                                                                                        ON tblCamera.KeyCamera = tblRecord.RefNoCamera
                                                                                                                  )
                                                                                                                  ON tblFieldTrip.KeyFieldTrip = tblRecord.RefNoFieldTrip
                                                                                                            )
                                                                                                            ON tblHowl.KeyHowl = tblRecord.RefNoHowl
                                                                                                      )
                                                                                                      ON tblLocation.KeyLocation = tblRecord.RefNoLocation
                                                                                                )
                                                                                                ON tblLocPrecision.KeyLocPrecision = tblRecord.RefNoPrecision
                                                                                          )
                                                                                          ON tblMonitoringYear.KeyMonYear = tblRecord.RefNoMonYear
                                                                                    )
                                                                                    ON tblOtherDNA.KeyOtherDNA = tblRecord.RefNoOtherDNA
                                                                              )
                                                                              ON tblPersons.KeyPersons = tblRecord.RefNoInformer
                                                                        )
                                                                        ON tblPhotoDoc.KeyPhoto = tblRecord.RefNoPhotoDoc
                                                                  )
                                                                  ON tblPrey.KeyPrey = tblRecord.RefNoPrey
                                                            )
                                                            ON tblScat.KeyScat = tblRecord.RefNoScat
                                                      )
                                                      ON tblSighting.KeySigthing = tblRecord.RefNoSighting
                                                )
                                                ON tblTerritoryName.KeyTerritoryName = tblRecord.RefNoTerritoryName
                                          )
                                          ON tblTrack.KeyTrack = tblRecord.RefNoTrack
                                    )
                                    ON tblUrinBlood.KeyUrinBlood = tblRecord.RefNoUrinBlood
                              )
                              ON tblUVNumber.KeyUVNo = tblRecord.RefNoUVNo
                        )
                        ON tblValidate.KeyValidate = tblRecord.RefNoValidate
                  )
                  ON tblIndividual.KeyIndividual = tblValidate.RefNoIndi
            )
            ON tblDen.KeyDen = tblRecord.RefNoDen
      )
      INNER JOIN
         tblPersons AS tblPersons_9 
         ON tblDen.RefNoWriterDen = tblPersons_9.KeyPersons 
   WHERE
      (
((tblRecord.RecRecordNumber) Like [Formularer] ! [frmSearchGeneral].[FindRecord] & "*") 
         AND 
         (
(tblUVNumber.UVNo) Like [Formularer] ! [frmSearchGeneral].[FindUV] & "*"
         )
         AND 
         (
(tblRecord.RecDate) Between IIf([Forms] ! [frmSearchGeneral].[FindDateStart] Is Null, # 1 / 1 / 1000 # , [Forms] ! [frmSearchGeneral].[FindDateStart]) And IIf([Forms] ! [frmSearchGeneral].[FindDateEnd] Is Null, # 12 / 31 / 9999 # , [Forms] ! [frmSearchGeneral].[FindDateEnd])
         )
         AND 
         (
(tblPersons.PerFullName) Like [Formularer] ! [frmSearchGeneral].[FindPerson] & "*"
         )
         AND 
         (
(tblPersons.PerOfficielObserver) Like [Formularer] ! [frmSearchGeneral].[FindOfficial] & "*"
         )
         AND 
         (
(tblLocation.LocCountry) Like [Formularer] ! [frmSearchGeneral].[FindCountry] & "*"
         )
         AND 
         (
(tblLocation.LocProvince) Like [Formularer] ! [frmSearchGeneral].[FindProvince] & "*"
         )
         AND 
         (
(tblLocation.LocMuncipality) Like [Formularer] ! [frmSearchGeneral].[FindMuncipality] & "*"
         )
         AND 
         (
(tblLocation.LocNearestTown) Like [Formularer] ! [frmSearchGeneral].[FindNearestTown] & "*"
         )
         AND 
         (
(tblLocation.LocAreaName) Like [Formularer] ! [frmSearchGeneral].[FindAreaName] & "*"
         )
         AND 
         (
(tblFieldTrip.FiTPathNumber) Like [Formularer] ! [frmSearchGeneral].[FindTrackNo] & "*"
         )
         AND 
         (
(tblValidate.ValSCALP) Like [Formularer] ! [frmSearchGeneral].[FindSCALP] & "*"
         )
         AND 
         (
(tblRecord.RecValidateYes) Like IIf([Formularer] ! [frmSearchGeneral].[FindValidate] = Yes, [Formularer] ! [frmSearchGeneral].[FindValidate], "*")
         )
         AND 
         (
(tblRecord.RecScatYes) Like IIf([Formularer] ! [frmSearchGeneral].[FindScat] = Yes, [Formularer] ! [frmSearchGeneral].[FindScat], "*")
         )
         AND 
         (
(tblRecord.RecPreyYes) Like IIf([Formularer] ! [frmSearchGeneral].[FindPrey] = Yes, [Formularer] ! [frmSearchGeneral].[FindPrey], "*")
         )
         AND 
         (
(tblRecord.RecOtherDNAYes) Like IIf([Formularer] ! [frmSearchGeneral].[FindOtherDNA] = Yes, [Formularer] ! [frmSearchGeneral].[FindOtherDNA], "*")
         )
         AND 
         (
(tblRecord.RecSightingYes) Like IIf([Formularer] ! [frmSearchGeneral].[FindSighting] = Yes, [Formularer] ! [frmSearchGeneral].[FindSighting], "*")
         )
         AND 
         (
(tblRecord.RecTrackYes) Like IIf([Formularer] ! [frmSearchGeneral].[FindTrack] = Yes, [Formularer] ! [frmSearchGeneral].[FindTrack], "*")
         )
         AND 
         (
(tblRecord.RecHowlYes) Like IIf([Formularer] ! [frmSearchGeneral].[FindHowl] = Yes, [Formularer] ! [frmSearchGeneral].[FindHowl], "*")
         )
         AND 
         (
(tblRecord.RecUrinBloodYes) Like IIf([Formularer] ! [frmSearchGeneral].[FindUrinBlood] = Yes, [Formularer] ! [frmSearchGeneral].[FindUrinBlood], "*")
         )
         AND 
         (
(tblRecord.RecCameraYes) Like IIf([Formularer] ! [frmSearchGeneral].[FindCamera] = Yes, [Formularer] ! [frmSearchGeneral].[FindCamera], "*")
         )
         AND 
         (
(tblValidate.ValInsideWolfZone) Like IIf([Formularer] ! [frmSearchGeneral].[FindWolfZone] = Yes, [formularer] ! [frmSearchGeneral].[FindWolfZone], "*")
         )
         AND 
         (
(tblValidate.RefNoValSpecies) Like [Formularer] ! [frmSearchGeneral].[FindValidateSpecies] & "*"
         )
         AND 
         (
(tblPack.PackName) Like [Formularer] ! [frmSearchGeneral].[FindPack] & "*"
         )
         AND 
         (
(tblIndividual.Individual) Like [Formularer] ! [frmSearchGeneral].[FindIndividual] & "*"
         )
         AND 
         (
(tblTerritoryName.TerritoryName) Like [formularer] ! [frmSearchGeneral].[FindTerritory] & "*"
         )
         AND 
         (
(tblRecord.Deactivated) = False
         )
         AND 
         (
(Year([RecDate])) = [Forms] ! [frmSearchGeneral].[FindYearSpecific]
         )
      )
      OR 
      (
((tblRecord.RecRecordNumber) Like [Formularer] ! [frmSearchGeneral].[FindRecord] & "*") 
         AND 
         (
(tblUVNumber.UVNo) Like [Formularer] ! [frmSearchGeneral].[FindUV] & "*"
         )
         AND 
         (
(tblRecord.RecDate) Between IIf([Forms] ! [frmSearchGeneral].[FindDateStart] Is Null, # 1 / 1 / 1000 # , [Forms] ! [frmSearchGeneral].[FindDateStart]) And IIf([Forms] ! [frmSearchGeneral].[FindDateEnd] Is Null, # 12 / 31 / 9999 # , [Forms] ! [frmSearchGeneral].[FindDateEnd])
         )
         AND 
         (
(tblPersons.PerFullName) Like [Formularer] ! [frmSearchGeneral].[FindPerson] & "*"
         )
         AND 
         (
(tblPersons.PerOfficielObserver) Like [Formularer] ! [frmSearchGeneral].[FindOfficial] & "*"
         )
         AND 
         (
(tblLocation.LocCountry) Like [Formularer] ! [frmSearchGeneral].[FindCountry] & "*"
         )
         AND 
         (
(tblLocation.LocProvince) Like [Formularer] ! [frmSearchGeneral].[FindProvince] & "*"
         )
         AND 
         (
(tblLocation.LocMuncipality) Like [Formularer] ! [frmSearchGeneral].[FindMuncipality] & "*"
         )
         AND 
         (
(tblLocation.LocNearestTown) Like [Formularer] ! [frmSearchGeneral].[FindNearestTown] & "*"
         )
         AND 
         (
(tblLocation.LocAreaName) Like [Formularer] ! [frmSearchGeneral].[FindAreaName] & "*"
         )
         AND 
         (
(tblFieldTrip.FiTPathNumber) Like [Formularer] ! [frmSearchGeneral].[FindTrackNo] & "*"
         )
         AND 
         (
(tblValidate.ValSCALP) Like [Formularer] ! [frmSearchGeneral].[FindSCALP] & "*"
         )
         AND 
         (
(tblRecord.RecValidateYes) Like IIf([Formularer] ! [frmSearchGeneral].[FindValidate] = Yes, [Formularer] ! [frmSearchGeneral].[FindValidate], "*")
         )
         AND 
         (
(tblRecord.RecScatYes) Like IIf([Formularer] ! [frmSearchGeneral].[FindScat] = Yes, [Formularer] ! [frmSearchGeneral].[FindScat], "*")
         )
         AND 
         (
(tblRecord.RecPreyYes) Like IIf([Formularer] ! [frmSearchGeneral].[FindPrey] = Yes, [Formularer] ! [frmSearchGeneral].[FindPrey], "*")
         )
         AND 
         (
(tblRecord.RecOtherDNAYes) Like IIf([Formularer] ! [frmSearchGeneral].[FindOtherDNA] = Yes, [Formularer] ! [frmSearchGeneral].[FindOtherDNA], "*")
         )
         AND 
         (
(tblRecord.RecSightingYes) Like IIf([Formularer] ! [frmSearchGeneral].[FindSighting] = Yes, [Formularer] ! [frmSearchGeneral].[FindSighting], "*")
         )
         AND 
         (
(tblRecord.RecTrackYes) Like IIf([Formularer] ! [frmSearchGeneral].[FindTrack] = Yes, [Formularer] ! [frmSearchGeneral].[FindTrack], "*")
         )
         AND 
         (
(tblRecord.RecHowlYes) Like IIf([Formularer] ! [frmSearchGeneral].[FindHowl] = Yes, [Formularer] ! [frmSearchGeneral].[FindHowl], "*")
         )
         AND 
         (
(tblRecord.RecUrinBloodYes) Like IIf([Formularer] ! [frmSearchGeneral].[FindUrinBlood] = Yes, [Formularer] ! [frmSearchGeneral].[FindUrinBlood], "*")
         )
         AND 
         (
(tblRecord.RecCameraYes) Like IIf([Formularer] ! [frmSearchGeneral].[FindCamera] = Yes, [Formularer] ! [frmSearchGeneral].[FindCamera], "*")
         )
         AND 
         (
(tblValidate.ValInsideWolfZone) Like IIf([Formularer] ! [frmSearchGeneral].[FindWolfZone] = Yes, [formularer] ! [frmSearchGeneral].[FindWolfZone], "*")
         )
         AND 
         (
(tblValidate.RefNoValSpecies) Like [Formularer] ! [frmSearchGeneral].[FindValidateSpecies] & "*"
         )
         AND 
         (
(tblPack.PackName) Like [Formularer] ! [frmSearchGeneral].[FindPack] & "*"
         )
         AND 
         (
(tblIndividual.Individual) Like [Formularer] ! [frmSearchGeneral].[FindIndividual] & "*"
         )
         AND 
         (
(tblTerritoryName.TerritoryName) Like [formularer] ! [frmSearchGeneral].[FindTerritory] & "*"
         )
         AND 
         (
(tblRecord.Deactivated) = False
         )
         AND 
         (
(Year([RecDate])) Between IIf([Forms] ! [frmSearchGeneral].[FindYearStart] Is Null, # 1 / 1 / 1000 # , [Forms] ! [frmSearchGeneral].[FindYearStart]) And IIf([Forms] ! [frmSearchGeneral].[FindYearEnd] Is Null, # 12 / 31 / 9999 # , [Forms] ! [frmSearchGeneral].[FindYearEnd])
         )
         AND 
         (
([Forms] ! [frmSearchGeneral].[FindYearSpecific]) Is Null
         )
      )

;

This is where Access goes from a 2 minute search and then delivers the results as it should to giving me:

"Query too complex"

The only thing I have done is to add two tables (tblDen and tblPersons_9) and two columns more RecDenYes (from tblRecords) and PerFullName (from tblPersons_9)

To make the query complete I need to add another two tables (tblAnimal_2 and tblAnimal_3) to be able to add the two columns (AniNumberOfAnimals and AniNumberOfAnimals) these will give me the number of animals from tblHowl and tblTrack.

You probably want to rewrite that `WHERE` clause. Without sample data it's incredibly hard to know what you want, but most likely you can remove a whole lot of parentheses there. I also recommend rewriting that `FROM` clause manually to do `((SomeTable INNER JOIN Something ON Somecondition) INNER JOIN SomethingElse ON SomeOtherCondition)` instead of Access's weird _joins first, on clauses later_ approach. — Erik A, Mar 04 '19 at 13:24
Can't quite tell but how many `JOINs` do you have? See [MS Access limitations specs](https://support.office.com/en-us/article/access-specifications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c). — Parfait, Mar 04 '19 at 13:33
_The query builder's SQL is quite long_ … you are answering your own question. Break it up in some (a series of) subqueries which you can debug individually - and/or write some or all of the filtering values to a temp table which you use to filter on. — Gustav, Mar 04 '19 at 13:34
@Parfait Seems the JOINs count is 32 and the limit is 16 (I did not know this, thanks for the information). So this could be the problem. It is funny that the query works with the first 30 joins though... — Christina Vedel-Smith, Mar 04 '19 at 20:21

score -1 · Answer 1 · answered Mar 04 '19 at 14:55

This is less of an answer and more of a first step to consider. I would take this monster and have a sql formatter/pretty printer parse it to make it readable. SQLInForm plugin for Notepad++ is what I've used here:

SELECT
    tblRecord.RecRecordNumber
  , tblUVNumber.UVNo
  , tblRecord.RecDate
  , tblRecord.RefNoInformer
  , tblPersons.PerFullName
  , tblPersons.PerOfficielObserver
  , tblLocation.LocCountry
  , tblLocation.LocProvince
  , tblLocation.LocMuncipality
  , tblLocation.LocNearestTown
  , tblLocation.LocAreaName
  , tblFieldTrip.FiTPathNumber
  , tblValidate.ValSCALP
  , tblRecord.RecValidateYes
  , tblRecord.RecFieldTripYes
  , tblRecord.RecScatYes
  , tblRecord.RecPreyYes
  , tblRecord.RecOtherDNAYes
  , tblRecord.RecSightingYes
  , tblRecord.RecTrackYes
  , tblRecord.RecHowlYes
  , tblRecord.RecUrinBloodYes
  , tblRecord.RecCameraYes
  , tblRecord.RecDenYes
  , tblValidate.ValInsideWolfZone
  , tblRecord.RecSampleType
  , tblRecord.RecSampleEvidence
  , tblRecord.RecDogPresent
  , tblRecord.RecGELat
  , tblRecord.RecGELong
  , tblRecord.RecCoordinates
  , tblRecord.RefNoCoorSys
  , tblRecord.RecUTMQuadrant
  , tblRecord.RecUTMCoorX
  , tblRecord.RecUTMCoorY
  , tblLocPrecision.LocationPrecision
  , tblRecord.RefNoValidate
  , tblValidate.ValSenckLabID
  , tblValidate.ValSenckType_mtDNA
  , tblValidate.ValSenckHaploType
  , tblValidate.ValSenckInfo_mtDNA
  , tblValidate.ValSenckType_KernDNA
  , tblValidate.ValSenckInfo_KernDNA
  , tblAnimal_1.AniNumberOfAnimals
  , tblAnimal.AniNumberOfAnimals
  , tblPersons_1.PerFullName
  , tblPersons_2.PerFullName
  , tblPersons_3.PerFullName
  , tblPersons_4.PerFullName
  , tblPersons_5.PerFullName
  , tblPersons_6.PerFullName
  , tblPersons_7.PerFullName
  , tblPersons_8.PerFullName
  , tblPersons_9.PerFullName
  , tblValidate.RefNoValSpecies
  , tblValidateSpecies.ValSpeGenus
  , tblValidateSpecies.ValSpeSpecies
  , tblValidateSpecies.ValSpeDanishName
  , tblPack.PackName
  , tblIndividual.Individual
  , tblIndividual.IndiSex
  , tblIndividual.IndiBornYear
  , tblIndividual.IndiBornPlace
  , tblIndividual.IndiDead
  , tblIndividual.IndiDeadCause
  , tblValidate.RefNoProbIndiGuess
  , tblValidate.ValIndiAgeAtSampleTime
  , tblTerritoryName.TerritoryName
  , tblMonitoringYear.MonYear
  , tblPhotoDoc.PhDPhotoNotes
  , tblPhotoDoc.PhDPermissionToUsePhotoText
  , tblPhotoDoc.PhDPhotoFiles
FROM
    (tblDen
    INNER JOIN
        (tblIndividual
        INNER JOIN
            ((tblValidateSpecies
            INNER JOIN
                (tblPack
                INNER JOIN
                    tblValidate
                    ON
                        tblPack.KeyPack = tblValidate.RefNoPack)
                ON
                    tblValidateSpecies.KeyValidateSpecies = tblValidate.RefNoValSpecies)
            INNER JOIN
                (tblUVNumber
                INNER JOIN
                    ((tblPersons AS tblPersons_8
                    INNER JOIN
                        tblUrinBlood
                        ON
                            tblPersons_8.KeyPersons = tblUrinBlood.RefNoWriterUrinBlood)
                    INNER JOIN
                        ((tblPersons AS tblPersons_7
                        INNER JOIN
                            tblTrack
                            ON
                                tblPersons_7.KeyPersons = tblTrack.RefNoWriterTrack)
                        INNER JOIN
                            (tblTerritoryName
                            INNER JOIN
                                ((tblAnimal
                                INNER JOIN
                                    (tblPersons AS tblPersons_2
                                    INNER JOIN
                                        tblSighting
                                        ON
                                            tblPersons_2.KeyPersons = tblSighting.RefNoWriterSight)
                                    ON
                                        tblAnimal.KeyAnimal = tblSighting.RefNoAnimal)
                                INNER JOIN
                                    ((tblPersons AS tblPersons_6
                                    INNER JOIN
                                        tblScat
                                        ON
                                            tblPersons_6.KeyPersons = tblScat.RefNoWriterScat)
                                    INNER JOIN
                                        ((tblPersons AS tblPersons_5
                                        INNER JOIN
                                            tblPrey
                                            ON
                                                tblPersons_5.KeyPersons = tblPrey.RefNoWriterPrey)
                                        INNER JOIN
                                            (tblPhotoDoc
                                            INNER JOIN
                                                (tblPersons
                                                INNER JOIN
                                                    ((tblPersons AS tblPersons_4
                                                    INNER JOIN
                                                        tblOtherDNA
                                                        ON
                                                            tblPersons_4.KeyPersons = tblOtherDNA.RefNoWriterOther)
                                                    INNER JOIN
                                                        (tblMonitoringYear
                                                        INNER JOIN
                                                            (tblLocPrecision
                                                            INNER JOIN
                                                                (tblLocation
                                                                INNER JOIN
                                                                    ((tblPersons AS tblPersons_3
                                                                    INNER JOIN
                                                                        tblHowl
                                                                        ON
                                                                            tblPersons_3.KeyPersons = tblHowl.RefNoWriterHowl)
                                                                    INNER JOIN
                                                                        (tblFieldTrip
                                                                        INNER JOIN
                                                                            ((tblAnimal AS tblAnimal_1
                                                                            INNER JOIN
                                                                                (tblPersons AS tblPersons_1
                                                                                INNER JOIN
                                                                                    tblCamera
                                                                                    ON
                                                                                        tblPersons_1.KeyPersons = tblCamera.RefNoWriterCam)
                                                                                ON
                                                                                    tblAnimal_1.KeyAnimal = tblCamera.RefNoAnimal)
                                                                            INNER JOIN
                                                                                tblRecord
                                                                                ON
                                                                                    tblCamera.KeyCamera = tblRecord.RefNoCamera)
                                                                            ON
                                                                                tblFieldTrip.KeyFieldTrip = tblRecord.RefNoFieldTrip)
                                                                        ON
                                                                            tblHowl.KeyHowl = tblRecord.RefNoHowl)
                                                                    ON
                                                                        tblLocation.KeyLocation = tblRecord.RefNoLocation)
                                                                ON
                                                                    tblLocPrecision.KeyLocPrecision = tblRecord.RefNoPrecision)
                                                            ON
                                                                tblMonitoringYear.KeyMonYear = tblRecord.RefNoMonYear)
                                                        ON
                                                            tblOtherDNA.KeyOtherDNA = tblRecord.RefNoOtherDNA)
                                                    ON
                                                        tblPersons.KeyPersons = tblRecord.RefNoInformer)
                                                ON
                                                    tblPhotoDoc.KeyPhoto = tblRecord.RefNoPhotoDoc)
                                            ON
                                                tblPrey.KeyPrey = tblRecord.RefNoPrey)
                                        ON
                                            tblScat.KeyScat = tblRecord.RefNoScat)
                                    ON
                                        tblSighting.KeySigthing = tblRecord.RefNoSighting)
                                ON
                                    tblTerritoryName.KeyTerritoryName = tblRecord.RefNoTerritoryName)
                            ON
                                tblTrack.KeyTrack = tblRecord.RefNoTrack)
                        ON
                            tblUrinBlood.KeyUrinBlood = tblRecord.RefNoUrinBlood)
                    ON
                        tblUVNumber.KeyUVNo = tblRecord.RefNoUVNo)
                ON
                    tblValidate.KeyValidate = tblRecord.RefNoValidate)
            ON
                tblIndividual.KeyIndividual = tblValidate.RefNoIndi)
        ON
            tblDen.KeyDen = tblRecord.RefNoDen)
    INNER JOIN
        tblPersons AS tblPersons_9
        ON
            tblDen.RefNoWriterDen = tblPersons_9.KeyPersons
WHERE
    (
        (
            (
                tblRecord.RecRecordNumber
            )
            Like [Formularer]![frmSearchGeneral].[FindRecord] & "*"
        )
        AND
        (
            (
                tblUVNumber.UVNo
            )
            Like [Formularer]![frmSearchGeneral].[FindUV] & "*"
        )
        AND
        (
            (
                tblRecord.RecDate
            )
            Between IIf([Forms]![frmSearchGeneral].[FindDateStart] Is Null,#1/1/1000#,[Forms]![frmSearchGeneral].[FindDateStart]) And IIf([Forms]![frmSearchGeneral].[FindDateEnd] Is Null,#12/31/9999#,[Forms]![frmSearchGeneral].[FindDateEnd])
        )
        AND
        (
            (
                tblPersons.PerFullName
            )
            Like [Formularer]![frmSearchGeneral].[FindPerson] & "*"
        )
        AND
        (
            (
                tblPersons.PerOfficielObserver
            )
            Like [Formularer]![frmSearchGeneral].[FindOfficial] & "*"
        )
        AND
        (
            (
                tblLocation.LocCountry
            )
            Like [Formularer]![frmSearchGeneral].[FindCountry] & "*"
        )
        AND
        (
            (
                tblLocation.LocProvince
            )
            Like [Formularer]![frmSearchGeneral].[FindProvince] & "*"
        )
        AND
        (
            (
                tblLocation.LocMuncipality
            )
            Like [Formularer]![frmSearchGeneral].[FindMuncipality] & "*"
        )
        AND
        (
            (
                tblLocation.LocNearestTown
            )
            Like [Formularer]![frmSearchGeneral].[FindNearestTown] & "*"
        )
        AND
        (
            (
                tblLocation.LocAreaName
            )
            Like [Formularer]![frmSearchGeneral].[FindAreaName] & "*"
        )
        AND
        (
            (
                tblFieldTrip.FiTPathNumber
            )
            Like [Formularer]![frmSearchGeneral].[FindTrackNo] & "*"
        )
        AND
        (
            (
                tblValidate.ValSCALP
            )
            Like [Formularer]![frmSearchGeneral].[FindSCALP] & "*"
        )
        AND
        (
            (
                tblRecord.RecValidateYes
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindValidate]=Yes,[Formularer]![frmSearchGeneral].[FindValidate],"*")
        )
        AND
        (
            (
                tblRecord.RecScatYes
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindScat]=Yes,[Formularer]![frmSearchGeneral].[FindScat],"*")
        )
        AND
        (
            (
                tblRecord.RecPreyYes
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindPrey]=Yes,[Formularer]![frmSearchGeneral].[FindPrey],"*")
        )
        AND
        (
            (
                tblRecord.RecOtherDNAYes
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindOtherDNA]=Yes,[Formularer]![frmSearchGeneral].[FindOtherDNA],"*")
        )
        AND
        (
            (
                tblRecord.RecSightingYes
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindSighting]=Yes,[Formularer]![frmSearchGeneral].[FindSighting],"*")
        )
        AND
        (
            (
                tblRecord.RecTrackYes
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindTrack]=Yes,[Formularer]![frmSearchGeneral].[FindTrack],"*")
        )
        AND
        (
            (
                tblRecord.RecHowlYes
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindHowl]=Yes,[Formularer]![frmSearchGeneral].[FindHowl],"*")
        )
        AND
        (
            (
                tblRecord.RecUrinBloodYes
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindUrinBlood]=Yes,[Formularer]![frmSearchGeneral].[FindUrinBlood],"*")
        )
        AND
        (
            (
                tblRecord.RecCameraYes
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindCamera]=Yes,[Formularer]![frmSearchGeneral].[FindCamera],"*")
        )
        AND
        (
            (
                tblValidate.ValInsideWolfZone
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindWolfZone]=Yes,[formularer]![frmSearchGeneral].[FindWolfZone],"*")
        )
        AND
        (
            (
                tblValidate.RefNoValSpecies
            )
            Like [Formularer]![frmSearchGeneral].[FindValidateSpecies] & "*"
        )
        AND
        (
            (
                tblPack.PackName
            )
            Like [Formularer]![frmSearchGeneral].[FindPack] & "*"
        )
        AND
        (
            (
                tblIndividual.Individual
            )
            Like [Formularer]![frmSearchGeneral].[FindIndividual] & "*"
        )
        AND
        (
            (
                tblTerritoryName.TerritoryName
            )
            Like [formularer]![frmSearchGeneral].[FindTerritory] & "*"
        )
        AND
        (
            (
                tblRecord.Deactivated
            )
            =False
        )
        AND
        (
            (
                Year([RecDate])
            )
            =[Forms]![frmSearchGeneral].[FindYearSpecific]
        )
    )
    OR
    (
        (
            (
                tblRecord.RecRecordNumber
            )
            Like [Formularer]![frmSearchGeneral].[FindRecord] & "*"
        )
        AND
        (
            (
                tblUVNumber.UVNo
            )
            Like [Formularer]![frmSearchGeneral].[FindUV] & "*"
        )
        AND
        (
            (
                tblRecord.RecDate
            )
            Between IIf([Forms]![frmSearchGeneral].[FindDateStart] Is Null,#1/1/1000#,[Forms]![frmSearchGeneral].[FindDateStart]) And IIf([Forms]![frmSearchGeneral].[FindDateEnd] Is Null,#12/31/9999#,[Forms]![frmSearchGeneral].[FindDateEnd])
        )
        AND
        (
            (
                tblPersons.PerFullName
            )
            Like [Formularer]![frmSearchGeneral].[FindPerson] & "*"
        )
        AND
        (
            (
                tblPersons.PerOfficielObserver
            )
            Like [Formularer]![frmSearchGeneral].[FindOfficial] & "*"
        )
        AND
        (
            (
                tblLocation.LocCountry
            )
            Like [Formularer]![frmSearchGeneral].[FindCountry] & "*"
        )
        AND
        (
            (
                tblLocation.LocProvince
            )
            Like [Formularer]![frmSearchGeneral].[FindProvince] & "*"
        )
        AND
        (
            (
                tblLocation.LocMuncipality
            )
            Like [Formularer]![frmSearchGeneral].[FindMuncipality] & "*"
        )
        AND
        (
            (
                tblLocation.LocNearestTown
            )
            Like [Formularer]![frmSearchGeneral].[FindNearestTown] & "*"
        )
        AND
        (
            (
                tblLocation.LocAreaName
            )
            Like [Formularer]![frmSearchGeneral].[FindAreaName] & "*"
        )
        AND
        (
            (
                tblFieldTrip.FiTPathNumber
            )
            Like [Formularer]![frmSearchGeneral].[FindTrackNo] & "*"
        )
        AND
        (
            (
                tblValidate.ValSCALP
            )
            Like [Formularer]![frmSearchGeneral].[FindSCALP] & "*"
        )
        AND
        (
            (
                tblRecord.RecValidateYes
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindValidate]=Yes,[Formularer]![frmSearchGeneral].[FindValidate],"*")
        )
        AND
        (
            (
                tblRecord.RecScatYes
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindScat]=Yes,[Formularer]![frmSearchGeneral].[FindScat],"*")
        )
        AND
        (
            (
                tblRecord.RecPreyYes
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindPrey]=Yes,[Formularer]![frmSearchGeneral].[FindPrey],"*")
        )
        AND
        (
            (
                tblRecord.RecOtherDNAYes
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindOtherDNA]=Yes,[Formularer]![frmSearchGeneral].[FindOtherDNA],"*")
        )
        AND
        (
            (
                tblRecord.RecSightingYes
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindSighting]=Yes,[Formularer]![frmSearchGeneral].[FindSighting],"*")
        )
        AND
        (
            (
                tblRecord.RecTrackYes
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindTrack]=Yes,[Formularer]![frmSearchGeneral].[FindTrack],"*")
        )
        AND
        (
            (
                tblRecord.RecHowlYes
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindHowl]=Yes,[Formularer]![frmSearchGeneral].[FindHowl],"*")
        )
        AND
        (
            (
                tblRecord.RecUrinBloodYes
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindUrinBlood]=Yes,[Formularer]![frmSearchGeneral].[FindUrinBlood],"*")
        )
        AND
        (
            (
                tblRecord.RecCameraYes
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindCamera]=Yes,[Formularer]![frmSearchGeneral].[FindCamera],"*")
        )
        AND
        (
            (
                tblValidate.ValInsideWolfZone
            )
            Like IIf([Formularer]![frmSearchGeneral].[FindWolfZone]=Yes,[formularer]![frmSearchGeneral].[FindWolfZone],"*")
        )
        AND
        (
            (
                tblValidate.RefNoValSpecies
            )
            Like [Formularer]![frmSearchGeneral].[FindValidateSpecies] & "*"
        )
        AND
        (
            (
                tblPack.PackName
            )
            Like [Formularer]![frmSearchGeneral].[FindPack] & "*"
        )
        AND
        (
            (
                tblIndividual.Individual
            )
            Like [Formularer]![frmSearchGeneral].[FindIndividual] & "*"
        )
        AND
        (
            (
                tblTerritoryName.TerritoryName
            )
            Like [formularer]![frmSearchGeneral].[FindTerritory] & "*"
        )
        AND
        (
            (
                tblRecord.Deactivated
            )
            =False
        )
        AND
        (
            (
                Year([RecDate])
            )
            Between IIf([Forms]![frmSearchGeneral].[FindYearStart] Is Null,#1/1/1000#,[Forms]![frmSearchGeneral].[FindYearStart]) And IIf([Forms]![frmSearchGeneral].[FindYearEnd] Is Null,#12/31/9999#,[Forms]![frmSearchGeneral].[FindYearEnd])
        )
        AND
        (
            (
                [Forms]![frmSearchGeneral].[FindYearSpecific]
            )
            Is Null
        )
    )
;

This is 606 line whopper now.

Access loves it some parentheses and most of the time they are 100% not needed. In fact with the exception of your single OR condition in your WHERE clause the rest of the parentheses are superfluous. Also Access is Grade A dumb when it sticks these nested INNER JOINs together like this. They are simply not needed and the ON clauses are just sprinkled about haphazardly. It's technically correct, but it's a mess to debug.

Rewritten:

SELECT
    tblRecord.RecRecordNumber
 , tblUVNumber.UVNo
 , tblRecord.RecDate
 , tblRecord.RefNoInformer
 , tblPersons.PerFullName
 , tblPersons.PerOfficielObserver
 , tblLocation.LocCountry
 , tblLocation.LocProvince
 , tblLocation.LocMuncipality
 , tblLocation.LocNearestTown
 , tblLocation.LocAreaName
 , tblFieldTrip.FiTPathNumber
 , tblValidate.ValSCALP
 , tblRecord.RecValidateYes
 , tblRecord.RecFieldTripYes
 , tblRecord.RecScatYes
 , tblRecord.RecPreyYes
 , tblRecord.RecOtherDNAYes
 , tblRecord.RecSightingYes
 , tblRecord.RecTrackYes
 , tblRecord.RecHowlYes
 , tblRecord.RecUrinBloodYes
 , tblRecord.RecCameraYes
 , tblRecord.RecDenYes
 , tblValidate.ValInsideWolfZone
 , tblRecord.RecSampleType
 , tblRecord.RecSampleEvidence
 , tblRecord.RecDogPresent
 , tblRecord.RecGELat
 , tblRecord.RecGELong
 , tblRecord.RecCoordinates
 , tblRecord.RefNoCoorSys
 , tblRecord.RecUTMQuadrant
 , tblRecord.RecUTMCoorX
 , tblRecord.RecUTMCoorY
 , tblLocPrecision.LocationPrecision
 , tblRecord.RefNoValidate
 , tblValidate.ValSenckLabID
 , tblValidate.ValSenckType_mtDNA
 , tblValidate.ValSenckHaploType
 , tblValidate.ValSenckInfo_mtDNA
 , tblValidate.ValSenckType_KernDNA
 , tblValidate.ValSenckInfo_KernDNA
 , tblAnimal_1.AniNumberOfAnimals
 , tblAnimal.AniNumberOfAnimals
 , tblPersons_1.PerFullName
 , tblPersons_2.PerFullName
 , tblPersons_3.PerFullName
 , tblPersons_4.PerFullName
 , tblPersons_5.PerFullName
 , tblPersons_6.PerFullName
 , tblPersons_7.PerFullName
 , tblPersons_8.PerFullName
 , tblPersons_9.PerFullName
 , tblValidate.RefNoValSpecies
 , tblValidateSpecies.ValSpeGenus
 , tblValidateSpecies.ValSpeSpecies
 , tblValidateSpecies.ValSpeDanishName
 , tblPack.PackName
 , tblIndividual.Individual
 , tblIndividual.IndiSex
 , tblIndividual.IndiBornYear
 , tblIndividual.IndiBornPlace
 , tblIndividual.IndiDead
 , tblIndividual.IndiDeadCause
 , tblValidate.RefNoProbIndiGuess
 , tblValidate.ValIndiAgeAtSampleTime
 , tblTerritoryName.TerritoryName
 , tblMonitoringYear.MonYear
 , tblPhotoDoc.PhDPhotoNotes
 , tblPhotoDoc.PhDPermissionToUsePhotoText
 , tblPhotoDoc.PhDPhotoFiles
FROM
    /*TblRecord is the driver here. Everything else joins to it snowflake style*/   
    tblRecord

    /*bring in camera data*/
    INNER JOIN
        tblCamera
        ON
            tblCamera.KeyCamera = tblRecord.RefNoCamera
    INNER JOIN
        tblAnimal AS tblAnimal_1
        ON
            tblAnimal_1.KeyAnimal = tblCamera.RefNoAnimal
    INNER JOIN
        tblPersons AS tblPersons_1
        ON
            tblPersons_1.KeyPersons = tblCamera.RefNoWriterCam

    /*Howl Data*/
    INNER JOIN
        tblHowl
        ON
            tblHowl.KeyHowl = tblRecord.RefNoHowl
    INNER JOIN
        tblPersons AS tblPersons_3
        ON
            tblPersons_3.KeyPersons = tblHowl.RefNoWriterHowl

    /*Field Trip Data*/
    INNER JOIN
        tblFieldTrip
        ON
            tblFieldTrip.KeyFieldTrip = tblRecord.RefNoFieldTrip

    /*Location Data*/
    INNER JOIN
        tblLocation
        ON
            tblLocation.KeyLocation = tblRecord.RefNoLocation

    /*Precision Data*/
    INNER JOIN
        tblLocPrecision
        ON
            tblLocPrecision.KeyLocPrecision = tblRecord.RefNoPrecision

    /*Monitoring Year Data*/
    INNER JOIN
        tblMonitoringYear
        ON
            tblMonitoringYear.KeyMonYear = tblRecord.RefNoMonYear

    /*Other DNA data*/
    INNER JOIN
        tblOtherDNA
        ON
            tblOtherDNA.KeyOtherDNA = tblRecord.RefNoOtherDNA
    INNER JOIN
        tblPersons AS tblPersons_4
        ON
            tblPersons_4.KeyPersons = tblOtherDNA.RefNoWriterOther

    /*Primary Persons data for the record*/
    INNER JOIN
        tblPersons
        ON
            tblPersons.KeyPersons = tblRecord.RefNoInformer

    /*Photo Doc Data*/
    INNER JOIN
        tblPhotoDoc
        ON
            tblPhotoDoc.KeyPhoto = tblRecord.RefNoPhotoDoc

    /*Prey Data*/
    INNER JOIN
        tblPrey
        ON
            tblPrey.KeyPrey = tblRecord.RefNoPrey
    INNER JOIN
        tblPersons AS tblPersons_5
        ON
            tblPersons_5.KeyPersons = tblPrey.RefNoWriterPrey

    /*Scat Data*/
    INNER JOIN
        tblScat
        ON
            tblScat.KeyScat = tblRecord.RefNoScat
    INNER JOIN
        tblPersons AS tblPersons_6
        ON
            tblPersons_6.KeyPersons = tblScat.RefNoWriterScat

    /*Sighting Data*/
    INNER JOIN
        tblSighting
        ON
            tblSighting.KeySigthing = tblRecord.RefNoSighting
    INNER JOIN
        tblAnimal
        ON
            tblAnimal.KeyAnimal = tblSighting.RefNoAnimal
    INNER JOIN
        tblPersons AS tblPersons_2
        ON
            tblPersons_2.KeyPersons = tblSighting.RefNoWriterSight

    /*Territory Name Data*/
    INNER JOIN
        tblTerritoryName
        ON
            tblTerritoryName.KeyTerritoryName = tblRecord.RefNoTerritoryName

    /*Track Data*/
    INNER JOIN
        tblTrack
        ON
            tblTrack.KeyTrack = tblRecord.RefNoTrack
    INNER JOIN
        tblPersons AS tblPersons_7
        ON
            tblPersons_7.KeyPersons = tblTrack.RefNoWriterTrack

    /*Urin Blood Data*/
    INNER JOIN
        tblUrinBlood
        ON
            tblUrinBlood.KeyUrinBlood = tblRecord.RefNoUrinBlood
    INNER JOIN
        tblPersons AS tblPersons_8
        ON
            tblPersons_8.KeyPersons = tblUrinBlood.RefNoWriterUrinBlood

    /*UV Number Data*/
    INNER JOIN
        tblUVNumber
        ON
            tblUVNumber.KeyUVNo = tblRecord.RefNoUVNo

    /*Validation Data*/
    INNER JOIN
        tblValidate
        ON
            tblValidate.KeyValidate = tblRecord.RefNoValidate
    INNER JOIN
        tblValidateSpecies
        ON
            tblValidateSpecies.KeyValidateSpecies = tblValidate.RefNoValSpecies

    /*Pack Data*/
        INNER JOIN
            tblPack
            ON
                tblPack.KeyPack = tblValidate.RefNoPack
        INNER JOIN
            tblIndividual
            ON
                tblIndividual.KeyIndividual = tblValidate.RefNoIndi

    /*Den Data*/
    INNER JOIN
        tblDen
        ON
            tblDen.KeyDen = tblRecord.RefNoDen
    INNER JOIN
        tblPersons AS tblPersons_9
        ON
            tblDen.RefNoWriterDen = tblPersons_9.KeyPersons
WHERE
    (
        tblRecord.RecRecordNumber like [Formularer]![frmSearchGeneral].[FindRecord] & "*"
        AND tblUVNumber.UVNo Like [Formularer]![frmSearchGeneral].[FindUV] & "*"
        AND tblRecord.RecDate Between 
            IIf([Forms]![frmSearchGeneral].[FindDateStart] Is Null ,#1/1/1000# ,[Forms]![frmSearchGeneral].[FindDateStart] 
            And IIf([Forms]![frmSearchGeneral].[FindDateEnd] Is Null,#12/31/9999#,[Forms]![frmSearchGeneral].[FindDateEnd]
        AND tblPersons.PerFullName Like [Formularer]![frmSearchGeneral].[FindPerson] & "*"
        AND tblPersons.PerOfficielObserver Like [Formularer]![frmSearchGeneral].[FindOfficial] & "*"
        AND tblLocation.LocCountry Like [Formularer]![frmSearchGeneral].[FindCountry] & "*"
        AND tblLocation.LocProvince Like [Formularer]![frmSearchGeneral].[FindProvince] & "*"
        AND tblLocation.LocMuncipality Like [Formularer]![frmSearchGeneral].[FindMuncipality] & "*"
        AND tblLocation.LocNearestTown Like [Formularer]![frmSearchGeneral].[FindNearestTown] & "*"
        AND tblLocation.LocAreaName Like [Formularer]![frmSearchGeneral].[FindAreaName] & "*"
        AND tblFieldTrip.FiTPathNumber Like [Formularer]![frmSearchGeneral].[FindTrackNo] & "*"
        AND tblValidate.ValSCALP Like [Formularer]![frmSearchGeneral].[FindSCALP] & "*"
        AND tblRecord.RecValidateYes Like (IIf([Formularer]![frmSearchGeneral].[FindValidate]=Yes ,[Formularer]![frmSearchGeneral].[FindValidate] ,"*")
        AND tblRecord.RecScatYes Like IIf([Formularer]![frmSearchGeneral].[FindScat]=Yes ,[Formularer]![frmSearchGeneral].[FindScat] ,"*")
        AND tblRecord.RecPreyYes Like IIf([Formularer]![frmSearchGeneral].[FindPrey]=Yes,[Formularer]![frmSearchGeneral].[FindPrey],"*")
        AND tblRecord.RecOtherDNAYes Like IIf([Formularer]![frmSearchGeneral].[FindOtherDNA]=Yes,[Formularer]![frmSearchGeneral].[FindOtherDNA] ,"*")
        AND tblRecord.RecSightingYes Like IIf([Formularer]![frmSearchGeneral].[FindSighting]=Yes ,[Formularer]![frmSearchGeneral].[FindSighting],"*")
        AND tblRecord.RecTrackYes Like IIf([Formularer]![frmSearchGeneral].[FindTrack]=Yes,[Formularer]![frmSearchGeneral].[FindTrack] ,"*")
        AND tblRecord.RecHowlYes Like IIf([Formularer]![frmSearchGeneral].[FindHowl]=Yes,[Formularer]![frmSearchGeneral].[FindHowl] ,"*")
        AND tblRecord.RecUrinBloodYes Like IIf([Formularer]![frmSearchGeneral].[FindUrinBlood]=Yes,[Formularer]![frmSearchGeneral].[FindUrinBlood] ,"*")
        AND tblRecord.RecCameraYes Like IIf([Formularer]![frmSearchGeneral].[FindCamera]=Yes,[Formularer]![frmSearchGeneral].[FindCamera] ,"*")
        AND tblValidate.ValInsideWolfZone Like IIf([Formularer]![frmSearchGeneral].[FindWolfZone]=Yes,[formularer]![frmSearchGeneral].[FindWolfZone] ,"*")
        AND tblValidate.RefNoValSpecies Like [Formularer]![frmSearchGeneral].[FindValidateSpecies] & "*"
        AND tblPack.PackName Like [Formularer]![frmSearchGeneral].[FindPack] & "*"
        AND tblIndividual.Individual Like [Formularer]![frmSearchGeneral].[FindIndividual] & "*"
        AND tblTerritoryName.TerritoryName Like [formularer]![frmSearchGeneral].[FindTerritory] & "*"
        AND tblRecord.Deactivated =False
        AND Year[RecDate] =[Forms]![frmSearchGeneral].[FindYearSpecific]
    )
    OR 
    (
        tblRecord.RecRecordNumber Like [Formularer]![frmSearchGeneral].[FindRecord] & "*"
        AND tblUVNumber.UVNo Like [Formularer]![frmSearchGeneral].[FindUV] & "*"
        AND tblRecord.RecDate Between 
            IIf([Forms]![frmSearchGeneral].[FindDateStart] Is Null,#1/1/1000#    ,[Forms]![frmSearchGeneral].[FindDateStart]) 
            And IIf([Forms]![frmSearchGeneral].[FindDateEnd] Is Null,#12/31/9999# ,[Forms]![frmSearchGeneral].[FindDateEnd])
        AND tblPersons.PerFullName Like [Formularer]![frmSearchGeneral].[FindPerson] & "*"
        AND tblPersons.PerOfficielObserver Like [Formularer]![frmSearchGeneral].[FindOfficial] & "*"
        AND tblLocation.LocCountry Like [Formularer]![frmSearchGeneral].[FindCountry] & "*"
        AND tblLocation.LocProvince Like [Formularer]![frmSearchGeneral].[FindProvince] & "*"
        AND tblLocation.LocMuncipality Like [Formularer]![frmSearchGeneral].[FindMuncipality] & "*"
        AND tblLocation.LocNearestTown Like [Formularer]![frmSearchGeneral].[FindNearestTown] & "*"
        AND tblLocation.LocAreaName Like [Formularer]![frmSearchGeneral].[FindAreaName] & "*"
        AND tblFieldTrip.FiTPathNumber Like [Formularer]![frmSearchGeneral].[FindTrackNo] & "*"
        AND tblValidate.ValSCALP Like [Formularer]![frmSearchGeneral].[FindSCALP] & "*"
        AND tblRecord.RecValidateYes Like IIf([Formularer]![frmSearchGeneral].[FindValidate]=Yes     ,[Formularer]![frmSearchGeneral].[FindValidate],"*")
        AND tblRecord.RecScatYes Like IIf([Formularer]![frmSearchGeneral].[FindScat]=Yes     ,[Formularer]![frmSearchGeneral].[FindScat] ,"*")
        AND tblRecord.RecPreyYes Like IIf([Formularer]![frmSearchGeneral].[FindPrey]=Yes     ,[Formularer]![frmSearchGeneral].[FindPrey]     ,"*")
        AND tblRecord.RecOtherDNAYes Like IIf([Formularer]![frmSearchGeneral].[FindOtherDNA]=Yes     ,[Formularer]![frmSearchGeneral].[FindOtherDNA]     ,"*")
        AND tblRecord.RecSightingYes Like IIf([Formularer]![frmSearchGeneral].[FindSighting]=Yes     ,[Formularer]![frmSearchGeneral].[FindSighting]     ,"*")
        AND tblRecord.RecTrackYes Like IIf([Formularer]![frmSearchGeneral].[FindTrack]=Yes   ,[Formularer]![frmSearchGeneral].[FindTrack]    ,"*")
        AND tblRecord.RecHowlYes Like IIf([Formularer]![frmSearchGeneral].[FindHowl]=Yes     ,[Formularer]![frmSearchGeneral].[FindHowl]     ,"*")
        AND tblRecord.RecUrinBloodYes Like IIf([Formularer]![frmSearchGeneral].[FindUrinBlood]=Yes   ,[Formularer]![frmSearchGeneral].[FindUrinBlood]    ,"*")
        AND tblRecord.RecCameraYes Like IIf([Formularer]![frmSearchGeneral].[FindCamera]=Yes     ,[Formularer]![frmSearchGeneral].[FindCamera]   ,"*")
        AND tblValidate.ValInsideWolfZone Like IIf([Formularer]![frmSearchGeneral].[FindWolfZone]=Yes    ,[formularer]![frmSearchGeneral].[FindWolfZone]     ,"*")
        AND tblValidate.RefNoValSpecies Like [Formularer]![frmSearchGeneral].[FindValidateSpecies] & "*"
        AND tblPack.PackName Like [Formularer]![frmSearchGeneral].[FindPack] & "*"
        AND tblIndividual.Individual Like [Formularer]![frmSearchGeneral].[FindIndividual] & "*"
        AND tblTerritoryName.TerritoryName Like [formularer]![frmSearchGeneral].[FindTerritory] & "*"
        AND tblRecord.Deactivated =False
        AND Year[RecDate] Between 
            IIf([Forms]![frmSearchGeneral].[FindYearStart] Is Null,#1/1/1000# ,[Forms]![frmSearchGeneral].[FindYearStart]) 
            And IIf([Forms]![frmSearchGeneral].[FindYearEnd] Is Null,#12/31/9999#    ,[Forms]![frmSearchGeneral].[FindYearEnd])
        AND [Forms]![frmSearchGeneral].[FindYearSpecific] Is Null
    );

This doesn't solve your complexity issue with Access, but it does solve it for humans.

What I would do is note the comments I've made in your joins and make a subquery for each one of those comments where you do the following. Joining each of these subqueries together in one final query.

SELECT fields, you, need
FROM tblRecord
    /*Den Data*/
    INNER JOIN
        tblDen
        ON
            tblDen.KeyDen = tblRecord.RefNoDen
    INNER JOIN
        tblPersons AS tblPersons_9
        ON
            tblDen.RefNoWriterDen = tblPersons_9.KeyPersons

The parentheses surrounding each join are still required in MS Access (unfortunately). — Lee Mac, Mar 04 '19 at 18:34
[Access requires parentheses in the `FROM` clause for queries which include more than one join.](https://stackoverflow.com/a/20929533/77335) — HansUp, Mar 04 '19 at 19:30
Thanks for the kick in the right direction and the disentanglement of the query builders SQL. I will rewrite the query following your directions,seems that with a limitation on joins, subquerys is the way to go. — Christina Vedel-Smith, Mar 04 '19 at 20:23

Query too complex - Is access query builder messing up the sql?

1 Answers1