Over 1 million tech questions and answers.

Solved: Access query report query paramater use the param

Q: Solved: Access query report query paramater use the param

I've got a lending table in which we lend items out, now they can be loan or permanent.

I can run a report with the query parameter on the ReqDate_Fld criteria Between [Report Start Date] And [Report End Date]

The report prints and counts everything borrowed.

Now I want to add more records to the report to include everything borrowed that was returned during that period specified by the criteria Paramenter set in the query. But using the Return Date (RetDate_Fld)

How can I select rows from the query based on the parameter date filled in when the query ran?
[Report Start Date] and [Report End Date].

I can build a separate report just for the returns using the current parameter but on the RetDate_Fld Between [Report Start Date] And [Report End Date] set by the query.

I've got it grouping at 3 levels to get it to break by Borrower, ReqID, and Request Date

Does this make any sense?
Thanks

RELEVANCY SCORE 200
Preferred Solution: Solved: Access query report query paramater use the param

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: Access query report query paramater use the param

You would have to have another group for whether or not the RetDate_Fld was filled in or not.
Add a new column to the query with the following Heading

RetDatefilled: iif(not isnull(RetDate_Fld), 2, 1)

That should place a 2 in the new column when there is a date and a 1 when there isn't.
You can now group on that data.

Read other 2 answers
RELEVANCY SCORE 108.8

NOTE: I could not post code because I must be really careful to protect proprietary information.

I work in an assembly plant and I've designed an Access 2003 program to deliver a set of 4 crucial parts to the floor all within a very narrow degree of tolerance. Further, the program integrates FIFO.

Ive done this by assigning each of the essential measurements codes and then running a series of queries:

Query 1 identifies the oldest Part A by sorting the available parts by the date they were inspected then by serial number (to break ties) and delivering the TOP 1.
Query 2 identifies the oldest Part B that is compatible with that Part A using the same method.
Query 3 identifies the oldest Part C that is compatible with that Part B.
Query 4 identifies the oldest Part D that is compatible with the other end of Part A. I have the parts in this order because we can begin assembly if we don't have a Part D on hand, but we must have Parts A, B, and C.
Finally, query 5 simply pulls the information from those queries into one location for a report.
As long as we have enough stock, life is good; but the moment a match cannot be made, it blows up. I cannot figure out how to make it go back to choose the next Part A. I need to figure out how and where to add code to each query to tell it that if no stock is compatible with the Part A produced by Query 1, to go back and pick the next one in line.

Can anyone help? Is the way I've gone about matching parts completely... Read more

A:Access 2003 – Query Based on Query – need code to rerun earlier query

Read other 11 answers
RELEVANCY SCORE 106.4

I have a query that lets the user search by date range and returns all records where the value in the data received field is between the date range or is null. The problem is that the date is entered on a form after some of the other info has been entered on another form and the info has to be entered this way and the records have to be on difference rows in the tables. So on the report the date shows up on a different line than the rest of the info, how can I combine the two rows on the report so they show all on the same line? Please help. Thanks.
 

A:Solved: Access report/query question

Read other 15 answers
RELEVANCY SCORE 106.4

Hello. I am using access to track what classes part-time professors at a college teach each term. My boss wants a report on the last term they taught. I have about 400 records, and the each term I have the amount of credits they have taught each term - That is how I know if they taught that term or not. I need to build a query that looks at those records and tells me the last one that is greater than 0. Or is that something I should export into a spreadsheet and build it there? Thanks
 

A:Solved: Access Database Query / Report - How to

Read other 9 answers
RELEVANCY SCORE 105.6

Hi there,

I'm very new to access and I hope I will get the simplest solution on my question below.

I have a table named "PurchaseOrder" and I'd want to create a query that will display the invoices that will due.

Eg:

Table1
PONo Customer Quantity SalesPrice InvoiceDueDate
3241 A 421 $12 17-Oct-2010
6589 B 521 $19 14-Oct-2010
2234 C 987 $32 15-Oct-2010
8697 D 888 $88 18-Oct-2010

I want to create 2 queries.

The first query will display all the invoices that will due 5 days before the exact due date.
Eg:
The invoice due date is 15-Oct-2010. The query will display information on 3241, 6589, and 2234.

For the second query, I want to display all the invoices that have exceeded the due date. The information on the respective invoices will be transfered from the query as mentioned earlier to the new query.

The scenario is as below:

Date: 16-Oct-2010

Query1

PONo Customer Quantity SalesPrice InvoiceDueDate
3241 A 421 $12 17-Oct-2010
8697 D 888 $88 18-Oct-2010

Query2

PONo Customer Quantity SalesPrice InvoiceDueDate
6589 B 521 $19 14-Oct-2010
2234 C 987 $32 15-Oct-2010
Date: 18-Oct-2010

Query2

PONo Customer Quantity SalesPrice InvoiceDueDate
8697 D 888 $88 18-Oct-2010

Query2

PONo Customer Quantity SalesPrice InvoiceDueDate
6589 B 521 $19 14-Oct-2010
2234 C 987 $32 15-Oct-2010
3241 A 421 $12 17-Oct-2010
Hope someone can help me with this. Really appreciate and hoping for ur help.

 

A:Query Access 2007 (how to transfer data from a query to another query)

The first query needs the following first Criteria Row of the InvoiceDueDate
between date() and date() + 5
The second query needs
< date()

I haven't tested it, just worked from memory. so let me know how it goes.
 

Read other 2 answers
RELEVANCY SCORE 105.2

Hi All,

I have a DB which has two reports which can be run depending on whether it is a single site or multisite.

I have added a yes/no tick box to show if a multisite, but what i need to do now, is to get access to run the correct report.

If Multisite not ticked to run Report A
If Multisite ticked to run Report B

I dont know enough about VBA to do this so any and all help would be helpful.
 

A:Solved: Access 2003- Report/Query (guessing VBA) Help

Read other 10 answers
RELEVANCY SCORE 105.2

Here's your Access-deficient friend again.

I'm still working on a database I described here before. I've come up with one table that holds fields for date, gender, county and yes/no fields for several possible referral sources. The data input person would just have to check a box next to the name on a form indicating that particular referral source was recommended. This is easy enough.

Now, I need to create a report based on a query that will give me the totals of the "yes" fields, or the boxes that were checked on the form. Does this make sense? If, for example, on 2/1/02, a female called from Smith County and was referred to CMH, FIA and SLVA (abbrevs) and another female from Smith County called on the same day and was referred to the same sources, I want a report that totals, say, 2 for CMH, FIA, SLVA, etc.

Thanks in advance!
 

A:SOLVED: Access 2000-Total of Yes' in query/report

Read other 6 answers
RELEVANCY SCORE 104.4

I have painstakenly begun the creation of a database that tracks people in various ways. However, the only benefit the database will have is if I can create reports that group them into relevant information. Most of the information that I need to report is based off of monthly, quarterly, and yearly groupings. However, I want the users to easily be able to access this information.

I would like to create queries (which will then feed the reports I plan to create) that break the information down into monthly, quarterly, and yearly reports based upon various dates included in the database. I have investigated the sample Northwind database, and it has this great Report dialog box to make it easy for users to gather the information they want. The only way I could figure out to gather information by date is to have Between dates Parameters (a confusing endeavor for some of my users). Does anybody have any suggestions for me to make my queries/reports easier to use for the novice user?

Just a note: I'm not great with VBA, but I do know how to type in it? (just can't write it very well myself)
 

A:Solved: Creating a monthly query/report in Access 2007

Read other 16 answers
RELEVANCY SCORE 104.4

I want to show all 12 months even though they are not present yet in the table. January, February.....

Here is the script I am using:

Month: Format([Date],"mmmm")

It only shows the months that are in the table. I need to show all months even though the field is empty. Is there another way in the report to show each month but has no data to fill it. Show 0 in the cell if empty?

Can this be done in a report to show 0 if table has no data for that month?

On the form I show all twelve months. If the cell is empty I get an error because there is no data to retrieve for that month.

DB attached. Thx!
 

A:Solved: Show all 12 Months in Query/Report Access 2007

In the column headings in properties of Month I added "January","February.....","December"
Of course you'll have to show all twelve months...
This forced every month to be shown even if there was no data for that month.
 

Read other 1 answers
RELEVANCY SCORE 104.4

i have a parameter query that creates a report from it but i want to use the query term in the title for the report e.g. paramete query for day of week, type monday in query box, title for report is Bookings on Monday
Any idedas (if you understand)

THANKS
STUART
 

A:Solved: Access - Query search term in report title

Is this also for your school project?
 

Read other 2 answers
RELEVANCY SCORE 104

I have written a macro to make a table from data in an existing table. Then I use the data in the new table to select information in another table. That select query does not allow me to update the information in the 2nd table. It is like the records are locked. Even if I create a new table myself the select queries are locked but all my pre-existing queries work fine. Is there some type of setting I am missing that causes the information to be locked in select queries? I am thinking somehow the setting may have been changed. Any thoughts would be appreciated.
 

A:Solved: Access Make Table Query and Select Query

Read other 7 answers
RELEVANCY SCORE 103.2

