Convert Text and Numeric in query
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
none seem to work
Thank you for taking the time to report an issue.
What's wrong... Please write below.
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'
I just want to know how numeric convert into text by using Access table
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
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.
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
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
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?
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?
In Access 2003/XP/2000/97, how do you change the date into a numeric value?
I have a query that returns employee tax data I then export to a text file to upload. I am having trouble figuring out how to format my numeric amount fields to what is required. I need to have my numeric fields at 11 digits in length, with leading zeros and two decimal places with an implied decimal (no decimal in output). So if I have 123.4 - I need it to output from the query as 00000012340 - any ideas on how I can do this