changing data type

Boots

unread,

May 30, 2006, 4:37:02 AM5/30/06

to

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

Brendan Reynolds

unread,

May 30, 2006, 5:22:59 AM5/30/06

to

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" <Bo...@discussions.microsoft.com> wrote in message
news:96B587BB-132E-4D4B...@microsoft.com...

Boots

unread,

May 30, 2006, 5:42:02 AM5/30/06

to

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

unread,

May 30, 2006, 5:55:29 AM5/30/06

to

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

--
Brendan Reynolds
Access MVP

"Boots" <Bo...@discussions.microsoft.com> wrote in message

news:D55747D5-7FE2-4438...@microsoft.com...

Jamie Collins

unread,

May 30, 2006, 6:39:12 AM5/30/06

to

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.

--

Douglas J Steele

unread,

May 30, 2006, 8:12:40 AM5/30/06

to

"Jamie Collins" <jamiec...@xsmail.com> wrote in message
news:1148985552....@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!)

Jamie Collins

unread,

May 30, 2006, 9:29:42 AM5/30/06

to

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.

--

Brendan Reynolds

unread,

May 30, 2006, 9:50:14 AM5/30/06

to

"Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:uSnYaJ%23gGH...@TK2MSFTNGP02.phx.gbl...

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