Passing Operator Into Query Via Function

Jan 16, 2006

I have built a function which passes values into the criteria of a saved query. It has worked very well until I recently built a query builder based on this same concept. The user can select from a list of operators "<=", ">=", ">", "<", "=".

Example, the function will pass into the criteria the following:
<150

Unfortunately, I have exhausted all avenues for passing the "<" less than or greater than signs into the criteria. The Access SQL forces an equals sign before the passed in criteria and thus treats the "<150" as the entire criteria.

I have overcome no criteria by defaulting the function value to pass in the wildcard "*" and putting "Like" in the criteria.

Has anyone every handled a "variable" operator in a saved query in Access? :confused:

I am aware that I can change the QueryDef, use temp tables, and do several other longer methods, but the success of this concept will reduce an large amount of coding effort as well as make the software much easier to transfer knowledge on.

This overall method is really a great way to reuse criteria based on a form without hard coding.

Thank you in advance for your help,

Dave

View Replies


ADVERTISEMENT

Forms :: Passing Operator Values To Criteria Field In Query

Mar 11, 2014

I have a query which contains figures. i have a search form based on this query and need to search using comparison operators such as <10000 or >500.

Is there a way to pass these value to criteria field in query ?

View 14 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

Forms :: Passing Text Box Data To A Function Via A Query

Jan 28, 2014

I have a function in a module that looks like this (it takes 2 dates as arguments):

Code:
Function get_KPIScanAgeRange(in_ScanDate As Date, KPIDate As Date) As String
Dim ret As String
ret = "Invalid"
' return value, by default is because age is negative number

[Code] .....

I'm trying to pass the date in a text box which exists in a form called "d3FormAging". The text box name is "KPIDate". The query looks like this:

Code:
SELECT Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate]) AS KPIScanAgeRange, Count(Query_d3_Open.[Scan date]) AS Scans
FROM Query_d3_Open
GROUP BY Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate])
ORDER BY Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate]);

For some reason Access doesn't recognize the "[Forms]![d3FormAging]![KPIDate]" when I pass it to the function. I get run-time error 3070 with information that Access can't recognize this expression even though the code should be correct as it's made with the expression builder and I'm sure the form that contains the text box is open when the query runs.

View 2 Replies View Related

Passing Each Record To A Function

Aug 1, 2005

I have written a simple module that sorts and compares two strings. Next I am trying to create an event procedure for a button that will send all the values from three selected colums to that function for comparison.


Below is function "Check" from the module

' sImp = importData
' sAns = answerData
' iOpt = option

Function Check(sImp As String, sAns As String, iOpt As Integer) As Integer

Dim lImp As Long 'Imported values (unsorted)
Dim lAns As Long 'Entered values (unsorted)
Dim lAcnt As Long
Dim lCcnt As Long
Dim sAnswer As String 'Correct
Dim iMini As Integer
Dim sImpSort As String
Dim sAnsSort As String


lImp = Len(sImp)
lAns = Len(sAns)

sImpSort = OrderString(sImp)
sAnsSort = OrderString(sAns)

For lAcnt = 1 To lImp
sAnswer = Mid(sImpSort, lAcnt, 1)

' option
Select Case iOpt
' all
Case 1
Check = 1
Exit Function

' or
Case 2
For lCcnt = 1 To lAns
If Mid(sAnsSort, lCcnt, 1) = sAnswer Then
Check = 1
Exit Function
End If
Next

' and
Case 3
If sImpSort = sAnsSort Then
Check = 1
Exit Function
End If
Case Else
End Select

Next

End Function



What would be the best way to send these values to the function? loop through a recordset?
(Note: Not sure how many records the DB will eventually store. Lets just say 10,000 for now)

Any suggestions would be greatly appreciated..

Thanks

salmonman

View 1 Replies View Related

Subforms - Passing Name To A Function

Feb 24, 2005

Hey long time browser, first time poster!

HELP!!!

Ok, here is the problem that I can't seem to find any posts on here talking about. I have a form, let's call it FormA and a subform (subformA). I am using a non-activeX calendar control, which I did not write. I was informed it could be used on subforms but its not working. In reviewing the code it is looking for the activeform name to be passed to it. This works when the calendar function is called in the FormA, but when it is called in SubformA, it can identify the field that I want the date selected to be entered into, but it does not recognize the subform as being the active form.

The code it's using to determine activeform name is:
Screen.activeform.name

From my research I have found that when the subform has the focus, the activeform.name returns the name of the parent form.

