Convert Numeric Value To Text

Mar 11, 2008

Im trying to construct a query which returns a field that has 21 different types of values.

Is there a way to assign a text value to each numeric value when the results are displayed?

Eg: 21 = "Local Sales", 22 = "National Sales", 23 = "International Sales".. and so on.

Ive looked at the Choose and IIF functions but I cant seem to work it out

..............

Anouther little problem, which should be basic. I want to exclude certain records with values such as 31 and 41 and 42.

When i type <> 31 or <> 41 <> 42 in the criteria they are all still displayed. Although when I type one of those criteria by themselves they ARE EXCLUDED. Is there something dumb I am missing here??

thanks in advance.

View Replies


ADVERTISEMENT

Queries :: How To Convert Text Fields To Numeric

Aug 16, 2014

I have an ms access Database(2013 version). There are about 10000 records. There are some columns with field property of "short text" but contains the values like that 0.4,7, 9.0 etc I would like to convert the "short text" into "double" without loosing information.

View 3 Replies View Related

Convert Text (YYMM) To Numeric Retaining Lead Zero Or Zeros (Year 2000)

Aug 28, 2012

I have a DB containing over 4 million rows of data, each with a SALES_PERIOD based on YYMM. Our Developers set it up as Text, and index this item accordingly, I would like to format as numeric with the lead zero to allow quicker indexing, but when I format it it returns to text this is the formula I use?

Format((Val([SALES_PERIOD])), "0000")

View 5 Replies View Related

Forms :: Sort Numeric In Alpha-numeric Text

Mar 11, 2014

I have a text field having data i-e HO-1, HO,2, ACW-25 and so on. The field name is nBadge_num and is Unique. The data in this field is sorted automatically like 1, 10, 11, 12, 13, 2, 3, 4, 5...because this is the text field.

The number on the form is automatically generated, when the user type HO- for example on field exit event. The last number will generate like HO-5.

Code for automatic number generation is:

Dim dbs As Database, rst As Recordset, Response
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Max(Right([nBadge_Num],Len([nBadge_Num])-" & Len(Me.NBadge_Num) & ")) AS MaxNo " _

[Code]....

My problem is when the number is generated it give HO-5 instead of HO-14, How can I sort the numeric part of the field ?

View 2 Replies View Related

Convert Characters To Numeric

Jul 15, 2006

I don't know VBA and I am not a programmer.
I have a table with locations identified by latitude and longitude.
I have separate fields for degrees minutes seconds and decimal degrees.
For example, 50 degrees 30 minutes 0 seconds is also stored as
50.5 degrees. I have a radial search query that finds all targets
within a given distance from a location. The three fields input by the
user are latitude, longitude and distance. The problem is the query
only works with decimal degrees. I want the user to be able to
input in a format like "50 30 0". How do I convert a character string
with non-numeric characters to a numeric string?

View 2 Replies View Related

Convert Memo To Numeric/double

Nov 10, 2005

I have two memo field in a table. I have to calcluate the sum of the two memo fileds. Here is my sql

Select sum (amt1+amt2) as tot from employee group by id;

I am getting error cannot have memo fields in aggregate function.

How do I convert the memo fields to numeric in sql query and caculate the sum.

Thanks.

View 1 Replies View Related

Search, Text VS Numeric.

Jun 21, 2005

I have made my Search Form that will search through my tables, some how I have a syntax error for the following: year, Section and ID, these fields are not text I use numeric values so I think the search is not working for that reason, I am not sure how can I fix it though do I have do add val() or sth.


Private Sub Command8_Click()

On Error GoTo Command8_ClickError

Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim strWhereCondition As String
Dim strSql As String

strWhereCondition = ""
strSql = "Select distinct Id From AcademicVideo Where True "

'================================'

If Not IsNull(ID) And Trim(ID) <> "" Then
strSql = strSql & " And [Id] = " & [ID]
End If

'================================'

If Not IsNull(Course) And Trim(Course) <> "" Then
If InStr(Course, "*") = 0 Then
strSql = strSql & " And [Course] = '" & [Course] & "'"
Else
strSql = strSql & " And [Course] like '" & [Course] & "'"
End If
End If

'================================'

