Topics Search

Convert Text and Numeric in query

Convert Text and Numeric in query
Views: 17
I have a query the appends an access database from an excel spreadsheet (via a link in Access) taken from another program. My problem is that one of the excel columns contains both text and numeric data and I keep getting a 'data mismatch'. I would like to convert both to text I have tried a number of options
exp2:csng([Zip Code])
exp2:Str([Zip Code])
none seem to work
Sponsored Links:

More topics

 

Date Conversion

Preview
I am trying to convert a date in the text format to Date format using the CDate() function in a sql query in MS Access, which is working fine. But when I try to export the results to a an excel file, the date is being displayed as text field again.

How do I correctly convert & format the text values and then export the values as Date (Numeric) in to Excel?

And the date is in the UK format 'dd/mm/y'
 

numeric convert into text by using Access table

Preview
I just want to know how numeric convert into text by using Access table
 

Justify numeric text field

Preview
I imported an Excel file into a table where the first field is a Text field (JobNo) that is left justified and can be either numeric or alphanumeric. In order to query against a legacy database I need to make sure the field is properly justified. If numeric then it needs to be 9 position space left filled such as " 12345". If alphanumeric then it can remain left justified such as "12345W". I know I can use Right(Space(9) & [JobNo],9) for the numeric field. I tried writing an Update Query using If IsNumeric but can't figure out the syntax.any combination I try returns a syntax error
 

Trouble linking a table to a query

Preview
I'm an "intermediate user" of Access 2003. I want to link a newly created two-field table to an existing query via a common numeric field, but I can't get it to work. (The second field in the tbl is a text field.) I've tried all different join options in the query design view, but the results still won't display when I run the query. I'm linking via the numeric field and trying to get the text field to display in the datasheet view.

I may have a data field incompatibility problem, but I can't be sure. I know how to check the data type in the table, but I can't find a way to check the data type for the calculated data field in the the query. (I'm trying to connect a numeric calculated field in the query to a counterpart numeric field in the table.)

I tried looking thru the online Help but haven't found a solution yet.
 

Convert number to fixed length text field

Preview
I imported an Excel sheet with a field defined as number/numeric. In my make table query I need to convert that field to a fixed 9 position field with leading spaces.
I cannot concatenate the field with a set number of leading spaces because the input field is not always th same size.
I have tried using different attributes of the Format function but no luck
 

Convert Text to number

Preview
How can I convert a text field to numeric value?

The text values a
35
15
1.5
0.015
9900

The desired result need to be:
35
15
1.5
0.015
9900

Then in a separate field I need to store the (Greater than) and (less than) signs from the same text filed so:

35 (please note the space between the and the number 35)
15 (please note that there is no space between and the number 15) 1.5 0.015
9900

Will give me:
 

text data to numeric data

Preview
there

I have one table has a column called evaluation ( text data type ), and I want to take the data and convert it into a number data type in another table or a query , something like the following
excellent=90 to 100
accepted = 60 to 79
under 60 is failed
 

Option Buttons convert values to text

Preview
I am designing a database for our audit teams to carry out reviews of our claims handlers and have put next to each of the questions on a form an option group of three option buttons, "Yes", "No", "Not Applicable" with the values "1", "-1" and "0" respectively.

This value is then stored in a table under therelevant field, intQuestionValue, for Q1, Q2, Q3 etc.

This works well for calculating scores, percentages etc. However the manager of the team has said that there will be times when he wants to report by the number of"No" answers for example, so that he look at trends and build these into training plans etc.

I do have a table, tblScores, which has two columns in it. One for the text element, "Yes", "No", "N/A" and the other has the corresponding numeric value in it.
Because there are numerous questions I cannot seem to create a join in a query for the numeric value to bring back the text value.
The only other method that came to mind was to write the text outcome to a table as well at the same time, but I'm not sure how I would do this at the same time as clicking the optionbutton, or would it all save when I (the user) closes the form?
 

Convert a date to a numeric value

Preview
In Access 2003/XP/2000/97, how do you change the date into a numeric value?
 

calculated field to display numeric data from combo box as text

Preview
I have entered numeric data into fields in evaluation of a number venue conditions.
Example fields are road, parking, fence, shade, etc.
Each field I entered a value 1-10 (meaning, 1 = unusable. 5 = just OK. 10 = excellent condition)
I run a query to show just these the condition values for each field at each venue, to summarise the venue's condition.
How do I now display the value 1 as text "unusable", 5 as text "Just OK" etc.
Can I use a calculated field next to each field in my query to display the text meaning for each value, so I can also keep the numeric value?