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.
I hope someone can help me with this issue. Currently I have numeric data that I need to convert. For example if a quantity is 42.5 in my data I need to format it to look like 00000042500 similarly if the quantity was -42.5 I need to format it to look like 00000042500-. Any assistance anyone can provided would be greatly appreciated.
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 ?
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.
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
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?
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??
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?
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).
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).
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.
Background I have a query (Q1) that retrives data from a table (Table 1). One of the fields in Table (F1) contains both text and numeric data (ie: 24 eggs). I want to separate these values in Q1.
Questions How can i in Q1 retrive only numeric data from F1 and display that data i a field? How can i in Q1 retrive only text from F1 and display that data i a field?
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.
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]")
"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 ".
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]" & "*"
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.
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.
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.
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.
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"
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.
I am new to this forum, and I am facing a problem while learning MS-Access.
I am preparing a database that stores the marks obtained by students in a particular paper in addition to their particulars in tables. A query is then generated to canculate the aggregate marks and result (Pass/Fail) for the student. It is then presented in a report fomatted as a marksheet, as well as one tabulation chart.
The database is very crude in format, but I am happy with it since I do not know programming at all, and still I could make it. I have used in built menu of "ms-access" and mouse clicks only for generating query. It was a learn by doing method.
The problem is that my marksheets require absent students to be marked as "ABSENT" or something of that kind. If I convert my Null values to :
Expr2: IIf(IsNull([E_1]),"ABSENT",[E_1])
It shows ABSENT for null value, but refuses to make column total for E_1; On the other hand if I do not enter such expression, it makes column total in tabulation report, but fails to mark "ABSENT" to those who did not appear. So I have to lose one thing to gain another, while I need both.
