Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query problem (2000)

    Hi,
    I have the following query (SQL view):

    <font face="Georgia">SELECT TblPayments.*, TblCounsel.*, TblPayments.DateTime, DateSerial(Nz(Year([TblPayments.DateTime]),1999),Nz(Month([TblPayments.DateTime]),1),1) AS Expr1
    FROM TblCounsel INNER JOIN TblPayments ON TblCounsel.AdvocateID = TblPayments.Advocate
    WHERE (((DateSerial(Nz(Year([TblPayments.DateTime]),1999),Nz(Month([TblPayments.DateTime]),1),1))=DateSerial([Forms]![FrmReports]![cboYear],[Forms]![FrmReports]![cboMonth],1)))
    WITH OWNERACCESS OPTION;</font face=georgia>

    It displays the message that the query is too complicated. What am I doing wrong?
    I suspect its to do with the field TblPayments.DateTime, which is in TblPayments, which extracts the details of when an entry is amended.
    I basically have two Combo boxes that are month and year, from this I select a date and then produce a report for this date.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query problem (2000)

    Try the following SQL:

    SELECT TblPayments.*, TblCounsel.*
    FROM TblCounsel INNER JOIN TblPayments ON TblCounsel.AdvocateID = TblPayments.Advocate
    WHERE Nz(Year([TblPayments.DateTime]),1999) = [Forms]![FrmReports]![cboYear] And Nz(Month([TblPayments.DateTime]),1) = [Forms]![FrmReports]![cboMonth]
    WITH OWNERACCESS OPTION;

    By splitting the condition into separate conditions for year and month, the calculation becomes easier for Access/SQL to manage.

Posting Permissions

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