Using Parameters when using an expression

E

Em D

I am trying to get a list of entries that are between two dates that I input
on a form. However, the date in the query that it is looking at is a
calculated date "Expr 1: Sum(tblMemos!MemoDate+tblMemos!FollowUpDays)". I
want to keep this expression to tell me the date for that entry but I want to
only show dates within a certain range.

Below is the SQL:

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Sum(tblMemos!MemoDate+tblMemos!FollowUpDays) AS [Expr
1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
WHERE ((([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays]) Between
[Forms]![frmPopUp_Memos]![Date 1] And [Forms]![frmPopUp_Memos]![Date 2]))
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>Sum([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays])
And (tblMemos.ReceivedDate) Is Not Null));
 
K

KARL DEWEY

You did not pose a question or say what was the results that you did not want.

I think you need to use this --
Expr 1: DateAdd("d", [tblMemos].[FollowUpDays], [tblMemos].[MemoDate])

and this --
HAVING ([tblMemos].[MainSubject] Is Not Null) AND
([tblMemos].[ReceivedDate] > DateAdd("d", [tblMemos].[FollowUpDays],
[tblMemos].[MemoDate])));
 
E

Em D

Sorry about not filling you in on the results I was getting before.
Initially, I was getting the error message that said my expression was too
complex. Now, I've changed some things around (new SQL below) and it isn't
returning any data. The result I'm trying to achieve is to only pull data
based on Expr 1 from a range of dates I provide.

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
AS [Expr 1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
And (tblMemos.ReceivedDate) Is Not Null)) AND
(DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays) Between [Enter date 1]
And [Enter date 2]);


KARL DEWEY said:
You did not pose a question or say what was the results that you did not want.

I think you need to use this --
Expr 1: DateAdd("d", [tblMemos].[FollowUpDays], [tblMemos].[MemoDate])

and this --
HAVING ([tblMemos].[MainSubject] Is Not Null) AND
([tblMemos].[ReceivedDate] > DateAdd("d", [tblMemos].[FollowUpDays],
[tblMemos].[MemoDate])));


--
Build a little, test a little.


Em D said:
I am trying to get a list of entries that are between two dates that I input
on a form. However, the date in the query that it is looking at is a
calculated date "Expr 1: Sum(tblMemos!MemoDate+tblMemos!FollowUpDays)". I
want to keep this expression to tell me the date for that entry but I want to
only show dates within a certain range.

Below is the SQL:

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Sum(tblMemos!MemoDate+tblMemos!FollowUpDays) AS [Expr
1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
WHERE ((([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays]) Between
[Forms]![frmPopUp_Memos]![Date 1] And [Forms]![frmPopUp_Memos]![Date 2]))
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>Sum([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays])
And (tblMemos.ReceivedDate) Is Not Null));
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try this --
SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",[tblMemos].[MemoDate],
[tblMemos].[FollowUpDays]) AS [Expr 1]
FROM tblMemos LEFT JOIN tblAssociateList ON tblMemos.FileNumber =
tblAssociateList.FileNumber
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d",[tblMemos].[MemoDate], [tblMemos].[FollowUpDays]) AND
Click to expand...
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


Em D said:
Sorry about not filling you in on the results I was getting before.
Initially, I was getting the error message that said my expression was too
complex. Now, I've changed some things around (new SQL below) and it isn't
returning any data. The result I'm trying to achieve is to only pull data
based on Expr 1 from a range of dates I provide.

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
AS [Expr 1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
And (tblMemos.ReceivedDate) Is Not Null)) AND
(DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays) Between [Enter date 1]
And [Enter date 2]);


KARL DEWEY said:
You did not pose a question or say what was the results that you did not want.

I think you need to use this --
Expr 1: DateAdd("d", [tblMemos].[FollowUpDays], [tblMemos].[MemoDate])

and this --
HAVING ([tblMemos].[MainSubject] Is Not Null) AND
([tblMemos].[ReceivedDate] > DateAdd("d", [tblMemos].[FollowUpDays],
[tblMemos].[MemoDate])));


--
Build a little, test a little.


Em D said:
I am trying to get a list of entries that are between two dates that I input
on a form. However, the date in the query that it is looking at is a
calculated date "Expr 1: Sum(tblMemos!MemoDate+tblMemos!FollowUpDays)". I
want to keep this expression to tell me the date for that entry but I want to
only show dates within a certain range.

