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.
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.
Instead of the combo box value being passed into the target query as it should I *always* receive a popup that states "Enter Parameter Value"
I set my filter criteria as such in the target query: "[Forms]![Invoice_Form]![comboFamily]" but for some reason, despite there being a value in that combo box, I am always prompted for a parameter. VBA code associated with a "Generate Invoice" button on the form causes the query and the report to open.
I've attached the database, everything is dummy data.
The suspect form is the "Invoice_Form" the suspect query is the "Invoice_Query" and the suspect report is the "Invoice".
If a number is entered in the "Enter Parameter Value" field that matches the primary key of a family in the "Family" table then a report is generated correctly (as it is currently configured). For some reason it seems that the form with the combo box just cannot be accessed by the query.
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??
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
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.
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?
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
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...
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
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
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
I have a form where users can specify crtieria for a query. The value that the user enters via a combo box on a form is entered in a query. The user then hits a button to run the query.
Problem is the entry i have in the criteria of the query that links to the form is not quite right. It work for when they enter a value but causes problems when they leave it blank. I currently have:
Like (IIf(IsNull([Forms]![Main]![CBGroup]),"*",[Forms]![Main]![CBGroup]))
CBGroup is the name of the combo box and this expression sits in the criteria of the group field.
If the user does not enter a value in the combo box i want the query to return all the records, including where the field is blank. However at the minute it only returns the records where there is any value, not the blanks as well.
The expression above works fine with Text boxes but not combos.
Sorry for the essay, thanks if you read down this far.
I have two tables: one that has (in part) call data from calls to a call center. The data includes the initiating (or calling) number/Start Date of the call/ Start Time/End Time/Duration, as well as City and State (abbreviation) of origin. I also have a table that includes time zones by state (abbreviation) [ie ME (Maine)= Timezone 1 and CA (California) = Timezone 4 in the US.] I am trying to parse the data by developing a query to break the call arrival into 1/2-hour increments. I.e. I want to see the total number of calls arriving by sum of calls by state (time zone) each 1/2-hr. When I set the criteria statement as "Start Time" Where (and in the criteria section) >#1:00:00 PM# And <#1:30:00 PM# the query fires off providing the required data. However, if I add the addtional OR statement >#3:00:00 PM# And <#3:30:00 PM# for instance, the query dies. I need to establish a parsing mechanism that, in a single query, takes the data from any date range and breaks it into 1/2-hour increments to determine where the calls are coming in from and how the pattern changes during the day (or over time). Thank you, in advance, for any assistance you may be able to provide.
I am trying to use an IIF function in each of three yes/no fields in a query to select records where one of the yes/no fields (as determined by a dialog form) = true, regardless of the value of the other two fields. Each record can contain any combination of Trues and Falses in these three fields.
The field to be evaluated will be determined based on a combo box on a dialog form. For example for the SALproc field the criteria cell contains: -
There are corresponding functions in the criteria cell for the otehr two fields
So when I am reporting for SAL I can include all records where SALproc = true, regardless of whether the other fields are true or false.
I can get these functions to work in three separate text boxes on the dialog a form, and was feeling quite clever, but I can't get them to work as query criteria. I get an error message saying it is typed incorrectly or too complex.
I have also tried using the IIF functions in text boxes on the dialog form, and then using the value of those text boxes as criteria in the query. I get the same error message.
I can also get the query to select the records I want by manually entering True for the SALproc field and * for the other two yes/no fields, so the concept of using some combination of true, *, * as criteria appears to be valid.
I would really appreciate some help on this, I suspect I am not far away from making it work.
I'm assuming that it is actually possible to use an IIF function as a query criteria ... ...
In the meantime I am going to try creating functions containing the IIF functions and use those as criteria.
I am using a function to set criteria in a an query. It reads the selected values from a combobox on a form and passes the appropriate value into the sql criteria.
E.g., the sql criteria is set to : like fnCountry()
And the function fnCountry() is something like;
If SelectedCountry = "All" Then fnCountry = "*" else fnCountry = SelectedCountry end if
This works fine for a single selection (SelectedCountry = Africa) but doesn't work if I try to combine multiple selections into the criteria string.
E.g. SelectedCountry = Africa Or Italy
So the criteria would need to be Like "Africa" Or like "Italy"
How else can i build this criteria with multiple values?
I have a form where I am using two combo boxes the second combo box fields are being based on what is selected from the first comb box. When I enter the DLookup criteria into the text box I am trying to lookup based on the selection of the combo boxes it returns nothing. Here is what my DLookup looks like: DLookup("[BranchNumber]", "[tblAllADPCoCodes]", "[ADPCompany]= '" & [cboADPCompany] & "' And [LocationNumber]= '" & [cboLocationNo] & "'")
I can't seem to get past this expression/criteria problem.
[Actual] is my field I want to grab data from [L3-4-5] is my main table [CDATE] is a form control where the user enters a date [Quarter] is a number field and want it to equal 1 [Partname] is a text field and string it to equal 1
My current expression is:
=DLookUp("ACTUAL","L3-4-5","ID=" & [CDATE] And "[QUARTER]=1" And "[PARTNAME]='1'")
Although my other expression on another form works.In my if code statement I have
I'm currently trying to build in access a replica of an atrocious search function in excel.
I have a list of data quite simply in 5 columns and i want to filter through this data about (10000 rows).
My form has 5 data points.
The first is Product Name this is a string (i've looked up a lot of codes to search strings and even partial strings but no one seems to have done what i need).
- Basically i need it to search for any / multiple parts of the string entered. - for example if someone enters apple trees june i need it to look for cells containing those three words in any order, even conjoined for example "appletreejune" would still return or "apples on a tree in june". - This is attached to a single col called Product Name.
Based on this search i need it to look for data in a col called mark type (which is selected by a drop down)
Then by Market Context (also a drop down)
Then by a start and an end date, however, only one of the values (start or end) needs to be between the start and the end dates listed in the start and end date columns in the table.
I am working with MS Access 2003. I have a form (frmCalendar) with a textbox (tbDay) and a combobox (cmbLineName)
I want to count the number of records based on the criteria from cmbLineName, and show the result in tbDate. Inside the Control Source of tbDate I put in the following:
The problem is that the result in tbDate is always 0; regardless of what is chosen in cmbLineName. I know that the record count should not be 0 for all criteria.
I am trying to aggregate IIF functions to give me the total in separate columns (fields) according to the criteria applied however I am getting an error message "You tried to execute a query that does not include the specific expression
as part of an aggregate function, and I cannot find why, The query is as follows:
SELECT
Tbl_Advisor_raw.Month, Sum(Tbl_Advisor_raw.ValuePay) AS ValuePay, Sum(Tbl_Advisor_raw.Salary) AS Salary, Sum(Tbl_Advisor_raw.NetRevenue) AS NetRevenue, IIf(ValuePay>0,(ValuePay/Salary),0) AS pcSpend,
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.
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.
