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?
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?
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.
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?
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.
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?
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
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.
(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.
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
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!
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
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?
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)
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
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 ?
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..
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")
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.
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.
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???
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?
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.
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.
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