Below is the SQL:

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Sum(tblMemos!MemoDate+tblMemos!FollowUpDays) AS [Expr
1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
WHERE ((([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays]) Between
[Forms]![frmPopUp_Memos]![Date 1] And [Forms]![frmPopUp_Memos]![Date 2]))
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>Sum([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays])
And (tblMemos.ReceivedDate) Is Not Null));
Click to expand...
Click to expand...
Click to expand...
 
E

Em D

This is returning a Syntax Error message.

KARL DEWEY said:
Try this --
SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",[tblMemos].[MemoDate],
[tblMemos].[FollowUpDays]) AS [Expr 1]
FROM tblMemos LEFT JOIN tblAssociateList ON tblMemos.FileNumber =
tblAssociateList.FileNumber
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d",[tblMemos].[MemoDate], [tblMemos].[FollowUpDays]) AND
Click to expand...
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


Em D said:
Sorry about not filling you in on the results I was getting before.
Initially, I was getting the error message that said my expression was too
complex. Now, I've changed some things around (new SQL below) and it isn't
returning any data. The result I'm trying to achieve is to only pull data
based on Expr 1 from a range of dates I provide.

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
AS [Expr 1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
And (tblMemos.ReceivedDate) Is Not Null)) AND
(DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays) Between [Enter date 1]
And [Enter date 2]);


KARL DEWEY said:
You did not pose a question or say what was the results that you did not want.

I think you need to use this --
Expr 1: DateAdd("d", [tblMemos].[FollowUpDays], [tblMemos].[MemoDate])

and this --
HAVING ([tblMemos].[MainSubject] Is Not Null) AND
([tblMemos].[ReceivedDate] > DateAdd("d", [tblMemos].[FollowUpDays],
[tblMemos].[MemoDate])));


--
Build a little, test a little.


:

I am trying to get a list of entries that are between two dates that I input
on a form. However, the date in the query that it is looking at is a
calculated date "Expr 1: Sum(tblMemos!MemoDate+tblMemos!FollowUpDays)". I
want to keep this expression to tell me the date for that entry but I want to
only show dates within a certain range.

Below is the SQL:

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Sum(tblMemos!MemoDate+tblMemos!FollowUpDays) AS [Expr
1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
WHERE ((([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays]) Between
[Forms]![frmPopUp_Memos]![Date 1] And [Forms]![frmPopUp_Memos]![Date 2]))
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>Sum([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays])
And (tblMemos.ReceivedDate) Is Not Null));
Click to expand...
Click to expand...
Click to expand...
 
E

Em D

This is returning a Syntax Error message.

KARL DEWEY said:
Try this --
SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",[tblMemos].[MemoDate],
[tblMemos].[FollowUpDays]) AS [Expr 1]
FROM tblMemos LEFT JOIN tblAssociateList ON tblMemos.FileNumber =
tblAssociateList.FileNumber
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d",[tblMemos].[MemoDate], [tblMemos].[FollowUpDays]) AND
Click to expand...
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


Em D said:
Sorry about not filling you in on the results I was getting before.
Initially, I was getting the error message that said my expression was too
complex. Now, I've changed some things around (new SQL below) and it isn't
returning any data. The result I'm trying to achieve is to only pull data
based on Expr 1 from a range of dates I provide.

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
AS [Expr 1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
And (tblMemos.ReceivedDate) Is Not Null)) AND
(DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays) Between [Enter date 1]
And [Enter date 2]);


KARL DEWEY said:
You did not pose a question or say what was the results that you did not want.

I think you need to use this --
Expr 1: DateAdd("d", [tblMemos].[FollowUpDays], [tblMemos].[MemoDate])

and this --
HAVING ([tblMemos].[MainSubject] Is Not Null) AND
([tblMemos].[ReceivedDate] > DateAdd("d", [tblMemos].[FollowUpDays],
[tblMemos].[MemoDate])));


--
Build a little, test a little.


:

I am trying to get a list of entries that are between two dates that I input
on a form. However, the date in the query that it is looking at is a
calculated date "Expr 1: Sum(tblMemos!MemoDate+tblMemos!FollowUpDays)". I
want to keep this expression to tell me the date for that entry but I want to
only show dates within a certain range.

