Problem Calculating Year-To-Date, Sales

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Hi Everyone,

I've created a report based on a query

Tables
MonthlyFigures-- ID, Date(mm/yyyy), Figures, Manufacturer, Store
Stores -- ID, Name, Post Code
Manufacturer -- ID Name
Manufacturer ID and Store ID are Related to MonthlyFigures Manufacturer
and store

So, I have a sales report that displays the Store Name and Postcode and a
subreport in its detail section, the subreport is called Monthly and is
based on a query called Report that has all the figures and details
required for the report (Which is linked by Store ID).

Report Query --
SELECT Monthlys.Date,
LastYears.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
MonthlyFigures.Figures,
(
Select Sum(MonthlyFigures.Figures)
FROM MonthlyFigures
WHERE Monthlys.Manufacturer = MonthlyFigures.Manufacturer AND
MonthlyFigures.Store = Monthlys.Store AND
MonthlyFigures.Date Between Monthlys.Date AND LastYears.Date;
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);

This query makes a table with all the values I need, the problem comes
when I create the Report from this, it states that Multi-level group by
clause are not allowed in a subquery.

I searched the net and found that maybe I should use a Dsum() instead
of the subquery, this is my attempt

SELECT Monthlys.Date AS rDate,
Monthlys.Manufacturer AS rManufacturer,
Monthlys.Store AS rStore,
Monthlys.Figures AS rFigures,
LastYears.Figures AS rLastFigures,
DSum(MonthlyFigures.Figures, "MonthlyFigures",
([MonthlyFigures].[Date]
Between [Monthlys].[Date] AND [LastYears].[Date]) AND
([MonthlyFigures].[Manufacturer] = [Monthlys].[Manufacturer]) AND
([MonthlyFigures].[Store] = [Monthlys].[Store])
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);

The YTD figures are way off (not even to total of everything that I was
getting)

This post if getting a little too long, so please let me know if I left
anything out.
 
Hi Everyone,

I've created a report based on a query

Tables
MonthlyFigures-- ID, Date(mm/yyyy), Figures, Manufacturer, Store
Stores -- ID, Name, Post Code
Manufacturer -- ID Name
Manufacturer ID and Store ID are Related to MonthlyFigures Manufacturer
and store

So, I have a sales report that displays the Store Name and Postcode and a
subreport in its detail section, the subreport is called Monthly and is
based on a query called Report that has all the figures and details
required for the report (Which is linked by Store ID).

Report Query --
SELECT Monthlys.Date,
LastYears.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
MonthlyFigures.Figures,
(
Select Sum(MonthlyFigures.Figures)
FROM MonthlyFigures
WHERE Monthlys.Manufacturer = MonthlyFigures.Manufacturer AND
MonthlyFigures.Store = Monthlys.Store AND
MonthlyFigures.Date Between Monthlys.Date AND LastYears.Date;
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);

This query makes a table with all the values I need, the problem comes
when I create the Report from this, it states that Multi-level group by
clause are not allowed in a subquery.

I searched the net and found that maybe I should use a Dsum() instead
of the subquery, this is my attempt
Click to expand...

-----
My mistake this is what the query should look like

SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum(MonthlyFigures.Figures, "MonthlyFigures",
([MonthlyFigures].[Date]
Between [Monthlys].[Date] AND [LastYears].[Date]) AND
([MonthlyFigures].[Manufacturer] = [Monthlys].[Manufacturer]) AND
([MonthlyFigures].[Store] = [Monthlys].[Store])
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
 
You might try something like this. If Manufacturer and Store are text, you
need to add quotes in the same manner that I added "#" around the dates.
SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum("Figures", "MonthlyFigures",
"[Date] Between #" & Monthlys.[Date] &
"# AND #" & [LastYears].[Date] &
"# AND [Manufacturer] = " & [Monthlys].[Manufacturer] &
" AND [Store] = " & [Monthlys].[Store])
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);


--
Duane Hookom
MS Access MVP


Brian said:
Hi Everyone,

I've created a report based on a query