The question: How can I determine the subform name and pass it as a variable to the calendar program?

Mark

View 1 Replies View Related

Modules & VBA :: Calling A Function And Passing Variables

May 29, 2015

I have never tried passing variables while calling a function so I don't know what the heck I'm doing. I'll give a simplified example of what I'm trying to do. The second variable vRank is reporting properly but the first one vID gets "stuck" on whatever the first item in the listbox is.

Code:
Dim vrt As Variant
Dim upSQL As String
For vrt = 0 To Me.List1.ListCount - 1
If Me.List1.Selected(vrt) = True Then
Call ChangeUp(Me.List1.Column(0, vrt), Me.List1.Column(1, vrt))

[Code] ....

View 3 Replies View Related

Query Not Working With &lt;&gt; Operator

Sep 4, 2007

Hi All

Can't understand why the following doesn't work when changing the operator.

Using the "<>"
SELECT qryColleagues.WCN, qryColleagues.ClgName, qryColleagues.Shift FROM qryColleagues, tblTempNew WHERE (((qryColleagues.WCN)<>[tblTempNew]![WCN])) GROUP BY qryColleagues.WCN, qryColleagues.ClgName, qryColleagues.Shift ORDER BY qryColleagues.ClgName;


the particaular part is (((qryColleagues.WCN)<>[tblTempNew]![WCN]))

it works if the operator is "=" but not "<>"

The thing is I'm sure this was working fine.

edit: I have just noticed that this works if there is only one record in the tblTempNew table. As soon as I put 2 records in there it does not work.

Any ideas.

Kev

View 7 Replies View Related

Having Trouble With OR Operator In Query

Nov 11, 2007

Hello everybody,

I am having trouble with this criteria on one field in a query

<>19 Or <>15 Or <>14 Or <>16 Or <>17 Or <>1 Or <>3

The query is still showing records that contain 14, 3 and 16.
Basically, I don't want to show records containing 19, 15, 14, 16, 17, 1 and 3.

Thanks

View 3 Replies View Related

Query Not Picking Up The &lt;&gt; Operator

Jul 3, 2006

I have two tables in the first table there is an outline number and the second table there is a task id (both are text strings, i.e. outline number and task id would be something like 8.15.3.1)

I want a query to return all the results in the first table that are not equal to the second table

I have tried the following but it returns all 54 records
Code:SELECT DISTINCT RawData.TeamLeadNumber, RawData.Category, RawData.OutlineNumber, RawData.OutlineDescription, RawData.Start, RawData.Finish, RawData.Milestone, RawData.PercentComplete, RawData.DeliverableDesc, RawData.ReleasePeriod, RawData.OutlineNumber2, RawData.OutlineDescription2FROM RawData, SharePointDataWHERE (((RawData.OutlineNumber)<>SharePointData.TaskID));

I have tried changing the where clause to have it as
Code:WHERE ((RawData.OutlineNumber)<SharePointData.TaskID) AND ((RawData.OutlineNumber)>SharePointData.TaskID);

but then no data is returned, and I know that of the 54 records 21 of them are the same so I should be seeing 33 records

View 1 Replies View Related

Missing Operator In Query

May 30, 2007

Hi everyone,

I have a problem with a query.
Just for info, I export some table from SQL to access. The same query in SQL work fine but when I try in Access come out this error:

------------------------------------------------------------------
Syntax error (missing operator) in query expression 'tbl_Style.FABRICID = tbl_Info.Infoid INNER JOIN tbl_Info AS tbl_Info_1 ON tbl_Info.Parent = tbl_Info_1.Infoid'.
------------------------------------------------------------------

The query:

SELECT tbl_Style.STYLECODE, tbl_Info_1.Infodata AS [Fabric Type]

FROM tbl_Style INNER JOIN

tbl_Info ON tbl_Style.FABRICID = tbl_Info.Infoid INNER JOIN

tbl_Info AS tbl_Info_1 ON tbl_Info.Parent = tbl_Info_1.Infoid


I appreciate your help.

Regards,
Tombino

View 3 Replies View Related

Query Problem (missing Operator)

May 29, 2007

Hi everyone,

I have a problem with a query.
Just for info, I export some table from SQL to access. The same quary in SQL work fine but when I try in Access come out this error:

------------------------------------------------------------------
Syntax error (missing operator) in query expression 'tbl_Style.FABRICID = tbl_Info.Infoid INNER JOIN tbl_Info AS tbl_Info_1 ON tbl_Info.Parent = tbl_Info_1.Infoid'.
------------------------------------------------------------------

