Results 1 to 3 of 3

Thread: SQL to string

  1. #1
    Join Date
    Nov 2003
    Posts
    23

    Unanswered: SQL to string

    I'm trying to convert the following SQL to a string for use as source criteria for a recordset. The problem is that the SQL returns 14 records and the recordset only returns 1 record. Can anyone see where the problem may be?

    SELECT Property.[Property Name], [Super Contact].[SC CCI Name] AS [Property Manager], [Correspondence Contact Index].[CCI Issue Date] AS [Issue Date], pmps.[PMP Type] AS [Type of Schedule], Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7) AS [Weeks Outstanding], PMP.Item, PMP.Location, [PMP History].[Survey Date], Correspondence.[Correspondence Ref], Max([Response Log].[RL Date Recieved]) AS [MaxOfRL Date Recieved]
    FROM (pmps RIGHT JOIN (PMP LEFT JOIN Property ON PMP.[Property Idnum]=Property.[Property Idnum]) ON pmps.[PMP Idnum]=PMP.Type) RIGHT JOIN ([Super Contact] RIGHT JOIN ([Contact Property Index] RIGHT JOIN (Correspondence RIGHT JOIN (([Correspondence Contact Index] LEFT JOIN [PMP History] ON [Correspondence Contact Index].[CCI Idnum]=[PMP History].[Correspondence Idnum]) LEFT JOIN [Response Log] ON [Correspondence Contact Index].[CCI Idnum]=[Response Log].[RL CCI]) ON Correspondence.[Correspondence Idnum]=[Correspondence Contact Index].[CCI Correspondence]) ON [Contact Property Index].[CPI Idnum]=[Correspondence Contact Index].[CCI CPI]) ON [Super Contact].[SC Idnum]=[Contact Property Index].[CPI Contact]) ON PMP.Idnum=[PMP History].[PMP Idnum]
    WHERE (((Property.[Property Sold])<>"Yes" Or (Property.[Property Sold])<>"Yes"))
    GROUP BY Property.[Property Name], [Super Contact].[SC CCI Name], [Correspondence Contact Index].[CCI Issue Date], pmps.[PMP Type], Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7), PMP.Item, PMP.Location, [PMP History].[Survey Date], Correspondence.[Correspondence Ref], CLng(DCount("idnum","action point","ignore='yes' AND [pmp history idnum]= " & [pmp history].idnum)), PMP.Deferred, CLng(DCount("idnum","action point","[pmp history idnum]= " & [pmp history].idnum))
    HAVING ((([Correspondence Contact Index].[CCI Issue Date])<#10/1/2003# Or ([Correspondence Contact Index].[CCI Issue Date]) Is Null) And ((pmps.[PMP Type])<>"dda audit") And ((Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7))>4) And ((Max([Response Log].[RL Date Recieved])) Is Null Or (Max([Response Log].[RL Date Recieved]))>=#10/1/2003#) And ((PMP.Deferred)<>"Yes") And ((CLng(DCount("idnum","action point","ignore='yes' AND [pmp history idnum]= " & [pmp history].idnum)))=0 Or (CLng(DCount("idnum","action point","ignore='yes' AND [pmp history idnum]= " & [pmp history].idnum)))<CLng(DCount("idnum","action point","[pmp history idnum]= " & [pmp history].idnum)))) Or ((([Correspondence Contact Index].[CCI Issue Date])<#10/1/2003# Or ([Correspondence Contact Index].[CCI Issue Date]) Is Null) And ((pmps.[PMP Type])="dda audit") And ((Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7))>8) And ((Max([Response Log].[RL Date Recieved])) Is Null Or (Max([Response Log].[RL Date Recieved]))>=#10/1/2003#) And ((PMP.Deferred)<>"Yes") And ((CLng(DCount("idnum","action point","ignore='yes' AND [pmp history idnum]= " & [pmp history].idnum)))=0 Or (CLng(DCount("idnum","action point","ignore='yes' AND [pmp history idnum]= " & [pmp history].idnum)))<CLng(DCount("idnum","action point","[pmp history idnum]= " & [pmp history].idnum))))
    ORDER BY Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7) DESC;


    The string version looks like this.

    Set APsRS = CurrentDb.OpenRecordset("SELECT Property.[Property Name], [Super Contact].[SC CCI Name] AS [Property Manager], [Correspondence Contact Index].[CCI Issue Date] AS [Issue Date], pmps.[PMP Type] AS [Type of Schedule], " & _
    "Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7) AS [Weeks Outstanding], PMP.Item, PMP.Location, [PMP History].[Survey Date], Correspondence.[Correspondence Ref], " & _
    "Max([Response Log].[RL Date Recieved]) As [MaxOfRL Date Recieved] " & _
    "FROM (pmps RIGHT JOIN (PMP LEFT JOIN Property ON PMP.[Property Idnum]=Property.[Property Idnum]) ON pmps.[PMP Idnum]=PMP.Type) RIGHT JOIN ([Super Contact] RIGHT JOIN ([Contact Property Index] " & _
    "RIGHT JOIN (Correspondence RIGHT JOIN (([Correspondence Contact Index] LEFT JOIN [PMP History] ON [Correspondence Contact Index].[CCI Idnum]=[PMP History].[Correspondence Idnum]) LEFT JOIN [Response Log] " & _
    "ON [Correspondence Contact Index].[CCI Idnum]=[Response Log].[RL CCI]) ON Correspondence.[Correspondence Idnum]=[Correspondence Contact Index].[CCI Correspondence]) " & _
    "ON [Contact Property Index].[CPI Idnum]=[Correspondence Contact Index].[CCI CPI]) ON [Super Contact].[SC Idnum]=[Contact Property Index].[CPI Contact]) ON PMP.Idnum=[PMP History].[PMP Idnum] " & _
    "WHERE (((Property.[Property Sold]) <> 'Yes' Or (Property.[Property Sold]) <> 'Yes')) " & _
    "GROUP BY Property.[Property Name], [Super Contact].[SC CCI Name], [Correspondence Contact Index].[CCI Issue Date], pmps.[PMP Type], Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7), PMP.Item, PMP.Location, " & _
    "[PMP History].[Survey Date], Correspondence.[Correspondence Ref], CLng(DCount('idnum','action point','ignore=yes AND [pmp history idnum]= ' & [pmp history].idnum)), PMP.Deferred, " & _
    "CLng(DCount('idnum','action point','[pmp history idnum]= ' & [pmp history].idnum)) " & _
    "HAVING ((([Correspondence Contact Index].[CCI Issue Date])<#10/1/2003# " & _
    "Or ([Correspondence Contact Index].[CCI Issue Date]) Is Null) And ((pmps.[PMP Type])<>'dda audit') " & _
    "And ((Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7))>4) " & _
    "And ((Max([Response Log].[RL Date Recieved])) Is Null Or (Max([Response Log].[RL Date Recieved]))>=#10/1/2003#) And ((PMP.Deferred)<>'Yes') " & _
    "And ((CLng(DCount('idnum','action point','ignore=yes AND [pmp history idnum]= ' & [pmp history].idnum)))=0 " & _
    "Or (CLng(DCount('idnum','action point','ignore=yes AND [pmp history idnum]= ' & [pmp history].idnum)))<CLng(DCount('idnum','action point','[pmp history idnum]= ' & [pmp history].idnum)))) " & _
    "Or ((([Correspondence Contact Index].[CCI Issue Date])<#10/1/2003# " & _
    "Or ([Correspondence Contact Index].[CCI Issue Date]) Is Null) " & _
    "And ((pmps.[PMP Type])='dda audit') And ((Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7))>8) " & _
    "And ((Max([Response Log].[RL Date Recieved])) Is Null " & _
    "Or (Max([Response Log].[RL Date Recieved]))>=#10/1/2003#) And ((PMP.Deferred)<>'Yes') " & _
    "And ((CLng(DCount('idnum','action point','ignore=yes AND [pmp history idnum]= ' & [pmp history].idnum)))=0 " & _
    "Or (CLng(DCount('idnum','action point','ignore=yes AND [pmp history idnum]= ' & [pmp history].idnum)))<CLng(DCount('idnum','action point','[pmp history idnum]= ' & [pmp history].idnum)))) " & _
    "ORDER BY Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7) DESC;", dbOpenDynaset)


    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: SQL to string

    Originally posted by trager
    I'm trying to convert the following SQL to a string for use as source criteria for a recordset. The problem is that the SQL returns 14 records and the recordset only returns 1 record. Can anyone see where the problem may be?

    SELECT Property.[Property Name], [Super Contact].[SC CCI Name] AS [Property Manager], [Correspondence Contact Index].[CCI Issue Date] AS [Issue Date], pmps.[PMP Type] AS [Type of Schedule], Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7) AS [Weeks Outstanding], PMP.Item, PMP.Location, [PMP History].[Survey Date], Correspondence.[Correspondence Ref], Max([Response Log].[RL Date Recieved]) AS [MaxOfRL Date Recieved]
    FROM (pmps RIGHT JOIN (PMP LEFT JOIN Property ON PMP.[Property Idnum]=Property.[Property Idnum]) ON pmps.[PMP Idnum]=PMP.Type) RIGHT JOIN ([Super Contact] RIGHT JOIN ([Contact Property Index] RIGHT JOIN (Correspondence RIGHT JOIN (([Correspondence Contact Index] LEFT JOIN [PMP History] ON [Correspondence Contact Index].[CCI Idnum]=[PMP History].[Correspondence Idnum]) LEFT JOIN [Response Log] ON [Correspondence Contact Index].[CCI Idnum]=[Response Log].[RL CCI]) ON Correspondence.[Correspondence Idnum]=[Correspondence Contact Index].[CCI Correspondence]) ON [Contact Property Index].[CPI Idnum]=[Correspondence Contact Index].[CCI CPI]) ON [Super Contact].[SC Idnum]=[Contact Property Index].[CPI Contact]) ON PMP.Idnum=[PMP History].[PMP Idnum]
    WHERE (((Property.[Property Sold])<>"Yes" Or (Property.[Property Sold])<>"Yes"))
    GROUP BY Property.[Property Name], [Super Contact].[SC CCI Name], [Correspondence Contact Index].[CCI Issue Date], pmps.[PMP Type], Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7), PMP.Item, PMP.Location, [PMP History].[Survey Date], Correspondence.[Correspondence Ref], CLng(DCount("idnum","action point","ignore='yes' AND [pmp history idnum]= " & [pmp history].idnum)), PMP.Deferred, CLng(DCount("idnum","action point","[pmp history idnum]= " & [pmp history].idnum))
    HAVING ((([Correspondence Contact Index].[CCI Issue Date])<#10/1/2003# Or ([Correspondence Contact Index].[CCI Issue Date]) Is Null) And ((pmps.[PMP Type])<>"dda audit") And ((Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7))>4) And ((Max([Response Log].[RL Date Recieved])) Is Null Or (Max([Response Log].[RL Date Recieved]))>=#10/1/2003#) And ((PMP.Deferred)<>"Yes") And ((CLng(DCount("idnum","action point","ignore='yes' AND [pmp history idnum]= " & [pmp history].idnum)))=0 Or (CLng(DCount("idnum","action point","ignore='yes' AND [pmp history idnum]= " & [pmp history].idnum)))<CLng(DCount("idnum","action point","[pmp history idnum]= " & [pmp history].idnum)))) Or ((([Correspondence Contact Index].[CCI Issue Date])<#10/1/2003# Or ([Correspondence Contact Index].[CCI Issue Date]) Is Null) And ((pmps.[PMP Type])="dda audit") And ((Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7))>8) And ((Max([Response Log].[RL Date Recieved])) Is Null Or (Max([Response Log].[RL Date Recieved]))>=#10/1/2003#) And ((PMP.Deferred)<>"Yes") And ((CLng(DCount("idnum","action point","ignore='yes' AND [pmp history idnum]= " & [pmp history].idnum)))=0 Or (CLng(DCount("idnum","action point","ignore='yes' AND [pmp history idnum]= " & [pmp history].idnum)))<CLng(DCount("idnum","action point","[pmp history idnum]= " & [pmp history].idnum))))
    ORDER BY Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7) DESC;


    The string version looks like this.

    Set APsRS = CurrentDb.OpenRecordset("SELECT Property.[Property Name], [Super Contact].[SC CCI Name] AS [Property Manager], [Correspondence Contact Index].[CCI Issue Date] AS [Issue Date], pmps.[PMP Type] AS [Type of Schedule], " & _
    "Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7) AS [Weeks Outstanding], PMP.Item, PMP.Location, [PMP History].[Survey Date], Correspondence.[Correspondence Ref], " & _
    "Max([Response Log].[RL Date Recieved]) As [MaxOfRL Date Recieved] " & _
    "FROM (pmps RIGHT JOIN (PMP LEFT JOIN Property ON PMP.[Property Idnum]=Property.[Property Idnum]) ON pmps.[PMP Idnum]=PMP.Type) RIGHT JOIN ([Super Contact] RIGHT JOIN ([Contact Property Index] " & _
    "RIGHT JOIN (Correspondence RIGHT JOIN (([Correspondence Contact Index] LEFT JOIN [PMP History] ON [Correspondence Contact Index].[CCI Idnum]=[PMP History].[Correspondence Idnum]) LEFT JOIN [Response Log] " & _
    "ON [Correspondence Contact Index].[CCI Idnum]=[Response Log].[RL CCI]) ON Correspondence.[Correspondence Idnum]=[Correspondence Contact Index].[CCI Correspondence]) " & _
    "ON [Contact Property Index].[CPI Idnum]=[Correspondence Contact Index].[CCI CPI]) ON [Super Contact].[SC Idnum]=[Contact Property Index].[CPI Contact]) ON PMP.Idnum=[PMP History].[PMP Idnum] " & _
    "WHERE (((Property.[Property Sold]) <> 'Yes' Or (Property.[Property Sold]) <> 'Yes')) " & _
    "GROUP BY Property.[Property Name], [Super Contact].[SC CCI Name], [Correspondence Contact Index].[CCI Issue Date], pmps.[PMP Type], Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7), PMP.Item, PMP.Location, " & _
    "[PMP History].[Survey Date], Correspondence.[Correspondence Ref], CLng(DCount('idnum','action point','ignore=yes AND [pmp history idnum]= ' & [pmp history].idnum)), PMP.Deferred, " & _
    "CLng(DCount('idnum','action point','[pmp history idnum]= ' & [pmp history].idnum)) " & _
    "HAVING ((([Correspondence Contact Index].[CCI Issue Date])<#10/1/2003# " & _
    "Or ([Correspondence Contact Index].[CCI Issue Date]) Is Null) And ((pmps.[PMP Type])<>'dda audit') " & _
    "And ((Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7))>4) " & _
    "And ((Max([Response Log].[RL Date Recieved])) Is Null Or (Max([Response Log].[RL Date Recieved]))>=#10/1/2003#) And ((PMP.Deferred)<>'Yes') " & _
    "And ((CLng(DCount('idnum','action point','ignore=yes AND [pmp history idnum]= ' & [pmp history].idnum)))=0 " & _
    "Or (CLng(DCount('idnum','action point','ignore=yes AND [pmp history idnum]= ' & [pmp history].idnum)))<CLng(DCount('idnum','action point','[pmp history idnum]= ' & [pmp history].idnum)))) " & _
    "Or ((([Correspondence Contact Index].[CCI Issue Date])<#10/1/2003# " & _
    "Or ([Correspondence Contact Index].[CCI Issue Date]) Is Null) " & _
    "And ((pmps.[PMP Type])='dda audit') And ((Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7))>8) " & _
    "And ((Max([Response Log].[RL Date Recieved])) Is Null " & _
    "Or (Max([Response Log].[RL Date Recieved]))>=#10/1/2003#) And ((PMP.Deferred)<>'Yes') " & _
    "And ((CLng(DCount('idnum','action point','ignore=yes AND [pmp history idnum]= ' & [pmp history].idnum)))=0 " & _
    "Or (CLng(DCount('idnum','action point','ignore=yes AND [pmp history idnum]= ' & [pmp history].idnum)))<CLng(DCount('idnum','action point','[pmp history idnum]= ' & [pmp history].idnum)))) " & _
    "ORDER BY Int((#10/1/2003#-[correspondence contact index].[cci issue date])/7) DESC;", dbOpenDynaset)


    Thanks
    Are you sure the recordset only returns 1 record?
    There are sometimes people who think so too, but they forget to browse through their recordset.
    A recordset only shows one record at a time and using the MoveNext-statement you go to the following record.

  3. #3
    Join Date
    Nov 2003
    Posts
    23
    I got a bit paranoid about making a silly mistake like that but I checked thoroughly.

    Definitely returns only 1 record viewable with excels copyfromrecordset function plus I performed a recordcount from the end of the recordset so definitely only 1 record.

    I think I have cured it now, I'm basing the recordset on a querydef of the original query, the one which returns 14 records. So unless you know whats wrong with the string this request is now closed.


    Thanks for the input artemide.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •