Data Type

  • Thread starter Thread starter Ivor Williams
  • Start date Start date
I

Ivor Williams

I use the attached to return a value in an append query. The only way I can
get it to work is if I set the Data Type in the table the data is being
appended to to Text. I want to set it to Number so I can summarize the
information, but get a data type mismatch when I set the Data Type to
Number. How can I work around this? [Qty] is Number, [StartDate] and
[EndDate] are Date/Time, [AtQty] and [VanQty] are Number.
 
I use the attached to return a value in an append query. The only way I can
get it to work is if I set the Data Type in the table the data is being
appended to to Text. I want to set it to Number so I can summarize the
information, but get a data type mismatch when I set the Data Type to
Number. How can I work around this? [Qty] is Number, [StartDate] and
[EndDate] are Date/Time, [AtQty] and [VanQty] are Number.
Click to expand...

Please post the SQL view of the query (not a picture).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John...

Hope this is what you want.
Click to expand...

No. It's not what I want.

Open the Query in design view.
On the menu select View... SQL.
Copy and paste the cryptic-looking text you see in the window to a new
message here.

It's not necessary to export it to .rtf or to any other type of file
attachment. It's just text.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Sorry, let's try this:

INSERT INTO tblCombinedTopSellers ( ItemNo, Days, DailyAvg, NeedFor18Wks,
AtQty, VanQty, [Desc], Qty, [Order], Category )
SELECT TOP 25 tblCarts.ItemNo,
DateDiff("d",(Forms!frmTopSellers.StartDate),(Forms!frmTopSellers.EndDate))
AS Days,
[Qty]/DateDiff("d",(Forms!frmTopSellers.StartDate),(Forms!frmTopSellers.EndDate))
AS DailyAvg,
[Qty]/DateDiff("d",(Forms!frmTopSellers.StartDate),(Forms!frmTopSellers.EndDate))*126
AS NeedFor18Wks, tblAtlantaStock.AtQty, tblVancouverStock.VanQty,
tblItems.Desc, Sum(tblCarts.Qty) AS SumOfQty,
IIf([Qty]/DateDiff("d",(Forms!frmTopSellers.StartDate),(Forms!frmTopSellers.EndDate))*126-tblAtlantaStock!AtQty-tblVancouverStock!VanQty>0,(([Qty]/DateDiff("d",(Forms!frmTopSellers.StartDate),(Forms!frmTopSellers.EndDate))*126)-[AtQty]-[VanQty]),"
") AS [Order], "Clocks" AS Category
FROM ((tblItems INNER JOIN tblCarts ON tblItems.ItemNo = tblCarts.ItemNo)
INNER JOIN tblAtlantaStock ON tblItems.ItemNo = tblAtlantaStock.ItemNo)
INNER JOIN tblVancouverStock ON tblItems.ItemNo = tblVancouverStock.ItemNo
GROUP BY tblCarts.ItemNo,
DateDiff("d",(Forms!frmTopSellers.StartDate),(Forms!frmTopSellers.EndDate)),
[Qty]/DateDiff("d",(Forms!frmTopSellers.StartDate),(Forms!frmTopSellers.EndDate)),
[Qty]/DateDiff("d",(Forms!frmTopSellers.StartDate),(Forms!frmTopSellers.EndDate))*126,
tblAtlantaStock.AtQty, tblVancouverStock.VanQty, tblItems.Desc,
IIf([Qty]/DateDiff("d",(Forms!frmTopSellers.StartDate),(Forms!frmTopSellers.EndDate))*126-tblAtlantaStock!AtQty-tblVancouverStock!VanQty>0,(([Qty]/DateDiff("d",(Forms!frmTopSellers.StartDate),(Forms!frmTopSellers.EndDate))*126)-[AtQty]-[VanQty]),"
"), "Clocks", tblCarts.Date
HAVING (((tblCarts.ItemNo) Between "00001" And "09999" And
(tblCarts.ItemNo)<>"00000") AND ((tblCarts.Date) Between
[Forms]![frmTopSellers].[StartDate] And [Forms]![frmTopSellers].[EndDate]))
OR (((tblCarts.ItemNo) Between "40000" And "49999"))
ORDER BY Sum(tblCarts.Qty) DESC;
 
I use the attached to return a value in an append query. The only way I can
get it to work is if I set the Data Type in the table the data is being
appended to to Text. I want to set it to Number so I can summarize the
information, but get a data type mismatch when I set the Data Type to
Number. How can I work around this? [Qty] is Number, [StartDate] and
[EndDate] are Date/Time, [AtQty] and [VanQty] are Number.
Click to expand...

You don't say WHICH field needs to be set to Text, but I'm guessing
it's:

IIf([Qty]/DateDiff("d",(Forms!frmTopSellers.StartDate),(Forms!frmTopSellers.EndDate))*126
-tblAtlantaStock!AtQty-tblVancouverStock!VanQty>0,
(([Qty]/DateDiff("d",(Forms!frmTopSellers.StartDate),
(Forms!frmTopSellers.EndDate))*126)-[AtQty]-[VanQty]),
" ") AS [Order]

The problem is that since one of the possible results of the IIF is a
Text String - " " - then the other is also constrained to be Text.
Replace the " " in the False branch of the IIF with the word NULL.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top