The query:

SELECT tbl_Style.STYLECODE, tbl_Info_1.Infodata AS [Fabric Type]

FROM tbl_Style INNER JOIN

tbl_Info ON tbl_Style.FABRICID = tbl_Info.Infoid INNER JOIN

tbl_Info AS tbl_Info_1 ON tbl_Info.Parent = tbl_Info_1.Infoid


I appreciate your help.

Regards,
Tombino

View 2 Replies View Related

Like Operator In Calculated Query Field

Aug 1, 2007

Good afternoon,

I have a field that contains semester info ie: F07 Sep.

What I am trying to do is using an if statement to test for which month and returning a section number based on the month. I can make it work using an exact match but am trying to make it work on the partial match "Sep" so I don't have to change it next year as F07 stands for Fall 2007

This is what I've tried and various combinations.
Section1: IIf([tblOnlineCoursesDetails]![Semester]="* Sep","70","")

Having trouble getting this to work.

Any help or suggestions would be appreciated.

Thx

Jerry

View 1 Replies View Related

Queries :: Using IN Operator With IIF In A Query Criteria

Jul 21, 2013

I am trying to select specific records based on input in a query. This criteria currently works to select everything with an ID less than 21 or 31 based on the users entry: <IIf([Enter "Y" for List 1]="Y",21,31)

I now need to change the query so that only certain records are selected based on the users entry. As an example, I would like to select 1, 3, 5 and 7 if they enter Y. And I would like to select 2, 4, 6 and 8 if they dont enter a Y.Here is what I have tried:

In IIf([Enter "Y" for List 1]="Y",(3,5,7,9),(2,4,6,8)).With this criteria, I get this error .The In operator you entered requires parentheses.

View 5 Replies View Related

Modules & VBA :: Missing Operator On A Delete Query

Dec 2, 2013

I'm trying to find a solution for this without success..I have this code:

Code:
CurrentDb.Execute "DELETE FROM Type WHERE Project_ID =" & Me.Project_ID & " & AND (Type = '" & Me.Txt_Type & "')"

and I'm getting this error message:Syntax error(missing operator) in query expression 'Project_ID = AND (Type = 'Webinar')'

View 4 Replies View Related

Syntax Error (missing Operator) In Query Expression

Oct 11, 2007

Morning all
The problem whihc i have is that, when trying to open the form "Create Timetable", through the switch board.... a message box appears saying "Syntax error (missing opertor) in query exprssion"
Any ideas on why it is showing this message box???
Help would be appricated
Thank you

View 3 Replies View Related

Syntax Error (missing Operator) In Query Expression

Aug 27, 2006

I have an asp page which modifies articles in my access database.
I added a field in the db called "Blocco" which is a checkbox field.

