Query problem

[cbearden]

I have created a query that pulls records that are > 45 days. My query has these fields:

Total_Days: Sum(Date()-[dtmSent]) - Total - Expression, Criteria - > 45 (on 2 lines)
chkAll - Criteria - False (on 2 lines)
intDealerNum - Criteria - Like "121###", Criteria(2nd line) - Like "124###"
memComments - datatype is Memo

I run the query and the Comments field is coming up as a small square box. I've taken the expression out and it works fine. Could someone help?

Thanks

 

[lupins46]

Rather than try to describe the query can you post the SQL view of the query that isn't working, please.

 

[cbearden]

SELECT [Dealer Contact Info].intDealerNum, [Dealer Contact Info].txtDealer, [Dealer Contact Info].txtContact, [Warrenty Log].dtmSent, [Warrenty Log].txtName, [Warrenty Log].curGcost, [Warrenty Log].curGrefund, [Warrenty Log].curSCcost, [Warrenty Log].curSCrefund, [Warrenty Log].curCLcost, [Warrenty Log].curCLrefund, [Warrenty Log].curOcost, [Warrenty Log].curOrefund, [Warrenty Log].memComments, [Vehicle Info].dtmRepo, [Vehicle Info].intMileage, Sum(Date()-[dtmSent]) AS Total_Days
FROM ([Dealer Contact Info] INNER JOIN [Warrenty Log] ON [Dealer Contact Info].intDealerNum = [Warrenty Log].intDealerNum) INNER JOIN [Vehicle Info] ON [Warrenty Log].intID = [Vehicle Info].intID
GROUP BY [Dealer Contact Info].intDealerNum, [Dealer Contact Info].txtDealer, [Dealer Contact Info].txtContact, [Warrenty Log].dtmSent, [Warrenty Log].txtName, [Warrenty Log].curGcost, [Warrenty Log].curGrefund, [Warrenty Log].curSCcost, [Warrenty Log].curSCrefund, [Warrenty Log].curCLcost, [Warrenty Log].curCLrefund, [Warrenty Log].curOcost, [Warrenty Log].curOrefund, [Warrenty Log].memComments, [Vehicle Info].dtmRepo, [Vehicle Info].intMileage, [Warrenty Log].chkAll
HAVING ((([Dealer Contact Info].intDealerNum) Like "121###") AND (([Warrenty Log].dtmSent)<#6/26/2005#) AND (([Warrenty Log].chkAll)=False) AND ((Sum(Date()-[dtmSent]))>45)) OR ((([Dealer Contact Info].intDealerNum) Like "124###") AND (([Warrenty Log].dtmSent)<#6/26/2005#) AND (([Warrenty Log].chkAll)=False) AND ((Sum(Date()-[dtmSent]))>45))
ORDER BY [Warrenty Log].dtmSent
WITH OWNERACCESS OPTION;

 

[PHV]

Avoid to GROUP BY a memo field, use First([Warrenty Log].memComments) as expression.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cbearden]

I dont' know SQL. How would I do that in Design View. There is not a "First" in the dropdown box that has Group By.

THanks

 

[PHV]

There is not a "First" in the dropdown box that has Group By
Even playing with the vertical scrollbar ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886