Queries :: Like Operator Not Working
Jul 3, 2014
I am trying to create a query in VBA to extract data for a report.
I am using the "Like" operator for one of the variable fields.
It works correctly if you use the full value, however when using a wildcard it returns no records. Example below.
MOP - Works returning several records.
M* - returns no records.
I am getting very frustrated - I am sure its something simple.
I hope I have uploaded a screen dump of the SQL statement that is being produced.
View Replies
ADVERTISEMENT
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
Jan 19, 2015
Code:
Dim AppendUsers As String
AppendUsers = "INSERT INTO tblPermissions ( empid )" & _
"SELECT tblEmployee.empid" & _
"FROM tblEmployee LEFT JOIN tblPermissions ON tblEmployee.[empid] = tblPermissions.[empID]" & _
"WHERE (((tblPermissions.empID) Is Null) AND ((tblEmployee.active)=Yes) AND ((tblEmployee.inputname)=Yes));"
If MsgBox("This will search for new users with valid logins and add to the permissions table, do you wish to continue?", vbYesNo, "Append Users") = vbYes Then
DoCmd.RunSQL (AppendUsers)
View 4 Replies
View Related
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
Apr 24, 2013
I have the following query, which I simplied to show only the WHERE clouse.
Code:
WHERE ((([Using our own appt date].PrblDiagCode) Like "519" Or ([Using our own appt date].PrblDiagCode)="414" Or ([Using our own appt date].PrblDiagCode)="428" Or ([Using our own appt date].PrblDiagCode)="250" Or ([Using our own appt date].PrblDiagCode)="430" Or ([Using our own appt date].PrblDiagCode)="585" Or ([Using our own appt date].PrblDiagCode)="573"));
Is there any way to also include the ones with fractions? For example, a record can be 519.3. I find that the current code doesn't give me the ones with fractions. What needs to change in the query?
View 1 Replies
View Related
Jan 31, 2014
I'm using the following for a field in a query:
ITINERANT: IIf([Day and Time1] Is Not Null,[Day and Time1],IIf([Day and Time2] Is Not Null,[Day and Time2],IFF([Day and Time 3] Is Not Null,[Day and Time 3],IFF([Day and Time 4] Is Not Null,[Day and Time 4],IFF([Day and Time 5] Is Not Null,[Day and Time 5]," ")))))
I'm getting the error message: Syntax error (missing operator).
I'm not very good at using the IIF statement.
I have 5 concatenated fields that may or may not have info.
I want to display all of the info or if blank; show nothing in the one field.
Also, would I be able to have a return after each of the 5 concatenated fields?Not sure how to do that in this statement?
View 2 Replies
View Related
Jun 22, 2015
I'm trying to run about 300 update statements and keep getting a syntax error of missing operator... what could be wrong?
UPDATE Applicants set [NBCC ID] = '351174' where SSN = '136861387'
UPDATE Applicants set [NBCC ID] = '350960' where SSN = '138885740'
UPDATE Applicants set [NBCC ID] = '350817' where SSN = '140082154'
UPDATE Applicants set [NBCC ID] = '351013' where SSN = '145766662'
View 11 Replies
View Related
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
Jan 9, 2014
I tend to work with large amounts of data (extracts from company systems) and I create a lot of Access/VBA based tools to automate processes.
I have an annoying error which has always appeared but I don't understand the root cause of it.
When viewing a query, if I filter, I get an error message pop up (though after clicking through the error I can still use the filter function):
"syntax error missing operator in expression 'name of field'".
This seems to happen when I add several calculated fields. Here are some examples of the conditions and calculated field formulas I'm using in this current one:
Conditions:
<>"CINEMATIC" And <>"SFX"
Not Like "*_ZZ*" And Not Like "*test*" And Not Like "EP_*"
[Forms]![FRMscriptPrintReview]![selectLangCombo]
Calculated Field:
Audio Ref Guide: IIf(Left$(Right$([TBLdata]![Script Resource],2),1)<>"_","?",Right$([TBLdata]![Script Resource],2))
I get the impression that its more of a bug with Access as the formulas aren't complicated really but need confirmation on this and if there is a way I can avoid it.
View 2 Replies
View Related
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
Sep 24, 2013
I have a query which returns a calculated percent. I have ordered that in descending order, and now want to see the top 50. So (In Access 2010) I entered 50 into 'Return'. But it returns all of the records!
Is this because pct is a calculated field? How can I correct this? The SQL seems to be correct.
Code:
SELECT TOP 50 HeciFail1.POHECI, HeciFail1.POQTY, HeciFail1.FAILQTY, IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) AS PCT
FROM HeciFail1
ORDER BY IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) DESC;
View 1 Replies
View Related
Feb 14, 2008
Any reason why some of the queries do not display the info correctly? as in the many to 1 or 1 to many relationships etc.. its just blank lines. How would I fix it (if nessicary)
http://img152.imageshack.us/img152/2986/queryqr8.th.jpg (http://img152.imageshack.us/my.php?image=queryqr8.jpg)
Thanks.
View 2 Replies
View Related
Aug 13, 2005
I have two tables linked by a one-to-many - Demographic and Episodes. I have been running numerous queries with no problem until now - and I can't find any reason that these are not working.
I need to print up a bunch of different reports based on the criteria entered into the fields (yes/true; date ranges; is null or is not null, etc.) For some reason, records that are not supposed to come up (out of date range, or all records whether yes or no) are coming up.
Could it be some simple thing that I've obviously missed? The only difference with these new queries is that significantly more fields have criteria entered, instead of just one or two fields. Do the fields have to be put in some particular order from the design view? When it didn't work the first time, for example, a field that should be "Is Not Null", I ended up entering the "Is Not Null" on the second criteria line also - hoping that would make a difference....or in another field, "Yes" on the first line and 'True" on the second. In some queries, it made it better - but it still pulled up a few odd records, due to the date range being ignored, or another field criteria not working, etc.
I am grateful for any help I can get on this - I'm going insane trying to figure it out!!
Thanks,
Janis :-)
View 5 Replies
View Related
Sep 15, 2005
I have set up an Attendance database at work, where a record is created for each member of staff when they are absent. The tables are set up as follows:
[tblStaff]: StaffID, FirstName, Surname, Team, JobTitle
[tblAbsence]: StaffID, StartDate, EndDate, Issue
What I want to be able to do is set up a query to search for a reason for absence on a certain date (btw my date format is set at dd/mm/yyyy). The problem I have is if the user needs to check a date in between the start and end date i.e. Start Date is 11/11/2005 end date is 11/12/2005 and the user checks on 01/12/2005 then this record will be found.
I know there is a simple way to do this but I can't seem to figure it out. Any help would be greatly appreciated!
View 2 Replies
View Related
Aug 30, 2014
Can't figure out why dlookup will not work for me in a query for certain dates?
Simple example to highlight the problem:
Table1 -
Read_Date----Read_Value
31/07/14-------10
01/08/14-------20
03/08/14-------30
20/08/14-------40
Query based on this table with the following calculated field:
Expr1: DLookUp("[Read_value]","Table1","Read_Date=#" &[Read_Date]& "#")
Query output -
Read_Date-----Expr1
31/07/14--------10
01/08/14
03/08/14
20/08/14--------40
Will not return a value for 01/08/14 or 03/08/14 (dd/mm/yy)
If you try it with many dates its skips several and I cannot see a pattern.
View 7 Replies
View Related
May 21, 2013
I have an simple Access 2001 database with one table in which I want to search multiple fields for entries which match upto four keywords using subqueries.I have entered the following sql code:
SELECT Components.[Component-type], Components.Value
FROM (SELECT Components.[Component-type], Components.Value
FROM (SELECT Components.[Component-type], Components.Value
FROM (SELECT Components.[Component-type], Components.Value
FROM Components
[code]....
and then refuses to save it giving the error: "Invalid bracketing of name 'SELECT Components.[Component-type"If I remove the changes it will save ok. This is only a test query as I will want to add many more fields and it is my first use of sql code.
View 8 Replies
View Related
May 24, 2013
I am combining forms and queries to make my DB more user friendly. Everything was working on the forms until I tried the export button.
View 2 Replies
View Related
Dec 11, 2014
I know a fair bit about access 2010. I have the following table
------------------------------------------------------------------------------
- product no ----- Branch ID --- POS Description --last sold date --
------------------------------------------------------------------------------
- 123 ------- 1 ----- fly rod ---- 19/01/2014 ----
- 123 ------- 2 ----- fly rod ---- 19/03/2014 ----
- 123 ------- 3 ----- fly rod ---- 19/05/2014 ----
- 123 ------- 4 ----- fly rod ---- 19/06/2014 ----
- 123 ------- 5 ----- fly rod ---- 19/09/2014 ----
- 633 ------- 1 ----- fly reel ---- 11/01/2014 ----
- 633 ------- 2 ----- fly reel ---- 11/03/2014 ----
- 633 ------- 3 ----- fly reel ---- 11/05/2014 ----
- 633 ------- 4 ----- fly reel ---- 11/06/2014 ----
- 633 ------- 5 ----- fly reel ---- 11/09/2014 ----
------------------------------------------------------------------------------
I need it to give me back, one row per product (product no) - the newest date (Date Last Sold)
So i am trying to find out the last time each product sold, regardless of where
Which i can do with MAX date, group by product num
SELECT SMKPLUDetail.[Product No], Last(SMKPLUDetail.[Branch ID]) AS [LastOfBranch ID], Last(SMKPLUDetail.[POS Description]) AS [LastOfPOS Description], Max(SMKPLUDetail.[Date Last Sold]) AS [MaxOfDate Last Sold]
FROM SMKPLUDetail
GROUP BY SMKPLUDetail.[Product No];
------------------------------------------------------------------------------
- product no ----- Branch ID --- POS Description --last sold date --
------------------------------------------------------------------------------
- 123 ------- 5 ----- fly rod ---- 19/09/2014 ----
- 633 ------- 5 ----- fly reel ---- 11/09/2014 ----
------------------------------------------------------------------------------
This is great, but ofcourse (last of is fine for description) as is does not change
But last of, always returns branch 11.. and i infact want the branch which is correct, the branch which the date last sold is from, the current record really
but i do not seem to be able to get it to show me, any other fields from the querry current row.
View 3 Replies
View Related
Feb 2, 2014
This is my passing criteria to query field:
IIf([Forms]![FrmUserSelection]![PricingType]=3,1 Or 2,[Forms]![FrmUserSelection]![PricingType])
My field is Pricing Type and holds records 1 or 2..I would like to pass value from my group option from a form.It works fine if group option has a value 1 or 2 but it displays no records if option value is 3 What is wrong with my code.
View 4 Replies
View Related
Jun 26, 2014
I have this update query that is triggered by an after update event on a main form. The record being updated are in a continuous subform. It works well except from the last added/modified record. If I save and close the form and then open it again it works for all records but if modify or add a record, the update query will not work for that last modified/added record.
I have tried several things such as save record, use dirty = false for the on exit event of the subform control but nothing works. Here is the procedure:
Code:
Private Sub cboPoCurrency_AfterUpdate()
On Error GoTo ErrHandler
Dim db As Database
Dim strSql As String
Dim lngID As Long
Dim dblRate As Double
Set db = CurrentDb
[Code] ....
View 14 Replies
View Related
Nov 8, 2013
I recently (temporarily)took over a position that uses an Access database that does not work properly, and I'm stumpped on how to fix it.
The query is supposed to pull all data where the "Date Overdue" field is less than today.
"Date Overdue" is a calculated value that pulls from the field "Date Input", which is in a text format (DDMMMYY) Such as 03NOV13. It is 8 days after the date input.
It prints out like this: "Monday, November 11, 2013" which is 8 days after the 3rd.
"Date Overdue" is set to this value:
Code:
DATE OVERDUE: DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))+8
"Date Overdue" has the criteria "<DateValue(CDate(Now()))"
I'm not going to go into all the different steps I've taken to try and get this to work because I've toyed with it a lot..
The output that I always seem to get is a mixture of all records that are available, before and after today's date, I just wanted those that are less than today.
I suspect that the date values that are shown in the query aren't true dates because when I click on the filter button it gives me this error:
"Syntax error (missing operator) in query expression 'DATE OVERDUE' "
NOTE: I'd like to add that this is just a regular Select query.
Code:
SELECT DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))
AS [PRODUCT END PERIOD], DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))+8
AS [DATE OVERDUE], [QBR ON EQUIP].DATEINPUT, [ALL ERRORS].[ERROR STATUS],
[Code] .....
View 1 Replies
View Related
Jun 2, 2013
I'm doing a subquery to select the top 5 of products for each supplier. The selection needs to be done randomly on the products for each supplier. For this I have made the following query (based on Allen Browne's example):
SELECT tblProducts_temp.SupplierID, tblProducts_temp.GTIN
FROM tblProducts_temp
GROUP BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN
HAVING (((tblProducts_temp.GTIN) In (SELECT TOP 5 Dupe.GTIN
FROM tblProducts_temp AS Dupe
WHERE Dupe.SupplierID = tblProducts_temp.SupplierID
ORDER BY RND(Dupe.GTIN) DESC)))
ORDER BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN;
The query returns a random number of products, but not a top 5. So for supplier X one time 3 products and the next time for supplier X 7 products.The query without the RND function, so just the top 5 works fine:
SELECT tblProducts_temp.SupplierID, tblProducts_temp.GTIN
FROM tblProducts_temp
GROUP BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN
HAVING (((tblProducts_temp.GTIN) In (SELECT TOP 5 Dupe.GTIN
FROM tblProducts_temp AS Dupe
WHERE Dupe.SupplierID = tblProducts_temp.SupplierID
ORDER BY Dupe.GTIN DESC)))
ORDER BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN;
View 2 Replies
View Related
Apr 17, 2014
I have a table like this
Cultureworkload
ReasonfortestingIRLNumberDiagnostic sputum specimens processedFollow up specimens processedReception
Follow up7/20130102-Jan-13
Diagnosis8/20131002-Jan-13
Follow up9/20131002-Jan-13
Diagnosis10/20131002-feb-13
Follow up11/20131003-feb-13
Diagnosis14/20131003-Mar-13
Diagnosis15/20131003-Apr-13
I built a query with the wizard to group by Reason for testing
The SQl was
TRANSFORM Count(Cultureworkload.IRLNumber) AS CountOfIRLNumber
SELECT Cultureworkload.Reasonfortesting, Count(Cultureworkload.IRLNumber) AS [Total Of IRLNumber]
FROM Cultureworkload
GROUP BY Cultureworkload.Reasonfortesting
PIVOT Format([Reception],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");
Although I filtered reception field by month to 1st quarter it shows empty months like Apr,May Jun etc.
View 3 Replies
View Related
Sep 10, 2013
I have a DCount Ranking query that worked fine last week and with this week's data came up with a glitch. Here is my DCount statement in the query:
Rank: CInt(DCount("*","qry_YTD_Territory_Totals","[Total]>=" & [Total]))
Here is my result which you can clearly see is not a tie.
Territory Total Rank
A 540.86 1
B 398.85 1
C 341.75 3
[code]...
View 1 Replies
View Related
Apr 22, 2013
I have a query where I use the search criteria of =Date()+1.
However I really need this to add on a working day (i.e. excluding weekends).
Is there an easy way to do this?
View 1 Replies
View Related
Apr 3, 2013
We are trying to find a way to have a query figure out when the hours of an employee reach 40 hours (Overtime) and figure the hours correctly. The Data Entry table contains the basic fields:
[Workdate].....The date the work was done
[WEDate].......The Week Ending Date. Always on Sunday, and calculated like this "[WorkDate]-Weekday([WorkDate]-1)+7"
[Employee].....Employee Name
[Qty].............# of hours worked that day
[RegHours].....Blank, to be updated by query
[OTHours]......Blank, to be updated by query
The problem we are running into is getting it to update the daily running totals (using the [WEDate] & [employee] as the "Group Bys"). We'd like use an update query to fill in the to blank fields with the correct calculations.
Example - Working 6 day week, at 9 Hours per day in the [Qty] field
Day.............[RegHours]......[OTHours]
Monday..............9..................0
Tuesday............18..................0
Wednesday........27..................0
Thursday...........36..................0
Friday...............40..................5
Saturday...........40.................14
Sunday.............40.................23
We have tried several things to get it to compute this field and haven't had much luck.
View 1 Replies
View Related