Below is the SQL:

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Sum(tblMemos!MemoDate+tblMemos!FollowUpDays) AS [Expr
1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
WHERE ((([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays]) Between
[Forms]![frmPopUp_Memos]![Date 1] And [Forms]![frmPopUp_Memos]![Date 2]))
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>Sum([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays])
And (tblMemos.ReceivedDate) Is Not Null));
Click to expand...
Click to expand...
Click to expand...
 
E

Em D

This is returning a Syntax Error message.

KARL DEWEY said:
Try this --
SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",[tblMemos].[MemoDate],
[tblMemos].[FollowUpDays]) AS [Expr 1]
FROM tblMemos LEFT JOIN tblAssociateList ON tblMemos.FileNumber =
tblAssociateList.FileNumber
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d",[tblMemos].[MemoDate], [tblMemos].[FollowUpDays]) AND
Click to expand...
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


Em D said:
Sorry about not filling you in on the results I was getting before.
Initially, I was getting the error message that said my expression was too
complex. Now, I've changed some things around (new SQL below) and it isn't
returning any data. The result I'm trying to achieve is to only pull data
based on Expr 1 from a range of dates I provide.

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
AS [Expr 1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
And (tblMemos.ReceivedDate) Is Not Null)) AND
(DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays) Between [Enter date 1]
And [Enter date 2]);


KARL DEWEY said:
You did not pose a question or say what was the results that you did not want.

I think you need to use this --
Expr 1: DateAdd("d", [tblMemos].[FollowUpDays], [tblMemos].[MemoDate])

and this --
HAVING ([tblMemos].[MainSubject] Is Not Null) AND
([tblMemos].[ReceivedDate] > DateAdd("d", [tblMemos].[FollowUpDays],
[tblMemos].[MemoDate])));


--
Build a little, test a little.


:

I am trying to get a list of entries that are between two dates that I input
on a form. However, the date in the query that it is looking at is a
calculated date "Expr 1: Sum(tblMemos!MemoDate+tblMemos!FollowUpDays)". I
want to keep this expression to tell me the date for that entry but I want to
only show dates within a certain range.

Below is the SQL:

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Sum(tblMemos!MemoDate+tblMemos!FollowUpDays) AS [Expr
1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
WHERE ((([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays]) Between
[Forms]![frmPopUp_Memos]![Date 1] And [Forms]![frmPopUp_Memos]![Date 2]))
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>Sum([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays])
And (tblMemos.ReceivedDate) Is Not Null));
Click to expand...
Click to expand...
Click to expand...
 
E

Em D

This is returning a Syntax Error message.

KARL DEWEY said:
Try this --
SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",[tblMemos].[MemoDate],
[tblMemos].[FollowUpDays]) AS [Expr 1]
FROM tblMemos LEFT JOIN tblAssociateList ON tblMemos.FileNumber =
tblAssociateList.FileNumber
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d",[tblMemos].[MemoDate], [tblMemos].[FollowUpDays]) AND
Click to expand...
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


Em D said:
Sorry about not filling you in on the results I was getting before.
Initially, I was getting the error message that said my expression was too
complex. Now, I've changed some things around (new SQL below) and it isn't
returning any data. The result I'm trying to achieve is to only pull data
based on Expr 1 from a range of dates I provide.

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
AS [Expr 1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
And (tblMemos.ReceivedDate) Is Not Null)) AND
(DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays) Between [Enter date 1]
And [Enter date 2]);


KARL DEWEY said:
You did not pose a question or say what was the results that you did not want.

I think you need to use this --
Expr 1: DateAdd("d", [tblMemos].[FollowUpDays], [tblMemos].[MemoDate])

and this --
HAVING ([tblMemos].[MainSubject] Is Not Null) AND
([tblMemos].[ReceivedDate] > DateAdd("d", [tblMemos].[FollowUpDays],
[tblMemos].[MemoDate])));


--
Build a little, test a little.


:

I am trying to get a list of entries that are between two dates that I input
on a form. However, the date in the query that it is looking at is a
calculated date "Expr 1: Sum(tblMemos!MemoDate+tblMemos!FollowUpDays)". I
want to keep this expression to tell me the date for that entry but I want to
only show dates within a certain range.

