# Thread: Query problem (2000)

1. ## 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. ## 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
•