The code (working) which the page used before was (I'm copying just the part that modified will cause the problem):

Code:SQLModifica = " UPDATE [Articoli] SET Articoli.Sezione = '"& FSezione &"', Articoli.Autore = '"& FAutore &"', Articoli.Titolo = '"& FTitolo &"', Articoli.Podcast = '"& FPodcast &"', Articoli.tags = '"& Ftags &"', Articoli.Data = '"& FData &"', Articoli.Ora = '"& FOra &"', Articoli.Letture = "& FLetture &", " If FBozza = "si" ThenSQLModifica = SQLModifica & "Articoli.Bozza = True "ElseSQLModifica = SQLModifica & "Articoli.Bozza = False "End IfSQLModifica = SQLModifica & "WHERE Articoli.ID = "& FID &" "If Session("BLOGAdmin") = False ThenSQLModifica = SQLModifica & "AND Articoli.Autore = '"& Session("BLOGNick") &"' "End IfSet RSModifica = Server.CreateObject("ADODB.Recordset")RSModifica.Open SQLModifica, Conn, 1, 3Set RSModifica = Nothing

After adding a radio type field in the form called Blocco (with yes or no values, working like the existing field "Bozza") I've modified the code adding something to update the db:

Code:SQLModifica = " UPDATE [Articoli] SET Articoli.Sezione = '"& FSezione &"', Articoli.Autore = '"& FAutore &"', Articoli.Titolo = '"& FTitolo &"', Articoli.Podcast = '"& FPodcast &"', Articoli.tags = '"& Ftags &"', Articoli.Data = '"& FData &"', Articoli.Ora = '"& FOra &"', Articoli.Letture = "& FLetture &", "If FBlocco = "si" ThenSQLModifica = SQLModifica & "Articoli.Blocco = True "ElseSQLModifica = SQLModifica & "Articoli.Blocco = False "End ifIf FBozza = "si" ThenSQLModifica = SQLModifica & "Articoli.Bozza = True "ElseSQLModifica = SQLModifica & "Articoli.Bozza = False "End ifSQLModifica = SQLModifica & "WHERE Articoli.ID = "& FID &" "If Session("BLOGAdmin") = False ThenSQLModifica = SQLModifica & "AND Articoli.Autore = '"& Session("BLOGNick") &"' "End IfSet RSModifica = Server.CreateObject("ADODB.Recordset")RSModifica.Open SQLModifica, Conn, 1, 3Set RSModifica = Nothing

The page itself is loaded with no errors but when the form takes action I get this:
Syntax error (missing operator) in query expression 'True Articoli.Bozza = False'.

why? I've just used the same code that worked with the field "Bozza" (which is a checkbox field too)

Sorry for my english

View 1 Replies View Related

Error Message '3075' Missing Operator In Query Expression

Dec 3, 2007

Sub PickRandom()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strTableName As String

' 1: Create a new temporary table containing the required fields
strSQL = "SELECT tblWoodrow Odom.ReviewTopic,tblWoodrow Odom.TeamMember1, tblWoodrow Odom.TeamMember2, tblWoodrow Odom.TeamMember3, tblWoodrow Odom.TeamMember 4" & _
"INTO tblTemp " & _
"FROM tblWoodrow Odom;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL (HERE I RECEIVE AN ERROR MESSAGE. WHAT I NEED TO DO HERE SO IT CAN RUN PROPERLY? ) DO I NEED TO DO A QUERY STRQSQL STATEMENT?)
DoCmd.SetWarnings True

' 2: Add a new field to the new table
Set db = CurrentDb()
Set tdf = db.TableDefs("tblTemp")
Set fld = tdf.CreateField("RandomNumber", dbSingle)
tdf.Fields.Append fld

' 3: Place a random number in the new field for each record
Set rst = db.OpenRecordset("tblTemp", dbOpenTable)
rst.MoveFirst
Do
Randomize
rst.Edit
rst![RandomNumber] = Rnd()
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing

' 4: Sort the data by the random number and move the top 25 into a new table
strTableName = "tblRandom_" & Format(Date, "ddmmmyyyy")
strSQL = "SELECT TOP 25 tblWoodrow Odom.ReviewTopic,tblWoodrow Odom.TeamMember1, tblWoodrow Odom.TeamMember2, tblWoodrow Odom.TeamMember3, tblWoodrow Odom.TeamMember 4" & _
"INTO " & strTableName & " " & _
"FROM tblTemp " & _
"ORDER BY tblTemp.RandomNumber;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

' 5: Delete the temporary table
db.TableDefs.Delete ("tblTemp")
End Sub

View 2 Replies View Related

Forms :: Syntax Error (Missing Operator) In Query Expression

Oct 24, 2013

Why am I receiving 'Syntax error (missing operator) in query expression' error message ? Below is the code.

stFrmName = "Previous Plan"
stLinkCriteria = "(Employee = " & strPreviousTeacher & ") And (Record_Date = " & strPreviousDate & ")"
DoCmd.OpenForm stFrmName, , , stLinkCriteria

View 2 Replies View Related

General :: Syntax Error Missing Operator In Query Expression

Jul 27, 2012

I've come across a simple error that has me baffled. I continue to receive the run time error 3075: Syntax error (missing operator) in query expression 'tblMasterPersonnel.FirstName"042" Or (tblMasterPersonnel.EmpID)="044"))'

Here is the strSQL

Code:
strSQL = " SELECT tblMasterPersonnel.EmpID, qryiuSSN.SSN, ""SSN"" AS [ID Type], """" AS TXN, tblMasterPersonnel.LastName, " & _
"tblMasterPersonnel.FirstName, tblMasterPersonnel.MI, """" AS Suffix, IIf([tblMasterPersonnel].[Gender]=True,""F"",""M"") AS Gender, " & _
"tblMasterPersonnel.Birthday, tblMasterPersonnel.PlaceBirth, """" AS [Country Code], """" AS Country, tblAddresses.Phone1, " & _
"tblAddresses.Street, tblAddresses.City, tblAddresses.State, tblAddresses.Zip, tblAddresses.Country, " & _

[code]....

View 4 Replies View Related

Queries :: Count Distinct Error - Missing Operator In Query Expression