Below is the SQL:

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Sum(tblMemos!MemoDate+tblMemos!FollowUpDays) AS [Expr
1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
WHERE ((([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays]) Between
[Forms]![frmPopUp_Memos]![Date 1] And [Forms]![frmPopUp_Memos]![Date 2]))
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>Sum([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays])
And (tblMemos.ReceivedDate) Is Not Null));
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

What is the rest of the message?

Try running from SQL View and see what it highlights.

--
Build a little, test a little.


Em D said:
This is returning a Syntax Error message.

KARL DEWEY said:
Try this --
SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",[tblMemos].[MemoDate],
[tblMemos].[FollowUpDays]) AS [Expr 1]
FROM tblMemos LEFT JOIN tblAssociateList ON tblMemos.FileNumber =
tblAssociateList.FileNumber
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d",[tblMemos].[MemoDate], [tblMemos].[FollowUpDays]) AND
Click to expand...
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


Em D said:
Sorry about not filling you in on the results I was getting before.
Initially, I was getting the error message that said my expression was too
complex. Now, I've changed some things around (new SQL below) and it isn't
returning any data. The result I'm trying to achieve is to only pull data
based on Expr 1 from a range of dates I provide.

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
AS [Expr 1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
And (tblMemos.ReceivedDate) Is Not Null)) AND
(DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays) Between [Enter date 1]
And [Enter date 2]);


:

You did not pose a question or say what was the results that you did not want.

I think you need to use this --
Expr 1: DateAdd("d", [tblMemos].[FollowUpDays], [tblMemos].[MemoDate])

and this --
HAVING ([tblMemos].[MainSubject] Is Not Null) AND
([tblMemos].[ReceivedDate] > DateAdd("d", [tblMemos].[FollowUpDays],
[tblMemos].[MemoDate])));


--
Build a little, test a little.


:

I am trying to get a list of entries that are between two dates that I input
on a form. However, the date in the query that it is looking at is a
calculated date "Expr 1: Sum(tblMemos!MemoDate+tblMemos!FollowUpDays)". I
want to keep this expression to tell me the date for that entry but I want to
only show dates within a certain range.

Below is the SQL:

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Sum(tblMemos!MemoDate+tblMemos!FollowUpDays) AS [Expr
1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
WHERE ((([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays]) Between
[Forms]![frmPopUp_Memos]![Date 1] And [Forms]![frmPopUp_Memos]![Date 2]))
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>Sum([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays])
And (tblMemos.ReceivedDate) Is Not Null));
Click to expand...
Click to expand...
Click to expand...
 
E

Em D

The message reads as follows: Syntax error in query expression
'(([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate] >
DateAdd ("d", [tblMemos].[MemoDate],[tblMemos].[FollowUpDays]) AND
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between CV'.

When I try to run it, the semi-colon at the end is highlighted.

KARL DEWEY said:
What is the rest of the message?

Try running from SQL View and see what it highlights.

--
Build a little, test a little.


Em D said:
This is returning a Syntax Error message.

KARL DEWEY said:
Try this --
SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",[tblMemos].[MemoDate],
[tblMemos].[FollowUpDays]) AS [Expr 1]
FROM tblMemos LEFT JOIN tblAssociateList ON tblMemos.FileNumber =
tblAssociateList.FileNumber
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d",[tblMemos].[MemoDate], [tblMemos].[FollowUpDays]) AND
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


:

Sorry about not filling you in on the results I was getting before.
Initially, I was getting the error message that said my expression was too
complex. Now, I've changed some things around (new SQL below) and it isn't
returning any data. The result I'm trying to achieve is to only pull data
based on Expr 1 from a range of dates I provide.

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
AS [Expr 1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
And (tblMemos.ReceivedDate) Is Not Null)) AND
(DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays) Between [Enter date 1]
And [Enter date 2]);


:

You did not pose a question or say what was the results that you did not want.

I think you need to use this --
Expr 1: DateAdd("d", [tblMemos].[FollowUpDays], [tblMemos].[MemoDate])

