Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

Makeing a text field act as a number field ???

I have a field in my DB that gets imported from a *.csv file.
This field is considered a text field but I would prefer it act as a number field.
This field is called "Living_Area" and it has a number of records that look like this:
1320 sqft.
2455 sqft.
900 sqft.
760 sqft.
1950 sqft.

etc...

Because it contains "sqft." it acts like a text field, how can I create a custom field or somthing similar that will make this field act as if it is a number field?

The reson I need it to act like a number field is becuase I will be exporting this from Access into a special excel file that allows you to search number fields using <,>,<=,>=...
and I want to have that capability w/ this field w/out having to get rid of "sqft."

Please help,
Thanks,
BassKozz
0
basskozz
Asked:
basskozz
  • 5
  • 3
  • 2
  • +2
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
create a query like this and export the query

            select val([Living_Area]) as LArea from tableName
0
 
Bob LambersonSoftware EngineerCommented:
Where ever you use the field, use the Val() as capricorn1 implies.

so if you set a textbox to the value in the field, do it like        txtArea.Text = Val(Living_Area)
This will put only the number value in the textbox, or any other control or variable you want to use, which will allow you to treat it as numeric.

Bob
0
 
basskozzAuthor Commented:
capricorn1 and Bob,

thanks for the advice,
Currently my Excel spreadsheet has a temporary macro in it that will convert the Living_Area field into a custom field, so that it will work like a number field:
Public Sub ChangeTextColumnToNumber(lngColumnNo As Long)
   
    '/select ther entire column
    Columns(lngColumnNo).Select
    '// remove all sqft
    Selection.Replace "sqft.", ""
    '// change the number format to display sqft
    Selection.NumberFormat = "0 ""sqft. """
    '// set the alignment to left
    Selection.HorizontalAlignment = xlLeft
End Sub
   

How can I set something like this up for the field in MS Access, so that when I export it to Excel this process will already be completed?

-BassKozz
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rey Obrero (Capricorn1)Commented:


try to export the query i posted and see the result without using your macro in excel.
0
 
basskozzAuthor Commented:
capricorn1,

I did try your query... all it does is selects the numeric value from the field minus the text "sqft.".

I am looking for a solution where I can have the cell display in excel w/ both the numeric value and the text but have the cell act as a numeric cell (ignoring the text)...

The MS Excel modual I posted can do this, but I would rather create a similar modual in MS Access and then export to MS Excel
0
 
Rey Obrero (Capricorn1)Commented:
sorry. i don't think access is capable of doing that.
0
 
basskozzAuthor Commented:
capricorn1,

Yah, thats what I figured.  Thanks for your input...
I will leave this post open to see if anyone has any other suggestions.

Thanks again,
BassKozz
0
 
Bob LambersonSoftware EngineerCommented:
can you post the code you are using to export to excel, or are you manually exporting it?

Bob
0
 
basskozzAuthor Commented:
manually exporting
0
 
davomanCommented:
Combining these options if you have a button that runs the export to a an excel file then in code you can call the macro from access and have it run straight away so that when you get the excel file the macro has already done its job. you would get the advantage you were looking for by combining the already suggested methods with autamating the running of your macro
0
 
basskozzAuthor Commented:
davoman,

how could I do that ?

-BassKozz
0
 
davomanCommented:

if you have already declared a workbook in the code the the below code will run the macro in the
workbook called runme


xlWbk.Application.Run "Test.xls!Runmee"

here is a microsft link

http://support.microsoft.com/kb/177760/EN-US/
0
 
manthaneinCommented:
perhaps you can use this  if you still want to it in  access

you can add another field  and set it's value to

cint(mid(strr,1,instr(1, strr, "s")-1))

therefore converting the string to integer
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now