I am learning access and have build a database. in my tables we have dates to track employee expiration dates and some tables that show due dates for maintenance on equipment. Field names look like this.
LicExpires, MVR, AR, and so forth...
On one of my queries for equipment due dates, I used this formula in the query; InspDue: DateAdd("yyyy",1,[LastInsp]), which meant that I had to add another field "InspDue".
On my employees query I have like 10 fields that have due dates in them and I was wondering how can I just get the names of the employees to show up with any one of these fields with due say like today and/or 30 days in the furture?
I have tried everything that I have read to make this work but when I use the >Date() expression I get back blank query.
I am to my witts end here.
Also in the same table, if the field is null how can I get it to show on the same query?
Any help is appreciated.

Thanks,

David
 

A:Solved: Access 2007 Query/Report to show expired dates

Read other 16 answers
RELEVANCY SCORE 101.2

I have a previously designed Access DB and there is a query that I am trying to understand. It has several tables that are queried more than once in a single query. First how do I determine the actual table name? I can only see the alias name. Also what is the purpose of using the same table twice in a query?
 

A:Solved: Access Query using tables more than once in same query

It is normally to do calculations or comparisons on prior or next records. It is the one weekness of Access Queries, the Totals function has some good arithmetic calculations like average, Count Sum etc but manipulating previous records like add or subtract values from the current record it can't do. So it requires more advanced SQl. Which is apperas you have.
Have you looked at the query in SQL view?
It should use the word "As" to designate the alias.
 

Read other 2 answers
RELEVANCY SCORE 97.6

Hi All,
I need some help is sorting out the issue that am seeing in IE 10,11 in specific.
As a part of our application we have a page that will list the different reports created by the user and the user can click on these created reports and view them.
In the "onclick" event of javascript the needed url is fed in for this page to navigate appropriately. Also along with this url certain query parameters are also sent, one being the report name that was created by the user.
For English no issues. But when the language is non-English, say Japanese the report name becomes a japanese anchor tag and the onclick will have &reportname=??? as a part of its query parameter. Now such URLs are not getting processed properly by IE
10, 11. Mozilla, chrome works fine here.
There are a lot said about this encoding issues in Ie10,11 but am looking for a more conclusive statement. Whether IE 10,11 internally does not do the URL encoding/decoding for query param appropriately?
pls note - when the anchor tag goes with a request as below,
&reportTitle=?????????
network tab in debugger shows it as it is &reportTitle=????????? while in other browser this got encoded automatically.
Hence we thought a forceful encoding is needed for IE and reframed the anchor tag as below (changed the logic),
&reportTitle=%e3%83%ac%e3%83%9d%e3%83%bc%e3%83%88%e3%81%ae%e5%ae%9f%e8%a1%8c%e7%b5%90%e6%9e%9c
network tab in debugger shows... Read more

Read other answers
RELEVANCY SCORE 97.6

Hi Access bods.
Can anyone tell me how to do an expression to query from DATATABLE (the name of table), containing

ID: Auto ID key for the table
SITEID: There are multiple sites
ITEMID: Each site will have multiple Items
QUALITYID: This is set to either F, or is left blank
Id like to find the Percentage of Non-F, to each SITEID and ITEMID combo. I hope this makes sense.
 

A:Solved: Access Query, Query

Read other 6 answers
RELEVANCY SCORE 97.2

Hello,
I am trying to narrow fields by Month, Quarter, and Year for several reports. Month and quarter work, but for some reason, when I try to set my dateEntered criteria for Year I do not receive any results. Here is what I am using so far for the dateEntered field criteria:

Month:
Between [Forms]![Individual Time Card Report Form]![Combo15] & "/01/" & [Forms]![Individual Time Card Report Form]![Text19] And [Forms]![Individual Time Card Report Form]![Combo15] & "/31/" & [Forms]![Individual Time Card Report Form]![Text19]

Quarter:
Between [Forms]![Individual Time Card Report Form]![Text29] & "/01/" & [Forms]![Individual Time Card Report Form]![Text34] And [Forms]![Individual Time Card Report Form]![Text32] & "/31/" & [Forms]![Individual Time Card Report Form]![Text34]

Year:
Between "1/01/" & [Forms]![Individual Time Card Report Form]![Text25] And "12/31/" & [Forms]![Individual Time Card Report Form]![Text25]

The text boxes refer to user input from an already open form (which works fine for Month and Quarter) What am I doing wrong with year? Or is there an easier method for searching for only records from a specific year? Thanks
 

A:Access 2007 Report Query

Read other 15 answers
RELEVANCY SCORE 97.2

Hi,

I have just created a query to feed data to my report. The query consists of;

Orderdate Between [start date] and [End Date]
Orderid
Ordertypeid [Enter Order Type ID]
Goodstotal

