Modules & VBA :: DLookup In Custom Function Not Returning Values
Sep 13, 2013
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.
View Replies
ADVERTISEMENT
Mar 26, 2014
Here is my VBA Dlookup Code:
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.
View 4 Replies
View Related
Jan 13, 2015
I have just added a function to a database to strip out any commas or quotation marks from a passed string.
However, it is returning a black string even though when I step through the function it creates the out string correctly and the variable "OutString" is populated when the Exit Function command is executed.
Calling code.
InString = Btxt
OutString = ""
Call Strip_String(InString, OutString)
SBtxt = OutString
[Code] .....
View 4 Replies
View Related
Jan 13, 2014
I'm trying to do a string compare between two variables. One string variable is part of an array (which I'm looping through), the other is passed to the function as an argument.the function should return the position of a field in the OrderBy string of a subform.Here's the VBA :
Code:
Private Function SortPosition(strOrderBy As String, strField As String) As StringDim arrSortedFields() As String
Dim i As Long
If Len(strOrderBy) > 0 And InStr(strOrderBy, strField) > 0 Then
arrSortedFields = Split(strOrderBy, ",")
[code]....
The weird thing is, the line in blue returns True when, by watching the variables, it appears that it should not, and returns False when it appears that it should?
arrSortedFields(i) = "[RandomField]"
strField = "RandomField"
arrSortedFields(i) Like "[" & strField & "]*" = False
arrSortedFields(i) = "[SomeFieldName]"
strField = "ADifferentField"
arrSortedFields(i) Like "[" & strField & "]*" = True
I would have thought the expression "[RandomField]" Like "[RandomField]*" should return True?And similarly the expression "[SomeFieldName]" Like "[ADifferentField]*" should return False?Have been using 'Like' for donkey's years and never seen it throw results like this before?
(P.S. The reason I need to use 'Like' rather than a straight = is to account for the possibility that a field may be sorted descending, and therefore to nullify the DESC keyword which may follow any given field...)
View 9 Replies
View Related
Jun 6, 2014
I'm having a problem with argument in custom function..My table is like this:
myValues
123.5
32.7
65.8
11.1
What I want to achieve is to multiply each value with the average of all values, so that at the end I get (avg(myValues) = 233.1):
myValues2
7196.963
1905.593
3834.495
646.8525
For this I would like to have a function (this is simplified example of bigger problem, that is why I need function, otherwise simple SQL would suffice as mentioned below also), so that I could do something like this:
'SELECT TestFn([myValues]) as myValues2 From MyTable' or
'SELECT TestFn("[myValues]") as myValues2 From MyTable'
and I should get the table above.
My code is like this
Code:
Public Function TestFn (FieldName) as String
Dim myAvg as Double
myAvg = Davg(FieldName, "myTable")
TestFn = FieldName * myAvg
End Function
I understand the problem is that Davg needs string, but if I change to string then the multiplication does not work. The 'FieldName' parameter does not have a Type specified because that is where the problem is if I give string Avg works ,but calculation does not. If I give Number, avg does not work.
P.S. "as String" at the end of function is not a mistake, I need number as a string
P.P.S I did not inculde NZ either as in my example it can never be Null.
View 8 Replies
View Related
Dec 8, 2014
I have a table EmployeeInfo containing three fields
EmployeeId
Name
FatherName
I have created form for this table.
I do not want to use autonumber for employeeid for some reasons. I want to place a button along employeeid test box on form.
User will click on the button it will get max employee id from employeeinfo table and add 1 and copy it into employee id text box.
What will be the code for button click event.
View 2 Replies
View Related
Dec 20, 2013
I am trying to calculate annual percentiles of a large set of data and I have only been successful at retrieving the percentile of the entire data set (and not by the grouping). See provided example database for code/query. Query1 is what I want to happen to make the Percentiles table.
View 14 Replies
View Related
Jun 10, 2014
I haven't created classes in a while, and I don't see what is the problem
I'm modifying a function to get fractions from double. It worked well, but it returned a string.
I need it to be in parts, so I created this class
Code:
Option Compare Database
Public Entier As Integer
Public nominateur As Integer
Public denominateur As Integer
Public Function getText()
getText = Entier & " " & nominateur & "/" & denominateur
End Function
I changed the function type and the assignation:
Code:
Function GetFraction(ByVal Num As Double) As nombreEnFraction
Set GetFraction = New nombreEnFraction
If Num = 0# Then
GetFraction = "None"
Else
Dim WholeNumber As Integer
Dim DecimalNumber As Double
[Code] ....
For some reasons, when I get to the end of the function, I get a 91 error, like if it was nothing
But when I use a spy, I see values in the object until the end.
View 1 Replies
View Related
May 19, 2014
I have two class variables (both arrays) among about 10 other class variables, that are not returning any values but "" for the string or #12:00:00AM" for a date. Here are my class variables:
Code:
'UPSData Class Module
Private p_LetterArray() As String
Private p_date() As Date
Private p_LetterArraySize As Integer
My Properties
Code:
'Properties
Public Property Get LetterArray(index As Integer) As String
LetterArray(index) = p_LetterArray(index)
End Property
Public Property Let LetterArray(index As Integer, NewValue As String)
[Code] ....
As I said, tLet and tDate result in "" and #12:00:00AM# respectively. When I step through the code, the values for tmp.LetterArray(0) is assigned "src" and tmp.UPSDate(0) stores "12/25/2013" correctly.
When I assign tLet and TDate, the same thing happens when stepping through the code. I'll use the LetterArray property to describe what happens:
Get LetterArray is called. p_LetterArray(0) does equal "src"
Let LetterArray is called. NewValue is "src" and p_LetterArray is "src" when End Property is highlighted in the debugger
Scope returns to Get LetterArray with End Property highlighted. In checking the values, LetterArray(0) = ""
Same steps happen with the same results ("12:00:00AM" vice "")
View 5 Replies
View Related
Jun 18, 2013
what I am trying to do with pretty much create a search query through code. So what's happening exactly is that the user enters a part number and expects to get 2 values: total orders and total items associated with the part number he/she entered. I have 3 tables and 2 of them are related. So I began my creating a query relating table 2 and table 3. I cannot include table 1 in the query.
1) Part Number is input by user
2) Search that Part Number into Table1
3) Take returned Parent Values associated with Part Number and store in Array
4) Modify Array values
5) Search Array values using a For Each loop into the query
6) Take the returned values found in query associated with each parent that was retrieved from the part number (user input) and return that through a table or query.
7) Also return the total rows in a specific column (Order Numbers)
View 10 Replies
View Related
Feb 28, 2014
I'm looking for some type of custom function that will search a specified column for any keywords listed inside another table.
I can run a query on each keyword individually, however there are 50 and it takes a long time each time I do it. I was hoping to write in a function for that column and it would just select all records that match.
These would all need to be a "like" with an " * " on each end of the word.
With SQL it would look something like:
Code:
select a.address1
from main_tbl as a
where a.address1 like '* north *'
or a.address1 like '* park *'
or a.address1 like '* south *';
I just want it to read each of the table values instead of hard coding them and the column name would be the function name so it can be used in any column I specify. I'm just not sure how to incorporate this into a custom function.
View 2 Replies
View Related
Aug 15, 2013
I'm pretty familiar with getting values from a table via Dlookup. What I want to do is almost the reverse if possible? I'm declaring a variable as follows:
Dim Ref as string
Ref = [lead_id]
This is from a form.What I'd like to be able to do is go to the table [list], reference the lead ID in the table via the variable then change the field [status] to "INCALL".Can this be done in a similar way to Dlookup?
UPDATE - here is the code I am trying to use
Dim ref As String
ref = [lead_id]
Dim MySQL As String
MySQL = "UPDATE vicidial_list SET"
MySQL = MySQL & "vicidial_list.status = 'INCALL' "
MySQL = MySQL & "WHERE (((vicidial_list.status)= Ref))"
DoCmd.RunSQL MySQL
Which gives me an update clause error
View 4 Replies
View Related
Jul 14, 2015
I have run into an issue with a basic DLookup. The database has grown in size and now we could have multiple entries, but I want it to return a certain one. So the information could be in it three times. Of course DLookup stops after the first one. How do I get it to loop to check the entire table? Someone mentioned to me to use a recordset, but how to write that as I have never used it before. Below is what I was using until this new request came up.
<code>
Private Sub txtloan1_AfterUpdate()
If IsNull(DLookup("[loan1]", _
"settlement", _
"[loan1]=""" & Me.txtloan1.Text & """ AND [status] = 'Open'")) = False Then
Cancel = True
MsgBox "Test", vbOKOnly, "Warning"
End If
End Sub
</code>
This was also executing after the user entered the information within a text field. I did not want them to enter all the data and then have it come back as a duplicate.
View 7 Replies
View Related
Mar 20, 2015
I suspect my design is flawed
Code:
SELECT tblData2.Prefix, tblData2.LineNum, tblData2.Year, tblComments.comment, tblComments.Address
FROM tblData2 LEFT JOIN tblComments ON tblData2.LineNum = (NumbersOnly([tblComments].[Address])
WHERE (((tblData2.MyYear)<1990))
ORDER BY tblData2.LineNum;
The NumbersOnly function returns a set of numbers from a custom formatted string. The above fails on the join (I think) but maybe there's another way of doing it?
View 4 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
Oct 28, 2013
I'm using a Dlookup in expression builder and it's only returning the value of the first record rather than the criteria I specify.
View 6 Replies
View Related
Aug 8, 2013
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;
View 3 Replies
View Related
Jul 16, 2013
I've created an array that I created and declared as a Public array in my module. I created a function that populates the array so that I can use the values in another function. I've gotten the array to populate but when I go to use the values in the array in another function, the array appears at Empty. I seem to be stuck on declaring it properly or something so that it can be used by other functions.
Public arrWebIDs As String
Public Function FillArray()
View 6 Replies
View Related
Oct 15, 2015
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.
Code:
=DLookUp("[JobName]","Jobs","ID=" & [tboJob])
JobName is the field I want to show from the table Jobs.
View 7 Replies
View Related
Apr 10, 2015
I have a form with couple of textboxes bound to a table. When the user opens the form to enter records, i want to write a function that would go through the textboxes to check whether the textboxes are left NULL. Now i can write code on button click for each form, but i was wondering if its possible to write a function that could be called for each form that i have to check for null values.
View 7 Replies
View Related
Jan 11, 2014
Trying to run a query where each 4 fields calling a custom function will not just re-run the same custom function over and over again for each field in a single record.
A Function has a huge amount of multiple queries and logic to perform.The Function returns a Integer, Integer, Integer, and optional Integer. Each integer requires a DLookup to lookup a String description value for each individual integer (in each of 4 fields).
The problem is, the DLookup in each column that runs against each of the integers re-run the same function.The result is that a single record, each of the 4 columns returning a single of the 4 values, the complex function is re-run 4 times.
The function is huge, part of a Business Rules Engine. Depending on the Rule-Meta data - it might launch up to a dozen queries and perform logic steps for each record. This is not the ordinary SQL Query.
Imagine if one record (for 1 field) takes 0.1 second to run. By referencing the function in 4 columns, this same function is re-run 4 times (0.4 Seconds) Against 50,000 records - this duplication of re-running the function for each column can really add up.
Possible Solutions: Researched Class Modules - There is a comment that the property Get, Let actually reduce performance. There are huge advantage of code documentation, documentation and centralization.It doesn't claim class modules reduce execution as each propery is returned. It also describes that Class Modules can't be called directly in a Query - unless each property is wrapped in a function.
Function Returns one String with delimiters: e.g 34;54;55;1 This single column goes into a Make Table (runs function one time per record) Then the D-Lookup is run against static local data. This pevented the function from being run over and over across the network linked data.
Final Solution: Eventually, the many hundred lines of VBA code for the Rules Engine will be converted into SQL Server T-SQL Functions on the server.For a Rule Engine development, Access has been great for a rapid protoype development and testing. The TSQL will be a final big step requiring re-coding. It is not currenty my option for the delivery time frame.
View 8 Replies
View Related
Jan 16, 2006
IIF function not returning the false value. I have the following results based on the query shown below.
SELECT Trade.Ref_No, Trade.CERS_Price, Market.[USD/EUR_Rate], IIf("CERS_Price_currency = US Dollar.USD",[Trade].[CERS_Price]*[Market].[USD/EUR_Rate] , 0) AS [Price In Euro], Market.[USD/CHF_Rate], Trade.CERS_Price_Currency
FROM Status INNER JOIN (Market INNER JOIN Trade ON Market.MarketDate = Trade.Trade_Date) ON Status.StatusID = Trade.StatusID;
Price In Euro USD/CHF_Rate CERS_Price_Currency CERS_Price
35 7 US Dollar. USD 5
91 7 Euro. EUR 13
1,715.00 7 Brazil Reais. BRL 245
759.00 33 Indian Rupees. INR 23.00
As you can see returns all calculations whether is USD or not.
Can you help please?
Ultimately I want to do this for all other currencies by nesting the IIF function.
Thanks
dfuas
View 2 Replies
View Related
May 15, 2007
Item: Access app front end for an SQL database (production tracking/job logging utility)
Problem: The app has a main search page that allows job entries to be located and opened by varies criteria (job#, job name, etc). I can currently run multiple searches on different criteria successfully but if I open a job window(which has been returned as result of my search) and then close that job window to do a new search my search function will no longer return any results(other then “Calculating….”) unless I close out the access app and reload.
Thanks in advance for any ideas that can point me in the right direction to hunt down my bug.
View 1 Replies
View Related
Jul 20, 2007
Hi anyone!
I have a table that tracks data for parts across different depots. Thus some depots might not have records for a part in a particular depot whilst others do on a given day. My table does not record a 0 value for parts in a dept that don't have a part on a given day, it simply does not record anything.
I would like to query all depots and have it return a 0 value where there are no records on a given day for a given part in that depot. Does anyone know how to do this? i.e. if no record return 0 as default.
Does anyone know how to do that? At the mo my query simply exlcudes all records on taht day for all depots?
Thanks in advance
View 2 Replies
View Related
Jan 18, 2007
I have been racking my brain trying to get this to work with no success :(
I have read through these 2 posts numerous times trying to apply the coding/methodolgy and can't seem to get it fine tuned.
http://access-programmers.co.uk/forums/showthread.php?t=31046&p=519211
http://www.access-programmers.co.uk/forums/showthread.php?t=112804&highlight=customid
Here is the structure I need to use for the number:
AR-CR-2007-00001
What I need is for the 00001 section to reset to 0 each year so the numbers will look like such:
AR-CR-2007-00001
AR-CR-2007-00002
AR-CR-2007-00003
AR-CR-2008-00001
AR-CR-2008-00002
AR-CR-2008-00003 etc....
I have the code working great to generate the different sections.
I have a table with 3 fields (seperating the number) and 1 field that is the combined number. The fields are:
FormatID (Text field),YearID (Text field),BaseID (Number field ) (These are populated using a form called "Test")
FormatID = AR-CR- (This is set as the default format/value)
YearID = 2007- (I have this obtained automatically using code in the default
value of the data tab) =Right(Date(),4) & "-"
BaseID = 00001 (Sequential by +1)
I have a button on the form called "Save" which I have placed this code in the "On-Click" event.
Private Sub Save_Click()
If IsNull(Me![BaseID]) Then
Me![BaseID] = Format(Nz(DMax("[BaseID]", "[tblTest]"), 0) + 1, "00000")
End If
Me![CustID] = [FormatID] & [DateID] & Format([BaseID], "00000")
End Sub
What I can't figure out is how to get the Base ID to restart with 00001 at the beginning of each year? What am I doing wrong?
I have also attached an image showing the increment change if I was to change the year to 2008 how it just incriments the last numbers instead of resetting to 00001.
View 13 Replies
View Related
Nov 28, 2007
Hello!
I've good a very very strange problem...
I do an insert of a new row into the table TRA. That insert creates an id for the new row.
Right after that I do a select of the new row, trying to get the new id created.
Well, the insert is working fine, but the select sometimes gives me values, sometimes not.
How is it possible?? I've tryied closing the database and opening it again, but is not working anyway...
Any idea??
My code right now is the following: (closing the database, setting recordset to nothing... and still not working properly)
sql = "insert into tra (semana, empleadoid) values('" & semana & "'," & empleadoId & "); "
DoCmd.RunSQL (sql)
db.Close
Set db = OpenDatabase(database)
sql = ""
Set rcset = Nothing
sql = "select * from TRA where (semana='" & semana & "' and empleadoId =" & empleadoId & "; "
View 10 Replies
View Related