If Not IsNull([Format]) And Trim([Format]) <> "" Then
If InStr([Format], "*") = 0 Then
strSql = strSql & " And [Format] = '" & [Format] & "'"
Else
strSql = strSql & " And [Format] like '" & [Format] & "'"
End If
End If

'================================'

If Not IsNull([Title]) And Trim([Title]) <> "" Then
If InStr([Title], "*") = 0 Then
strSql = strSql & " And [Title] = '" & [Title] & "'"
Else
strSql = strSql & " And [Title] like '" & [Title] & "'"
End If
End If

'================================'

If Not IsNull([Lecturer]) And Trim([Lecturer]) <> "" Then
If InStr([Lecturer], "*") = 0 Then
strSql = strSql & " And [Lecturer] = '" & [Lecturer] & "'"
Else
strSql = strSql & " And [Lecturer] like '" & [Lecturer] & "'"
End If
End If

'================================'

' If Not IsNull([Section]) And Trim([Section]) <> "" Then
' If InStr([Section], "*") = 0 Then
' strSql = strSql & " And [Section] = " & [Section] & "'"
' Else
' strSql = strSql & " And [Section] like " & [Section] & "'"
' End If
' End If

'================================'

If Not IsNull([Semester]) And Trim([Semester]) <> "" Then
If InStr([Semester], "*") = 0 Then
strSql = strSql & " And [Semester] = '" & [Semester] & "'"
Else
strSql = strSql & " And [Semester] like '" & [Semester] & "'"
End If
End If

'================================'

If Not IsNull([Year]) And Trim([Year]) <> "" Then
If InStr([Year], "*") = 0 Then
strSql = strSql & " And [Year] = " & [Year] & "'"
Else
strSql = strSql & " And [Year] like " & [Year] & "'"
End If
End If

'================================'


If Not IsNull([Description]) And Trim([Description]) <> "" Then
If InStr([Description], "*") = 0 Then
strSql = strSql & " And [Description] = '" & [Description] & "'"
Else
strSql = strSql & " And [Description] like '" & [Description] & "'"
End If
End If

'================================'


Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
' If (rs2.RecordCount = Null) Then

' MsgBox "Enter A valid Record"

If (rs.RecordCount = 0) Then
MsgBox "Could Not found "

Else
strWhereCondition = "[Id] In (" & rs!ID
Do While Not rs.EOF
strWhereCondition = strWhereCondition & ", " & rs!ID
rs.MoveNext
Loop
strWhereCondition = strWhereCondition & ")"
End If
rs.Close

If strWhereCondition <> "" Then
DoCmd.OpenForm "ACVideo", acNormal, , strWhereCondition
DoCmd.Close acForm, "Search AcVideo"
End If

Exit Sub

Command8_ClickError:
MsgBox Err.Number & " " & Err.Description

Resume Next
End Sub

View 2 Replies View Related

Numeric To Text In Excel Help

Mar 4, 2008

Hello there,

In excel spreadsheet, when I eg type in a numeric column 10 1500 so on, I would column11 appear text such as one thounsand and five hundred. How can I convert this?

Would that be possible?

Thank you in advance

Cheers

View 1 Replies View Related

Formatting Numeric Data As Text

Mar 14, 2007

Access 97 SR1

I am importing a file that has two numeric fields in it. I was asked to import as fixed length text fields with leading zeroes and no decimal point.

I am supposed to export the data in Csv format.

Source field from IBM iSeries DB: quantity is 15,3. Quantity source value is 9, requested text format of five characters 00009, the Csv result is 9. The field in Access is defined as Text 5 characters.

Source field from IBM iSeries DB: price is 17,4. Price source value is 1020.0000, requested text format of nine characters 000001020, the Csv result is 1200.00. The field in Access is defined as Text 9 characters.


Thanks,

Dave

View 1 Replies View Related

Enter Text Into A Numeric Field

May 14, 2005

I use Access as my grading program at school. Right now, if I want to excuse a student for an assignment, I delete that record.

What I would like to do is put an "X" or some other string into a field that is numeric and Access would ignore that score when computing the average.

A longshot, but I thought I would ask.

Thanks.

View 3 Replies View Related

Querying Text In A 'numeric' Combo Box

Dec 19, 2005

I have a number of categories on which I assign a relative risk - at the moment H, M and L. Previously I had these set as a value selection combo and they were as 'text'.