This works fine but i need all 3 order types to be in 1 report but each id seperate. The ID's are:
1= Postal
2= Telephone
3= Web

Could anyone help me with this please? Any help will be appreciated.
 

A:Access 2003 Report/Query Help

Read other 9 answers
RELEVANCY SCORE 97.2

I'm using MS Access 2007. I have a report that pulls data from a query where I have a parameter set so that it selects specific data depending on what I put in the dialog box. When I just pull up the query, everything works fine; I click on the query, a dialog box comes up, I put in the specific parameter value, and voila!...the requested records appear. The report that I built using the same query almost works as well, except I have to enter the parameter value 3-4 times before the requested report comes up. I'm flummoxed! Any help out there? Thanks!
 

A:Access Report Using Parameter Query

Read other 12 answers
RELEVANCY SCORE 96

Hiyo.
On my Access db switchboard, I have:

- a Button - "CatReportBtn"

- a Combo box "CatCombo" which has a Row Source of "SELECT CatTable.CatName FROM CatTable WHERE CatType =1 ORDER BY CatTable.CatName;

- Hidden textbox "SelectedCat" with a Control Source of =[CatCombo]

Clicking the button opens the CatRpt report. The report has a Back button on it, which closes the report and re-opens the Switchboard.
The query (CatRptQry) behind this report has date range parameters on it.

So I can:
Select a value from CatCombo and then click the CatReportBtn, enter a date range, and the report displays data about the selected cat only, for the date range specified.

OR

Don't select any value from the CatCombo, just go ahead and click the CatReportBtn, enter a date range, and the report displays data about all cats for the selected date range.

So far, awesome.

Now, I'm being asked if the reports can be exported to Excel. So considering my options....

1) I could just go ahead and export the report directly to Excel. It's not pretty, and the user would have to do lots of re-formatting every time, so that's not much good.

