I am trying to modify the output to only show discontinued items. While the query is in datasheet view, the text is read as "YES" or "NO" under discounted items. So, I tried entering "YES" in criteria while in design view, but keep getting an error message stating, "Data type mismatch in criteria expression".
View RepliesI have an existing query, created using the query wizard which works just fine - however, I would like to modify it to return only instances where there are 3 or more records appearing.
Essentially, its an employee history report for a particular action done by those employees, which returns all records between two dates as specified by the user. What I would like to do is only show those employees who have had more than three instances of this action in the given date period.
I am thinking along the lines of DCount? but how it would be phrased in the query?
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 got 2 tables i am trying query against. in table 1 i have a list of records which only appear once in the table under 1 field. in table 2 list of records which may have the same record appear under the same field numerous of times with different data in other fields on the table. i have created the reationship between both tables but i am getting multiple records appear in the output of the query where i only want the record to appear once in the output.
View 2 Replies View RelatedHow could i get output of a Query in a VBA variable ?? I'm trying as below butIi'm getting error:
Dim output As Integer
vari = DoCmd.OpenQuery("qryCompare", acViewNormal, acReadOnly)
Output of a query is a single MAX value.
Is there a built in function which can be used to create line numbers in a query?
I've written a query to calculate year to date (YTD) points for yachts in a series of races and sorted it in descending order - so yacht 1 is coming first, yacht 2 is coming second etc. I'm looking for a way to add sequential numbering (starting at one and increasing by one for each line) into the query to represent their YTD places. Or this this something that should be left to the report which uses the query?
I'm far from being an Access expert, so forgive me if I don't make perfect sense. I have a database where questions and pages are entered on a daily basis. I have a table with three columns: data, pages, and questions. I have a query where I can sumarize the data by Month. (I created a query by using the wizard and I chose to summarize by Month). This returns all of the data, summarized by Month. I want to limit it further by year, so that it would return 12 months of data. I am at a loss! I've tried typing [Type Year] but I get no responses. I was able to create a limiting one where I type in the month and year [Type Month and Year] that works great, but it doesn't translate into the year only.
Thanks for any help you can lend!
Michele
Is it possible to modify data values in a cross tab query?
I tried and it won't let me delete or type anything.
Thanks.
I have a query that is based off of a union query
SELECT clvpertech.clvtech1 AS Technician, Sum(clvpertech.CLVCode) AS CLVs
FROM clvpertech
GROUP BY clvpertech.clvtech1;
This is returning a sum of all the procedures a technician does for the entire database.
This is working.
I need to break this down by day, week , month, and year base off of the datecomp1 or datecomp2 or datecomp3 or datecomp4 fields having a date in them.
I made this query to get the daily results and it works
SELECT clvpertech2.clvtech1 AS Technician, Sum(clvpertech2.CLVCode) AS CLVs
FROM clvpertech2
WHERE (((clvpertech2.datecomp1)=Date$())) OR (((clvpertech2.datecomp2)=Date$())) OR (((clvpertech2.datecomp3)=Date$())) OR (((clvpertech2.datecomp4)=Date$()))
GROUP BY clvpertech2.clvtech1;
How can I add different queries to get the results by breaking down the date field for year, week, and month?
I tried this for the month but I get Invalid Procedure Call when I try to run it???
SELECT clvpertech2.clvtech1 AS Technician, Sum(clvpertech2.CLVCode) AS CLVs
FROM clvpertech2
WHERE (((clvpertech2.datecomp1)=DatePart("mmm",[datecomp1]))) OR (((clvpertech2.datecomp2)=DatePart("mmm",[datecomp2]))) OR (((clvpertech2.datecomp3)=DatePart("mmm",[datecomp3]))) OR (((clvpertech2.datecomp4)=DatePart("mmm",[datecomp4])))
GROUP BY clvpertech2.clvtech1;
whenever I run/execute a query in Access it is modifying the first record on the table in which it is calling the data from.For example if the first record might contain the following:
Record A: John Doe, Oct, 2014, Account is Active
And lets say I am running a query to pulling records from Nov 2014.The Month and Year Fields in the above example for Record A gets modified to the query search parameters for Nov.Is there some of of record lock or controls that I need to adjust to prevent this from occurring?
Hi,
Self learning trying to modify a query fieldname and criteria thru code.
Have a small form with a button making a copy of a query/s (eventually making about 50 copies).
Once these have been made, would like to open the query up, which I can do, then modify both
the fieldname and the field criteria to suit my needs from parameters set in the form.
How do I do this if it can be done ?
Thanks in advance
Ian T
I have a pass through query which I want to add a parameter to the end.
The pass through query is very long and has to crate temporary tables on SQL Server in order to generate the required output.
I do not want to retype or try and paste all of this code into a vba module.
Can I use querydefs or something in the querydefs collection to extract the code into a new query and append my parameter along the way?
A simple way of looking at this could be
querytemplate:-
"set nocount off
Select UWREF
from policy
where UWREF like '"
parameter:- (picked up from a text box on a form)
"ABC123"
additional text to follow:-
"%' set nocount on"
In a nutshell that's all I want to do, but as stated beforehand the real world query has a shed load of other code.
I have created a query and one of the columns of the output displays a check box because this is the data type of the underlying table.
I would like the query to output either Y or N rather than displaying the checkbox.
How would I do this?
I've created several expressions in a query to test for values in a field (one column for each value for use in a report).
The expressions output the value 1 where the test is true. I planned to sum them to establish how many times the value is true but this isn't working. I am only offered a Count and this returns the number of records. Other fields (numeric) are offering me a sum at the total line and are working as expected.
I assume the expressions are outputting the value one as text rather than as a number but I can't work out how to change this.
I'm trying to sum up 2 expressions.
1st. UsedAF: nz(DSum("AF_lengte+AF_zaagbreedte";"VRMUTSTAF";"VR ID = " & [VR].[VRID]);0)
2nd. UsedRES: nz(DSum("RES_lengte+RES_zaagbreedte";"VRRESSTAF";" VRID = " & [VR].[VRID]);0)
Now with a third expression I like to sum those 2 up.
3rd. Used: [UsedRES]+[UsedAF]
The thing I run into is when 1st has a value of 15 and 2nd a value of 0 the outcome of 3rd is 150 while it should be 15 ofcourse.
When I change the first 2 expressions to Sum (at Totals) the 3rd works properly. Yet the other 2 comes with wrong values.
Hi,
I am trying view the query output's in different text on the form and I do not want to see them again in query window.How do I hide this.. comments
???
I have a database that enables the banking details for a small club to be generated as an Excel spreadsheet that is then emailed as an attachment to the treasurer. The event procedure below works fine but I have been unable to incorporate the banking date from a control called BankDate to end up with the Excel file being in the form DGC Banking dd-mm-yy - is this possible?
DoCmd.OutputTo acOutputQuery, "Export Banking", "Excel97-Excel2003Workbook(*.xls)", "DGC Banking.xls", True, "", 0, acExportQualityPrint
I'm building a query using wildcard "like". See the attached file. My question is that why doesn't the query "result" return "aaaaa" for "aaa" is contained within "aaaaa"?I'm basically limiting my records to those found in tbl_site based on a wildcard "like".
View 12 Replies View RelatedI am trying to get the output of several nested IIF expressions to show in only one filed in the table created. The output needs to be captured in a table. I am staring with create table to get it to work before I go ahead and use the sql to update the original table (the table the data is from)
I can get each to run fine independently and the whole thing runs too, the issue is it wants to put each expression in a new column, thus it is forcing me to label the expression.
Can I change this to only output in one column or add some more sql to do so?
Here is the SQL (table and filed names have been changed to protect the innocent)
BTW, I cannot change the Table the data comes from, I am stuck with the single table structure (BOO).
************************************************** *************
SELECT TABLE1.ID,
IIf([TABLE1]![STAGE]="D",
IIf([TABLE1]![PROJ_ACTUAL_D_DATE] Is Not Null,"Tier 1",
IIf([TABLE1]![APPROVAL_D]="Y","Tier 2",
[Code] .....
I'm using Access 2007 SP3.
Whenever I export reports to PDF, the output appears zoomed and clipped. No extra pages are generated as they would be if I'm going over margins, it's just the report is clipped.
The report looks perfect in preview mode, and it looks perfect when going to an actual printer. However, when using OutputTo to save it as a PDF, this is when the report content is clipped.
Here is the code I'm using:
DoCmd.OpenReport MyReport, acViewPreview
DoCmd.OutputTo acOutputReport, "", acFormatPDF, "MyReport.pdf"
DoCmd.Close acReport, MyReport
I open the report in preview mode first so events are fired that show/hide various objects based on fields in the recordset.
I've tried reinstalling, and I've tried this on two different machines, one running Windows 7 and one running Windows Server 2008...both with the same results.
I'm writing a simple job scheduling database, so I've got resource availability (by week) and job list (by week). I've written a single-column query whose only output is a unique list of weeks with resources available, and another single-column query whose only output is a unique list of weeks with at least one job allocated.
How do I join these so that I can get a single-column list of weeks that have either (or both) of resources and jobs?
So Query1 returns:
25/08/2014
08/09/2014
15/09/2014
while Query2 returns:
01/09/2014
08/09/2014
15/09/2014
22/09/2014
I want query3 to return:
25/08/2014
01/09/2014
08/09/2014
15/09/2014
22/09/2014
Hi,
I have a select query that selects certain customer email addresses and
I want to output the results as csv, which can be used directly in a "To:" field to create a group email.
The SQL of my query is:
SELECT [Customer Details].[Email Address]
FROM [Customer Details] INNER JOIN [Orders] ON [Customer Details].[Customer ID] = [Orders].[Customer ID]
WHERE ((([Orders].[Licence Declaration])=No) AND (([Orders].[Licence Status])="Unlicensed"));
I have found some SQL code on the internet as follows:
"SELECT Field1, field2 FROM table WHERE field1='" & [FORM]![PARAMETER] & "';"
but adapting my code with that is a little bit beyond me...can anyone help?!
Cheers,
Dave
Hi,
I have a query which pulls key fields from a Table and exports to an excel file through the Output to cmmd.
In the table, i have defined a few fields with Look-up criteria and Combo / LIsts. When the Excel sheet is created the Lists are not downloaded. The purpose is to scrub the data and provide users an excel sheet for update of the Combo box for upload in another MS access table.
Please help, this feature is extremely critical for our work. Thanks in advance.
Hello, I’m pretty bad with VB code but I need something pretty simple. I have a button on my form and when I click it I want it to run a query and output a certain field value to a label. I know I’ll need VB code for this, so any sample code would help a ton!
View 4 Replies View RelatedCould someone tell me how to put the out put of a query into a textbox in a form.
I have asked this question on a couple of other sites but I still cant figuer this out.
I am a beginner so please make and easy explanation.
thanx
is it possible to automatically output the results of a query to notepad?
View 2 Replies View Related