and this --
HAVING ([tblMemos].[MainSubject] Is Not Null) AND
([tblMemos].[ReceivedDate] > DateAdd("d", [tblMemos].[FollowUpDays],
[tblMemos].[MemoDate])));


--
Build a little, test a little.


:

I am trying to get a list of entries that are between two dates that I input
on a form. However, the date in the query that it is looking at is a
calculated date "Expr 1: Sum(tblMemos!MemoDate+tblMemos!FollowUpDays)". I
want to keep this expression to tell me the date for that entry but I want to
only show dates within a certain range.

Below is the SQL:

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Sum(tblMemos!MemoDate+tblMemos!FollowUpDays) AS [Expr
1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
WHERE ((([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays]) Between
[Forms]![frmPopUp_Memos]![Date 1] And [Forms]![frmPopUp_Memos]![Date 2]))
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>Sum([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays])
And (tblMemos.ReceivedDate) Is Not Null));
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

I found a couple of misplaced parenthesis in the WHERE statement.
Try this --
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d",[tblMemos].[MemoDate], [tblMemos].[FollowUpDays])) AND
Click to expand...
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


Em D said:
The message reads as follows: Syntax error in query expression
'(([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate] >
DateAdd ("d", [tblMemos].[MemoDate],[tblMemos].[FollowUpDays]) AND
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between CV'.

When I try to run it, the semi-colon at the end is highlighted.

KARL DEWEY said:
What is the rest of the message?

Try running from SQL View and see what it highlights.

--
Build a little, test a little.


Em D said:
This is returning a Syntax Error message.

:

Try this --
SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",[tblMemos].[MemoDate],
[tblMemos].[FollowUpDays]) AS [Expr 1]
FROM tblMemos LEFT JOIN tblAssociateList ON tblMemos.FileNumber =
tblAssociateList.FileNumber
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d",[tblMemos].[MemoDate], [tblMemos].[FollowUpDays]) AND
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


:

Sorry about not filling you in on the results I was getting before.
Initially, I was getting the error message that said my expression was too
complex. Now, I've changed some things around (new SQL below) and it isn't
returning any data. The result I'm trying to achieve is to only pull data
based on Expr 1 from a range of dates I provide.

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
AS [Expr 1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
And (tblMemos.ReceivedDate) Is Not Null)) AND
(DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays) Between [Enter date 1]
And [Enter date 2]);


:

You did not pose a question or say what was the results that you did not want.

I think you need to use this --
Expr 1: DateAdd("d", [tblMemos].[FollowUpDays], [tblMemos].[MemoDate])

and this --
HAVING ([tblMemos].[MainSubject] Is Not Null) AND
([tblMemos].[ReceivedDate] > DateAdd("d", [tblMemos].[FollowUpDays],
[tblMemos].[MemoDate])));


--
Build a little, test a little.


:

I am trying to get a list of entries that are between two dates that I input
on a form. However, the date in the query that it is looking at is a
calculated date "Expr 1: Sum(tblMemos!MemoDate+tblMemos!FollowUpDays)". I
want to keep this expression to tell me the date for that entry but I want to
only show dates within a certain range.

Below is the SQL:

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Sum(tblMemos!MemoDate+tblMemos!FollowUpDays) AS [Expr
1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
WHERE ((([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays]) Between
[Forms]![frmPopUp_Memos]![Date 1] And [Forms]![frmPopUp_Memos]![Date 2]))
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>Sum([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays])
And (tblMemos.ReceivedDate) Is Not Null));
Click to expand...
Click to expand...
Click to expand...
 
E

Em D

I am getting the same error and it's highlighting the semi-colon still.

KARL DEWEY said:
I found a couple of misplaced parenthesis in the WHERE statement.
Try this --
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d",[tblMemos].[MemoDate], [tblMemos].[FollowUpDays])) AND
Click to expand...
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


Em D said:
The message reads as follows: Syntax error in query expression
'(([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate] >
DateAdd ("d", [tblMemos].[MemoDate],[tblMemos].[FollowUpDays]) AND
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between CV'.

When I try to run it, the semi-colon at the end is highlighted.

KARL DEWEY said:
What is the rest of the message?

Try running from SQL View and see what it highlights.

--
Build a little, test a little.


:

This is returning a Syntax Error message.

:

