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 Replies
ADVERTISEMENT
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
Oct 2, 2006
I'm trying to make changes to records that are 3 years old. The date format is text and it displays as Ex. "20020831" or yyyymmdd.
i try <"20020101" but I get no results and I know there should be.
I'm a rookie and only know how to query using short date format.
ideas anyone?
thx
View 1 Replies
View Related
Jan 27, 2006
Right, I think this is a quick and easy one....just not for me!
I have a combo box with a list of names. I want to use whichever name a user selects as criteria for a query.
What do I need to type in the query criteria box or as SQL?
View 2 Replies
View Related
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
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
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 6 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Jan 8, 2007
Hi friends,
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.
Can anybody help me at this?
pc
View 5 Replies
View Related
