I don't know how to describe the problem
I have a sql string that is working
Code:
CardSQL = "SELECT RoundID, BreedingCard.PairNumberID, Coupled, DateFirstEgg, TotalEggs, DateHatch, QtyHatched, " _
     & "DateRinged, DateFlyOut, CageNumber, RingSize, " _
     & "Male.BirdID, Male.BirdName, Male.BirdColor, Male.BirdOrigin, " _
     & "Female.BirdID, Female.BirdName, Female.BirdColor, Female.BirdOrigin, Species " _
     & "FROM Birds AS Male, Birds As Female, Birds AS Young1, Birds AS Young2, " _
     & "BirdPairing, Species, BreedingCard, Misc " _
     & "WHERE BirdPairing.MaleID = Male.BirdID And BirdPairing.FemaleID = Female.BirdID And " _
     & "BirdPairing.SpeciesID = Species.SpeciesID And BreedingCard.PairNumberID = BirdPairing.PairNumberID"
I also needed fields( Young1, Young2, Young3, Young4, Young5, Young6, Young7, Young8, Young9, Young10 from table breedingcard)
I've put them in the string
so far so good still working
Code:
CardSQL = "SELECT RoundID, BreedingCard.PairNumberID, Coupled, DateFirstEgg, TotalEggs, DateHatch, QtyHatched, " _
     & "DateRinged, DateFlyOut, CageNumber, RingSize, " _
     & "Young1, Young2, Young3, Young4, Young5, Young6, Young7, Young8, Young9, Young10, " _
     & "Male.BirdID, Male.BirdName, Male.BirdColor, Male.BirdOrigin, " _
     & "Female.BirdID, Female.BirdName, Female.BirdColor, Female.BirdOrigin, Species " _
     & "FROM Birds AS Male, Birds As Female, Birds AS Young1, Birds AS Young2, " _
     & "BirdPairing, Species, BreedingCard, Misc " _
     & "WHERE BirdPairing.MaleID = Male.BirdID And BirdPairing.FemaleID = Female.BirdID And " _
     & "BirdPairing.SpeciesID = Species.SpeciesID And BreedingCard.PairNumberID = BirdPairing.PairNumberID"
Then I tried to do this that is working except when Young1 in the table is empty ore 0 then it won't work
( & "Young1.BirdID, Young1.BirdName, Young1.BirdColor, Gender, " _
& "Young2.BirdID, Young2.BirdName, Young2.BirdColor, Gender, " _)

when I leave the last part of ( & "BreedingCard.Young1 = Young1.BirdID And Young1.GenderID = Misc.MiscID AND " _
& "BreedingCard.Young2 = Young2.BirdID And Young2.GenderID = Misc.MiscID")
it works again but the result is wrong

Code:
CardSQL = "SELECT RoundID, BreedingCard.PairNumberID, Coupled, DateFirstEgg, TotalEggs, DateHatch, QtyHatched, " _
     & "DateRinged, DateFlyOut, CageNumber, RingSize, " _
     & "Young1, Young2, Young3, Young4, Young5, Young6, Young7, Young8, Young9, Young10, " _
     & "Young1.BirdID, Young1.BirdName, Young1.BirdColor, Gender, " _
     & "Young2.BirdID, Young2.BirdName, Young2.BirdColor, Gender, " _
     & "Male.BirdID, Male.BirdName, Male.BirdColor, Male.BirdOrigin, " _
     & "Female.BirdID, Female.BirdName, Female.BirdColor, Female.BirdOrigin, Species " _
     & "FROM Birds AS Male, Birds As Female, Birds AS Young1, Birds AS Young2, " _
     & "BirdPairing, Species, BreedingCard, Misc " _
     & "WHERE BirdPairing.MaleID = Male.BirdID And BirdPairing.FemaleID = Female.BirdID And " _
     & "BirdPairing.SpeciesID = Species.SpeciesID And BreedingCard.PairNumberID = BirdPairing.PairNumberID And " _
     & "BreedingCard.Young1 = Young1.BirdID And Young1.GenderID = Misc.MiscID AND " _
     & "BreedingCard.Young2 = Young2.BirdID And Young2.GenderID = Misc.MiscID"
I hope this is enough info else let me know