Try this --
SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",[tblMemos].[MemoDate],
[tblMemos].[FollowUpDays]) AS [Expr 1]
FROM tblMemos LEFT JOIN tblAssociateList ON tblMemos.FileNumber =
tblAssociateList.FileNumber
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d",[tblMemos].[MemoDate], [tblMemos].[FollowUpDays]) AND
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


:

Sorry about not filling you in on the results I was getting before.
Initially, I was getting the error message that said my expression was too
complex. Now, I've changed some things around (new SQL below) and it isn't
returning any data. The result I'm trying to achieve is to only pull data
based on Expr 1 from a range of dates I provide.

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
AS [Expr 1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
And (tblMemos.ReceivedDate) Is Not Null)) AND
(DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays) Between [Enter date 1]
And [Enter date 2]);


:

You did not pose a question or say what was the results that you did not want.

I think you need to use this --
Expr 1: DateAdd("d", [tblMemos].[FollowUpDays], [tblMemos].[MemoDate])

and this --
HAVING ([tblMemos].[MainSubject] Is Not Null) AND
([tblMemos].[ReceivedDate] > DateAdd("d", [tblMemos].[FollowUpDays],
[tblMemos].[MemoDate])));


--
Build a little, test a little.


:

I am trying to get a list of entries that are between two dates that I input
on a form. However, the date in the query that it is looking at is a
calculated date "Expr 1: Sum(tblMemos!MemoDate+tblMemos!FollowUpDays)". I
want to keep this expression to tell me the date for that entry but I want to
only show dates within a certain range.

Below is the SQL:

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Sum(tblMemos!MemoDate+tblMemos!FollowUpDays) AS [Expr
1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
WHERE ((([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays]) Between
[Forms]![frmPopUp_Memos]![Date 1] And [Forms]![frmPopUp_Memos]![Date 2]))
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>Sum([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays])
And (tblMemos.ReceivedDate) Is Not Null));
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

[tblMemos].[FollowUpDays] and [tblMemos].[MemoDate] were reversed in the
DateAdd.
Try it now --
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d", [tblMemos].[FollowUpDays], [tblMemos].[MemoDate])) AND
Click to expand...
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[FollowUpDays], [tblMemos].[MemoDate] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


Em D said:
I am getting the same error and it's highlighting the semi-colon still.

KARL DEWEY said:
I found a couple of misplaced parenthesis in the WHERE statement.
Try this --
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d",[tblMemos].[MemoDate], [tblMemos].[FollowUpDays])) AND
Click to expand...
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


Em D said:
The message reads as follows: Syntax error in query expression
'(([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate] >
DateAdd ("d", [tblMemos].[MemoDate],[tblMemos].[FollowUpDays]) AND
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between CV'.

When I try to run it, the semi-colon at the end is highlighted.

:

What is the rest of the message?

Try running from SQL View and see what it highlights.

--
Build a little, test a little.


:

This is returning a Syntax Error message.

:

Try this --
SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",[tblMemos].[MemoDate],
[tblMemos].[FollowUpDays]) AS [Expr 1]
FROM tblMemos LEFT JOIN tblAssociateList ON tblMemos.FileNumber =
tblAssociateList.FileNumber
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d",[tblMemos].[MemoDate], [tblMemos].[FollowUpDays]) AND
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


:

Sorry about not filling you in on the results I was getting before.
Initially, I was getting the error message that said my expression was too
complex. Now, I've changed some things around (new SQL below) and it isn't
returning any data. The result I'm trying to achieve is to only pull data
based on Expr 1 from a range of dates I provide.

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
AS [Expr 1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
And (tblMemos.ReceivedDate) Is Not Null)) AND
(DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays) Between [Enter date 1]
And [Enter date 2]);


:

You did not pose a question or say what was the results that you did not want.

I think you need to use this --
Expr 1: DateAdd("d", [tblMemos].[FollowUpDays], [tblMemos].[MemoDate])

and this --
HAVING ([tblMemos].[MainSubject] Is Not Null) AND
([tblMemos].[ReceivedDate] > DateAdd("d", [tblMemos].[FollowUpDays],
[tblMemos].[MemoDate])));


--
Build a little, test a little.


:

