Results 1 to 8 of 8
  1. #1
    Lounger
    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.

  2. #2
    Bronze Lounger
    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 user-defined 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

  3. #3
    Bronze Lounger
    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

  4. #4
    Plutonium Lounger
    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 non-integer, 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

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 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>

  6. #6
    New Lounger
    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.

  7. #7
    Bronze Lounger
    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.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 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>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •