Thanks for your reply Ken. The query as it works is:
SELECT RWPlusOverheadAndContract.[Cost Centre],
BusinessCaseCCOhead.Overhead
AS OverheadPercent, Contract.Description, RWPlusOverheadAndContract.Date,
-[Amount] AS PosAmount, -Round([Amount]*[OverheadPercent],2) AS
OverheadAmount, RWPlusOverheadAndContract.Document,
RWPlusOverheadAndContract.Account, BusinessCaseCCOhead.ContactPersonName
AS
ContactPersonName, RWPlusOverheadAndContract.ContractID AS BusinessCaseID,
BusinessCaseCCOhead.ContactPersonName AS CName,
Forms!OverheadCalcChooseDates!txtStartDate AS StartDate,
Forms!OverheadCalcChooseDates!txtFinishDate AS FinishDate
FROM BusinessCaseCCOhead INNER JOIN (RWPlusOverheadAndContract INNER JOIN
Contract ON RWPlusOverheadAndContract.ContractID = Contract.ID) ON
(BusinessCaseCCOhead.BusinessCase = RWPlusOverheadAndContract.ContractID)
AND
(BusinessCaseCCOhead.CostCentre = RWPlusOverheadAndContract.[Cost Centre])
WHERE
(((RWPlusOverheadAndContract.Date)<=[Forms]![OverheadCalcChooseDates]![txtFinishDate]
And
(RWPlusOverheadAndContract.Date)>=[Forms]![OverheadCalcChooseDates]![txtStartDate]));
When I change it to:
SELECT RWPlusOverheadAndContract.[Cost Centre],
BusinessCaseCCOhead.Overhead
AS OverheadPercent, Contract.Description, RWPlusOverheadAndContract.Date,
-[Amount] AS PosAmount, -Round([Amount]*[OverheadPercent],2) AS
OverheadAmount, RWPlusOverheadAndContract.Document,
RWPlusOverheadAndContract.Account, BusinessCaseCCOhead.ContactPersonName
AS
ContactPersonName, RWPlusOverheadAndContract.ContractID AS BusinessCaseID,
BusinessCaseCCOhead.ContactPersonName AS CName,
Forms!OverheadCalcChooseDates!txtStartDate AS StartDate,
Forms!OverheadCalcChooseDates!txtFinishDate AS FinishDate,
[Forms]![OverheadCalcChooseDates]![cmbCostCentre] AS Expr1
FROM BusinessCaseCCOhead INNER JOIN (RWPlusOverheadAndContract INNER JOIN
Contract ON RWPlusOverheadAndContract.ContractID = Contract.ID) ON
(BusinessCaseCCOhead.BusinessCase = RWPlusOverheadAndContract.ContractID)
AND
(BusinessCaseCCOhead.CostCentre = RWPlusOverheadAndContract.[Cost Centre])
WHERE
(((RWPlusOverheadAndContract.Date)<=[Forms]![OverheadCalcChooseDates]![txtFinishDate]
And
(RWPlusOverheadAndContract.Date)>=[Forms]![OverheadCalcChooseDates]![txtStartDate])
AND (([Forms]![OverheadCalcChooseDates]![cmbCostCentre]) Is Null Or
([Forms]![OverheadCalcChooseDates]![cmbCostCentre])=[RWPlusOverheadAndContract].[Cost
Centre]));
it doesn't work in 2003 but does in 2007! Aaargh! My client has 2003, so I
would really like to stick with that, but could probably get them to
upgrade
if that's the only answer.
Look forward to your reply,
Michael
--
Revelation 20:11-15
Ken Snell (MVP) said:Post the SQL statement of the query. I'm guessing that you're using a
Totals
query, and grouping on the date field; sometimes that can cause this
"weird"
result.
--
Ken Snell
<MS ACCESS MVP>
"Michael Manukau Inst of Technology NZ"
Ken Snell (MVP) said:I don't see anything obvious (I'm assuming that the addition of the
"[Forms]![OverheadCalcChooseDates]![cmbCostCentre] AS Expr1
" calculated field to the query is not causing the query to return more
than 2000 bytes in a record now, but you may need to check that
possibility), but let's try explicitly declaring the textboxes from the
forms as parameters (I'm assuming that the cmbCostCentre contains a text
data type) -- and let's put the Date field in [ ] delimiters:
PARAMETERS [Forms]![OverheadCalcChooseDates]![txtStartDate] DateTime,
[Forms]![OverheadCalcChooseDates]![txtFinishDate] DateTime,
[Forms]![OverheadCalcChooseDates]![cmbCostCentre] Text ( 255 );
SELECT RWPlusOverheadAndContract.[Cost Centre],
BusinessCaseCCOhead.Overhead
AS OverheadPercent, Contract.Description,
RWPlusOverheadAndContract.[Date],
-[Amount] AS PosAmount, -Round([Amount]*[OverheadPercent],2) AS
OverheadAmount, RWPlusOverheadAndContract.Document,
RWPlusOverheadAndContract.Account, BusinessCaseCCOhead.ContactPersonName
AS
ContactPersonName, RWPlusOverheadAndContract.ContractID AS BusinessCaseID,
BusinessCaseCCOhead.ContactPersonName AS CName,
Forms!OverheadCalcChooseDates!txtStartDate AS StartDate,
Forms!OverheadCalcChooseDates!txtFinishDate AS FinishDate,
[Forms]![OverheadCalcChooseDates]![cmbCostCentre] AS Expr1
FROM BusinessCaseCCOhead INNER JOIN (RWPlusOverheadAndContract INNER JOIN
Contract ON RWPlusOverheadAndContract.ContractID = Contract.ID) ON
(BusinessCaseCCOhead.BusinessCase = RWPlusOverheadAndContract.ContractID)
AND
(BusinessCaseCCOhead.CostCentre = RWPlusOverheadAndContract.[Cost Centre])
WHERE
(((RWPlusOverheadAndContract.[Date])<=[Forms]![OverheadCalcChooseDates]![txtFinishDate]
And
(RWPlusOverheadAndContract.[Date])>=[Forms]![OverheadCalcChooseDates]![txtStartDate])
AND (([Forms]![OverheadCalcChooseDates]![cmbCostCentre]) Is Null Or
([Forms]![OverheadCalcChooseDates]![cmbCostCentre])=[RWPlusOverheadAndContract].[Cost
Centre]));
I noted that you're using Date as the name of a field in a table. It and
many other words are reserved words in ACCESS and should not be used for
control names, field names, etc. See these Knowledge Base articles for
more information about reserved words and characters that should not be
used:
List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266
Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763
See this site for code that allows you to validate your names as not being
VBA keywords:
basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18
--
Ken Snell
<MS ACCESS MVP>
"Michael Manukau Inst of Technology NZ"
Thanks for your reply Ken. The query as it works is:
SELECT RWPlusOverheadAndContract.[Cost Centre],
BusinessCaseCCOhead.Overhead
AS OverheadPercent, Contract.Description, RWPlusOverheadAndContract.Date,
-[Amount] AS PosAmount, -Round([Amount]*[OverheadPercent],2) AS
OverheadAmount, RWPlusOverheadAndContract.Document,
RWPlusOverheadAndContract.Account, BusinessCaseCCOhead.ContactPersonName
AS
ContactPersonName, RWPlusOverheadAndContract.ContractID AS
BusinessCaseID,
BusinessCaseCCOhead.ContactPersonName AS CName,
Forms!OverheadCalcChooseDates!txtStartDate AS StartDate,
Forms!OverheadCalcChooseDates!txtFinishDate AS FinishDate
FROM BusinessCaseCCOhead INNER JOIN (RWPlusOverheadAndContract INNER JOIN
Contract ON RWPlusOverheadAndContract.ContractID = Contract.ID) ON
(BusinessCaseCCOhead.BusinessCase = RWPlusOverheadAndContract.ContractID)
AND
(BusinessCaseCCOhead.CostCentre = RWPlusOverheadAndContract.[Cost
Centre])
WHERE
(((RWPlusOverheadAndContract.Date)<=[Forms]![OverheadCalcChooseDates]![txtFinishDate]
And
(RWPlusOverheadAndContract.Date)>=[Forms]![OverheadCalcChooseDates]![txtStartDate]));
When I change it to:
SELECT RWPlusOverheadAndContract.[Cost Centre],
BusinessCaseCCOhead.Overhead
AS OverheadPercent, Contract.Description, RWPlusOverheadAndContract.Date,
-[Amount] AS PosAmount, -Round([Amount]*[OverheadPercent],2) AS
OverheadAmount, RWPlusOverheadAndContract.Document,
RWPlusOverheadAndContract.Account, BusinessCaseCCOhead.ContactPersonName
AS
ContactPersonName, RWPlusOverheadAndContract.ContractID AS
BusinessCaseID,
BusinessCaseCCOhead.ContactPersonName AS CName,
Forms!OverheadCalcChooseDates!txtStartDate AS StartDate,
Forms!OverheadCalcChooseDates!txtFinishDate AS FinishDate,
[Forms]![OverheadCalcChooseDates]![cmbCostCentre] AS Expr1
FROM BusinessCaseCCOhead INNER JOIN (RWPlusOverheadAndContract INNER JOIN
Contract ON RWPlusOverheadAndContract.ContractID = Contract.ID) ON
(BusinessCaseCCOhead.BusinessCase = RWPlusOverheadAndContract.ContractID)
AND
(BusinessCaseCCOhead.CostCentre = RWPlusOverheadAndContract.[Cost
Centre])
WHERE
(((RWPlusOverheadAndContract.Date)<=[Forms]![OverheadCalcChooseDates]![txtFinishDate]
And
(RWPlusOverheadAndContract.Date)>=[Forms]![OverheadCalcChooseDates]![txtStartDate])
AND (([Forms]![OverheadCalcChooseDates]![cmbCostCentre]) Is Null Or
([Forms]![OverheadCalcChooseDates]![cmbCostCentre])=[RWPlusOverheadAndContract].[Cost
Centre]));
it doesn't work in 2003 but does in 2007! Aaargh! My client has 2003, so
I
would really like to stick with that, but could probably get them to
upgrade
if that's the only answer.
Look forward to your reply,
Michael
--
Revelation 20:11-15
Ken Snell (MVP) said:Post the SQL statement of the query. I'm guessing that you're using a
Totals
query, and grouping on the date field; sometimes that can cause this
"weird"
result.
--
Ken Snell
<MS ACCESS MVP>
"Michael Manukau Inst of Technology NZ"
message I have a semi-complicated query that works. When I make it slightly
more
complicated, the date fields that used to work suddenly start
displaying
in
Chinese characters, even thought it wasn't these fields that I
altered.
Help!