I am trying to get a list of entries that are between two dates that I input
on a form. However, the date in the query that it is looking at is a
calculated date "Expr 1: Sum(tblMemos!MemoDate+tblMemos!FollowUpDays)". I
want to keep this expression to tell me the date for that entry but I want to
only show dates within a certain range.

Below is the SQL:

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Sum(tblMemos!MemoDate+tblMemos!FollowUpDays) AS [Expr
1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
WHERE ((([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays]) Between
[Forms]![frmPopUp_Memos]![Date 1] And [Forms]![frmPopUp_Memos]![Date 2]))
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>Sum([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays])
And (tblMemos.ReceivedDate) Is Not Null));
Click to expand...
Click to expand...
Click to expand...
 
E

Em D

Unfortunately it still doesn't like it. I'm getting the same error message
and it is still highlighting the semi-colon.

KARL DEWEY said:
[tblMemos].[FollowUpDays] and [tblMemos].[MemoDate] were reversed in the
DateAdd.
Try it now --
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d", [tblMemos].[FollowUpDays], [tblMemos].[MemoDate])) AND
Click to expand...
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[FollowUpDays], [tblMemos].[MemoDate] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


Em D said:
I am getting the same error and it's highlighting the semi-colon still.

KARL DEWEY said:
I found a couple of misplaced parenthesis in the WHERE statement.
Try this --
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d",[tblMemos].[MemoDate], [tblMemos].[FollowUpDays])) AND
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


:

The message reads as follows: Syntax error in query expression
'(([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate] >
DateAdd ("d", [tblMemos].[MemoDate],[tblMemos].[FollowUpDays]) AND
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between CV'.

When I try to run it, the semi-colon at the end is highlighted.

:

What is the rest of the message?

Try running from SQL View and see what it highlights.

--
Build a little, test a little.


:

This is returning a Syntax Error message.

:

Try this --
SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",[tblMemos].[MemoDate],
[tblMemos].[FollowUpDays]) AS [Expr 1]
FROM tblMemos LEFT JOIN tblAssociateList ON tblMemos.FileNumber =
tblAssociateList.FileNumber
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d",[tblMemos].[MemoDate], [tblMemos].[FollowUpDays]) AND
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


:

Sorry about not filling you in on the results I was getting before.
Initially, I was getting the error message that said my expression was too
complex. Now, I've changed some things around (new SQL below) and it isn't
returning any data. The result I'm trying to achieve is to only pull data
based on Expr 1 from a range of dates I provide.

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
AS [Expr 1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
And (tblMemos.ReceivedDate) Is Not Null)) AND
(DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays) Between [Enter date 1]
And [Enter date 2]);


:

You did not pose a question or say what was the results that you did not want.

I think you need to use this --
Expr 1: DateAdd("d", [tblMemos].[FollowUpDays], [tblMemos].[MemoDate])

and this --
HAVING ([tblMemos].[MainSubject] Is Not Null) AND
([tblMemos].[ReceivedDate] > DateAdd("d", [tblMemos].[FollowUpDays],
[tblMemos].[MemoDate])));


--
Build a little, test a little.


:

I am trying to get a list of entries that are between two dates that I input
on a form. However, the date in the query that it is looking at is a
calculated date "Expr 1: Sum(tblMemos!MemoDate+tblMemos!FollowUpDays)". I
want to keep this expression to tell me the date for that entry but I want to
only show dates within a certain range.

Below is the SQL:

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Sum(tblMemos!MemoDate+tblMemos!FollowUpDays) AS [Expr
1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
WHERE ((([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays]) Between
[Forms]![frmPopUp_Memos]![Date 1] And [Forms]![frmPopUp_Memos]![Date 2]))
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>Sum([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays])
And (tblMemos.ReceivedDate) Is Not Null));
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

The only thing I can suggest is to start taking it apart. Make a copy to
work with.
Maybe first removed the complete WHERE statement. If it work without it
then remove parts of WHERE at a time to find bad section.
If error without WHERE then the problem is elsewhere so take out something
more.

--
Build a little, test a little.


Em D said:
Unfortunately it still doesn't like it. I'm getting the same error message
and it is still highlighting the semi-colon.

