Currently, I am using the following code to add criterias to a Query before I run it:
Code:
qdf.SQL = "SELECT * from [Offshore_Projects] WHERE [Business Field].Value = " & Tall
Unfortunately, I struggle with adding additional criterias for example something like this:
Code:
qdf.SQL = "SELECT * from [Offshore_Projects] WHERE [Business Field].Value = 3" AND [Offshore_Projects] WHERE [Location] = ""Longyearbyen"""
i have a query name "Query01". I want to add a criteria ">=#1/5/2015#" in the field of "PaymentDate" using VBA.
View 1 Replies View RelatedI have one datatable and i have to list the duplicates from it based on different criterias. It contains invoices, we would like to avoid duplicated payments.I'm struggling with a query. Basically i have a working one, i have to add one more criteria to filter out a couple of rows where the text column is "freight", but anyhow i have tried, it's not working. Without that one line where the not equal is, it's working as it should, when i'm adding that line it ruturns the same as before, but it should give me 108 less lines. The below one is based on an other query result called Duplicates_Rule1.Here is the query:
Code:
SELECT * FROM Duplicates_Rule1
WHERE (((Duplicates_Rule1.BSEG_DocumentNo) In (SELECT [BSEG_DocumentNo] FROM [Duplicates_Rule1] As Tmp
GROUP BY [BSEG_DocumentNo]
HAVING Count(*)=1 ))
AND (Duplicates_Rule1.Check) Is Null)
and (Duplicates_Rule1.BSAK_Text <> "FREIGHT")
ORDER BY Duplicates_Rule1.BSEG_DocumentNo;
I've got two tables in my Access 2010 database - both are identical in structure, the difference being one (Dove) contains old data and the other (Dove Data File) contains updated data. The primary key for these tables is "TowerBase".
I want to use an update query to update only the changes from the Dove Data File table into the Dove table - but for certain records only.
I've managed so far to get a query which displays only records that have changed in a particular field (Bells). Up to here I think is all good...
SELECT [Dove Data File].*
FROM [Dove Data File] INNER JOIN Dove ON [Dove Data File].TowerBase = Dove.TowerBase
WHERE (((Dove.Inactive)<>True) AND ((Exists (SELECT NULL
FROM Dove
WHERE [Dove Data File].TowerBase = Dove.TowerBase
AND [Dove Data File].Bells <> Dove.Bells))=True));
But now I want to add in criteria and this is the bit I'm struggling with. I need this query to now display only records where there is no associated record in my Visit Dates table. In other words, the Visit Dates table has the "TowerBase" ID along with a visit date. These records I do not want to see in the query, as I don't want to update any changes for these from the Dove Data File table to the Dove table.
I have a query I'm working with that finds data that changes, calculates the numbers, then prints a result in another column. The problem is that if I don't have any changes, then I have a blank result. Normally this wouldn't be a problem, but I'm making reports available to other users and would like this populated.
What seems simplest is to add an expression in the criteria field for the column that displays data after crunching numbers. Since this only has a display if something has changed, then I need an expression added if there is no data. Can I add something like an IsNull expression to display the results from another column in the same table? The data will end up being redundant, but I'm ok with that for now. the report should probably have the expression, but the column is already here in this table.
I am trying to map certifications done by colleagues in my department.There are 4 certifications and I have which I have pulled out from the Certifications tables using individual query for each certification.Now, I want to add "Certification-Name_Certified" col which will have "yes" or "no" values for each certification to the master data of the department personnel as it only have unique records using a query.
View 4 Replies View RelatedI've got a query that does exactly what this (http://www.access-programmers.co.uk/forums/showthread.php?t=89564&highlight=null+records) chap got his to do.
However I want to add a "*" character to the criteria to allow for searches with partial matches. Here's the criteria that works:
[title]=[Forms].[Search]![Title] Or [Forms].[Search]![Title] Is Null
However when I try the logical extension:
[title]=[Forms].[Search]![Title] & "*" Or [Forms].[Search]![Title] Is Null
this doesn't work, and nor does:
[title]=([Forms].[Search]![Title] & "*") Or [Forms].[Search]![Title] Is Null
WHAT DO I DO? I'M TIRED AND I CANT THINK STRAIGHT! :eek: thanks and sorry for being so dumb!
I am trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.
Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 CohortQ([InputDate]))
In the VBA, InputDate is defined as a date
Code:
Function CohortQ(InputDate As Date) As Integer
If InputDate = 0 Then
CohortQ = 0
Exit Function
End If
[Code] .....
But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows.
If I put the function into the query,
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 Year([InputDate])*10+DatePart("q",[InputDate]))
it works.
I have a field in a query that shows amount of hours a site is covered. This field name is Coverage hours and it changes depending on the number of work days in a month. This part is working correctly but some times additional hours may be requested by the customer.
I want to add a another field to this query that will prompt me if their were any additional covered hours. Then I would have the ability to enter a value and the shown output for this field would be the value entered plus the standard coverage hours in the other field.
I'm not sure how to set up the new field.
I have a database which tracks the performance of my team and how long it took them to send something out to the customer. We have a target of 5 minutes.
So I quite a few queries to drill down this information. I have a summary query that takes that information and tells me the total amount of things sent, the amount of things sent in time and the amount of things sent late.
My team have to get at least 95% out on time. So how would I go about adding this bit into the query. The calculation is:
The amount sent on time / the total amount * 100
But is there anyway to add this into a 4th column displayed next to these figures?
Looking around a few people have talked about SQL but I no nothing about this and it seems quite daunting, is there a way to do this as a calculated field?
I would like to use an append query that appends data from a union query into a table, but doesn't add duplicates based on a certain field.
I have been trying to use this code, but it doesnt seem to work.
INSERT INTO [Cotton13/14]
SELECT DISTINCT [Contracted Farmers].*
FROM [Contracted Farmers]
WHERE NOT (SELECT * FROM [Cotton13/14] FROM [Cotton13/14] AS [Cotton13/14] WHERE [Cotton13/14].[OF Codec] = [Contracted Farmers].[OF Codec])
Cotton13/14 is the table I want to append to and Contracted Farmers is the union query. However the code is giving me an error.
I have created a crosstab query that I will use in a subform and a subreport. However, Access won't let me add the fields to the query that I need to link the subform to the form. Is it possible to add fields to a crosstab query?
View 6 Replies View RelatedI have a query built that gives me info per employee. I built a question into the criteria that ask for the employees name. When a person types the name results are given. If the person mispells the name, results are not given.
I have everyones name on a seperate table. How do i create a pull down menu on the query to choose the names?
iwhen i add a field to allow me to add the parameters for a search between to dates. the query will only count the ethnicity of people who complained on the same day and give the others as a single number. i need to allow the user to search between dates as to generate the data for a report.
View 3 Replies View RelatedBasically, I have a Combo Box [Forms]![Sales]![Site] which a user can use to select either an exact location or a market area for multiple locations.
I also have a query for sales with a SellingBranch field on.
I want the query to display sales for only specific selling branches as chosen using the above Combo box. However, I'm struggling to get it to display multiple selling locations for one selection. For example I want to tell it that if [Forms]![Sales]![Site] is "Bolton MA" then the query needs to show results for when the SellingBranch is either "Bolton" or "Chorley".
At present I can get it to work to show just Bolton but don't know how to adjust it to show Bolton and Chorley when "Bolton MA" is selected.
IIf([Forms]![DatesSalesLeague]![Site]="BOLTON MA","BOLTON","MANCHESTER")
We have a query that calculates the cost total of three different types of costs between three different parameters. I now need to run a query that prompts the user for the days currency conversion rate to create a new report. I don't want to permanently convert the original fields, it is purely a monthly report where when the report is run is shows both British £ and USD.
View 3 Replies View RelatedI have a query which looks up records from a table, now what I would like to do is append the word All Projects to this query - is it possible, and if so how?
View 7 Replies View RelatedHi,
I am in need of help to sort out some records. I have tow existing queries I would like to combine and get one final set of records out of. They go like this:
Query 1. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 x x (check boxes)
Y ACD 2 x x
Query 2. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 8/8/05 9/8/05 10/8/05 (query performs
Y ACD 2 8/8/05 9/8/05 10/8/05 calculations)
What I am looking to retrive through the third query is this:
Query 3. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 8/8/05 9/8/05
Y ACD 2 8/8/05 10/8/05
Where the third query only shows the calculated dates when the check box is true. I have tried to go through the expression builder, but to no avail. I either get all records, like query 2 or I get nothing reported. I am not sure how to limit the records based on the check boxes.
Thanks,
CB
Cannot seem to find an answer to this, but please point me in the correct way if you know of one!
Quite simple i think, but blank mind at moment!
How would i use the values in a table/query as the criteria for another query? I believed i could type in [qryOne]![classification] in the criteria box, but this does not seem to work.
Thanks in advance,
Emily
I have a table with a field TDate (dd/mm/yyyy format). A query with calculated fields is lying on this table. I want to put a date criteria in this query, by a combobox in an unbound form, where the date format has to be mmmm/yyyy and has to be updated as new TDates come in.
Trying to pick up values for the combobox from the TDates field (and formatting them), I get a list with several same values. This is of course expected as there are many records on the same month, even on the same day of the month.Is there a way to have this list with unique values for each TDates month/year?
In Access 2007, I put into the table, in a certain field, a certain word field, so I put into design view for that query, in that field, Like "Field" and even though the word field is in that table in that field, it doesn't show it in that particular query?
View 14 Replies View RelatedI am trying to use criteria for Product column (i.e keep only product A) Total for Sales column to aggregate the values and it seems that is cannot be done simultaneously.
Product Sales
A 34
A 45
B 21
A 11
B 23
Eventually i want to get a table like below
Product Sales
A 90
I get the following warning : you tried to execute a query that does not include the specifies expression as part of an aggregate function.Why is this happening. I don't want to create 2 different queries to achieve it. Is there a way to do it i the same query?
It is possible to create a parameter query in Access that will search for records that match the parameter entered by the user AND contain all other records that have that value plus other text.
For instance you may want Access to return all records that begin with the letter Q.
To do this you need to enter the following expression into the criteria row:
Like [Find records beginning with:] & "*" Or Is Null
The user can enter a Q and the criteria concatenates the wildcard character * to this parameter value, to find all text strings that begin with a Q.
By also using Or Is Null in the criteria the user can enter a blank value into the parameter query to return all records, even if this field is blank.
But how do I search if i want all the records with John anywhere in the field???
I am trying to use a textbox value as a query criteria using the code [Forms]![Home]![mtdDateBox], following the standard structure of referencing a form item. However, I am not returning any records, when it should be giving some. The data in the textbox follows the date format "DD.MM.YYYY" but is used as a string.
Is there something I am missing out? I am using MS Access 2010
I want to create a query with multiple conditions. Basically if the person Passes any of this trainings they need to show up in my query..how do you do it?
SELECT tblMasterUsers.userid, tblMasterUsers.Licenses, tblMasterUsers.firstname, tblMasterUsers.lastname, tblMasterUsers.email, tblMasterUsers.npn, tblMasterUsers.Region, tblMasterUsers.ABSID, CMPreport2014.[Ahip status], CMPreport2014.[LP Status] AS [AZ Product Training], CMPreport2014.[LP Status1] AS [CA Product Training], CMPreport2014.[LP Status2] AS [OR WA Product Training], CMPreport2014.[LP Status3] AS [Fraud Waster Abuse],
[code]....
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.