By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,188 Members | 1,762 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,188 IT Pros & Developers. It's quick & easy.

Query question - Access 2003

P: n/a
ms
I successfully ran queries with functions in Access 2000 that no longer run in
2003. Also, when I convert it back to 2000 and run from 2000, I continue to get
a compile error.
The compile error is on all fields in the select list below with the IIF
statement and the RIGHT function.
Example:
INSERT INTO INVCNT ( PartNo, PartRev, CtQty, LocKey, SubLoc, AdjQty )
SELECT [IM2].[ItemNumber], [ItemMaster].[RevLev],
IIf([IM2].[QtyOnHand]>0,Right("0000000000000" &
CStr([IM2].[QtyOnHand]*10000),13),"0000000000000"),
[WhseCode_INVCNT].[Control_LocKey], [IM2].[BinLocation],
IIf([ItemMaster].[QtyOnHand]>0,Right("0000000000000" &
CStr([ItemMaster].[QtyOnHand]*10000),13),"0000000000000")
FROM (IM2 LEFT JOIN ItemMaster ON [IM2].[ItemNumber]=[ItemMaster].[ItemNo])
INNER JOIN WhseCode_INVCNT ON [IM2].[WhseCode]=[WhseCode_INVCNT].[MAS90_WhseCde]
WHERE [WhseCode_INVCNT].[Control_LocKey]<>'Dont Convert';

Even without the IIF statement like this in another select statement, if returns
a compile error.
RIGHT("0000000" & CStr([IM1].[PurchhaseU/MFactor]*1000),7)
Even without the "0000000" and CStr, it still fails on the RIGHT function. The
TRIM function doesn't work in another query I am using.

I am trying to zero-fill the left most characters so a value of 1000 is
"0001000" when I export it to a fixed-width text file. The table field is Text
and 7 characters in length.

I need to zero fill these types of fields. Does anyone have a way to do this
within the query or even another way?

Thank you,
Mark
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
http://www.mvps.org/access/bugs/bugs0001.htm

So the article might be a little dated, but it describes your problem
exactly.

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.