KARL DEWEY said:
[tblMemos].[FollowUpDays] and [tblMemos].[MemoDate] were reversed in the
DateAdd.
Try it now --
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d", [tblMemos].[FollowUpDays], [tblMemos].[MemoDate])) AND
Click to expand...
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[FollowUpDays], [tblMemos].[MemoDate] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


Em D said:
I am getting the same error and it's highlighting the semi-colon still.

:

I found a couple of misplaced parenthesis in the WHERE statement.
Try this --
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d",[tblMemos].[MemoDate], [tblMemos].[FollowUpDays])) AND
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


:

The message reads as follows: Syntax error in query expression
'(([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate] >
DateAdd ("d", [tblMemos].[MemoDate],[tblMemos].[FollowUpDays]) AND
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between CV'.

When I try to run it, the semi-colon at the end is highlighted.

:

What is the rest of the message?

Try running from SQL View and see what it highlights.

--
Build a little, test a little.


:

This is returning a Syntax Error message.

:

Try this --
SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",[tblMemos].[MemoDate],
[tblMemos].[FollowUpDays]) AS [Expr 1]
FROM tblMemos LEFT JOIN tblAssociateList ON tblMemos.FileNumber =
tblAssociateList.FileNumber
WHERE (([tblMemos].[MainSubject] Is Not Null) AND ([tblMemos].[ReceivedDate]
DateAdd("d",[tblMemos].[MemoDate], [tblMemos].[FollowUpDays]) AND
([tblMemos].[ReceivedDate] Is Not Null) AND
(DateAdd("d",[tblMemos].[MemoDate],[tblMemos].[FollowUpDays] Between
CVDate([Enter date 1]) AND CVDate([Enter date 2]));

--
Build a little, test a little.


:

Sorry about not filling you in on the results I was getting before.
Initially, I was getting the error message that said my expression was too
complex. Now, I've changed some things around (new SQL below) and it isn't
returning any data. The result I'm trying to achieve is to only pull data
based on Expr 1 from a range of dates I provide.

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Dateadd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
AS [Expr 1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays)
And (tblMemos.ReceivedDate) Is Not Null)) AND
(DateAdd("d",tblMemos!MemoDate,tblMemos!FollowUpDays) Between [Enter date 1]
And [Enter date 2]);


:

You did not pose a question or say what was the results that you did not want.

I think you need to use this --
Expr 1: DateAdd("d", [tblMemos].[FollowUpDays], [tblMemos].[MemoDate])

and this --
HAVING ([tblMemos].[MainSubject] Is Not Null) AND
([tblMemos].[ReceivedDate] > DateAdd("d", [tblMemos].[FollowUpDays],
[tblMemos].[MemoDate])));


--
Build a little, test a little.


:

I am trying to get a list of entries that are between two dates that I input
on a form. However, the date in the query that it is looking at is a
calculated date "Expr 1: Sum(tblMemos!MemoDate+tblMemos!FollowUpDays)". I
want to keep this expression to tell me the date for that entry but I want to
only show dates within a certain range.

Below is the SQL:

SELECT tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate, Sum(tblMemos!MemoDate+tblMemos!FollowUpDays) AS [Expr
1]
FROM tblAssociateList INNER JOIN tblMemos ON tblAssociateList.FileNumber =
tblMemos.FileNumber
WHERE ((([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays]) Between
[Forms]![frmPopUp_Memos]![Date 1] And [Forms]![frmPopUp_Memos]![Date 2]))
GROUP BY tblAssociateList.FileNumber, tblAssociateList.AssociateLast,
tblAssociateList.AssociateFirst, tblMemos.MainSubject, tblMemos.SubType,
tblMemos.MemoDate, tblMemos.FollowUpDays, tblMemos.FollowUpDate,
tblMemos.ReceivedDate
HAVING (((tblMemos.MainSubject) Is Not Null) AND
((tblMemos.ReceivedDate)>Sum([tblMemos]![MemoDate]+[tblMemos]![FollowUpDays])
And (tblMemos.ReceivedDate) Is Not Null));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"Undefined Function" in Expression when using VBA module in query 1
Problem with query 2
Change expression from month to quarter 7
Parameters to search between years 2
field expression problem when using parameter 3
Problem using Query Criteria with an Expression 6
Can I create a "Sum" on an expression that is "Counting" in query? 1
Date query parameters entered from form not working 1

Top