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.
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)
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.
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?
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?
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]
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.
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 ..."
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;
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)
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!!
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:
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!
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.
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.
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.
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
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],
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;
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.
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
