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.
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.
I want this function to populate value from a any field selected from any table to any text box in any form ...... Lets say in a Database named TestDB we have a Table named tblTest , a Form named frmTest and in this form( frmTest ) we have one Bound Combo Box named cmbTest and one unbound TextBox named txtTest
We assume that the table tblTest has three fields : TestID , FName and LName .
We also assume that there are already some records in the Table tblTest .
If the function F() is already programmed it should take as arguments as it follows :
F(FormName as??? ,ControlName as ???, TableName as ???, FieldName as ???, ID_Field_Name_of_the_Table as ???, Combo_box_selected_ID as ???)
In result the function should (probably) DLookup (FieldName , TableName , ID_Field_Name_of_the_Table = Combo_box_selected_ID ) and then store the value in a variable ( probably Variant ???) , lets say called varSetValue.
The problem is IF this is the correct way to handle that issue , than what should i do next.... I really don't know how to pass a Control's name to a function from the Event module of the ComboBox -
I mean - how can I obtain the value so it is usable for the function .... What data type should be the function arguments so I can use them to set a value for a control in any form .
In example : If the Dlookup() is somehow successfull then I want to assign the varSetValue to the txtTest what should I do : FormName.ControlName.Value = varSetValue >??????
I have read a lot materials but couldn't find a good answer for that , and aswell what data type should be the arguments that the function accepts and how do I obtain Controls and Forms names so I could use them in the function as described above .
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?
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...)
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 have the following in a query to assign a sequence number to each record:
(SELECT Count(*) FROM [qryPTLActivity] AS T WHERE T.EmployeeID=[qryPTLActivity].[EmployeeID] AND T.PickDateTime <= [qryPTLActivity].[PickDateTime] AND T.PickAisle=[qryPTLActivity].[PickAisle])
I have used this in other databases without issue but for some reason this time I keep getting an "Invalid Argument for Function" error.
There are about 75 distinct sets of ID's and Names not shown here
The rows were sorted by the Mtr_Readings and we have to take the best four readings out of each group and average them. If a group has more than four rows then the top 4 are taken out and the balance is discarded. If the group has four rows or less all of the rows are taken.
I did try nested select statements and kept getting "invalid argument to function" exceptions.
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.
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.
I have a form with a button to print preview a report. This report needs one input parameter before executing. The computer this will run on is a touch screen and does not have a keyboard. Windows 7 has a "on screen keyboard" program. I want this to run first so that my user can input the parameter.
I have the following which throws an "Invalid procedure Call or Argument"
Sub CallTeclado() Dim RetVal RetVal = Shell("c:windowssystem32osk.exe", vbNormalNoFocus) End Sub
I'm developing an application where I want to call the keyboard up on the screen when a user enters a field. This is my setup:
Windows 8.1 32 Bit, Access Runtime 2010. Exact lines of code are:
Dim RetVal RetVal = Shell("C:Program FilesCommon Filesmicrosoft sharedinkTabTip.exe")
These lines of code work perfectly fine on my development PC which is running Windows 7 64 bit, Access/Office 32 bit.
I know the path to the exe is good. I can navigate and double click it and it works great. but the shell command is resulting in the invalid procedure.
I found this code on a website that uses a form to search all tables in my database. Problem is that when I click "search" I get an invalid argument error. I am guessing that there is a problem with my SQL string. Missing space? Missing quotation marks? etc etc.... Anyway, here is the code:
Dim tdf As DAO.TableDef Dim fld As DAO.Field Dim rs As DAO.Recordset Dim strSearch As String Dim strTableName As String
Why I am not able to get all records by excluding the Numrows argument in the getrows method?
Sub Test2() Dim myrset As Recordset Set myrset = CurrentDb.OpenRecordset("SELECT * FROM Holidays;") myrset.MoveLast myrset.MoveFirst MsgBox Excel.Application.WorksheetFunction.Networkdays(#8/1/2014#, #8/31/2014#, myrset.GetRows(myrset.RecordCount)) MsgBox Excel.Application.WorksheetFunction.Networkdays(#8/1/2014#, #8/31/2014#, myrset.GetRows()) End Sub
Second MessageBox is giving a wrong value.
Is it a mandatory one? Or Do I have to do some ritual like (Movelast) before that?
(Holidays table is just having the values in the array only ie. #08/15/2014# and #08/29/2014#)
I'm having an issue getting a return value from a stored procedure that I'm calling from VBA. This is what I have at the moment:
Code: Dim strDate As String Dim strWOStatus As String Dim CurrentConnection As ADODB.Connection Dim adoCMD As ADODB.Command Dim adoRS As ADODB.Recordset Dim ParamReturn As ADODB.Parameter
[Code] .....
The problem I am having is this error: Error: 424 Description: Object Required
The line of code it errors on is:
Code: Set .Parameters("@PartsUSedMTD").Value = ParamReturn
And the value of ParamReturn is always Null after it hits the line before it.
So it seems like it's not really creating the parameter variable SQL Server needs to run
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 am using MS Access with SharePoint Lists and some Access Tables to generate reports in pre-formatted MS Excel Worksheets. My main module opens several classes. One of the classes receives a worksheet to keep in a historical book. This class can receive any one of several different types of pre-formatted worksheets. After all is said an done the class adds a cover sheet to the consolidated workbook and adds hyperlinks to the other sheets, kinda like a table of content.In the class I have the following in the general declaration.
code: Public Enum BookType A = 1 B = 2 End Enum Private pBookType As BookType
Later in the class I have the following:
Code: Public Property Let LoadBookType(ByVal btNewValue As BookType) pBookType = btNewValue AddWorkBook End Property
in the main modual I have the following
Code: classConsolidated.LoadBookType = A
When assigning the above property it passes in "11"
if I change it to the following
Code: classConsolidated.LoadBookType = BookType.A
it passes in "1" as I expect and use in a Select Case statement later in the class.So why does one pass in "11" but the other passes in "1"?
I am having trouble with the below returning the correct number of records, and can't see why.
I have one table, tblDevice, which has 4 columns, ID | DeviceRecNo | ExcludeFromCheck | StockLocationID
ID - Autonumber DeviceRecNo - Number ExcludeFromCheck - Number (1 = yes, 0 - No) StockLocationID - Number
I have the following running as part of some code, but it is not returning the correct number of records, and I cant see why not. I have tried creating this in a query in Access itself, and copy the SQL into VBA and it still doesn't return the correct number records.
I have 4 records in there for testing, all with StockLocationID = 3, all with different DeviceRecNo, and two each of ExcludeFromCheck, 2x0 and 2x1
Code: Public Function test() Dim db As dao.Database Dim rs1 As dao.Recordset Set db = CurrentDb Set rs1 = db.OpenRecordset("SELECT DeviceRecNo FROM tblDevice WHERE StockLocationID = 3 AND ExcludeFromCheck = 'No'") Debug.Print rs1.RecordCount End Function
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 "")
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)
