Modules & VBA :: How To Run A Function From SQL Query In Access

Nov 26, 2013

I started using Access recently and I have an inquiry about VBA codes and sql queries. I have function which has the following signature: RAG(Stability as Integer, DS_SYNC_RATE as Integer, Profile as String) as String.

I would like to call this function in Insert sql query in order to calculate and add a new column to my table. I tried this basic query:

Insert INTO my_table VALUES (RAG(my_table.stability,my_table.ds_sync_rate, my_table.profile))

but it doesn't seem to work...

View Replies


ADVERTISEMENT

Modules & VBA :: Creating A Function That Counts Records And Use That Function In A Query

Dec 11, 2013

So basically I need making a function that will count the number of records from another table/query based on a field from the current query.

View 2 Replies View Related

Modules & VBA :: Run Outlook Function From Access

Mar 6, 2015

I have an Outlook Function that i am trying to run from Access.What i am looking for is to Open Outlook through Access and run the Outlook function from Access.

View 7 Replies View Related

Modules & VBA :: Possible To Call Windows Search Function Into Access?

Nov 3, 2014

I need a search function that will not just open files based on their names but also drill into the documents and search based on what's in it. The only way I can see now is to use the Window's search function (the one at the start button). Is it possible to call that function into access?how do I re-create the search style of the windows search bar for a specific folder? Something like streamreader

View 1 Replies View Related

Modules & VBA :: How To Write A Function That Does Query

Jul 22, 2013

I have a query string in the following code. I want to put the entire code into a function so I can use it somewhere else as well.

Code:

Private Sub Form_Load()
'To enable the AllowAddition property once the form is opened
Dim dbs As DAO.Database

[Code]......

View 5 Replies View Related

Modules & VBA :: How To Use Split Function Query

Feb 18, 2014

I have a query

Code:
SELECT tblZlecenia.ID_Zlecenia, tblZlecenia.TerminOdbioru, tblOffset.ID_RodzajZlecenia, tblZlecenia.NazwaZlecenia, tblZlecenia.Id_K, tblZlecenia.Id_Rodzaj_Pracy
FROM tblZlecenia LEFT JOIN tblOffset ON tblZlecenia.ID_Zlecenia = tblOffset.ID_Zlecenia
where

In where I would like to put code like this:

