Criteria Is Passed By Function Call

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 Replies


ADVERTISEMENT

Modules & VBA :: Static Shell Function Call Works But Dynamic Call Fails

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

Forms :: Field Not Being Passed To Query As Criteria?

Apr 18, 2013

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.

View 4 Replies View Related

How Do I Call This Function?

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

Function Call From Qry - What If Null???

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

Call A Function In Excel

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

Using A List Box To Call VBA Function Or Macro

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

Call A Function From A Field On A Form

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

Forms :: How To Call A Function From A Variable

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

How To Call A Written Function That Are In DLL File In A Form?

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

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]![lstChosenFunction](tblTest.price, 5) 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 4 Replies View Related

Modules & VBA :: Possible To Call Windows Search Function Into Access?

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

Modules & VBA :: Call A Preimport Function Whenever Transfer-spreadsheet Is Called?

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

Modules & VBA :: Using Shell Function To Dynamically Call Different Pdfs That Are In A Directory?

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

Criteria Entry To Call Combo Box

Nov 7, 2005

Hi can anyone help please.

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.

HELP!

View 2 Replies View Related

Multiple "Where" Criteria For Call Arrivals

Mar 24, 2006

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.

View 1 Replies View Related

Help With IIf Function In Criteria

Aug 11, 2006

I have the following IIF function in the criteria of a date field.

IIf(Weekday(Now())=6,Between Now()-2 And Now()-10)

I want to return dates between 2 and 10 days ago, if today is Friday, but this doesnt return any values at all.

Is the statement above correct ?

TIA.

Mark

View 3 Replies View Related

IIF Function As Query Criteria

Nov 14, 2007

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: -

=IIf([Forms]![LevyReportDialog]![DialogCombo]="SAL","True","*")

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.

View 9 Replies View Related

Queries :: Using A Function To Set SQL Criteria

Feb 5, 2014

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?

View 3 Replies View Related

DLookup Function Using Two Combo Boxes As Criteria

Oct 10, 2007

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] & "'")

View 1 Replies View Related

Forms :: DLookup Multiple Criteria Function

Jan 31, 2015

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

Me.DAYS_TRAINED.Value = DLookup("Days", "TRAINED LH A-PILLAR", "ID=" & FTM_NAME)

So I think I don't understand the multiple criteria part.

View 3 Replies View Related

Forms :: Search Function Using Multiple Criteria?

Oct 8, 2013

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.

View 4 Replies View Related

Forms :: DCount Function With Combobox Criteria

Oct 7, 2014

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:

=DCount("[EncounterID]","tblEncounters","[LineName] = ' " & "Forms![frmCalendar]![cmbLineName] = " ' ")

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.

View 4 Replies View Related

Query Criteria Function And Operator List?

Jun 3, 2012

Is there someway within Access itself or the documentation to see a complete list of:

Functions such as sum, median, average

Operators such as "and" "or"

View 2 Replies View Related

Queries :: Aggregate IIF Function To Get Total According To Criteria In Access

Mar 18, 2013

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

'IIF(Tbl_SKG.Value_Grp = 1,sum(Tbl_Advisor_raw.ValuePay),0)'

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,

[Code] .....

View 9 Replies View Related

Queries :: Query Criteria To Pull Its Value From Control On A Form - IIF Function

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







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