Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old June 29th, 2008, 12:16 PM
Authorized User
Join Date: Apr 2006
Posts: 46
Thanks: 0
Thanked 1 Time in 1 Post
Default query problem

hi, i have a little problem, with a query!
i have the records, example:


and i want to take in a new field the same records without the "th."
only the numbers!
should i use in a query iif function? and if yes how?

Old June 30th, 2008, 06:57 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

Will there always only be 3 characters to the right of the period in the value? If so, then do something like:

SELECT Right([MyField], 3) As NewNumber FROM ...

If there will be more or less, then you may have to use an array.

If there will always be only two characters and a period to the left of the value you want, then you could also try:

SELECT Right([MyField], (Len([MyField]) - 3)) As NewNumber FROM ...

but this may be tricky.

Did that help?


Look it up at: http://wrox.books24x7.com
Old June 30th, 2008, 02:22 PM
Friend of Wrox
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts

Much easier than
SELECT Right([MyField], (Len([MyField]) - 3)) As NewNumber FROM ...

is simply
SELECT Mid([MyField], 4) As NewNumber FROM ...

But he says he wants to create a NEW FIELD with those values and *ONLY* get those that have numbers after the period.

And I *assume* that the new field will be an integer.


First, add the new field:
     ALTER TABLE tablename ADD thNumbersOnly INT NULL
And then use UPDATE to put in all the valid valuse:
     UPDATE tablename
     SET thNumbersOnly = CLNG( MID( thField, 4 ) )
     WHERE ISNUMERIC( MID( thField, 4 ) )

That's not perfect. If he has a field with a value such as
then the number there is too large for an integer field and he'd get an error. Or if there is a field with the value
then "1.2E22" passes the ISNUMERIC test but of course is way too large to be an INT value.

But I'd bet it would work in a real DB situation.
Old July 3rd, 2008, 10:16 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG

That last value is not a number, it's text: th.wqt... so that puzzles me. Also, what do you mean a NEW field? Are you storing a calculated value? If the value is always the number part of that th.nnnnn then you should be calculating that each and every time, not storing it.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Problem. rupen Access 3 April 27th, 2007 07:43 AM
Query Problem bundersuk VB Databases Basics 0 December 30th, 2006 07:50 AM
problem with query harpua Classic ASP Databases 1 January 24th, 2005 12:36 PM
Problem in query leo_vinay Classic ASP Databases 5 January 21st, 2005 06:32 AM
query problem mateenmohd SQL Server 2000 7 September 9th, 2003 11:58 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.