Code:
Split(tblOffset.DziedziczonePo, ";")(1) & ";" & Split(tblOffset.DziedziczonePo, ";"(2) & ";" = "tak;tak"

Is it possible to create such a query?

View 1 Replies View Related

Modules & VBA :: Replace Function In Access 2007 For HTML Email In Outlook 2010

Aug 4, 2014

My company recently upgraded our MS Office from 2007 to 2010 (except for Access).

Previously, when I had Access 2007 and Outlook 2007, I had a process that generated 50+ dynamic emails from an Outlook template file (.oft).

The code would loop through a listbox and replace the template's default text to a string of text specific to the selection in the listbox by utilizing the Replace() function on the MailItem .HTMLBody.

Since the upgrade to Outlook 2010, the code is able to run, however, the Replace() function is no longer working; Instead, each email that is generated maintains the template's default text.

The only thing that is not working is the Replace() function, all other aspects of the code work fine.

I've provided a simplified version of the code below:

Dim myOlApp As Outlook.Application
Dim objMailMessage As MailItem
Dim stBody As String
Set myOlApp = Outlook.Application
Set objMailMessage = myOlApp.CreateItemFromTemplate("C:UsersDesktop emplate.oft")

[Code] .....

I've recreated the template file in Outlook 2010, thinking that the template created with Outlook 2007 would be the culprit, but to no avail.

What could have changed from Outlook 2007 to Outlook 2010 that would render my previously valid code ineffective?

Are there certain references I need to enable in both Access and Outlook to allow VBA in Access modify the content in an Outlook email?

View 12 Replies View Related

Modules & VBA :: Undefined Function For A Module Called In A Query?

Sep 8, 2013

This module is giving me the "undefined function" error message when I try to run my query. I don't know why, but I have checked that there are no references with "missing" and there are not. I also added the word "Public" to the function becasue that was advised by another forum user. I thought it worked perfectly the first time I ran this query, but now it is not working and I do not recall making any changes. I have called the module basFunctions:

Option Compare Database
'************************************************* *********
'Declarations section of the module
'************************************************* *********
Option Explicit
'================================================= =========
' The DateAddW() function provides a workday substitute
' for DateAdd("w", number, date). This function performs
' error checking and ignores fractional Interval values.
'================================================= =========
Public Function DateAddW(ByVal TheDate, ByVal Interval)
Dim Weeks As Long, OddDays As Long, Temp As String

[code].....

View 3 Replies View Related

Modules & VBA :: How Can Function Return Multiple Values And Not Re-run In Query

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

Modules & VBA :: Using Public Function To Feed A Variable String To Query

Feb 26, 2014

I am using a public function to feed a variable string to a query. So far I have got:

Code:
Public Function ClientStreetModule(firstLVar As Variant, streetVar As Variant, newFL As Variant) As String
Dim cslStr1 As String, newStreet As String
newStreet = Right(streetVar, Len(streetVar) - Len(newFL))

[code]....

However, I only need to use newStreet as the true part of iif, in which instance all are longer. At least I think this is the problem. I realise I might need to use NZ but am not sure how. Why it is evaluating and giving errors for all records and not just when the iif criteria is true as I want it to?

View 7 Replies View Related

Modules & VBA :: Pulling Data Using Query - Combine Two Functions Into Third Function

Jun 28, 2013

I created two functions to use when I'm pulling data using a query (QBE). I would like to combine the two into a 3rd Function that works like this:

BB_Only = DishNetActivityCode = 1 and VideoActivityCode = 0
Hybrid = DishNetActivityCode = 1 and VideoActivityCode = 1
V_Only = DishNetActivityCode = 0 and VideoActivityCode = 0

Function DishNetActivityCode(service_code_string As String) As Integer
Dim CodeArray, i As Integer

[Code] .....

View 14 Replies View Related

Count Function In Access Query.

Apr 11, 2007

i'm trying to count the total number of records found for this query. i used the following expression (code) in the QED field row Total Incidents: Count([IncidentID]) which, result in an error message.

Can sum1 help me solve this problem. any help would be highly appreciated.

View 4 Replies View Related

ROUNDUP Function In MS Access Query

Aug 8, 2006

Hi,
I am on a elementary level of MS Access skill.
I am trying to create an expression, where I want to round up the value in a field.
Expr1: ROUNDUP([compass_pro_cost_data]![billed_weight],0)
When executing the qry, I get :
"Undefined function "ROUNDUP" in expression, I am using Access 2000. What am I doing wrong, or Is there another way ?

View 6 Replies View Related

Aggregate Function Max In Update Query In Access

Dec 31, 2007

Hi ALL,

I have 2 tables, trying to update one table filed by using max(onefiled) from other table.

my qyery is as follows:

my tables are tblSubFlowForecast and tblPickCalendar

I have to update one field in tblSubFlowForecast by taking max(onefiled) from tblPickCalendar.

UPDATE tblSubFlowForecast INNER JOIN tblPickCalendar ON tblSubFlowForecast.Delivery=tblPickCalendar.Delive ry SET tblSubFlowForecast.[Latest Replen Date] = ( SELECT max(Pick) FROM tblPickCalendar WHERE Type="Replen");

I am getting error saying that "Operation must be an updateable query" :(


I have also tried in other way:

UPDATE tblSubFlowForecast SET [Latest Replen Date] = (SELECT max(Pick) FROM tblPickCalendar WHERE tblSubFlowForecast.Delivery=tblPickCalendar.Delive ry AND tblPickCalendar.Type="Replen" ) FROM
tblSubFlowForecast,tblPickCalendar

This is giving me syntax error (missing operator) :(


Can any body tell me why it is giving error?
Thanks in advance

View 3 Replies View Related

General :: Public Function Not Callable In Access Query

Sep 17, 2013

I created a Public Function called fNetWorkDays in my access database, however, when I try to use it in an expression I get "Undefined function 'fNetWorkDays' in expression". The Public Function is in a standard Module in my vba project. Why I cannot call it in my queries?

View 3 Replies View Related

Queries :: Combining Data In Access Query Using Builder Function

May 13, 2014

I have a table in Access 2010 and in one field i have multiple records of the same data as in the next field it has unique data for example:

NameColour
CarBlue
CarGreen
CarYellow
BusOrange
BusPurple
BusRed

I am trying to run a query which will effectively group up the "Name" field and combine the "colour" field against the name using a ";". so it would look like this:

NameColour
CarBlue;Green;Yellow

How i would do this.

View 5 Replies View Related

Modules & VBA :: Inserting Function Within A Sub

Feb 25, 2015

I am new at VBA in access and I am working on an application I didn't develop but I am maintaining.

Code:
Private Sub Command122_Click()
Dim formName As String
formName = getFormName(tblName)
DoCmd.OpenForm (formName)
DoEvents
Forms(formName).FilterOn = False

[Code] ....

I am trying to to write afunction that translates the deptid into a table 'id (myreference)

View 1 Replies View Related

Modules & VBA :: Multiplication With Str Function

May 6, 2015

I got the following problem.

This code doesn't calculate the exact value:

Code:

Sub TEST()
Dim strSQL As String
Dim b As Double
DoCmd.SetWarnings False
b = DLookup("MC", "COMMON_DISTRIBUTION", "YEAR = 2015")

[Code] ....

My variable b is 0.230.
The TNS is multiplied with 0.228.
It seems a problem with the Str(b).

View 5 Replies View Related

Modules & VBA :: How To Run A Function In Subroutine

May 17, 2014

I have a database which needs to be updated everyday. So I decided to make a subroutine to perform the update steps just by clicking on a button.

The problem is that I can run all the steps except one! At the end of the steps, there is a function/module that must be run to calculate and complete the job.

How I can run a function in a vba macro. This is my code:

Private Sub cmdUpdateDatabase_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry1AddNewStockSymbols"
DoCmd.OpenQuery "qry2AddDailyPrice"
DoCmd.OpenQuery "qry3UpdateStockDailyPrice"
DoCmd.OpenQuery "qry4AddOverallStockValue"
DoCmd.OpenQuery "qry5AllSymbolsActiveDaysTractions"
DoCmd.OpenQuery "qry6SortToCalculateDailyRemainedStock"
DoCmd.SetWarnings True
End Sub

Module name is: CalculateDailyRemainedStock

View 3 Replies View Related

Modules & VBA :: Run Function From Another Database

Aug 7, 2014

I have 2 database files.

database A has a button (buttonRun) that when clicked, it opens database B and runs the autoexec macro.

In database A, there's a label control (lblRun) next to the buttonRun that has its back style property set to transparent.

What I'm trying to accomplish is this:

After the procedure in database B completes, it exits the system. I want

lblRun's back style property to be set to normal.

I already have a function that does this and the function works fine from within the database A.

Code:
Dim frm As Form
Dim color As Variant
Set frm = Form_frmReportingDashboard
frm!lbl_cmslite.BackStyle = 1

I need to trigger that function from database B before it exits.

View 8 Replies View Related

Modules & VBA :: Dir Function Empty For Certain Drives

Sep 17, 2014

I have a module for importing several excel files from a folder in Access. The user is prompted to select the folder which contains the Excel files.The issue:The code works fine if I select a folder from my company network drive (strFile variable returns with correct file name).The code does not work if I copy and paste the same folder to my desktop and select this location (strFile variable returns empty)

Code:

Option Compare Database
Private Sub btn_ImportData_Click()
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim intWorkbookCounter As Integer
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection

[code]....

View 7 Replies View Related

Modules & VBA :: Possible To Initialize A Function Like Do For Variables?

Jul 15, 2015

Sometimes we need a one-line function to just get the database path or things like that we cannot do on a query or on a Macro Object.

Like:

Function GetDatabasePath() = currentproject.path

possible?

View 5 Replies View Related

Modules & VBA :: Function Not Available In Expression Error

Sep 10, 2013

I have got a problem on one of our computers.

We use Microsoft Access databases to enter test data and eventually generate reports.

On 1 computers i get the following error when opening the report:

"Function is not available in expressions in query expression 'Left(.....)"

I figured this is a reference problem so the next thing i did was to pinpoint what reference was causing this error.

It turns out it is the Microsoft DAO 3.6 Object Library. Simply removing the reference and adding it again fixes it and allows you to open reports just fine.

The problem is though, this message comes back every now and then. Which is getting annoying and the person who is making the reports is about to throw his computer out of the window.

View 2 Replies View Related

Modules & VBA :: Argument In Custom Function

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

Modules & VBA :: Runtime Error 52 When Using Dir Function

Jul 2, 2013

I'm trying to delete a file in a network location. Sometimes the file is there, sometimes it's not, so I'm using the following code:

Code:
strPath = "etwork driveMy Folder"
strFilename = "MyFile.doc"
If Not Dir(strPath & strFilename) = "" Then Kill strPath & strFilename

Occasionally, I get a runtime 52 error when the file doesn't exist. I don't understand why I'm getting this when, if the file doesn't exist, the Dir function should return "".I searched the forums for Runtime 52, and didn't find anything pertaining to this.

View 11 Replies View Related

Modules & VBA :: Function To Define Colors

May 1, 2014

I have one color scheme I want to use all through the database I am developing. The next examples have just one color defined, to make it simpler (a dark blue, that I would call B1)

Code:
private sub setlabel()
Dim B1
B1 = RGB (0,52,105)
me.label1.forecolor = B1
end sub

... however this means I have to repeat the color definition every sub, so I thought would be neater to define a function to set my color codes (I have 20 colors).

Code:

Function SetColor()
Dim B1
B1 = RGB (0,52,105)
End function

My objective, is when I'm working in forms, Iwould (ideally) call this function "setcolor" and just write my code for the blue. I tried the examples below:

Code:
Private sub setlabel()
SetColor()
me.label1.forecolor = B1
end sub

[code]...

Again, this is probably some definition of arguments or dimensions that I am not aware oh. How to predefine the colors in a function to give them a "short" code which I can call in any sub in the database?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved