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?
Iff(fico>600,1,0) as g,
I found fico is a string in access table. so the above does not work.
How to fix this problem
Thank you very much.
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 query with a Start Time where the need to return a set integer in another field in my query. I am attempting to get this to work in my StripSecondsQry.
I am not getting any error messages and I am not getting any output, When I view this in the Locals window I can see that it should be returning 7, but instead I get nothing unless I change it to
Code:
Function SortStart(StartTime As String) As Integer
then I get zero.
I had this working within the query, but I had to add one more time and then received a message that the expression was too complex.
Code:
Sort_Start: IIf([StartTime]="7:00 AM",1,IIf([StartTime]="8:00 AM",2,IIf([StartTime]="8:45 AM",3,IIf([StartTime]="9:00 AM",4,IIf([StartTime]="9:15 AM",5,IIf([StartTime]="10:00 AM",6,IIf([StartTime]="10:15 AM",7,IIf([StartTime]="10:30 AM",8,IIf([StartTime]="12:00 PM",9,IIf([StartTime]="1:30 PM",10,IIf([StartTime]="1:45 PM",11,IIf([StartTime]="2:00 PM",12,IIf([StartTime]="3:00 PM",13,IIf([StartTime]="4:00 PM",14))))))))))))))
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)
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.
Hi,
I have a field in my database which captures either single digit numbers or comments in text format. I want to be able to count the numbers but obviously I've had to use a memo field in order to capture both numbers and text.
The only way I can think of is to take the field and look for single character responses, then convert these into a number field so that it can be counted.
Does anyone have any idea how to do this?
Thanks.
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 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] ....
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.
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.
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]...
I would like to convert a text string to integer.
Lots of posts say to use val, but it is not listed in access 2010
So I am trying,
Creating a field that is numeric then just referring to the string field.
This works good except where it finds actual text. It puts the value "#Type!" in there.
Would there be some kind of function to check for an error or check if the value is text.
Access 2007
I can't figure out how to replace a period that is in the middle of a string and end up with 10 digits. For example 55.5555 would be 5500005555. I can use replace() but the tricky part is I have to end up with 10 digits.
Ultimately what I'm trying to do is - when a user enters 55.5555, 555.5, 5.5 or any variation they will be able to find the corresponding record. So a wildcard for the search or the replacement of the "." with enough zeros for 10 digits.
Here is what I'm using now - i making them enter the full 10 digit number but would like to give them the ability to use the period in place of the zeros.
Function Search()
Dim lssql As String
Dim lsSn As Recordset
Dim db As Database
Dim lsMessage As String
Dim sMsg As String
Dim vRetVal As Variant
Set db = CurrentDb()
[Code] .....
Have a strings like this
Code:
dsa;hwq;67;dk;71c
Code:
uqiea;762c;iyh
Is there any possibilites to write a function which find number in string, even if some part (between ";" and ";") has number and text (like 762c)?
if there is number in string then function is true
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?
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 am having a query which having a category field like Electrical, Sports, House hold etc.
What I want that if i select Electrical then it should return 15, if Sports then 10 and so on i think this could be done through this below mentioned VBA but it need change from integer to text...
Option Compare Database
Public Function fncGrade(intNum%) As String
Select Case intNum
Case 0 To 1: fncGrade = "Same as Previous"
Case 2 To 32: fncGrade = "C-3"
Case 33 To 40: fncGrade = "C-2"
Case 41 To 50: fncGrade = "C-1"
Case 51 To 60: fncGrade = "B-3"
Case 61 To 70: fncGrade = "B-2"
Case 71 To 80: fncGrade = "B-1"
Case 81 To 90: fncGrade = "A-2"
Case 91 To 100: fncGrade = "A-1"
Case Else:: fncGrade = "X-X"
End Select
End Function
I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.
So if my input table looks like this
[strField]
"kick the ball"
"return the pass"
my query result should looks like this
[Orig] [new]
"kick the ball" "kick"
"kick the ball" "the"
"kick the ball" "ball"
"return the pass" "return"
"return the pass" "the"
"return the pass" "pass"
Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.
With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.
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
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.
In one table, I have a few fields. One of the field is "ItemSequence" and another one is "TotalPcs"."ItemSequence" is where user key in the sequence number for one or more item. 5 example for possible content of "ItemSequence" is as following :
1) 7
2) 4,6,9
3) 5-9
4) 3,5,9, 23-25
5) 3-5, 8-10
"TotalPcs" is the total number of items key in to "ItemSequence". For the 5 example above, the related "TotalPcs" should be as following:
1) 1 (1 item, which is item 7 alone)
2) 3 (3 item which is item 4, 6 and 9)
3) 5 (5 item which is item 5, 6, 7, 8 and 9)
4) 6 (6 item which is item 3, 5, 9, 23, 24 and 25 )
5) 6 (6 item, which is item 3, 4, 5, 8, 9 and 10)
For time being, the user have to count manually to get the "TotalPcs". I wonder is there a way to calculate the "TotalPcs" by programming?
Basically, I am trying to calculate a integer number difference from two dates (TAT = Due-Date - Result_Date). The number is calculated and excludes weekends and ideally holidays (for that I have a tblHoliday but not sure how to use it). The function below seems to calculate a number but doesn't exclude weekends.
For example, if Due_Date is 9/26/2014 and Result_Date is 9/30/2014, then TAT is calculated to be 5 (should be 2).Since 9/26/2014 is a Friday only Friday and Monday are used in the calculation.
Code:
Option Compare Database
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Long
Dim intCount As Long
intCount = 0
[code]...
MS-Access VBA code to separate numbers and string from an alphanumeric string.
Example:
Source: 598790abcdef2T
Output Required: 598790
Source: 5789065432abcdefghijklT
Output Required: 5789065432
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
I have the following SQL statement set out in my union query.
SELECT [Field1], [field 2]
FROM [Table 1]
UNION
SELECT [Field 1], [field 2]
FROM [Table 2]
However I need to make field one numeric, so I can have this 00000 format in the column. I think I need to do this by using CAST and NUMERIC (5) but I am not sure how I would set this out in my SQL statement as when I do it comes back with Syntax error or argument.
Please can anyone help?