Queries :: Call A Function Via Control Element In SQL
Jul 18, 2014
is it possible to call a function in a query with the input of a control element?
I have a form, and there is a list box in which I can choose the calculate method. In the query, a field should calculated with the choosen function.
Example:
Code : SELECT tblTest.price, tblTest.Date, forms![formtest] As Calc
...
The function has the same name as I can choose in the listbox. But, if I run the query a error appears.
Code : "Function forms![formtest]![lstChosenFunction] could not be found"
how I can call a function in SQL with the input of an listbox.
View Replies
ADVERTISEMENT
Sep 4, 2013
I'm having to recode some old MS Access DBs so they will run in the following environments:
Office 2000 on WinXP
Office 2003 on WinXP
Office 2010 on WinXP
Office 2000 on Win7
Office 2003 on Win7
Office 2010 on Win7
When I wrote my code for Office 2000 on WinXP things were simple because directory paths were the same across all computers and I could hard code pathing when using a shell command to launch other files.
My new approach is to make a function call to the Windows registry to determine the default executable and path for opening a file based upon its extension (see apicFindExecutable in basWindows API module).
I'm able to use code to create a shell call and debug print it to the immediate window. If I put my cursor in the immediate window at the end of the shell call and hit [enter] the external file will open as desired. If I try to open the external file directly through code, I get a file not found error.
To recreate the error take the following steps:
(1) browse to files that are accessible from your computer
(2) click the PREPARE DATA AND OPEN MAIL MERGE DOCUMENTS command button
Shell function call is made by the fnOpenFile function located in the basOpenFile module. There has to be a trick here that I'm missing.
View 5 Replies
View Related
May 3, 2007
I did some searching and found this function posted by someone in response to the exact same problem I'm now encountering.
But how do I call this in my query?
Function StripZeros(pstr As String) As String
Dim n As Integer
n = 1
Do While Mid(pstr, n, 1) = "0"
n = n + 1
Loop
n = IIf(n > 1, n, 1)
StripZeros = Mid(pstr, n)
End Function
View 2 Replies
View Related
Aug 16, 2014
I want the Query Criteria to pull its value from a control on a form.The form control either has data or is null. (My problems occur when the form control is Null). The field in the table either has data, is null or is blank.
Code:
=IIf(IsNull([Forms]![FormName]![FormControl]),"" Or Is Null,[Forms]![FormName]![FormControl])
This works for the records with fields that are blank.
Code:
=IIf(IsNull([Forms]![FormName]![FormControl]),"",[Forms]![FormName]![FormControl])
This works for the records with fields that have data.
Code:
Like IIf(IsNull([Forms]![FormName]![FormControl]),"*",[Forms]![FormName]![FormControl])
This works for the records with fields that are Null or Blank if i drop the iif function but then i lose the ability to pull criteria data from the form control.
Code:
"" Or Is Null
View 3 Replies
View Related
Dec 1, 2005
Hi all!
I have a query that returns some fields with "#Error" ...
The reason is that I call a function in the query, and the function chokes if there are no matches in my query criteria and therefore no argument is sent to the function.
I tried putting this in the function:
var = Iif(IsNull(var) = True, 0, var)
-but I still get "#Error" when there are no matching records.
I presume this is because not even Null is given to the function as an arg.
Should I make the arg optional or how can I solve this??
Thanks :)
View 6 Replies
View Related
Oct 29, 2004
I have linked an excel workbook as different tables in Access.
The workbook contains a Microsoft Query. I have now found out how I can open and edit the workbook from access, how can I Update/refresh the MSQuery before I close it?
Here is what I have:
Private Sub cmdTest1_Click()
Dim MyXL As Object
Set MyXL = CreateObject("Excel.Application")
Set MyXLSheet = GetObject("c: est.xls")
MyXL.Application.Visible = True
MyXLSheet.Parent.Windows(1).Visible = True
MyXL.Application.Cells(1, 1).Value = Now()
MyXLSheet.Close SaveChanges:=True
MyXL.Application.Quit
Set MyXL = Nothing
End Sub
eroness
View 1 Replies
View Related
Feb 23, 2007
http://www.access-programmers.co.uk/forums/showthread.php?t=123538
Following on from that thread, I would really like to tidy up other areas of my front end. I am using the idea that Roy suggested and it works very well.
At the moment, I have a form, that has buttons on it that people click when they want to generate specific sheets from excel using data from access using VBA on the form. These work fine, however, now that the database is going live and all features need to be added, I think it would be nice to have one small form, with a list box.
I have converted a copy of the code on the form and placed in a module by declaring then functions instead of private subs.
At this stage, I tried doing it with macros running the code by using a series of runcode. I then created a table that has the macro name stored in it along with a decriptive bit of text detailing the excel chart that gets produced to make it easy for the user.
The list box populates itself correctly, but the problem I am having is I do not know how to make the 'go' button look at the list box, pull the hidden macro/function name from the list, then go and run the macro/function.
I have tried looking about, but have not found anyhthing.
View 4 Replies
View Related
Aug 10, 2007
Dear Access Query Expert
I have created a query which has a function call as the criteria for one of the numerical fields. The function returns a string expression such as .....
1) 132 OR 142 OR 156
2) 132 OR 142
3) 132
..... into the criteria section of the numerical field.
Unfortunately, the query doesn't work if the criteria is generated by the function call. However, if I hardcode the criteria (don't send a function call but directly write 132 OR 142 OR 156) the query works.
I am puzzled and I am not sure how to solve this problem.
Thank you so much.
View 1 Replies
View Related
Aug 8, 2006
Is it possible to call a function from a field on a form, ie, in the
control source put = FunctionName(abc)? Then I want to concantenate
it to another variable so it would look like:
=FunctionName(abc) & TextVariable
I tried this and got the ?Name on the form, like it couldn't find the field
name, which is really a function I'm calling.
Is this possible and if so, what am I doing wrong?
Thanks in advance for any help.
View 1 Replies
View Related
Apr 5, 2014
I have the following in Form and it works OK
Call RunForm("frmProviderLookup", "", "Edit", "", "", "Normal", "Normal")
I want to build a combo box with several different RunForm variations as follows:
RunForm("frmProviderLookup", "", "Edit", "", "", "Normal", "Normal")
RunForm("frmCustomerLookup", "", "Edit", "", "", "Normal", "Normal")
RunForm("frmVendorLookup", "", "Edit", "", "", "Normal", "Normal")
etc, there are many of these
When I select one the variations from the combo box the RunForm statement is stored in strAction varaible..How do I code the Call strAction line? (I want only one call statement).In other words how to call a Function from a variable...The start of my module Function works OK and looks like this:
Public Function RunForm(FormName As String, _
Optional WhereCondition As String = "", _
Optional xMode As String = "", _
Optional filterName As String = "", _
Optional Args As String = "", _
Optional WindowMode As String = "", _
Optional View As String = "")
' Parameters (all optional except parameter 1)
' 1 - Form Name
' 2 - Where condition
' 3 - Mode [Add, Edit, View(Read Only), ""(PropertySettings), DS(Datasheet Edit)]
' 4 - Filter Name
' 5 - Opening Arguments
' 6 - Window Mode
' 7 - Form View
View 1 Replies
View Related
Oct 14, 2006
I have 3 Controls in a form, Control1, Control2, and Control3. Control2, and Control3 both have procedue under event OnDblClick, and I want to wirte code under Control1 OnClick to call OnDblClick of Control2 and then OnDblClick of Control3. Have try several ways but failed. It is another way besides copy whole procedure from OnDblClick into OnClick code?
View 3 Replies
View Related
Jan 16, 2007
With the script in the form that is part of MS-Access. I wonder how do I access/call the written function that are in the DLL file I made. I appreciate your help on this one...
View 2 Replies
View Related
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
Oct 16, 2014
I know its not possible. But Just wanted to confirm. Not at all possible?
Code:
Function PreImport(BookToImport As Workbook)
For Each cell In BookToImport.Sheets(1).Rows(1).Cells
cell.Value = Trim(cell.Value)
Next cell
BookToImport.Sheets(1).UsedRange.Rows(1).Replace ".", "_"
End Function
View 1 Replies
View Related
Aug 13, 2013
I'm looking to use the shell function to dynamically call different pdfs that are in a directory. However I'm getting run time error 5 "Invalid procedure or call argument".
Here is the code (very basic I know)..
Private Sub Liste_Documentation_DblClick(Cancel As Integer)
Dim PathName As String
PathName = Me.Liste_Documentation.Column(2)
' Debug.Print PathName
Shell PathName
End Sub
A typical filepath name is as follows..
S:VenteVendeursMarcCRMDOCSDiligences KYC - LABFT - V 2013 04 23.pdf
View 8 Replies
View Related
Mar 29, 2006
Hi,
I have an MS Access application installed on a number of machines and it is running fine on all of them except one.
On one of the server when I am submitting a form to search the database I get the error message:
"Automation Error. Element not Found."
Same version of MS Access on all machines. We are thinking of reinstalling MS Access on the machine to see if this would resolve the problem but just want to check if anybody else has any other suggestions???
Has anyone come across this problem before?
Any ideas or suggestions about how I might resolve it would be appreciated.
Thanks
View 1 Replies
View Related
Dec 8, 2011
I was tasked by my CFO yesterday to add a new element to my existing database. I will try to keep what I am trying to do simple.
Basically we do finance for multiple projects. each project has employers.
My database records these employers names, SSN, dates registered, program they are in, and a few other elements as well.
What I have been tasked to do is create a new table with the project managers in that table. this way in a query or report i can pull "sally sue's" projects or whoever I needed to. There are many PM's who have more than one project.
Here is what I have for relationships:
On the Project Managers table I have a PK for ID and then the PM Name as a second field. the ID field is linked to the Programs table which has a PM ID and Program name
The Program table is also related to the employer table which has all of the employers and their info. the relation between the two tables is the Program Name.
So when I run a query or report my goal is to be able to pick any project manager and get the associated count of employers under all of that projects managers programs. I have gotten errors when running queries. Perhaps I have the relationships set up wrong?
View 1 Replies
View Related
Dec 5, 2014
I've created a query to identify payees with only initial instead of first name for cheque payments.The query produces the correct results as far as I have seen so far.
Code:
SELECT Daily_Work_Allocation.contact_reference, Daily_Work_Allocation.Payee, Len(Mid([payee],InStr([payee]," ")+1,(InStr(InStr([payee]," ")+1,[payee]," ")-InStr([payee]," ")-1))) AS Expr1, Daily_Work_Allocation.payment_method, Left([contact_reference],5) AS Expr2, Mid([payee],InStr([payee]," ")+1,(InStr(InStr([payee]," ")+1,[payee]," ")-InStr([payee]," ")-1)) AS Expr3
FROM Daily_Work_Allocation
WHERE (((Daily_Work_Allocation.payment_method)="Cheque") AND ((Left([contact_reference],5))="PPI70"));
However I only wish to see those records that have a value of 1 for 3rd field (Expr1).When I enter 1 in criteria I get the IPC message?
View 4 Replies
View Related
Nov 20, 2014
I have a report in which I'm trying to hide some elements from view in case a specific value is Null. Access seems to do this automatically if the report is in print-preview-mode, but not in normal view-report-mode.
I've tried something like this, but it's not really working:
Code:
private sub report_load()
if nz(txtfield, 0) = 0 then
txtfield.visible = false
end if
end sub
Is there some way to achieve this, maybe?
View 5 Replies
View Related
Mar 28, 2008
Hello there,
I need help regarding how to use two queries in one pivot chart
I have 2 queries one for placed status and other for Not Placed Status
View 2 Replies
View Related
May 28, 2014
I have an issue with a BeforeUpdate procedure that I am trying to get working. I'm using simple elements here as this is a test DB, not the real one yet.
I'm trying to get the value from a Text Box TextData into a string cString. I've used a Dim statement to declare cString - I think the problem I am having is trying to get the actual data from the form. The error I am getting is:
Run Time error '2465'
Microsoft Access cannot find the field "|1" referred to in your expression.
The code I am using is:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim cString As String
cString = [Forms!FormData!TextData]
End Sub
(I've removed some code that is either comments or working since I have to retype things!)
The line cString = [Forms!FormData!TextData] is where the debugger highlights as the error.
View 3 Replies
View Related
Jun 28, 2013
I have a query that displays the average call rate on various pieces of equipment over the last 12 months. I was wondering if there is a way to have Access look at this query and determine if the call rates are trending up or down over that time frame. Kind of like a trend line would do on a chart in Excel, only I want this to show me the word Up or Down in my results. Is this possible?
Here is the query I am using:
Code:
TRANSFORM FormatNumber(Avg([All Call Rate]),2) AS AvgCallRate
SELECT List_With_TNC_tbl.Device, List_With_TNC_tbl.Model, List_With_TNC_tbl.[Item Num]
FROM List_With_TNC_tbl
GROUP BY List_With_TNC_tbl.Device, List_With_TNC_tbl.Model, List_With_TNC_tbl.[Item Num]
PIVOT Format([Month Start Date],"mmm") In ("Jan","Feb","Mar","Apr","May", "Jun", "Jul","Aug","Sep", "Oct", "Nov", "Dec");
View 3 Replies
View Related
Jul 4, 2013
I am trying to run a query in Access 2010 but I am getting an "Invalid Procedure Call" error. I searched online and found that this error can be caused by broken references.
I opened the VB editor (Alt F11) to search for "Missing" references but I do not see any. These are the 4 that are checked.
1. Visual Basic for Applications
2. Microsoft Access 14.0 Object Library
3. OLE Automation
4. Microsoft Office 14.0 Access database engine Object Library
View 8 Replies
View Related
Jan 18, 2006
I am totally new to ms access form development.
I have to construct a form with a text box a command button and listbox.
listbox contains the list of items.I have populated the list box with the items in the tables.Now on the same page I have to add new item via text box and when click command button it should be added(all these thing I have done).
Problem is that I have to do some thing like
if a type any letter in the text box all listbox item are arrange like that like
If Items are abcd,aaaa,aabb, aa,abb,bba,bcd,bab
I enter ba in the textbox then itme should be arrange like
bab
bba
bcd
aaaa
aabb
abcd
View 2 Replies
View Related
Aug 7, 2013
When running a query in Access 2013 or 2010 we get an ODBC call failed. However when we run just the form, which the query connects to, it works just fine.
View 3 Replies
View Related
Oct 4, 2013
I am trying to read the value of a control on an open form from a Public Function in a Module with no luck. I suspect the issue is syntax.
Code:
Public Function MyFunction(varFormName As String, varControlName As String)
If Forms(varFormName).Controls(varControlName) = True Then ' = 0 even when control is -1
Else
End If
End Function
The variables pass through fine but the If statement equals zero regardless of the state of the form's control. I tried several variations to no avail.
If Forms(varFormName).Form.Controls(varContactControl ) = True Then
If Forms(varFormName)!Form.Controls(varContactControl ) = True Then
Etc.
View 4 Replies
View Related
