General :: Which Function In Access Return Last Value In A String

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 Replies


ADVERTISEMENT

Queries :: Function To Return Required Value In SQL String

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

Modules & VBA :: SQL CAST Function - Return Integer Value Of String

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

Modules & VBA :: Fixed Path Information - Function To Return String

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

General :: String To Return Textbox Sum

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

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 14 Replies View Related

General :: String Function To Detect First Alphabetical Character?

Jul 18, 2014

I have a string where alphabetical and numerical characters are mixed up. I need a string function where I can find the index of the first non-numerical character. I tried with the mid function but could not get it to work.

View 13 Replies View Related

General :: Specifying Connection String In MS Access Forms

May 9, 2013

I have a question about connection strings. Basically I have an access front end and an access back end. It works fine for now, but when it gets rolled out, I don't want to have to manually relink the tables every time. Is it possible to hard-code the database connection strings for all the tables used in the solution as the paths won't change once deployed?

View 9 Replies View Related

File Search To Return Path String

Apr 25, 2007

Hi, I have a command button that opens a pdf file. However i will be distributing the database as a runtime package and need to account for people having different versions of adobe reader so need to search for the filepath string where Acord32.exe is found. This is the code I have but I am stuck! Ay help aprpeciated!

Private Sub Command4_Click()

Dim stAppName As String
Dim stPathName As String
Dim fs As Object

Set fs = Application.FileSearch
With fs
.LookIn = "C:Program Files"
.SearchSubFolders = True
.FileName = "AcroRd32.exe"
.Execute
????? stAppName = .FoundFiles
Set fs = Nothing
End With

'specify path name for version of adobe acrord32.exe
'stAppName = "C:Program FilesAdobeReader 8.0ReaderAcroRd32.exe "
stPathName = GetIniSetting("C:WINDOWSSSI_DL3_PROGRS.ini", "DIR", "REPORT_FILELOCATION") & "HOOF.pdf"
Shell stAppName & stPathName, vbMaximizedFocus

End Sub

View 1 Replies View Related

Queries :: Return Value In A Text Box On A Report If String Contains?

Mar 24, 2014

In my query, I have the week number and year arranged like this - "Y14-W11"

I want to return a value in a text box on a report if the string contains, for example, W11. In this textbox I've put the expression

Code:
=IIf([Y##-W##]="*" & "W11" & "*","2100000","BLAH")

But this just returns the falsepart no matter if the string contains W11 or not.

View 3 Replies View Related

General :: VLookup Function For Access

Aug 20, 2014

I am working on recreating an excel file in access. My excel files uses the Vlook up function to get data from a table for calculations.I am calculating Fruit Solids.

=VLOOKUP(BRIXVALUE,'Brix Table'!$A$1:$F$752,6)*QUANTITY

(BRIX VALUE references a cell, and quantity references a cell just used the names instead of cell references).The Brix table has 751 entries. The headers for the following tables are "Brix, Gravity, Weight, GFL, LBS, FS/Gal".So the look up table takes the BRIX value, finds it in the first column then takes the value in the 6th column(FS/Gal) then multiples that by the Quantity inserted in the other table. I didnt create the Vlook up access form so I am trying to get an understanding and recreate it in access.

In access i made a table called BRIX to store the Brix table information like the one in excel.Then i have fields in a different table where you enter the quantity, and brix value of the product. I now need to make the Vlook Up function in excel work for my access data.

View 4 Replies View Related

Modules & VBA :: Return Single Value From Table And Assign It To String - DLookup Not Working

Sep 3, 2014

I am trying to return a single value from a table and assign it to a string to be used later but Dlookup isnt working at all. below is the code im using and the error message im recieving is "wrong number of arguements or invalid property assignment"

Code:
Sub boo()
Dim result As Integer
result = dlookup("Definition", "Config", "Parameter = 'Mail Folder'")
End Sub

View 13 Replies View Related

General :: Find Function / Expression In Access

Nov 8, 2012

I'm relavtively new to databases but after a steep learning curve (being left with many databases created by my old manager with no instructions on use) I'm getting there.What I am trying to do is pull out the date from records in a field called "Model_ID". The Model_ID field contains this kind of information:

Endscopy20120726JSmith
GISurgery20120521JDoe

I want to bring back "20120726" or "20120521". In excel I can do this with the mid and find functions but find doesn't work in access. I've tried combining the mid function with instr function but it comes back as too complex!

View 10 Replies View Related

Function Return Wrong Value

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

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

Get String Function

Apr 1, 2008

Hye

I have an Access database in which one of the queries has following fields:

Reference1:Getstring([Reference],1) and the second is

Reference2:Getstring([Reference],2)

I cant find any reference to Getstring function's use or functionality. Microsoft's website only says "Obsolete" indicating this is no longer used. (i am using access 2003)

Any help is appreeciated.

Many thanks
Jay

View 3 Replies View Related

How To Return An Array From A Function ? And Handle The Result After ?

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

Count Function To Return Unique Records

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

Modules & VBA :: Case Differentiation Through Return Value Of A Function

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

Modules & VBA :: Public Type (variable) For Function To Return More Than One Value

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

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

DatePart Function, String Functions

Nov 1, 2005

Hey all I have a I was wondering if you knew how I can get a part of my datepart function out, I have taken the year from the the date with the date part function and now I need only the 05, so what can I do to get it out? Thanks MY CODE: Dim Num as string Num = DatePart("yyyy", STRDATE)
I looked up the code for a right function and it is suppose to work on a string, so I tryed it on this. My CODE:
Dim Year As String
Year = RIGHT(Num, 2)
I obtained a type mismatch WHY???

View 7 Replies View Related

Function For Combining 2 String Expressions?

Jan 12, 2007

I am having a conversion problem combing the following 2 string expressions.

Condition 1: Format([TestDate],'mmm dd", "yyyy')

Condition 2: NZ([TestDate],"No Exam Found")

I want to convert the dates to string values, and evaluate for Null fields, if Null display "No Exam Found."

I am having trouble getting both of these string expressions to work together.
If I use them together the NZ statement fails.

I can get the date value in the column to display as a string "Feb 14, 2004"
or
If I use the 2nd condition if a field is Null display "No Exam Found", separately.

But how to do I get these two conditions to work together on the same column of information?


[TestDate]
02/14/2004 ---->"Feb 14, 2004"
03/22/2005
07/21/2005
05/11/2004
10/12/2004
(BLANK) ----> "No Exam Found"
09/28/2004
03/14/2006


I am comfortable with Access, and basic Queries, but I am a non-programmer. I am tipping my toe in the Access programming water with Michael Alexander's book "Microsoft Access Data Analysis: Unleashing the Analytical Power..." and his Web tutorials... which has given me enough confidence to tackle this.

I have searched the web and have been toiling over this for about 2 weeks now.

Thanks

View 5 Replies View Related

Queries :: How To Use String Split Function

May 26, 2013

How many kind of string inbuilt function we have or if we create our on function so how to call that function in your query...

View 6 Replies View Related

Queries :: Failing To Filter By Criteria After Return Values From A Function?

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

Modules & VBA :: Function - Can A String Be Optional Variable?

Aug 26, 2014

I am amending some Code I found online for an audit table, I need to store additional information in the table that is associated with some forms but not others.

I have researched about putting optional variables in, but I read this only works with the type VARIANT.

Is there a way to make a string optional as my fields contain text?

here is my code so far:

Sub AuditChanges(IDField As String, UserAction As String, Optional UserID As String, Optional DeviceID As String, Optional SimID As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date

[Code]...

View 5 Replies View Related







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