Results 1 to 8 of 8

20021213, 18:30 #1
 Join Date
 Oct 2002
 Posts
 25
 Thanks
 0
 Thanked 0 Times in 0 Posts
XP (Left$([Avg],InStr(1,[Avg],'.')1)) & Right$([Avg],Len([Avg])InStr(1,[Avg],'.'))) with values without the '.')
Disclaimer: I do not think currency data type helps me solve problem.
Values are numeric, calculations are made, now I want to go over to string functions.
Create two numeric fields with:
DoCmd.RunSQL "ALTER TABLE [tblFIType_PeerGroups_Redemptions] ADD Avg NUMERIC;"
DoCmd.RunSQL "ALTER TABLE [tblFIType_PeerGroups_Redemptions] ADD MaxAvg NUMERIC;"
Perform calculations:
DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [Avg]=0;"
DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [MaxAvg]=0;"
DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [Avg]=([SumOfREDEMPTION_AMT]/[CountOfID]) WHERE [CountOfID]>49;"
DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [MaxAvg]= [Avg]*1.2;"
DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [MaxAvg]=125.00 WHERE [MaxAvg]=0;"
DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [Avg]=125.00 WHERE [Avg]=0;"
DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [MaxAvg]=125.00 WHERE [Avg]<20.00;"
Now I am ready to go over to string to parse and pad to get six digit strings
However, InStr function to parse around "." when value has no "." returns #ERROR
e.g.,
Have amounts like:
19.22
20.22
13.53
That are changed to
value & "00"
i.e.,
192200
202200
135300
So, I use
Left$([Avg],InStr(1,[Avg],".")1)) & Right$([Avg],Len([Avg])InStr(1,[Avg],".")))
Not all values contain the "."
These are the integer values from the caluclation that have no significant digits after the decimal.
e.g.,
9
13
80
90
that do not have significant digitis left of the decimal hence the InStr returns #ERROR
they need to be
090000
130000
800000
900000
How can I evaluate to find the values without the "." so I can add ".00" to the end and have all string values ready to go with the rest of my parsing/padding
Something like
Left$([Avg],InStr(1,[Avg],".")1))=False or True;
No clue.
Have tried combo of InStr and Length however, integers retrun an #ERROR value not a length
the rest e.g.
IIf(Len([MaxAvg])=5,"0" & [MaxAvg],"00" & [MaxAvg]))
TIA.

20021213, 20:14 #2
 Join Date
 Nov 2001
 Location
 Arlington, Virginia, USA
 Posts
 1,394
 Thanks
 0
 Thanked 3 Times in 3 Posts
Re: XP (Left$([Avg],InStr(1,[Avg],'.')1)) & Right$([Avg],Len([Avg])InSt
You can use a function like this:
Public Function FormatAsText(ByVal dblVal As Double) As String
FormatAsText = Replace(Format(dblVal, "00.00"), ".", "") & "00"
End Function
The Replace function gets rid of the decimal point. Note that the Replace function was introduced in VB/VBA 6.0 and will NOT work in a query expression, at least not in A2K, so you'd have to wrap it in a userdefined function as shown above to use in query. I defined the numerical field as Double. Also to note the obvious, I would not recommend naming a field "AVG" since AVG is also name of an SQL aggregate function.
HTH

20021213, 22:50 #3
 Join Date
 Nov 2001
 Location
 Arlington, Virginia, USA
 Posts
 1,394
 Thanks
 0
 Thanked 3 Times in 3 Posts
Re: XP (Left$([Avg],InStr(1,[Avg],'.')1)) & Right$([Avg],Len([Avg])InSt
PS: Got a chance to test with AXP (Access 2002) and in XP you CAN use Replace function directly in query expression. Example:
SELECT [AVG], Replace(Format([AVG],"00.00"),".","") & "00" AS Expr1
FROM Table2;
This expression will format the AVG field in desired string format. Recommend use of brackets whenever referencing AVG field in a query expression.
HTH

20021213, 23:59 #4
 Join Date
 Dec 2000
 Location
 Sacramento, California, USA
 Posts
 16,775
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: XP (Left$([Avg],InStr(1,[Avg],'.')1)) & Right$([Avg],Len([Avg])InSt
You're getting an error when you use InStr on an integer because of this:
Left$([Avg],InStr(1,[Avg],".")1))
If the value is an integer, Instr will return a 0, so your statement translates into Left$([Avg], 1), which gives you an error. If you break it into several parts and use the instr function to return the location of the decimal (by the way, other parts of the world don't use the dot as a decimal delimiter) in the first part, you can then test that value to see whether you have an integer or something else. If you have an integer, you just use the value in the field.
If you have a noninteger, you could use
Mid([Avg], 1, intDecimal 1) & mid([Avg], intDecimal + 1)
to make it a whole number, where intDecimal represents the location returned by InStr.Charlotte

20021214, 00:43 #5
 Join Date
 Dec 2001
 Location
 Melbourne, Australia
 Posts
 4,616
 Thanks
 0
 Thanked 32 Times in 32 Posts
Re: XP (Left$([Avg],InStr(1,[Avg],'.')1)) & Right$([Avg],Len([Avg])InSt
Why not just multiply it by 10,000?
Or am I missing something?
Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

20021214, 07:22 #6
 Join Date
 Nov 2001
 Location
 Virginia
 Posts
 24
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: XP (Left$([Avg],InStr(1,[Avg],'.')1)) & Right$([Avg],Len([Avg])InSt
A little bit of knowledge is a dangerous thing.
Oops.
Thanks both.

20021214, 09:37 #7
 Join Date
 Nov 2001
 Location
 Arlington, Virginia, USA
 Posts
 1,394
 Thanks
 0
 Thanked 3 Times in 3 Posts
Re: XP (Left$([Avg],InStr(1,[Avg],'.')1)) & Right$([Avg],Len([Avg])InSt
Because then you'd get a number and he needs a string. You'd still need to use Format function to return a string, but you are right, it would be simpler syntax because then you would not even need Replace function, and thus more efficient. Revised example:
SELECT [AVG], Replace(Format([AVG],"00.00"),".","") & "00" AS AVGSTR1, Format(([AVG]*10000),"000000") AS AVGSTR2
FROM Table2;
In this example both calculated fields provide identical results.

20021214, 18:59 #8
 Join Date
 Dec 2001
 Location
 Melbourne, Australia
 Posts
 4,616
 Thanks
 0
 Thanked 32 Times in 32 Posts
Re: XP (Left$([Avg],InStr(1,[Avg],'.')1)) & Right$([Avg],Len([Avg])InSt
Well, why not multiply by 10,000 then format?
Format(Number*10000,"000000")
Pat <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>