Queries :: Like Operator - Include Fractions In Result
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?
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
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.
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 am trying to develop a customized program in MS Access, but one of the input parameters needs to be a decimal or fraction (ie: I need to enter values that are not whole number integers). Unfortunately, it seems that MS Access won't enable me to input values that are not whole number integers. I had this same difficulty in the past, and in that case, it turned out that the non-integer value was not used. But in the current program I'm trying to develop, I definitely need to get the non-integer data inputting to work, since the parameter that I'm entering these values for is "percentage". It turns out that if I enter a value from 0.51 to 1, it rounds up to 100%, and if I enter a value that's 0.5 or lower, it rounds down to 0%. There is no in-between. I tried looking into the various settings in DESIGN mode, but nothing seems to correct this problem. Is there anyone out there reading this that has insight into my problem? Could my version of MS Access have a software corruption? Or is there just a setting that I've overlooked? I appreciate any advice that can be offered. Thanks for your help.
I have a table with a yes/no field. It's linked to a form with a yes/no box, but the outcome I want is that if the form box is unchecked, the query returns all the "yes" records - which is trivial; however I want to return ALL the records if the form box is checked.
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.
Each month I use Access to import text files and export cleansed data to Excel.
The information contains (i.e. forecast data) that has 6 months of data. Each month, the data is rolled over to include the new month and delete the oldest month of data.
Is there a query(s) or module that can automate this process?
I have a table with a string field for a street. It might have data such as:
Bleeker Street or Bleeker St.
I want to create a query that will enable the user to search for a string by entering a parameter. Is there a way to construct a query using wildcards for a string so that if the user types in "Bleeker" both the above values will be returned?
if its possible to have a button that after clicking it, it doesn't show certain records. Reason being I have a "shows" for each month on our personal site and amazon, and we've been selling on those for about 2 years, and I have a "show" for each month. But, that quickly clutters up the list box. Any way to create a button the (after typing in the name) it doesnt show only those record(s)?
How can I include a field from a table in to a query - that has no relationship?
For example...
The 'Despatch' field is in a seperate table called 'Product', because we despatch multiple products for any ONE order at different times, so it needs to be against the Product and not the full job itself.However, when I include the 'Product' table into the query and insert the 'Despatch' field into my query, and run it comes up with an error...
"The SQL Statement could not be executed because it contains ambiguouse outer joins. To force one of the joins to be performed first, create a seperate query that performs the first join and then iclude that query in your SQL statement". Here's the current SQL statement
Code: SELECT [Materials Requested].[Job No], [Materials/Orders].Total, Jobs.[Project Name], Product.Despatch FROM Product, Orders INNER JOIN (((Customer RIGHT JOIN Jobs ON Customer.[Customer ID] = Jobs.[Customer ID]) INNER JOIN [Materials Requested] ON Jobs.[Job No] = [Materials Requested].[Job No]) INNER JOIN [Materials/Orders] ON [Materials Requested].[Materials Requested ID] = [Materials/Orders].[Materials Requested ID]) ON Orders.[Orders ID] = [Materials/Orders].[Orders ID];
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 am using Between Me.Firstdate and Me.Lastdate in a query. I thought that it would include both Firstdate and Lastdate in the range. But it appears that it does not. Is there a way to include Firstdate and Lastdate in the range of dates?
I haven't created classes in a while, and I don't see what is the problem
I'm modifying a function to get fractions from double. It worked well, but it returned a string.
I need it to be in parts, so I created this class
Code: Option Compare Database Public Entier As Integer Public nominateur As Integer Public denominateur As Integer Public Function getText() getText = Entier & " " & nominateur & "/" & denominateur End Function
I changed the function type and the assignation:
Code: Function GetFraction(ByVal Num As Double) As nombreEnFraction Set GetFraction = New nombreEnFraction If Num = 0# Then GetFraction = "None" Else Dim WholeNumber As Integer Dim DecimalNumber As Double
[Code] ....
For some reasons, when I get to the end of the function, I get a 91 error, like if it was nothing
But when I use a spy, I see values in the object until the end.
I am setting up a database to hold staff details, and would like a query to show each member of staff's total hours and FTE.
Staff name etc is in tblStaff Staff shift details are in tblShifts linked via staffID
tblShifts will contain details of the shift worked on each day of the week, but the majority of our staff work a standard shift - e.g 8-4, 9-5. Therefore what I wanted to do was in tblStaff set a field named shiftPattern to 1 2 3 or 4. 1 indicating a custom shift, and any other number indicating a set shift defined in a separate table.
The problem I have is that my query only returns people who have details in tblShifts - regardless of their shiftPattern value. If I enter a blank record in tblShifts it will do the above as intended.
I have written a user defined function that calculates the end of the current month. This I named EndOfThisMonth. It works well as a function. Now I would like to use it as date criteria to include in a query. The function is included as such EndOfThisMonth().
The field on which this function is to enter as a criteria is another calculated date function called Due.
When I run this query I get an error message saying Undefined Function 'EndOfThisMonth' in expression.
i I have two queries.. What i'm hoping is to combine the result into one query but not in one column only but instead the result of the second query should be beside the first query.. The result of the second query should be added as a new column.
First Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailReceived FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName;
Second Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailProcessed FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName, tbl_rEceived_eMail.ProcessedYN HAVING (((tbl_rEceived_eMail.ProcessedYN)="Y"));
