Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

changing data type

Discussion in 'Information Technology' started by Boots, Jul 28, 2006.

  1. Boots

    Boots
    Expand Collapse
    Guest

    Hi. I am trying to run a query to get results from two linked tables. For
    some inexplicable reason the data types on the two fields that the link
    should occur on, are different data types. One is number and one is text.
    I need to run a sub query to convert the text type field to a number type.
    I no how to do this the other way round using to_char (number to text) but
    can't do it the way I need to (text to number). The characters in the text
    field are all integers (no letters). any help on how I can achieve this
    would be great
     
  2. Loading...

    Similar Threads Forum Date
    S Asia Thinking about the big move: The Sikh way of life changing in Khyber-Pakhtunkhwa Breaking News Mar 22, 2014
    Opinion Haanji's Quick Takes on a Changing World (September 23, 2013) Breaking News Sep 22, 2013
    Changing my last name to Kaur... Questions and Answers May 15, 2013
    Life-changing Inspirational Stories Mar 31, 2013
    USA Sikhism and the Changing Electoral Demographic Breaking News Mar 8, 2013

  3. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    There's the Val function ...

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctval.asp

    Or various functions to convert to specific numeric types, e.g. CInt for
    Integer, CDbl for Double, CCur for Currency, etc.

    BTW: 'to_char'? That's Oracle, isn't it?

    --
    Brendan Reynolds
    Access MVP

    "Boots" <Boots@discussions.microsoft.com> wrote in message
    news:96B587BB-132E-4D4B-8B4D-97BFE3BF26A4@microsoft.com...
    > Hi. I am trying to run a query to get results from two linked tables.
    > For
    > some inexplicable reason the data types on the two fields that the link
    > should occur on, are different data types. One is number and one is text.
    > I need to run a sub query to convert the text type field to a number type.
    > I no how to do this the other way round using to_char (number to text) but
    > can't do it the way I need to (text to number). The characters in the
    > text
    > field are all integers (no letters). any help on how I can achieve this
    > would be great
    >
    >
     
  4. Boots

    Boots
    Expand Collapse
    Guest

    Yes 'to_char' is Oracle. The linked tables come from a massive Oracle db.
    I am using access to query it. I've worked out that I can use 'to_number'
    and this should work.



    "Brendan Reynolds" wrote:

    > There's the Val function ...
    >
    > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctval.asp
    >
    > Or various functions to convert to specific numeric types, e.g. CInt for
    > Integer, CDbl for Double, CCur for Currency, etc.
    >
    > BTW: 'to_char'? That's Oracle, isn't it?
    >
    > --
    > Brendan Reynolds
    > Access MVP
    >
    > "Boots" <Boots@discussions.microsoft.com> wrote in message
    > news:96B587BB-132E-4D4B-8B4D-97BFE3BF26A4@microsoft.com...
    > > Hi. I am trying to run a query to get results from two linked tables.
    > > For
    > > some inexplicable reason the data types on the two fields that the link
    > > should occur on, are different data types. One is number and one is text.
    > > I need to run a sub query to convert the text type field to a number type.
    > > I no how to do this the other way round using to_char (number to text) but
    > > can't do it the way I need to (text to number). The characters in the
    > > text
    > > field are all integers (no letters). any help on how I can achieve this
    > > would be great
    > >
    > >

    >
    >
    >
     
  5. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    It would not work in a JET query. Are you using pass-through queries?

    --
    Brendan Reynolds
    Access MVP

    "Boots" <Boots@discussions.microsoft.com> wrote in message
    news:D55747D5-7FE2-4438-A694-FF1384FFF8D9@microsoft.com...
    > Yes 'to_char' is Oracle. The linked tables come from a massive Oracle
    > db.
    > I am using access to query it. I've worked out that I can use 'to_number'
    > and this should work.
    >
    >
    >
    > "Brendan Reynolds" wrote:
    >
    >> There's the Val function ...
    >>
    >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctval.asp
    >>
    >> Or various functions to convert to specific numeric types, e.g. CInt for
    >> Integer, CDbl for Double, CCur for Currency, etc.
    >>
    >> BTW: 'to_char'? That's Oracle, isn't it?
    >>
    >> --
    >> Brendan Reynolds
    >> Access MVP
    >>
    >> "Boots" <Boots@discussions.microsoft.com> wrote in message
    >> news:96B587BB-132E-4D4B-8B4D-97BFE3BF26A4@microsoft.com...
    >> > Hi. I am trying to run a query to get results from two linked tables.
    >> > For
    >> > some inexplicable reason the data types on the two fields that the link
    >> > should occur on, are different data types. One is number and one is
    >> > text.
    >> > I need to run a sub query to convert the text type field to a number
    >> > type.
    >> > I no how to do this the other way round using to_char (number to text)
    >> > but
    >> > can't do it the way I need to (text to number). The characters in the
    >> > text
    >> > field are all integers (no letters). any help on how I can achieve
    >> > this
    >> > would be great
    >> >
    >> >

    >>
    >>
    >>
     
  6. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Brendan Reynolds wrote:
    > There's the Val function ...
    >
    > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctval.asp
    >
    > Or various functions to convert to specific numeric types, e.g. CInt for
    > Integer, CDbl for Double, CCur for Currency, etc.


    Out of interest, do you have a real life example where Val is
    preferable to CDbl (or a more specific cast function)? The example in
    the help

    Val(" 1615 198th Street N.E.")

    doesn't sound credible to me! Thanks.

    BTW it should be noted that if the OP is casting data as double float
    to join the two tables then the inexact nature of the type means that
    an equi-join should be avoided and replaced with a BETWEEN construct
    (or equivalent).

    Jamie.

    --
     
  7. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1148985552.455762.62840@g10g2000cwb.googlegroups.com...
    >
    > Brendan Reynolds wrote:
    > > There's the Val function ...
    > >
    > >

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctval.asp
    > >
    > > Or various functions to convert to specific numeric types, e.g. CInt for
    > > Integer, CDbl for Double, CCur for Currency, etc.

    >
    > Out of interest, do you have a real life example where Val is
    > preferable to CDbl (or a more specific cast function)? The example in
    > the help
    >
    > Val(" 1615 198th Street N.E.")
    >
    > doesn't sound credible to me! Thanks.


    While I agree that Val(" 1615 198th Street N.E.") doesn't seem that
    useful, Val("1615 Main Street N.E.") is perhaps a little more believable.

    That will return 1615: CLng("1615 Main Street N.E.") will return an error.


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)
     
  8. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Douglas J Steele wrote:
    > While I agree that Val(" 1615 198th Street N.E.") doesn't seem that
    > useful, Val("1615 Main Street N.E.") is perhaps a little more believable.
    >
    > That will return 1615: CLng("1615 Main Street N.E.") will return an error.


    Sorry, I still don't get it. Why in real life would you want to cast
    "1615 Main Street N.E." as a double float? There better ways of parsing
    an address...?

    Thanks again,
    Jamie.

    --
     
  9. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:uSnYaJ%23gGHA.896@TK2MSFTNGP02.phx.gbl...
    > "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    > news:1148985552.455762.62840@g10g2000cwb.googlegroups.com...
    >>
    >> Brendan Reynolds wrote:
    >> > There's the Val function ...
    >> >
    >> >

    > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctval.asp
    >> >
    >> > Or various functions to convert to specific numeric types, e.g. CInt
    >> > for
    >> > Integer, CDbl for Double, CCur for Currency, etc.

    >>
    >> Out of interest, do you have a real life example where Val is
    >> preferable to CDbl (or a more specific cast function)? The example in
    >> the help
    >>
    >> Val(" 1615 198th Street N.E.")
    >>
    >> doesn't sound credible to me! Thanks.

    >
    > While I agree that Val(" 1615 198th Street N.E.") doesn't seem that
    > useful, Val("1615 Main Street N.E.") is perhaps a little more believable.
    >
    > That will return 1615: CLng("1615 Main Street N.E.") will return an error.


    Val will also accept an empty string, returning 0 (zero) where CDbl would
    raise an error. But I do not claim that this is a 'real world example' of
    Val being 'preferable' to CDbl. It was never my attention to emphasise Val
    over the explicit conversion functions. I originally intended to post links
    to the help topics for both Val and the explicit conversion functions. I got
    distracted when I discovered that the explicit conversion functions do not
    appear in the Access 2003 VBA help lists of functions. Take a look at the
    following two lists. The explicit conversion functions don't appear in
    either of them, and some of the date functions appear under the wrong
    categories in the categorised list. I've reported the error.

    Functions (by category)
    http://office.microsoft.com/assistance/hfws.aspx?AssetID=HP011359591033

    Functions (alphabetical)
    http://office.microsoft.com/assistance/hfws.aspx?AssetID=HP011353121033

    --
    Brendan Reynolds
    Access MVP
     

Share This Page