Topics Search

Convert Text and Numeric in query

Convert Text and Numeric in query
Views: 18
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:

Similar posts...

 

Date Conversion

Date Conversion Icon
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'
 

Option Buttons convert values to text

Option Buttons convert values to text Icon
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

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

Using Access 2003 convert numeric month to text for reports

Using Access 2003 convert numeric month to text for reports Icon
Looking for a way to create a report using data that has dates formated as 01/01/2003 ( basically mm/dd/y ) but want to display on the report sorted under categories ( using a query to sort the categories ) but printed out as long version of the month. January 2005 etc.
 

Public Function results are exporting as text and not numeric

Public Function results are exporting as text and not numeric Icon
If f < 0 Then
JAV = (e * f) * -1
Else
JAV = 0
End If
End Function

The result is exporting as a text field and not numeric and I can't specify as currency in query. Here is how I am calling the function.

ExtVariance: JAV([ONHAND],([AVGCOST]-[DirectCurrentPrice])*-1)
 

Lookup Based on Report Text Box

Lookup Based on Report Text Box Icon
I have a report which contains a text box with a numeric value. I have a table with the numeric value and a text description equivalent. Can I create an additional text box on the reportthat will look up the value in the text box and return the text equivalent?
 

A problem in join query

A problem in join query Icon
I have 3 tables in Access:

1- categories
catId AutoNumber,
catName text,

2- subCategories
subCatId AutoNumber,
subCatName text,
catId Numeric

3- models
modelId AutoNumber,
modelName text,
subCatId Numeric,
display true/false

On categories page, have to display only those categories which have models listed under them or at least 1 model has its display property set to true.
 

Can you build an if statement inside a query

Can you build an if statement inside a query Icon
I am designing a query that takes a string and numeric field from two different tables then finds the difference between the two numeric fields. In the string fields I have the criteria which is the same for both tables.

The problem I am having is when either the criteria is not met it crashes. I need to have a way to incorporate an if statement or something that can take the string from the string field then check to see if there is a value in the numeric field then show that value or if there is none then set the value to zero and finish the query. What do I need to do to make this work?
 

Union Query and multiple entries

Union Query and multiple entries Icon
I'm running into an issue with a union query - it is not properly gathering data from the table. Database is relatively simple - I enter numeric values in various fields: Example: Field [Front damage] - gets a numeric value of 1 every time a damage is found.

There is a simple query which shows the sum of the values entered into each field per month. I compare the sum monthly with another table through a union query. this is where the problem occurs.

For example: June 2011 in the union query - shows a sum of 16 - however the simple query shows a sum of 17 for the same month.
The issue seems to be with double entries on the same day. What I mean by that is - On June 16 (ID 2468) there are entries in two fields - Field A has a numeric value of 1 and Field B has a numeric value of 1.

Seems like the union query is only picking up one of the values. How can I get the query to pick up both values?
 

Preventing Numeric Data from being entered into a Microsoft Access Text field

Preventing Numeric Data from being entered into a Microsoft Access Text field Icon
There may be times when you wish to prevent numeric values from being entered into a text data type field.
When you have set the data type to Text you will have the following settings: Text or combinations of text and numbers, as well as numbers that don't require calculations, such as phone numbers.