General :: Filter A Function Return Value?
Jun 27, 2012
I have a function which I want to return the value of a field.
Public Function fieldValue(tblName As String, fldName As String)
Dim drs As Recordset
Set db = CurrentDb
Set drs = db.OpenRecordset(tblName)
fieldValue = drs.Fields(fldName).Value
drs.Close
Set drs = Nothing
End Function
I need to return the value obtained, for a specified User identified with a numeric variable.What is the best way forward? - Some sort of filter or DLookup and how to code this?
View Replies
ADVERTISEMENT
May 15, 2013
I have two date fields in a table. I need to find the days between these dates and, if its greater than 7 days, I want to display the record in a report, so far, I have found a hand function that allows me to enter two dates and it returns a long data value representing the number of days in between the two dates. (google "I've developed the following code to count the business days between 2 dates." and its the second one that starts as SOLVED I made no mods to it as it does what i need it to do.
So, i added this to its own module within the data base for use within a query. My test query basically displays the unique ID, the start date and the end date and then displays the values returned from the function. here is the SQL:
SELECT [Main Table].[Unique ID], [Main Table].[Start date], [Main Table].[End Date], (Business_Days_Between([End Date],[Start date])) AS [Days between]
FROM [Main Table]
WHERE ((([Main Table].[Start date]) Is Not Null) AND (([Main Table].[End Date]) Is Not Null));
When ran, this Query works... However, when I enter a criteria like =2 or > 7, it says data type mismatch. I have even attempted the CInt() function to make sure its formated as int but i continue to get the same error.
View 11 Replies
View Related
Aug 14, 2013
Which function in access return the the last value in a string.
Period 1 Period 2 Period 3
10 20 30
I need the function to return 30.The reason behind this I have different periods for categories and my formula I'm using needs the ending value.
View 1 Replies
View Related
May 24, 2005
Dear all
i have a problem that i don't know what to do with it?
Here it is:
i have a cost table in which FO cost is saved.This FO cost is used in different calculation, in different forms. so i create a function called get_FO_cost() as below.
Function get_FO_cost()
Dim s As String
Dim c, r As Object
Dim v As Single
Set c = Application.CurrentProject.Connection
Set r = CreateObject("ADODB.Recordset")
s = "select FO_Cost from Cost_table where month = '" & Me![month] & "'"
r.Open s, c, 1
If r.RecordCount <> 0 Then
v = r![FO_cost]
End If
r.Close
Set c = Nothing
get_FO_Cost = v
End Function
when I use this function for calculation then it returns wrong value.
for Example: the value in the table for FO_cost = 0.005 ( it has a datatype single in table)
then the value in form for this
25 * get_FO_Cost() = 0.124999997206032
but actually 25 * 0.005 = 0.125
when i debug the function code it shows FO_cost value 0.005, but when the debug is over the calculated value i.e. 25 * get_FO_Cost() is 0.124999997206032
Please help
View 3 Replies
View Related
Jan 29, 2008
Hi there !
I display some buttons on my Form. I should do an SQL request to know how many and what the button should display. It works fine with a RecordSet.
Now I want to move the code that do the stuff to a function. I read that I can't return a pointer to a RecordSet and I should used a variant with a getrows.
So I try this :
*here is my function
Function get_nom_operation(ByVal cnn As ADODB.connection) As Variant
Dim requetteSQL As String
Dim rst As New ADODB.Recordset
requetteSQL = "SELECT libelle " _
& "FROM operation;" _
rst.Open requetteSQL, cnn
get_nom_operation = rst.GetRows
End Function
* and here is the code that call the function
Dim res As Variant
Set res = get_nom_operation(conn)
Dim i As Integer
'i = 1
For i = LBound(res) To UBound(res)
Set Obj = Me.Controls.Add("forms.CommandButton.1")
With Obj
.Name = "monButton" & i
.Object.Caption = res(0,i)
.Left = 14
.Top = 25 * i
.Width = 60
.Height = 20
End With
'ajout de l'objet dans la classe
Set Ge = New gere_event
Set Ge.CButton = Obj
Collect.Add Ge
i = i + 1
Next
But it doesn' work, and I don't know why...
The error doesn't show where the code is stoped, but only show the call to display this form.
someone could show me some way to find the solution please ?
Thanks a lot.
View 1 Replies
View Related
Sep 20, 2005
Not sure if I worded my subject correctly. Here's my problem.. I've used query to combine 2 tables of data..
Here are my fields..
I've assignment_num field, project_num field, project_name field, emplyee_name field.
here are my data..
assignment_num field: 1001, 1002, 1003, 1004, 1005
project_num field: 20,20,15,16,15
employee_name field: tom peter, tom peter, tom peter, julie, sally
What I want is to display the employee name with 2 projects or more...
What i have done?
I've done a group by and then count project_num field.. I also added a condition whereby the countofproject_num > 1.. everything went well except that the countofproject_num display 3 instead 2..
pls help.
View 13 Replies
View Related
Jan 11, 2015
I have the following function declared however cant get it to work in the sql string..
Code:
Public Function GetSystemID() As String
GetSystemID = fOSUserName
End Function
However cant get it to return the required value in the SQL string..
Code:
DoCmd.RunSQL "INSERT INTO tblLogs (LoginUser, LoginTime, SystemUser) " _
& "VALUES(forms.frmlogin.txtUserID.Value, Now(),GetSystemID)
View 3 Replies
View Related
Jan 3, 2014
I would like to select a case depending on the output of a function.
This function tests the syntax of the reporting month.
If the syntax is fine nothing should be done further in the main sub else it should return to the Input window for the reporting month.
Somehow it doesn't work out.
Code:
Public Function RepMonthCheck(rep_date As String) as Boolean
If Len(rep_date) <> 6 Or Left(rep_date, 2) > 12 Or Left(rep_date, 2) < 1 Then
MsgBox ("Reporting rep_date is not in the correct format = mmyyyy")
Return False
ElseIf Right(rep_date, 4) > 9998 Then
MsgBox ("No forecast available for year 9998")
[Code] ....
View 3 Replies
View Related
Sep 23, 2014
Question for Documentation purpose: Should the Public Type be declared in its own module?
Or should it be declared in a standard module where non-public functions use it? It is not for a Form module use.
For a Rule Engine, a function is calling one record on 4 different SQL Views (as linked tables) that have the same field format.
For speed, the recordset should only be opened once. However, there are multiple values that must be returned to the result table multiple fields.
One way to return multiple values is an Array. That has over head too.
Another way is to create multiple public variables. Not my choice for documentation. Another is to create a string.
This is a pure code module with several non public functions / subs. What is the documentation preference? List a Public Type close to the function, or place it in the Global module?
Background: A function can only have one return value.
By creating a public Type, multiple values can be returned.
Code:
Public Type Income
Wages As Currency
Dividends As Currency
Other As Currency
Total As Currency
End Type
Use this structure as the return type for a function. In a real situation, the function would look up your database tables to get the values, but the return values would be assigned like this:
Code:
Function GetIncome() As Income
GetIncome.Wages = 950
GetIncome.Dividends = 570
GetIncome.Other = 52
GetIncome.Total = GetIncome.Wages + GetIncome.Dividends + GetIncome.Other
End Function
To use the function, you could type into the Immediate Window:
GetIncome().Wages
(Note: the use of "Public" in the Type declaration gives it sufficient scope.)
Important Notice The way this function is called will work, but is wrong from the aspect it re-calls the recordset over and over.
See the proper way to use it submitted below.
View 5 Replies
View Related
Aug 28, 2013
Need to use CAST to return integer value of string (digits as data type string).
Where clause looks like this:
... Where Cast([Price File] as int) > 0
works fine in SQL Server but not sure what syntax is in VBA . Using Paul Baldy's suggestion to set Select statement as string and do the debug.print to verify that SQL has no goofs ... looks good but not to Access. What is proper syntax?
View 12 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
May 30, 2014
I have a database where I need to return only the first record of each key (named "process key") accordingly to a time frame I set.
Consider the table below:
Code:
ProcessContact dateSalesmanClientClient
KeyKey
99904/28/2014 11:39:00SALESMAN 1111CLIENT 1
88804/28/2014 10:13:00SALESMAN 1222CLIENT 2
55504/22/2014 14:29:00SALESMAN 2333CLIENT 3
66604/16/2014 12:14:00SALESMAN 3444CLIENT 4
55504/16/2014 11:47:00SALESMAN 2333CLIENT 3
If I filter the date, e.g., from April 1st to 30th, I want to get as a result 4 records, one of each client and only the latest record from CLIENT 3 (04/22).
If I filter the date, e.g., from April 1st to 20th, I want to get as a result only the records of clients 4 and 3 from 04/16 (as every other contact is from beyond 04/20).
If I filter the date, e.g., from April 25th to 30th, the return must be only the top 2 records (the only ones after April 25th).
I tried using a query grouping the totals on "process key" and the other cells set as "first", but if I filter from April 1st to 20th, for example, no records from CLIENT 3 gets shown, since Access processed the "first" record request before the date filter, and therefore only considered the contact from 04/22, and as it is later than 04/20, wasn't shown on the query.
View 9 Replies
View Related
Jun 20, 2014
The code has fixed path information on a lot of places in different SQLs (DoCmd.RunSqL command). I want to replace fixed path info with variable path info. Variable path info is stored in the table.
I managed to achieve that in the following manner:
Code:
Dim db As Database
Dim dbName as String
Set db = CurrentDb
Set rs = db.OpenRecordset ("TableName", dbOpendynaset)
rs.FindFirst ("ID = " & 2)
[Code] ....
where I would use as variable Function name instead of dbName.
How to make module that will enable to use Function name as variable path information for SQL queries?
View 10 Replies
View Related
Aug 2, 2015
I am using an apply filter in a form to find records in a split form.
The macro which I am using is:
[SLIDE] Like "*" & [Forms]![SALES]![ENTERREFDATA] & "*"
However I the filter to return only records which match the text exactly.
View 2 Replies
View Related
Aug 26, 2015
=Sum(IIf([Pass or Fail]="Pass" And [Pay Period]="02" And [Location]="Purple",1,0))/IIf(Sum(IIf([Pay Period]="02" And [Location]="Purple",1,0)) = 0, 1, Sum(IIf([Pay Period]="02" And [Location]="Purple",1,0)))
For [Pay Period] ="02" is there a way to make it filter by a combo box from the Mainform? This code is on a Subform the combo box is on a Mainform
Forms![Overview_]![Combo35] <the path for the combo box.
I tried inserting it where the 02 was but it just returns 0 and if I remove the "" it will error the whole thing out. Same if I try to just remove the [Pay Period]="02" and replace it fully it errors out.
View 5 Replies
View Related
Apr 26, 2013
Was wondering if there is a way to incorporate the DatePart function in the below statement to filter out sales tax by quarter? I have two drop down boxes that filter the year and the state but the below only totals the tax for the year and state. I am trying to add 4 text boxes to show the quarterly break down of sales tax.
The below text box is in a form pulling the data from a query.
Text Box
=DSum("[SalesTaxCharged]","[Sales Tax Calculation Qry]","Year([Order Date])=[SelectedYear] And
[StateProv]=[SelectedState] And Not [Tax Exempt]")
View 6 Replies
View Related
Jun 15, 2013
I want to filter the records in a combo box (cboSupplier) using the In() function.
On the form I have an unbound control called "intFilter". It contains the following string: 23, 58
The SQL statement for the combo box is
Code:
SELECT SupplierID, SupplierName
FROM tblSupplier
ORDER BY SupplierName;
In the criteria for field SupplierId I want to use the In() function so that the only records returned by the combo box are those in "intFilter". I have tried
Code:
In (Forms![frmSupplierReport]![intFilter]
but this does not work.
If I put: In (23, 58) in the criteria it works, but I cannot hard code it because the string in "intFilter" will change on the fly!
How would I achieve this?
View 4 Replies
View Related
Jun 15, 2015
I have a textbox on a subform and I want to get the sum in a string as follows.
Code:
Dim s1 As String
s1 = Sum(Forms!CountItem!CountItemLastCount.Form!ThisCount)
MsgBox s1
When I use the above I get an error message saying - Sub or function not defined and it highlights the sum part of the equation.
I have been using the sum criteria in a textbox but if the user doesn't tab of the box then it doesn't see it as being updated.
I have tried me.dirty and everything else that usually works like send keys tab event, requery form and controls, a left mouse click but nothing is working, therefore I thought that code to actually update the textbox may work.
View 8 Replies
View Related
Oct 7, 2013
I have a subform when a user presses Return Or Enter it doesnt go to a new record but instead enters a new line in the same records how can i prevent this from happeneing.
View 1 Replies
View Related
Mar 14, 2015
I have a form with a field within it called ID number, another table contains the ID number with a person's name next to it. I was wondering how I can make it so that in the form, when the ID number is entered, the name of the person also shows next to it and does so automatically for each different record. I am pretty sure that a subform is needed however it doesn't seem to work for me so I must be doing it wrong. How would I do this?
View 1 Replies
View Related
Jan 3, 2014
i have aform which class module where i make 2 queries the first works fine but the other always return values with zero my select syntax is in clsschool and the query is
Code:
strSQL = "SELECT tblgrade.Grade, tblClasses.NoOfclasses, tblClasses.NoOfSessions " & _
"FROM tblgrade INNER JOIN tblClasses ON tblgrade.IntGradeId=tblClasses.IntGradeId " & _
"WHERE tblClasses.intschoolinfoid = " & intId
View 10 Replies
View Related
May 23, 2013
if there is a VBA command that returns the current view of the current active report. I am running code the uses the SetFocus Action, which works fin in Report view, but when I try to go to Print Preview view, Access throws an error stating that the command or action is not available in the current view - and the GetFocus action is the culprit.I can skip the GetFocus action if I know the Report is in Print Preview mode.
View 1 Replies
View Related
Feb 20, 2015
My combo boxes are not returning the actual combo box values back to my table, instead in the table it is displaying the combo box data list number i.e 1,2,3,1,5,1 etc where it should be displaying a property address.
View 2 Replies
View Related
Apr 8, 2014
I would like a field's default value to return the date of the previous Monday.
For example:
Now = Tuesday, 08-Apr-14
Return Monday, 07-Apr-14
Further example:
Now = Saturday, 12-Apr-14
Return Monday, 07-Apr-14
View 6 Replies
View Related
Feb 25, 2013
I am wanting to make a text box on a form, to return a number (amount of records returned by a query)basically so if the query returns 5 records, the text box on the form will show"5",
Lets say the form is called "A", and the query is called "B" .How do I put this in the source control of the textbox ? < if this is right too ?
View 1 Replies
View Related
Oct 23, 2013
I'm using Dmax() to return a max number in a field which I then want to inc by 1 for a new record. Dmax is returning 999. I believe Dmax therefore thinks it's a text field. So where do I change this to a number field?
View 2 Replies
View Related
