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]")
I am having a problem with a Dlookup query. I want to achieve the following - I have 2 different tables
Demography Table - consisting of 2 fields, City and Region
For example City Region NYC NAM
The other Table totalflow consists of several field, one of the is flow from.
For example Flow from NYC WAS SEA
I want to do the following. Lookup the Flow from filed in table totalflow, and compare it to City in demography Table. If City = Flow from, return the value in Region. All are text fields.
I have tried following Query, but it only returns the same value for all rows. (from SQL view)
SELECT DLookUp("[Region]","Demography Table","[City] = '" &[Flow from]& "'") AS test FROM [Demography Table], flowsize;
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.
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 ?
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.
i have tblDates that has two number long integer fields named FiscalYear and FiscalMonth. then there is my field PerSeq is number long integer.
in my Query1, i have 2 fields that use a question in the field part of the qry like: Desired FiscalYear: [Enter Fiscal Year] EG 2007 Desired FiscalMonth: [Enter Fiscal Month] EG 2
And now the Qry question. The field in the qry "Desired BaseSeq". i want this dlookup to get PerSeq out of tblDates based on my response to the Desired FiscalYear and Desired FiscalMonth.
I start it out here but it is wrong i know.
Desired BaseSeq: DLookUp("PerSeq","tblDates","[Desired FiscalYear] And [Desired FiscalMonth]=" & [tblDates].[FiscalYear] And [tblDates].[FiscalMonth])
In the "qryEmployeeWTax" i need to Lookup the [InitialTax] from the "qryreftbl_WTAX" based from the [ETPES] and [SalaryAfterStatutoryDeductions], so i am using this function:
Code: Public Sub test() Dim frm As Form Set frm = Forms!StationLevelSummary
[code]...
At the bottom im printing the content of the controls which are on my form. These should return 1 number, but for some reason it does not. Ive used this code many times but I cant figure out why nothing is being returned.
I made a custom function to look certain value from table based on couple of criteria that it gets from query where I want to use it. Function's code is below:
Code: Public Function PotteryWeights(strLocusID As Long, nrPotSubID As Long) As Variant Dim priSubID As Long Dim priLocusID As Long Dim priResult As Variant priSubID = nrPotSubID
[Code] ...
However, when I use it in query it only returns Case else - option and everything else is empty.
I'm working on my first report and am trying to get my textbox, tboJob to show a field value and not the ID. I have tried the following Dlookup in my control source with no luck.
I am pulling some information, and I am needing a report to show the value "score" if the field is numeric, but if it is not numeric, retain it's original value. for instance if the field is 1234 it needs to show on the report as "score" but if it is MSP it needs to say MSP.
I'm trying to pull a date from a table into a text box on a form tab control using DLookup and I just can't figure out what is wrong with my DLookup expression:
=DLookUp("DateOrdered","tDateOrdered","PrNumber=" & [PrNumber]) ----- (DateOrdered is short date, PrNumber is text, db is split Access 2013)
Whats missing in this expression? I've tried every criteria variant I could find but to no avail.
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 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 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.
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.