2) I could add another button to the report ("Data View"), which when clicked would run the query. The user could then export this to Excel without the formatting hassle (but she'd obviously need to then add her own header, totals, etc).
If I did this, is there a way to 'read' the repo... Read more

A:Access open report and query with same params

There are a couple of options.
Excel can be set up to be linked to the Query and refreshed on opening, the headings wouldd be preset.
The Query can be exported using VBA, if it is part of your current button the Query will automatically open with the parameters.
The Query Recordset could placed in the Excel sheet using VBA, I posted VBA code on this recently.
 

Read other 1 answers
RELEVANCY SCORE 96

I have a report I run based on a query that works perfectly. I'm managing event registration for an association of school student councils. The report is a separate page for each school that summarizes the information I have for that school relative to its participation, i.e. school name, address, who is coming, how much they owe, etc.

It works fine when I want a page for all of the schools, but sometimes I just want a single school...not all of them. I copied my original query and set up a parameter in the copy [What school?] and it selects the school I want. However, when I run a copy of my original report I made changing the source to my new query, when I open the report, the parameter box opens, I put in the school name I want, and the box comes up again, and eventually my report opens with no data in it.

Help? I don't do VBA, but I get around Access pretty well.
 

A:Access 2007 - Report from Query with Parameter

Read other 9 answers
RELEVANCY SCORE 96

All I am trying to do is move a value from a Query, into a text box on a report.

I created a Query, which adds up all the numbers in another query, and provides me with a total. I am trying to add this to a footer of an existing report I already use. So as I mentioned, I just want to popualte the report text box with the value from the query, so that it happens automatically.

I have tried putting formulas into the "control source" on the "data" tab of the text box, but nothing seems to work right.

Can this be done??

Thanks.
 

A:Transferring Query Data to a Report in Access

Read other 6 answers
RELEVANCY SCORE 96

am attempting to put together an access report. I want the user to have the ability to input a number (3 digits) that is associated with a region and the report should print all locations associated with that Region. The Table lists the region number along with the region name, such as 125 - Macon. So the user has to input the Region Number and Region name to match exactly. Is there a way to use the 3 digit number input by the user along with a wild card to print all locations (regions) beginning with 125.
 

A:Access Report Query - Wild Card use

Why not use a combo box to allow the user to select number and region exactly, it cuts out typo errors?
You can use a wild card in the Criteria of the report's Query, it uses the "Like" structure, here is an example
Like forms![formname]![fieldname] & "*"
where formname is the name of a form anf fieldname is the name of a field or combo on that form.
 

Read other 1 answers
RELEVANCY SCORE 96

I was trying to create a report from scratch based on a parameter query in Access 2007. But my query did not show up in the "Fields List Box" where I could pick the fields I wanted for my report. I saw all the tables in my database with their associated fields in that box but no query. Is there anything I was doing wrong? Please help

Thanks
 

A:How to base a report on a query in access 2007

The easiest way to accomplish this is to highlight the query and Hit report - you can then strip it all out and start from scratch again but it will link the correct fields

Its strange i know but it jsut doesnt like to work with queries.

The alternative is, if you really do want to start from scratch is create the Text Box (unbound) and link it in properties to the relevant field. Like in the picture
 

Read other 2 answers
RELEVANCY SCORE 96

I am still discovering Access 2002. I can run querys ok and then base a report on the Query. My problem when i run that saved query again to get different results... the report does not update. (I cut my teeth on Lotus Approach and having problems with MS Access).

I dont want to make a new report format every time I run a query.

Steve
 

A:Updating Query Report : Access 2002

Read other 11 answers
RELEVANCY SCORE 96

At the end of my Query and Report I want to show a total of records returned. Can anyone tell me how to do this please. Thankyou.
 

A:Access - Query/Report records total

Read other 7 answers
RELEVANCY SCORE 96

Hi There,

I have a slight problem with reports.

Here goes..............

I have a time commence and a time end field.
Within the query window, I have the Duration: ([Time End]-[Time Comm]) in a field and the properies are set to: h" hour(s) "n" min(s) ".

When I run a query, it shows the time as 0 Hour(s) 40 min(s) etc.
My question is this. How can I ask the duration field to show telephone calls with a duration of 20 minutes+?
This is to work out staff's bonus payment.
Also, can I point out that I am not too great in coding etc. A command would be much easier for me to follow, also, I do know that when I create the field I am asking help for, I need to add that field within the report window. It is just the comand for the query I would like help on.

Any help much appreciated!
Thanx
 

A:Access 2002 query/report calculations.

Read other 16 answers
RELEVANCY SCORE 95.2

Hi!

I'm pretty much a newbie in Access, it's been years and it's gone totally out of my head. I've done alright setting up an intital database, but the scope of the project has grown more and more complicated and I'm having trouble keeping up.

Due to the large number of total fields associated with this database, I have created 5 seperate tables in which to house the data collected. I need queries which will pull information from all the databases, sort results alphabetically and then link to a report.

Here's what I need to do. User comes in through the switchboard, is provided the option to run a form titled something like "Find by Product Manufactured". He would select the kind of product he would like from a pull-down menu AND be given the opportunity to hand enter a value as one of those choices. He would click "Run" and the report would pull the desired information.

Right now, the product types are entered as True/False values (or nulls) in 19 fields, viewable in forms as a checkbox, the 19th field being an "Other" text field with hand entered text from clients. I would really, REALLY like to do this in some other way than creating 19 seperate queries showing all data based on one field - please tell me there is another way?

(I also have no idea, although I have read your tutorials, how to get the pull-down menu to show up in the first place)

HELP HELP HELP!!! This seems like a complicated request, thoug... Read more

A:Access 2003 Switchboard/Report/Query Dilema

Read other 16 answers
RELEVANCY SCORE 95.2

Just found someone who helped write the code...no further help needed.

I have a form to filter some data via a query and you can either view the query results or print a report of the data in a slightly different format. I can't get the filtered result to populate in the report to be printed.

Here is my vba code. Am I missing something? I want anything that is checked a priortiy to be the only thing to print. When I check priority I can view the query results but it doesn't translate into the report. Help??

Private Sub cmdPrintReport_Click()
'The following code displays the pre-close report based on user entered criteria.
Dim strSQL As String
'Check that the user has filled out the form correctly.
If Not EntriesValid Then Exit Sub
'Check that the SQL string was built correctly.
If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If
'Run the query.
CurrentDb.QueryDefs("P3-2qry").SQL = strSQL
'Display the report.
DoCmd.OpenReport "P3-3arpt", acViewPreview
DoCmd.Close acForm, Me.Name
 

A:Report won't print from filtered query Access 2007

How many nested if statements are in that thing? I wouldn't even know where to begin looking in that mess to find out where the problem lies. What are you declaring a string as boolean? The function Boolean is true or false.

Have you set a breakpoint at the end of the code and walked through it?
 

Read other 3 answers
RELEVANCY SCORE 95.2

I have a query, "My_Query" which accepts a parameter "Number_of_Years". I can open this query directly, and it prompts me for the value of "Number_of_Years"

I would like to be able to pass this value from a report.

In Sub Report_Open I have the following code:
Code:
Set dbsReport = CurrentDb
Set qdf = dbsReport.QueryDefs("My_Query")

intNumYear = InputBox("Please enter the number of years for this report", "Number of Years")
qdf.Parameters("Number_of_Years") = intNumYear

Set rstReport = qdf.OpenRecordset()
I am populating fields in the report based upon the result set in rstReport. Report's data source is set to "My_Query"

The problem I have is that I am prompted twice for my input (Once from the report, and once from the Query)

I would like to know if there was a way to pass the intNumYear to the query so it will not prompt the user a 2nd time.

Thanks for your assistance.
 

Read other answers
RELEVANCY SCORE 92

For a report I have a customer table, an invoice table and a revenue or payment table.

I need to produce a report that shows all the invoices created during a given month and year - that part is easy.

But then I also need to show the first payment given on that invoice. This is a situation where there is always a down payment made at the issuance of the invoice - but sometimes people will make more than one payment during a month.

Example:

I want the customer listed for each new invoice, the total amount of that invoice and what the inital payment on that invoice was: [CustomerID][Customer Name] [Invoice No][Total Invoice Amount] [Payment Amt]

So if there was an invoice issued for $100 and they paid 50 and 25 during the month it was issued and the balance on the next month, I still want to show it on this report as a $100 invoice with a $50 downpayment only.

(The tables are established and populated so it is too late to change the payment table to allow for a unique downpayment field. They all go in as payments.)

My problem is that if they made more than one payment during the month, the whole line with the customer name and invoice number shows up for each payment made. I only wnat the FIRST payment. I need a payment query that shows only the first payment in the month of invoice issuance.

Does that make sense?
 

A:SQL Query question for Access 2010 query

Yes it makes sense, I would create a pre-query that uses the Month & Year but is set to "Totals" and Date Minimum.
Then create the Report query using the pre-query linked to the new query's table via the InvoiceID this will limit both the month, year and minimum payment.
 

Read other 1 answers
RELEVANCY SCORE 92

I have a query that I have with these fields:
CoName;EquipType;UnitNo;YearOfMfg;Make;VIN;LastInsp;INSPDUEateAdd("yyyy",1,[LastInsp]);TireSize;EquipStatus;InActiveDate;CoCode
This is what I have now:
SELECT [A_Co Info Table].CoName, [K_Equipment List Table].EquipType, [K_Equipment List Table].UnitNo, [K_Equipment List Table].YearOfMfg, [K_Equipment List Table].Make, [K_Equipment List Table].VIN, [K_Equipment List Table].LastInsp, DateAdd("yyyy",1,[LastInsp]) AS INSPDUE, [K_Equipment List Table].TireSize, [K_Equipment List Table].EquipStatus, [K_Equipment List Table].InActiveDate
FROM [A_Co Info Table] INNER JOIN [K_Equipment List Table] ON [A_Co Info Table].CoCode = [K_Equipment List Table].CoCode
WHERE (((DateAdd("yyyy",1,[LastInsp]))<=Date()+60 Or (DateAdd("yyyy",1,[LastInsp])) Is Null) AND (([K_Equipment List Table].CoCode) Like [Enter CoCode:])) OR ((([K_Equipment List Table].LastInsp) Is Null) AND (([K_Equipment List Table].CoCode) Like [Enter CoCode:])) OR ((([K_Equipment List Table].InActiveDate) Is Not Null) AND (([K_Equipment List Table].CoCode) Like [Enter CoCode:]))
ORDER BY [K_Equipment List Table].UnitNo;

What I need is a way to be able to show the INSPDUE using this <=Date()+60 Or Is Null.
When I do this, it will show all the inspection past due from todays date + 60 or if the field is blank.
However, I don't need it to show anything if the LastInsp and the INSPDUE is not null.
Example, the LastInsp is given a d... Read more

A:Solved: using query for a report

Read other 16 answers
RELEVANCY SCORE 88.8

I recently built a call log database in Access 2007 for my team to use when calling agents on our top errors we see from a vendor's error report. My boss is requesting a report to see how many items have been resolved

I have a query started and I am trying to complete this task in a single query (if possible) instead of several queries (as I've done for a couple other things).

I have 3 tables I'm using: Assignment, AgtInfo and CallLog
Joins as follows: Assignment.st=AgtInfo.agcy_st
AgtInfo.agt_id=CallLog.agt_id

Fields in the results: Assignment.assigned_analyst, CallLog.rpt_mnth, CallLog.resolved

Resolved is a yes/no field. I've done a query that converts the -1 to a 1, so that the sum is a positive number for the "Yes" values. I'm not sure how to enter the expression to subract the "Yes" values from the total number of values (yes/no) to get to my "No" count.

Essentially, I'd like the results to display as:

assigned_analyst, rpt_mnth, CountYes_resolved, CountNo_resolved

Somehow the connection is just not clicking in my brain with everything I've read though searches. Any help is appreciated! Thanks!
 

A:Solved: Access 2007 Query: Return count of "Yes" and "No" values for Analyst/Report M

It is easier to add another column to counf the Nos, use
0 or is null
as the IIf statement and use a 1 to count them
 

Read other 3 answers
RELEVANCY SCORE 82

Hi everyone
Wondering if anyone could help. I have a table which displays workers and certificates. Is there a way that i could display the worker and his relevant certificates in a form without displaying the ones that they havent got? I have the relevant certificates in a yes-no format...
Kindest regards
 

A:Solved: Access query

Read other 16 answers
RELEVANCY SCORE 82

I want to be able to use the "QTY" column, so I can query different part numbers at different times. I would like the query to work with the reports I have in the program, so I can print labels.
The query I created does not seem to work the way I want it too. It does not show the labels with the pictures.

Looking for help.

Mike
I can email the zipped file.
 

A:Solved: Access Help on a Query

Read other 13 answers
RELEVANCY SCORE 82

I am trying to allow users to look up a text string in a larger database. The problem is, there's two columns I need to look the number up in. One with special characters, one without.

My thoughts on how to do this were a query with an or statement, but I don't want the user to have to enter the text string twice. The other idea is a table, which links to both tables, but I'm not sure how to make that work and haven't tried.

This is the query I tried, but it doesn't work.

SELECT Item_Master_PROD_20120618.MFG_ITM_ID, Item_Master_PROD_20120618.[Clean MFG_ITM_ID], Item_Master_PROD_20120618.INV_ITEM_ID, Item_Master_PROD_20120618.[Item Description], Item_Master_PROD_20120618.[Category Cd Desc], Item_Master_PROD_20120618.[Manufacturer Name], Item_Master_PROD_20120618.MFG_ID, Item_Master_PROD_20120618.STD_UOM, Item_Master_PROD_20120618.[Vendor Price]
FROM Item_Master_PROD_20120618
GROUP BY Item_Master_PROD_20120618.MFG_ITM_ID, Item_Master_PROD_20120618.[Clean MFG_ITM_ID], Item_Master_PROD_20120618.INV_ITEM_ID, Item_Master_PROD_20120618.[Item Description], Item_Master_PROD_20120618.[Category Cd Desc], Item_Master_PROD_20120618.[Manufacturer Name], Item_Master_PROD_20120618.MFG_ID, Item_Master_PROD_20120618.STD_UOM, Item_Master_PROD_20120618.[Vendor Price]
HAVING ((((Item_Master_PROD_20120618.MFG_ITM_ID) OR (Item_Master_PROD_20120618.Clean MFG_ITM_ID)) =[Enter Manufacturer Catalog Number]));
 

A:Solved: Access Either Or Query

Nevermind, fixed my own problem. Amazing the clarity posting something gives.
 

Read other 2 answers
RELEVANCY SCORE 82

Hello all
Im trying to create a report for a database. The database is a address update db. We put info of the clients new address and when the our system was updated with the new address. This help prevent sending more then one letter to the same insured. Now what i want to do is have the report pull off the ones where we have not recieved the address update. Which is system updated field.i want the report to be by client. So if i type in microsoft it will bring up everyone that works for microsoft that has not given us a new address. i know how to create the query so it pulls up just microsoft but i can figure out how to bring up just the one that havent gien us the info yet. the fields are left blank when they havnet been update.
I hope this all makes since if someone can help me that would be great

Thanks
 

A:Solved: access query

If you use the Query By Example (QBE) editor window, include the "system updated" field and type Is Null as the Criterion.

HTH

chris.
 

Read other 3 answers
RELEVANCY SCORE 82

i am trying to create a query that changes the names of positions so that i can group them based on similarity. ie. A Sr. VP operations and a Sr. VP Human Resources are the considered the same "SENIOR EXEC", where as the VP operations & VP Human Resources are considered "SENIOR MGRS"

now here is what i have so far.

Position Area: IIf([Position]="GM" Or [Position]="Assistant General Manager","GM/AGM",IIf([Position]="SR*","Senior Exec","Senior Manager"))

all is ok, except for the following
IIf([Position]="SR*"
what i want it to do is recognize anything that begins with SR, so that i do not have to type in every position. this does not seem to work and anytime i have a SR position i get a result of "Senior Manager", where as it should result in "Senior Exec",

Now if this is not the simplest request for a early morning.
 

A:Solved: Access Query using IIF & *

Read other 8 answers
RELEVANCY SCORE 82

Hi All,

Can any one help me in creating query in MS access for the below formula;

=IF(A2=A3,0,1)

Example:
The query has to match the first and second record if it matches then it will update as "0".
Can anyone tell how i can do that?

Thanks i advance.
Regards,

Vinoth
 

A:Solved: MS Access Query

Read other 16 answers
RELEVANCY SCORE 82

hi there i have a from containing check boxes if these check boxes are ticked then they add 5 pounds to a bill. does anyone know how to do this with a query.
 

A:Solved: access query

Read other 12 answers
RELEVANCY SCORE 82

hello:

I created a query so that I can create a report. Basically my query has all this fields:

Name 1 Due Received date

Name 2 Due Received date

Name 3 Due Received date

Basicaly what I want is that all fields with a value on name field and due is "YES" and received date is empty, the I want all the names to shoe on the report.

So on my query i put name =Is not null Due "yes" Received date is null

And it doesnt seem to work. Can anyone help
 

A:Solved: Access Query

Read other 7 answers
RELEVANCY SCORE 82

Ive attached an Access database that has a table and a query. The issue is that this database only has a Beginning Balance set to Period 1 and for all other Periods it has only a Period Change number.

I need to take the Period 1 Beginning Balance and Period 1 Period Change to make Period 2 Beginning Balance.

For Period 3 Beginning Balance I then need to take Period 2 Beginning Balance and Period 2 Period Change.
and so on....

So from the table I would need to have the following
Item # 11110
Beginning Balances
Period 1 - 76317.72
Period 2 - 77138.48
Period 3 - 76788.48
Period 4 - 78664.94
Period 5 - 68402.14
Period 6 - 41960.36
Period 7 - 20032.24

I tried to do this in a query but..... I can't figure out the best way to continue - I was only able to get Period 2.
 

A:Solved: Access - Query

Read other 16 answers
RELEVANCY SCORE 82

I am trying to create an Access Query that will find duplicates within a field in a table.

In this field, there will be entries like ABC123 or ABC123-03/03/2009

I need the duplicate query to regonize that the two entries above are the same.

Here is what I have done, but cannot get it to work properly....

First

Field: TNumber
Table: Test

Field: Left(TNumber,6)

Duplicate Query

Field: TNumber Field: TNumber
Table: First Query
Total: First

Field: NumberOfDups: TNumber
Table: First Query
Total: Count
Criteria: >1

Field: TNumber
Table: First Query
Total: GroupBy

I have purposely entered duplicate values within the table to test the duplicate query. It does not indicate that there is any duplicated values within the table.
 

A:Solved: Access Query

Read other 16 answers
RELEVANCY SCORE 82

I have a query (called Ancient Art A) where i have run to see what children are on a specific course. once i run this query i want to be able to transfer a few of the students to a course called (Ancient Art B). but when i change the course title for one person to ancient art b (in the query) all the students in ancient art A get transfered into ancient art b? i know i can manually go into the records and change them manually but i thought it was possible this way?

thanks
 

A:Solved: Access Query help

Read other 6 answers
RELEVANCY SCORE 82

Hi Guy's I am struggeling to make a query work correctly. I have a table with project records and a sub table with multiple comments for each project. I need to make a report by project types but first I need to get my query to return each project record, but ONLY with the newest comment. In other words it is now returning a record for every comment (You can see if you run it)

Thanks, Joe
 

A:Solved: Access Query

Read other 12 answers
RELEVANCY SCORE 82

I need to find records where a field's value is "", or " " or perhaps NULL (i don't know how Access treats those values).

For example, this works to find records where the field's value is "comp":

Code:
SELECT * FROM tablename where item = "comp";
However, the following don't work to find records where the field's value is empty, blank, or perhaps NULL:

Code:

SELECT * FROM tablename where item = "";
SELECT * FROM tablename where item isnull;
SELECT * FROM tablename where item = NULL;

Those queries produce an empty result set.
 

A:Solved: MS Access Query

The correct answer is

Code:
SELECT * FROM tablename where item is null;

 

Read other 1 answers
RELEVANCY SCORE 82

Im having difficutly trying to work out how to bring together 3 different types of infomation.
I have a Mobile Device list table called TblMobileDeviceID which be used to input new Mobile Devices as and when we buy them. I also have a TblMobileSimID, which again, when having a new number it is in the list of sim's so we know what we have.
I then have the User infomation. I have a table with all the Users with Office they are allocated too. TblUserID

Think i need to Create another table with the UserID, MobileDeviceID, SimID...however i just dont know.

I already have the Sim and Device info in my Tables. I want to allocate Jo Bloggs with a particular mobile Device from my list, and Sim. How do i do it?

Would be greatful for some guidence please
 

A:Solved: Access Query Help

Read other 16 answers