More recently I have changed this now to a lookup and put the risk rating into a separate table. As a consequence the field is now 'numeric' but displays text from my combo.

When running a linked query I now get an error message instead of the required response.

My query should report back a 'Yes' if the queried cell contains an 'H' or 'M'.

Audiometry: IIf([tblRiskProfile]![Noise]="H" Or [tblRiskProfile]![Noise]="M","Yes","No")

However, this obviosuly doesn't work now because of the cell properties being numeric. How do I ammend the string above to run the same query?

View 1 Replies View Related

Save Numeric Results As Text

Oct 20, 2006

I have a query that calculates the number of games won and lost over the course of a season.
I would like to export the query results as a report in Excel format.

What I need to do is take the won/loss field in the query
WonLost:([GamesWon]&"-"&[GamesLost])
and export the result as text.

When I do it as above, the results in Excel are numeric.

Example: Games Won = 12 Games Lost = 3 it exports it as 9
(12 subtract 3).

View 7 Replies View Related

Forms :: Numeric Only Input For Text Box

May 26, 2013

I want numeric only input for a certain textbox. I can use a ISNUMERIC function to test this, but prefer to use the Keypress event. So far got the following:

Code:

Private Sub txt_Position_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
Case 8 ' backspace
Case Asc("1") To Asc("9")
Case Asc("-")
If InStr(1, Me.txt_Position.Text, "-") > 0 Or Me.txt_Position.SelStart > 0 Then

[code]...

It is working alright, except I want to avoid input that has multiple leading zeros, such as 00000 or -00000 (i.e. minus sign with mulitple leading zeros).

View 10 Replies View Related

Tables :: Difference Between Numeric And Text?

Jul 1, 2013

I have a field that will only get numeric values, numbers. And it will always have four characters or less. I do have times when I worry my data by over eighty's certain size or I average value, or both. Is this possible when using a text field.

View 14 Replies View Related

Numeric Field When Null Display Text

Dec 14, 2006

Hi All,

I have a field called PRICE and obviously it is a NUMERIC field as it need to perform calculations such as calculating Total Quantity * Price etc. At the moment the PRICE is inputted manually by the User on a Form and when it has no Price it is simple left blank.

What I wish to include is that on the REPORT when the field is empty it writes the text FOC instead of leaving it empty.

Can you please anyone suggest a way of doing this.

Thanks any help will be much appreciated :o

View 8 Replies View Related

Queries :: DLookup Returning Numeric Value As Text

Apr 19, 2013

I have a Dlookup which returns the correct value but returns the text of the instead of a numeric value. I have triple checked that the fields are all numeric. I suspect there is something wrong with my Dlookup syntax as it is my biggest weakness. In the following syntax what could be wrong?

DLookUp("SSER","TABSSS",[grosspay] & " Between [TABSSS].[Minrange] And [TABSSS].[Maxrange]")

View 4 Replies View Related

General :: Extract Text And Numeric From A String

Feb 12, 2013

"Att Mr/Mrs Vezi Your DEFAULTED account has been handed over to XXX. Payment DUE IMMEDIATELY Ref 1315519509. Tel 0009377500"

I need to extract the word "Ref " plus the number that follows it from the above string. The problem is that the word "Ref " is not always in the same place and the number following it is not always the same length. (ie it could look like "Ref 123456 " or "Ref 1234567 ") The only commonality between the records is that the number will always follow the word "Ref ".

View 1 Replies View Related

Forms :: Filter On Text And Numeric Values?

Oct 17, 2013

I've created a macro to use an unbound textbox to filter a form. In a query, I combined 3 fields to enable an easy search over. Sadly, only text characters work for the search, so whenever I search for numeric values, it returns null. This doesn't happen if I utilise the filter over just a numeric field - only when the search field combined multiple fields.

Is there a simple solution? My marco filter is

Code:
[Forms]![STAFF SEARCH FORM]![SEARCHDATA] Like "*" & "[SEARCHFIELD]" & "*"

View 1 Replies View Related

Queries :: Format Text To Numeric Percentage

Jan 29, 2015

I have a text percentage that reads 28.0%. I want to convert to a numeric percentage that reads either 0.28 or 28.0%, preferably the latter. The Val function returns an error.

View 3 Replies View Related

Option Button - Use Text Instead Of Numeric Value In My Query/report

Apr 26, 2007

Hello,
I am not sure what cateogory to choose for this question but I would greatly appreciate your help.

I have 5 buttons in my option group:
1. very often
2. often
3. occasionally
4. rarely
5. never

I have to produce a report using description of each button in my report, not numeric value. I heard something about global variable but I am not sure how to do it.

Many thanks,
Debbie

View 1 Replies View Related

Queries :: Concatenate - Difference Between Numeric And Text Fields

Aug 6, 2015

I have a Query contains field that is :

Code : AllNv: Concatenate("SELECT NAll FROM NormalsQ WHERE SerName='" & [SerName] & "'" & " ORDER BY NormID")

but [SerName] is a text type field that is not Primary Key.

I have in the FamilyTbl , [ServiceID] is numeric type field, and a primary key. I try :

Code ; AllNv: Concatenate("SELECT NAll FROM NormalsQ WHERE ServiceID='" & [ServiceID] & "'" & " ORDER BY NormID")

but this returns all [NAll] records. I use concatenate :

Code:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ", _
Optional pstrLastDelim As String = "") _
As Variant

[Code] .....

View 14 Replies View Related

Queries :: Limit Query Results To Numeric Value Of Text Field?

Aug 18, 2014

I have a table that has several fields including CallID (autonumber) and SKU (text)

SKU can be anything up to 9 characters, sometimes numeric sometimes alphanumeric. For example: 24300, AA23145, G58d444, 24999, 89332,...

Based on the Count of CallID I can easily get the top20 calls on each SKU. This is the query I use for that:

Code:
SELECT TOP 20 Count(Calls.CallID) AS CountOfCallID, Calls.SKU
FROM Calls
GROUP BY Calls.SKU
HAVING ((Not (Calls.SKU) Is Null))
ORDER BY Count(Calls.CallID) DESC;

The problem is that now I have been asked to create two different lists. One that has the top 20 SKU that range from 24520 and 24599 and another one that does the res tof the SKUs.

Obviously my problem is that the SKU field is text, not numbers so I can't just limit the results in the query by using "Between 24520 and 24500" in the query criteria.

View 7 Replies View Related

Modules & VBA :: Preventing Duplicates On Multiple Fields (Numeric And Text)

Oct 15, 2013

How to prevent duplicates on the combination of two fields - text & numeric?

I'm currently using the code below that warns users when the combination of two fields have already been used. (Combination of the TWO fields has to always be unique so if used again will warn the user)

Works well when both fields are numeric but fails when the JobDetails field is changed to text in the main table (tblPPMPLanner)

Code:
Option Compare Database
Option Explicit
Private Function IsDuplicateRecord() As Boolean
On Error Resume Next
Dim PreviousRecordID As Long
IsDuplicateRecord = False

[Code] ....

The field that should be a text field is called "JobDetails"

View 5 Replies View Related

General :: Excel Numeric Data To Be Imported As Text In MS Access

Aug 1, 2015

Attached in the ZIPPED file is an Excel spreadsheet.

Columns A is all numeric, and needs to be represented in access as a text field.
Column B is a mixed format of dates entered and in some instances only plain numeric. I need to import this column as is into a text field in access.

I tried importing the excel sheet, but the data gets changed.I tried to linked the Excel sheet but it also had an influence on the data.In both cases the influence of change is NOT throughout. Hence my need to get this spreadsheet into access as is.

View 7 Replies View Related

Forms :: Text Prompt In Fields When Bound To A Numeric Field

Sep 12, 2014

I have several combo box fields in a bound form where they are selecting values from a list (values stored in a separate table) and then loading a number into the bound table field when selected.

How can I put a text prompt in these fields when loading the form which gets removed when focused and of course is not permitted to attempt a save into the bound numeric fields? I've done quite a bit of searching but can only find materials about doing this on bound text fields. I've also seen solutions using Nz which don't seem to work.

View 4 Replies View Related

Custom Input Mask Using Both Text And Numeric Characters For A Form

Feb 19, 2015

I am trying to create a custom input mask using both text and numeric characters for a form.

Example would me ABCD-12345678. However the first 4 letters would would need to be able to change.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved