Queries :: Format Text To Numeric Percentage
Jan 29, 2015I 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 Replies
ADVERTISEMENT
Modules & VBA :: Text Box Percentage Format
Mar 2, 2015I have set the format of a text box (named: scrILS) to percentage. It shows on the form as 0.00%
When I try to run calculations off of it like: = [scrILS]*[totBuysFYDP1], it throws this error: #TYPE!
It seems as though the textbox is keeping it as text for the percent sign "%" is preventing any calculations against it... here's the strange part... it doesn't happen initially when I open the form... the calculations work and I get no error, but I have code that recalculates everything based on values picked in a list box... on the requery I get the error.
When I look at the watch frame for that control it shows the scrILS value as:
"00.0%" not 00.0%... any thoughts on this?
I attempted to just take the thing as a string and use a replace function against the "%" then calculating... that works, but then the initial form load throws the error.
Seems like I am missing something in the property settings, but I don't know what. Is there something that forces the format value, setting percentage as a number vs. text?... I thought that was just part of the format setting...no?
Queries :: Getting A Percentage From A Text Count
Jun 11, 2013I have a query that has clients sorted in the row area and a column from my table that is set up as text but only has yes/no options for the data. I have a pivot table that counts the yes's and counts the no's and finds a grand total; however, I want to be able to find the percentage of pass's given the grand total. I'm able to do this in Excel and was wondering if it was possible in Access.
View 2 Replies View RelatedQueries :: Percentage (Pass / Fail) In A Text Column
Jun 6, 2013I have about 20 columns in a query and each row is a customer.
The columns are either pass/fail but are text fields and not a yes/no set up. I want to get the percentage of a pass in the column.
Queries :: DLookup Returning Numeric Value As Text
Apr 19, 2013I 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]")
Queries :: How To Convert Text Fields To Numeric
Aug 16, 2014I 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 RelatedForms :: Sort Numeric In Alpha-numeric Text
Mar 11, 2014I 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 ?
Queries :: Concatenate - Difference Between Numeric And Text Fields
Aug 6, 2015I 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] .....
Queries :: Limit Query Results To Numeric Value Of Text Field?
Aug 18, 2014I 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.
Format As A Percentage With An Iif
Jul 4, 2005In one of my queries I calculate a percentage based on a change from one year to the next; however, sometimes there will not be any prior year data hence the percentage is undefined (i.e. current year amount/0=undefined). To stop the query from displaying an error when this is the case I wrote the following:
CapitalSurplusChange: IIf([P/Y Operating Data]![CapitalandSurplus]=0,"N/A",(Nz([C/Y Operating Data]![CapitalandSurplus],0)-Nz([P/Y Operating Data]![CapitalandSurplus],0))/Nz([P/Y Operating Data]![CapitalandSurplus],0))
I have the properties of the field set to percentage, but the calculation does not display as a percentage but as a long decimal. If I take out the Iif the percentages display fine however there are errors for the undefined calculations (hence why I inserted the Iif). How can I amend the code above to only display the "N/A" answers as text. I know about the formatpercent function, however I am exporting this output to excel and this when using the formatpercent or formatnumber operation the data still displays as text and not a number. Any ideas?
Percentage Format In Table
Aug 23, 2005Greetings all,
I am probably just being dense.
But how do I go about entering a percentage of say 5% in a numer field formated to percentage in a table?
If I enter 5 in translates to 500%, .05 as 0!
Thanks,
Brian.
Zimbabwe.
Change Format To Percentage
Aug 7, 2013I have a "make table" query. Inside that query there is a simple expression that divides two fields by one another to arrive at a %. Example 5/100=5%. However, the data returned is not in a % format.I know that I can go into table properties and change the fromat to "percentage", but that will only work for the current table that I have created. Once I re-run the "make table" query, I lose the change to the format, and my value again returns to a number, not a %..
Is there any way to always have a percentage returned without having to manually change the format each time that I run the query, or having to make a "delete" and "append query" ?
Numeric Format
Nov 2, 2004Numeric format was set to
$Currency 2 decimals
after getting the input form to work...
the numbers now round to the nearest dollar?!*?
What could I have messed up?
Any help appreciated.
Sink or Swim
Numeric Format Problem
Nov 5, 2007Hello,
I have a little problem. I am using th sendObject command where 'str_Body' is the concatenated body of the message. It contains some fields from an open form. When opening the email, the value of the numeric fields do not appear exactly in the same format as they look on the form (how they should be). For instance in the form I have number "$293.80" and when opening the email this value becomes only "293.8". Is there anyway that I can fix this up?
Thank you everyone
Format Textbox To Display Currency And Percentage
May 22, 2006Hi,
I have a query with 3 fields - PaymentAmount and PaymentTotal. The third field is a percentage of the two e.g:
PaymentAmount = 10000
PaymentTotal = 12000
PaymentPercentage = 83.333333333333
In my form I would like a text box to display the two combined i.e. £10.000.00 (83.33%).
At the moment it displays 10000 83.3333333333333. I can use £#,##0.00 to format the current half, but how can I then format the percentage to display 83.33%?
Thanks
Forms :: Format Graph Axis To Percentage
Oct 15, 2014How can I change with VBA the Y axis of a graph to percentage?
sqlstr = simple sql giving with date, value
Graph47.RowSource = sqlstr
I can give the axes min/maximumscales
.Axes(2, 1).MinimumScale = frm_MinscaleY
set the colors and weights
.seriescollection(1).Border.Color = graphcolor
.seriescollection(1).Border.Weight = Lineweight
.seriescollection(1).MarkerSize = MarkerWeight
.seriescollection(1).MarkerBackgroundColor = graphcolor
But everything I try to set the axis to percentage does not work... No failure message, but nothing happens. I have tried multiple things:
.seriescollection(1).datalabels.numberformat = "0,0%"
.seriescollection(1).datalabels.numberformat = "#,#%"
.seriescollection(1).numberformat = "percentage"
.seriescollection(1).numberformat = "0,0%"
.SeriesCollection(1).tickLabels.NumberFormat = "%"
and a lot more....
How To Calculate A Percentage Of Two Fields And Format Properly
Feb 11, 2013I have two fields CountOFAppeal Description and Amount Solicited.
I just want to get the percentage of response.
So this is what I used:
=[CountOfAppeal Description]/[Amount Solicited]*100
When I go into the properties to format it to % and add decimal places I get a crazy number
2520.46% When it should be 25.20%
When I change the format to general number the decimal point is in the right place 25.20.
Yes/No Feilds Display In Numeric Format When In Union Query
Jan 25, 2008Hi all,
I have looked everywhere for a solution to this and found none. Its an annoying issue which I would like to get around.
Here is what happens.
I have a union query that gathers rows with different criteria together. The query works execept that the yes/no feild which is set to display as "Yes" or "No" is displayes as 0 for No and -1 for Yes.
I understand that the numeric values are the underlying values of yes/no feilds in access, however they are displayed as yes/no in all my queries and reports execept my union query.
I will include the union query here in case.
The following code is a function that I use to build the union query on the fly based on some parameters. If you are asking why I am doing it like this is becasue I need the rows that have a value for the feild schFeilds(0) to be on top of the final results followed by the rows that don't have any values for the above mentioned feild, and I want them sorted. I found this method to be the only one that gives me the result in the order I need.
' ----------------------------------------------------------
Public Function makeMainPanelQry()
On Error GoTo Err_makeMainPanelQry
Dim db As Database
Dim qd_window As QueryDef
Dim qd_nowindow As QueryDef
Dim strSQL As String, sql_window As String, sql_nowindow As String
Set db = CurrentDb
Set qd_window = db.QueryDefs("Q1")
Set qd_nowindow = db.QueryDefs("Q2")
strStatus = cmbStatus.Value
strReboot = cmbARStatus.Value
strFilter = makeHostFilter(strStatus, strReboot)
schFeilds = Split(getRSchechuleFeild(), ",")
sql_window = "SELECT " & pbl_ReleaseTable & ".Hostname, " & pbl_ReleaseTable & ".Status, " & pbl_ReleaseTable & ".Excluded, " & _
pbl_ReleaseTable & ".AutoReboot as Auto, " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & ", " & _
HEAT_PROFILE_NODECOM & "." & schFeilds(1) & _
", Technician.FirstName as Assigned, 1 as Priority FROM (" & pbl_ReleaseTable & " LEFT JOIN " & HEAT_PROFILE_NODECOM & " ON " & _
pbl_ReleaseTable & ".Hostname = " & HEAT_PROFILE_NODECOM & ".DeviceName) " & _
"LEFT JOIN Technician ON " & pbl_ReleaseTable & ".TechID = Technician.TechID " & _
"WHERE ((" & pbl_ReleaseTable & ".Excluded) = False) " & _
strFilter & "and " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & " Not Like """" "
'" ORDER BY " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & ", " & pbl_ReleaseTable & ".Status"
qd_window.sql = sql_window
sql_nowindow = "SELECT " & pbl_ReleaseTable & ".Hostname, " & pbl_ReleaseTable & ".Status, " & pbl_ReleaseTable & ".Excluded, " & _
pbl_ReleaseTable & ".AutoReboot as Auto, " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & ", " & _
HEAT_PROFILE_NODECOM & "." & schFeilds(1) & _
", Technician.FirstName as Assigned, 2 as Priority FROM (" & pbl_ReleaseTable & " LEFT JOIN " & HEAT_PROFILE_NODECOM & " ON " & _
pbl_ReleaseTable & ".Hostname = " & HEAT_PROFILE_NODECOM & ".DeviceName) " & _
"LEFT JOIN Technician ON " & pbl_ReleaseTable & ".TechID = Technician.TechID " & _
"WHERE ((" & pbl_ReleaseTable & ".Excluded) = False)" & _
strFilter & "and " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & " Like """" or " & _
HEAT_PROFILE_NODECOM & "." & schFeilds(0) & " is NULL"
'" ORDER BY Priority, " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & _
'", " & HEAT_PROFILE_NODECOM & ".Status;"
qd_nowindow.sql = sql_nowindow
strSQL = "SELECT * FROM Q1 " & _
"UNION ALL SELECT * FROM Q2 " & _
"order by Priority, " & schFeilds(0)
Set qd_window = Nothing
Set qd_nowindow = Nothing
Set db = Nothing
makeMainPanelQry = strSQL
Exit_makeMainPanelQry:
Exit Function
Err_makeMainPanelQry:
MsgBox Err.Description
Resume Exit_makeMainPanelQry
End Function
' ----------------------------------------------------------
I woudl really appreciate any assitance as its my last hope for a solution.
Thanks and regards
Sep
Queries :: Export To Text File Format?
Oct 13, 2014I currently have a query pulling data from a database - I need to now export the data to a text file to import it into a different database. I need the format to be like below. Wondering how I can tell the query to go to the second line and then the third line like below.
*,9215146,BUILDERS LLC,4285 NICOLET,DALLAS,TX,75201
P,2
C,2,,BRD
Queries :: Unusual Date Format In Text File
Jan 5, 2015Someone decided to be clever and send a text file with a stupid date format, is it possible to show off a little here and get a working date format, it's a text file I am working with: Dec 18 2014 01:12PM.
Not that is an exactly cut/paste so spaces are as they are here, ideally would like to get 18/12/14 13:12.
Queries :: Format Field As Password Or Hidden Text
May 22, 2014I have a query that I am sending as a spreadsheet to various service technicians listing parts that they need to take inventory of. Then, after they take the inventory, they send the spreadsheet back to me and I upload their counts back into my database. Right now, my query contains the Bin number, part number, part description, and a blank column for the technician's count. I have just learned, that the company does not want the Bin number to be visible in the report. In order to upload the technician counts into the database when they return the spreadsheet, I need to be able to reference the Bin number to get the count on the right line.
Is there something that I can do so that the Bin number will be visible in the report when the database reads it, but not to the technician?
The only solution I could think of was to format the Bin Number as a password. Unfortunately, I can't get that to work in the query results. If I save the bin number as a password in the table, is there a way to make it visible on reports for internal use (so the people in headquarters who are responsible for over all inventory can still see each bin number)? I found how to hide the field in the query results. However, when I send the results to the technician, the field is not there at all.
Queries :: Link Table To Represent Data - Format Text As Date
Nov 15, 2013I have a link table that has a field that represents dates - but they are actually just text. It's a long story but the source is not going to change - so I have to try and deal with it. I need it to behave as a date - and am hoping to do this in a query. The data looks like this:
7/24/12
10/08/13
I have tried various things but it does not seem to totally do the trick - if I sort on it it still does not sort as one would expect from an actual date field. How do I do this?
Queries :: Convert Phone Number In Text Format And Remove Dashes Or Parenthesis
Aug 7, 2014Is there a way to convert a phone number in text format into a number and remove any dashs or parenthesis. What function can I use ?
old format (951) 244-3011
new format 9512443011
Queries :: Converting CSV File Into Text Format - Export Records With Specific Series And Date
Jun 7, 2015I have a CSV file and want to convert it in a text format with some filtered data and with some formatting. This is an everyday task for me. So I made a table and imported the data in to it by the command :
DoCmd.TransferText acImportDelim, "fo Import Specification", "fo", FileName:="C:UserswelcomeDesktopfo.csv", HasFieldNames:=True
Actually I have a column "SERIES", contains various series like "EQ", "BE", "DR", "BZ", "D1" and so on. And one more column with the dates having 4 / 5 current months dates and one next months date and one next to next month's date. And every date has got several thousand records.
now the issue is that : After importing these several thousand records, I want to export it but with a specific date and with a specific series.
The other thing is that, these dates change every month so if hard coded, the problem will occur the next month.
I use this code for export :
DoCmd.TransferText acExportDelim, "NewFnoSpec", "fnoquery", "C:UserswelcomeDesktopFO Output.txt", True
this code is working fine but when the month will change, the code won't work.
Can we have a date & series picker attached to this query, so it can export the records with the specified SERIES & DATE.
I tried putting a textbox on the form named TxtDate and in a Query ( Design mode ) under the date column, in criteria I have put [Forms]![Futures]![TxtDate] and after putting this line, the query becomes empty and no data is there.
Search, Text VS Numeric.
Jun 21, 2005I 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
Numeric To Text In Excel Help
Mar 4, 2008Hello 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