Jan 23, 2015

I am trying to get a count of the unique customers in an access 2010 database

After some research it seems i should be using

SELECT Count(Distinct [Customer]) FROM [tblMain];

But when i use this i get

Syntax error (missing operator) in query expression 'Count(Distinct Customer)'.

I have tried leaving out the square brackets but this does not work....

View 3 Replies View Related

Queries :: Average IF Statement-syntax Error (missing Operator) In Query Expression

Jun 5, 2014

I am trying to get Average If function to access sql. I have columns Period and Costs_Per_Capita, result should be like like this:

Costs_Per_Capita Period CALCULATED_Period_Avg_Costs
15,505 1 15976.27582
16,368 1 15976.27582
16,037 1 15976.27582
15,995 1 15976.27582
15,000 2 16000
17,000 2 16000

I tried:

SELECT Costs.Costs_Per_Capita, Costs.Period
IIF (Period = 1, (Select AVG(Costs_Per_Capita) From Costs Where Period = 1),
(Select AVG(Costs_Per_Capita) From Costs Where Period = 2)
AS result
FROM Costs;

But get "syntax error (missing operator) in a query expression ..."

View 8 Replies View Related

Forms :: Sorting Fields - Syntax Error (Missing Operator) In Query Expression

Sep 16, 2013

I have created a form based on a query. The funny thing is when I tried to sort fields on the form, the following message box pops up:

Syntax Error (Missing Operator) In (Field Name)

I just did the exact same thing several weeks ago, and that first form could sort fields normally. The only difference between the first one and this is that this second query was based on several tables, while the first was based on a single table, although I doubt that is where the problem is.

I forgot to add that I could sort fields where the field name doesn't have spaces in it. For example, the field name "Customer ID" triggers the syntax error, while the field "S/N", "Company", etc. can be sorted like normal.

View 7 Replies View Related

Modules & VBA :: Creating A Function That Counts Records And Use That Function In A Query

Dec 11, 2013

So basically I need making a function that will count the number of records from another table/query based on a field from the current query.

View 2 Replies View Related

Passing Criteria To A Query

Dec 6, 2005

Hi

I have recently started using queries to base my forms on. Even for basic forms i am using the query as the record source.

If for example i have a standard query with no criteria e.g. a employee form. How would i then use that query to create a report that filters for example a list of employees for a certain company?

Would i have to create a seperate query to base the report on? because the form when opened will always filter that criteria?

If this is the case, is there a way to keep a query set up with no criteria but when the object being used is opened some code will pass to the query the criteria e.g. a combo box on a form list all the companies and when one is selected it passes this to the query e.g. without the criteria already being in the query?

Hope that makes sense.

thanks

scott

View 1 Replies View Related

Passing Parameters To A Query

Feb 1, 2006

Guys I need your help/Advice...

In my Access Database I have a query (lets say qry1) and in this query i have 2 fields for start and end date, which is provided by 2 Get functions.

also i have qry2 based on qry1
then qry3 based on qry2
and finally qry4(using sql in code) based on qry3, and non of these 3 queries have the start and end date fields.

now here is the problem: I am trying to set the criteria in qry4 and then open a record set on this query(qry4) to use the data that it pulls out...


strsql = ""
strsql = "SELECT Sum AS AREA_TOTAL " _
& "FROM qry3 " _
& "WHERE ENERGY_AREA like '" & Area & "';"

Set MyDB = CurrentDb

Set rst = MyDB.OpenRecordset(strsql)



but when the last line is executed I get this error message:

Runtime Error '3061':
Too few parameters, expected 2.

i also tried doing this:

strsql = ""
strsql = "SELECT Sum AS AREA_TOTAL " _
& "FROM qry3 " _
& "WHERE ENERGY_AREA like '" & Area & "';"

Set MyDB = CurrentDb


MyDB.QueryDefs("qry4").sql = strsql

Set rst = MyDB.OpenRecordset("qry4")


but when the last line is executed it gives me an error message saying that the query does not exist or the name is not spelled correctly. (Ps. I have created the query and the criteria does update once the Select statement is run in the code!)

again the reason for this is that the query has not been populated as the main query (qry1) needs 'strat date' and 'end date'!

Is there anyway I can pass these 2 parameters to qry4 directly using code? If there is a way then this will definitly work as i tried opening the query manually in the Query window and after I input the 2 dates in the input box the query ran successfully!

I would appreciate any help/suggestion guys, I need to sort this out quickly as i have a deadline... Cheers

View 1 Replies View Related







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