Results 1 to 15 of 15
  1. #1
    msadiqrajani is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    12

    Filter data by number as text column.

    Hi,

    I have large dataset(more then 15,00,000 rows)
    In one column I have Amount stored as text eg: $15,000.00 $ 12,000.00 $ 17,520.00

    Data is imported from various small files and is updated regularly.
    Now I need to filter dataset based on this column criteria like > 15000, <= 5000

    How can I configure query to filter data.For example data are:
    Id Detail Value Part
    25252 Some text here $17,294.00 1
    14202 Some text here $18,684.00 2
    12142 Some text here $9,214.00 1
    35214 Some text here $1,214.00 2
    78565 Some text here $17,714.00 1
    95214 Some text here $13,274.00 6
    96587 Some text here $19,684.00 4
    78563 Some text here $8,374.00 3
    85634 Some text here $10,714.00 1


  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can add a column to the query. In design view:

    ValueNumeric: CCur(Value)

    that will give you a numeric column you can use for the criteria. Of course, the ideal would be to change that field to Currency instead of Text.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    msadiqrajani is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    12
    I GOT #Error FOR ALL ROWS USING CCUR
    ALSO WHEN I TRIED TO CHANGE COLUMN FROM TEXT TO CURRENCY........ITS DELETING DATA OF WHOLE COLUMN....

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What is the SQL of the query you tried to use it in? Did you change the field name to yours?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    msadiqrajani is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    12
    I used thus query where fieldname is F29

    SELECT CCur([F29]) AS Expr1 FROM Data;

    Data is imported through DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Data", filename, False

    Access forcing me to set all columns in table as text...I am merging many Excel sheets to table.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That looks okay, offhand. Can you post the db, or a sample with a limited number of records?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    msadiqrajani is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    12
    See attached database.....It has over 200 rows
    I need formula for field F29 in data table

    In module 1 is a code I am using to import data function called "copydata"
    Attached Files Attached Files

  8. #8
    msadiqrajani is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    12
    Has any one got it working?

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sorry, I didn't have 2010 available but I just fired up a VM with it. I only get #Error when the field is blank. It appears to contain spaces, not Null, so try:

    IIf(Len(Trim([F29]))=0,0,CCur([F29]))
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    msadiqrajani is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    12
    I tried you last one.....
    I got 0.00 for blank fields and #Error for field with data

  11. #11
    msadiqrajani is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    12
    Hurrahhhhh......I got it working.

    I used ------newF29: Val(Replace(Replace([f29],"$",""),",",""))------As a formula in query.I replaced both $ sign and Comma
    I was unable to filter in this query,.....So I saved this query and created new one to filter this query.

    Its working......But will it make effect in performance when data will be more then 1.5 million rows.?

  12. #12
    msadiqrajani is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    12
    Thank you very much for your help and time..

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Funny, because it worked fine in my test, see attached. In any case, glad you found a working solution.

    Click image for larger version. 

Name:	PaulTest.jpg 
Views:	2 
Size:	112.1 KB 
ID:	8838
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    msadiqrajani is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    12
    In my solution case..
    Will it be good for 1.5 million rows data.
    Or I try for other solution too.

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It may be slow, but it should work. Is it an option to change that field to currency and convert data as it's imported? That way you'd have the field in the proper format and queries should run faster.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 2
    Last Post: 07-21-2012, 01:21 AM
  2. Replies: 1
    Last Post: 12-08-2011, 08:03 AM
  3. Replies: 1
    Last Post: 08-18-2011, 08:35 AM
  4. pulling text values into FK number column
    By REBBROWN in forum Database Design
    Replies: 2
    Last Post: 08-30-2010, 05:04 PM
  5. Filter non-text data
    By bdriscoll in forum Access
    Replies: 2
    Last Post: 09-25-2009, 05:49 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