Tables
MonthlyFigures-- ID, Date(mm/yyyy), Figures, Manufacturer, Store
Stores -- ID, Name, Post Code
Manufacturer -- ID Name
Manufacturer ID and Store ID are Related to MonthlyFigures Manufacturer
and store

So, I have a sales report that displays the Store Name and Postcode and a
subreport in its detail section, the subreport is called Monthly and is
based on a query called Report that has all the figures and details
required for the report (Which is linked by Store ID).

Report Query --
SELECT Monthlys.Date,
LastYears.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
MonthlyFigures.Figures,
(
Select Sum(MonthlyFigures.Figures)
FROM MonthlyFigures
WHERE Monthlys.Manufacturer = MonthlyFigures.Manufacturer AND
MonthlyFigures.Store = Monthlys.Store AND
MonthlyFigures.Date Between Monthlys.Date AND LastYears.Date;
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);

This query makes a table with all the values I need, the problem comes
when I create the Report from this, it states that Multi-level group by
clause are not allowed in a subquery.

I searched the net and found that maybe I should use a Dsum() instead
of the subquery, this is my attempt
Click to expand...

-----
My mistake this is what the query should look like

SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum(MonthlyFigures.Figures, "MonthlyFigures",
([MonthlyFigures].[Date]
Between [Monthlys].[Date] AND [LastYears].[Date]) AND
([MonthlyFigures].[Manufacturer] = [Monthlys].[Manufacturer]) AND
([MonthlyFigures].[Store] = [Monthlys].[Store])
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
The YTD figures are way off (not even to total of everything that I was
getting)

This post if getting a little too long, so please let me know if I left
anything out.
Click to expand...
Click to expand...
 
You might try something like this. If Manufacturer and Store are text, you
need to add quotes in the same manner that I added "#" around the dates.
SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum("Figures", "MonthlyFigures",
"[Date] Between #" & Monthlys.[Date] &
"# AND #" & [LastYears].[Date] &
"# AND [Manufacturer] = " & [Monthlys].[Manufacturer] &
" AND [Store] = " & [Monthlys].[Store])
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
Click to expand...


Hi Duane, Thanks for the reply,

The Manufacturer and Stores are linked fields to other tables (I'm not
sure of the exact terminology), So the Field contains a numerical
identifier, also I tried adding the hashes around the dates and got the
same results,
So now I'm not sure what I'm doing wrong, I'm trying to turn this
query with subquery
----------
SELECT Monthlys.Date, LastYears.Date, Monthlys.Manufacturer,
Monthlys.Store, Monthlys.Figures, LastYears.Figures,
MonthlyFigures.Figures, (
Select Sum(MonthlyFigures.Figures)
FROM MonthlyFigures
WHERE Monthlys.Manufacturer = MonthlyFigures.Manufacturer AND
MonthlyFigures.Store = Monthlys.Store AND MonthlyFigures.Date Between
Monthlys.Date AND LastYears.Date;
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
----------
Into a different query that I can generate a report from, which is why I
was trying the following.
----------
SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum(MonthlyFigures.Figures, "MonthlyFigures",
([MonthlyFigures].[Date] Between [Monthlys].[Date] AND
[LastYears].[Date]) AND ([MonthlyFigures].[Manufacturer] =
[Monthlys].[Manufacturer]) AND ([MonthlyFigures].[Store] =
[Monthlys].[Store]) ) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
 
Try this:
SELECT Monthlys.Date As MthDate, LastYears.Date As LYDate,
Monthlys.Manufacturer, Monthlys.Store,
Monthlys.Figures as MthFigures, LastYears.Figures as LYFigures,
Dsum ("Figures","MonthlyFigures",
"Manufacturer=" & Monthlys.Manufacturer &
" AND Store = " & Monthlys.Store & " AND [Date] Between #" &
Monthlys.Date & "# AND #" & LastYears.Date & "#") AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);


--
Duane Hookom
MS Access MVP


Brian said:
You might try something like this. If Manufacturer and Store are text,
you
need to add quotes in the same manner that I added "#" around the dates.
SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum("Figures", "MonthlyFigures",
"[Date] Between #" & Monthlys.[Date] &
"# AND #" & [LastYears].[Date] &
"# AND [Manufacturer] = " & [Monthlys].[Manufacturer] &
" AND [Store] = " & [Monthlys].[Store])
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
Click to expand...


Hi Duane, Thanks for the reply,

The Manufacturer and Stores are linked fields to other tables (I'm not
sure of the exact terminology), So the Field contains a numerical
identifier, also I tried adding the hashes around the dates and got the
same results,
So now I'm not sure what I'm doing wrong, I'm trying to turn this
query with subquery
----------
SELECT Monthlys.Date, LastYears.Date, Monthlys.Manufacturer,
Monthlys.Store, Monthlys.Figures, LastYears.Figures,
MonthlyFigures.Figures, (
Select Sum(MonthlyFigures.Figures)
FROM MonthlyFigures
WHERE Monthlys.Manufacturer = MonthlyFigures.Manufacturer AND
MonthlyFigures.Store = Monthlys.Store AND MonthlyFigures.Date Between
Monthlys.Date AND LastYears.Date;
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
----------
Into a different query that I can generate a report from, which is why I
was trying the following.
----------
SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum(MonthlyFigures.Figures, "MonthlyFigures",
([MonthlyFigures].[Date] Between [Monthlys].[Date] AND
[LastYears].[Date]) AND ([MonthlyFigures].[Manufacturer] =
[Monthlys].[Manufacturer]) AND ([MonthlyFigures].[Store] =
[Monthlys].[Store]) ) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
Click to expand...
 
All the YTD figures were Errors ( data type mismatch) I should get some
more time today to look closer, and I really appreciate the help...

(Brian - using "google groups"!)

Duane said:
Try this:
SELECT Monthlys.Date As MthDate, LastYears.Date As LYDate,
Monthlys.Manufacturer, Monthlys.Store,
Monthlys.Figures as MthFigures, LastYears.Figures as LYFigures,
Dsum ("Figures","MonthlyFigures",
"Manufacturer=" & Monthlys.Manufacturer &
" AND Store = " & Monthlys.Store & " AND [Date] Between #" &
Monthlys.Date & "# AND #" & LastYears.Date & "#") AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);


--
Duane Hookom
MS Access MVP


Brian said:
You might try something like this. If Manufacturer and Store are text,
you
need to add quotes in the same manner that I added "#" around the dates.
SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum("Figures", "MonthlyFigures",
"[Date] Between #" & Monthlys.[Date] &
"# AND #" & [LastYears].[Date] &
"# AND [Manufacturer] = " & [Monthlys].[Manufacturer] &
" AND [Store] = " & [Monthlys].[Store])
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
Click to expand...


Hi Duane, Thanks for the reply,

The Manufacturer and Stores are linked fields to other tables (I'm not
sure of the exact terminology), So the Field contains a numerical
identifier, also I tried adding the hashes around the dates and got the
same results,
So now I'm not sure what I'm doing wrong, I'm trying to turn this
query with subquery
----------
SELECT Monthlys.Date, LastYears.Date, Monthlys.Manufacturer,
Monthlys.Store, Monthlys.Figures, LastYears.Figures,
MonthlyFigures.Figures, (
Select Sum(MonthlyFigures.Figures)
FROM MonthlyFigures
WHERE Monthlys.Manufacturer = MonthlyFigures.Manufacturer AND
MonthlyFigures.Store = Monthlys.Store AND MonthlyFigures.Date Between
Monthlys.Date AND LastYears.Date;
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
----------
Into a different query that I can generate a report from, which is why I
was trying the following.
----------
SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum(MonthlyFigures.Figures, "MonthlyFigures",
([MonthlyFigures].[Date] Between [Monthlys].[Date] AND
[LastYears].[Date]) AND ([MonthlyFigures].[Manufacturer] =
[Monthlys].[Manufacturer]) AND ([MonthlyFigures].[Store] =
[Monthlys].[Store]) ) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
Click to expand...
Click to expand...
 
Just to confirm:
Figures is numeric
Manufacturer is numeric
Store is numeric
Date is date
GetReportDate() returns a date

All numeric fields align to the right when viewed in datasheet.
--
Duane Hookom
MS Access MVP


All the YTD figures were Errors ( data type mismatch) I should get some
more time today to look closer, and I really appreciate the help...

(Brian - using "google groups"!)

Duane said:
Try this:
SELECT Monthlys.Date As MthDate, LastYears.Date As LYDate,
Monthlys.Manufacturer, Monthlys.Store,
Monthlys.Figures as MthFigures, LastYears.Figures as LYFigures,
Dsum ("Figures","MonthlyFigures",
"Manufacturer=" & Monthlys.Manufacturer &
" AND Store = " & Monthlys.Store & " AND [Date] Between #" &
Monthlys.Date & "# AND #" & LastYears.Date & "#") AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);


--
Duane Hookom
MS Access MVP


Brian said:
9 Dec 2005 07:50:06 -0600, Duane Hookom wrote:

You might try something like this. If Manufacturer and Store are text,
you
need to add quotes in the same manner that I added "#" around the
dates.
SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum("Figures", "MonthlyFigures",
"[Date] Between #" & Monthlys.[Date] &
"# AND #" & [LastYears].[Date] &
"# AND [Manufacturer] = " & [Monthlys].[Manufacturer] &
" AND [Store] = " & [Monthlys].[Store])
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);


Hi Duane, Thanks for the reply,

The Manufacturer and Stores are linked fields to other tables (I'm not
sure of the exact terminology), So the Field contains a numerical
identifier, also I tried adding the hashes around the dates and got the
same results,
So now I'm not sure what I'm doing wrong, I'm trying to turn this
query with subquery
----------
SELECT Monthlys.Date, LastYears.Date, Monthlys.Manufacturer,
Monthlys.Store, Monthlys.Figures, LastYears.Figures,
MonthlyFigures.Figures, (
Select Sum(MonthlyFigures.Figures)
FROM MonthlyFigures
WHERE Monthlys.Manufacturer = MonthlyFigures.Manufacturer AND
MonthlyFigures.Store = Monthlys.Store AND MonthlyFigures.Date Between
Monthlys.Date AND LastYears.Date;
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
----------
Into a different query that I can generate a report from, which is why
I
was trying the following.
----------
SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum(MonthlyFigures.Figures, "MonthlyFigures",
([MonthlyFigures].[Date] Between [Monthlys].[Date] AND
[LastYears].[Date]) AND ([MonthlyFigures].[Manufacturer] =
[Monthlys].[Manufacturer]) AND ([MonthlyFigures].[Store] =
[Monthlys].[Store]) ) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
Click to expand...
Click to expand...
Click to expand...
 
Just to confirm:
Figures is numeric
Manufacturer is numeric
Store is numeric
Date is date
GetReportDate() returns a date

All numeric fields align to the right when viewed in datasheet.
Click to expand...


Yeah, you were right, I hadn't changed the fields back to numbers after
messing around with them earlier, the figures appear now, but the amounts
are still off, I haven't yet figured what they add up to
 
Yeah, you were right, I hadn't changed the fields back to numbers after
messing around with them earlier, the figures appear now, but the amounts
are still off, I haven't yet figured what they add up to
Click to expand...

Still having trouble identifying these figures,

this is a list of the total figures for the manufacturer WITHIN the
desired period


mm/yyyy - Figures
11/2004 - -138
12/2004 - 3895
01/2005 - 2824
02/2005 - -2592
03/2005 - 1744
04/2005 - 102
05/2005 - 9392
06/2005 - 343
08/2005 - -3645
09/2005 - -83
10/2005 - 3045
11/2005 - 3677
TOTAL (YTD) = 18654
The Following aren't in the range I'm testing (11/2004-11/2005)
07/2004 - 21247
08/2004 - 4592
09/2004 - 12632
10/2004 - 568

The Result I'm getting from the query is 45620
The Dsum criteria seems to be ok, so I'm lost!
any thoughts?
 
It's near impossible to determine what's wonky. Have you tried opening the
debug window and entering something like:

?Dsum ("Figures","MonthlyFigures", "Manufacturer= x AND Store = y AND
[Date] Between #a# AND #b#")
where x and y are numbers and a and b are dates.
 
Back
Top