Results 1 to 2 of 2
  1. #1
    aarrivas is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2013
    Posts
    1

    Extract multiple numerical values from a text field

    I have a text field containing random notes input by users in no particular order. The text field contains two numerical values I'd like to extract and import into two fields I've created. Here's an example of what I'm working with.



    Text Field: Project Description
    entry 1 "Installation of 42 photovoltaic solar panels, 4.16kW capacity"
    entry 2 "Installation of roof mounted solar panels (3.12kW, 20 rooftop modules)"
    entry 3 "32 photovoltaic solar panels (16 of which are flush with the roof-top, remaining 16 reverse-pitch), 3.72kW capacity)

    A bit of a rough example but entries are not formatted in any specific manner and my goal is to extract the kW capacity and the number of solar panels installed and import the value into two separate fields ("kW, and "#_panels")

    All the values pertaining to kW capacity are double values containing two decimal places, and all values pertaining to the amount of solar panels installed are whole integers. But as you can see from entry 3, there are additional values specifying the placement of the panels.

    My question, is it possible to extract these values and populate the two fields with the numerical data? I want one field containing the kW capacity, and the other field to contain the total number of solar panels.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,195
    The kW number might not be too hard:

    Val(Mid(x, InStr(x,"kW")-4))

    All depends on consistency, assumes kW will be in the string only once and will always be there and the integer is single digit.

    The other value lacks any consistency in placement and identifier.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. DLookup and Numerical Values
    By The Professor in forum Programming
    Replies: 5
    Last Post: 02-07-2013, 11:54 AM
  2. Replies: 3
    Last Post: 10-15-2012, 01:18 PM
  3. Extract certain values from field
    By tylerg11 in forum Programming
    Replies: 19
    Last Post: 09-23-2011, 03:27 PM
  4. Extract text from middle of a field
    By AccessNubie in forum Access
    Replies: 3
    Last Post: 11-02-2009, 01:13 PM
  5. Extract numbers from text string strored in a field.
    By khabdullah in forum Programming
    Replies: 2
    Last Post: 12-23-2007, 06:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums