Altering SQL Query When Launching Report
Jan 9, 2006
Ok, so I have a form that I am using to apply certain parameters to a report. The form allows the user to specify one or more fields to sort by and then launches a Report with those statements in the Report's where clause using DoCmd.OpenReport. However, when certain fields are searched, I need to add information to the SELECT and FROM parts of my Report's query. I can't figure out how to do this with Access, if it is even possible. Otherwise, I will need to find a way around this.
Thanks for your help,
Alex
View Replies
ADVERTISEMENT
Jan 10, 2006
Hi. Basically, I've been having trouble with this for a while now. I thought I got it working yesterday, but it turns out not. Here is the situation.
I have built a form that contains several combo boxes and a few text boxes for input. The user enters some or all of this information and clicks "Generate Report". Upon clicking this button, the form needs to launch a generic report that is populated with data using the search terms specified in the form. However, to add complexity, only certain parts of the SELECT and FROM clauses can exist depending on which options are completed on the search form.
I tried: DoCmd.OpenReport stDocName, acPreview, sqlQuery, whereCond
Where sqlQuery changed based on which settings were specified. However, this did not help because the actual query that belongs to the Report never seemed to change: sometimes it was missing columns.
I tried also to set rptNominationsList.RowSource = sqlQuery, but that also doesn't seem to work. On this one I get "Object required".
Thanks in advance!
Alex
View 5 Replies
View Related
Jan 28, 2014
What I'd like to do is place a Calendar on a form, then either click or double click on a specific date on that calendar to pop up a report showing records for only the date selected on the calendar. I have an ActiveX Calendar on my form, but I don't see any Event Procedures for Click or Double Click, just Update, Enter, Exit, Focus. Also, if that is not possible, can you select the date, then click a command button to do it instead?
View 1 Replies
View Related
Apr 14, 2015
I have created a report that prints a transaction input via a form. All the data has been posted to tables while the document details are still on the form. The source for the report is a query that gets its "Document ID" from the current form as its CRITERIA. This works fine. I click a button and the report prints.
Now I've added a datasheet that lists all the "Document IDs" that have been posted within a given date range. I've added a Macro to open(reprint) the same report when any Document ID is double-clicked. This is working except that it prompts for a Parameter Value and references Forms!DocDataEntry!txtDocumentID. (Note: this is the name of the original data entry form which is no longer open)
If I manually type the Document ID (that I just double-clicked) in the parameter box, the report prints correctly. But this shouldn't be necessary.
I know I'm missing a WHERE clause on the Macro that opens the report but nothing I've input works. I can't even hard code a document number. Actually any Where clause provided prompts additional parameter boxes to open and they ALL require the SAME INFORMATION... the Document ID.
I'm thinking that the Criteria on the Document ID in the query should be changed to allow a Document ID from any active source.
View 9 Replies
View Related
Aug 19, 2004
I want to link files that are associated with a row in a table. I have added a field to the table that contains the file name and have added a field on my form to display the filename. I have set the "Is Hyperlink?" option to yes on the properties panel. But when I click on the link, nothing happens. I get no error message, nothing. I want to be able to link, word, powerpoint, visio, etc. Any suggestions?
View 2 Replies
View Related
Nov 9, 2005
Hello, I use a database at work that I made. Unfortunatly, I did not add a field for numbering the items that a customer orders. When entering an order It is important to my customers to keep it in a certain order. how do I change it so it adds a numbering to all the past orders as well? I've been trying to think of a way for months and cannot do this on my own. Thank you to anyone who takes the time to help me out. If you have any questions I'll try to answer them the best I can.
View 6 Replies
View Related
May 12, 2007
Hello.
Your help is very much appreciated.
I want to launch the "partial replica wizard" from code.
Can it be done?
(It is located in Tools -> Replication ->partial replica wizard
Thank you.
Cheers
Roy
View 5 Replies
View Related
Jun 10, 2006
I have a form designed in DS mode. If I open it directly (double click on it on the Panel), it works fine
When opening it programmatically (from a button in another form) it shows just ONE record, in spite of having room enough for several records.
May I get some help to check what am I doing wrong?
thanks
View 2 Replies
View Related
Dec 13, 2005
I have a line chart built in Access. Is there anyway I can change the line colors like I can in Excel? The default fuschia, bright yellow, and bright blue are horrendous.
View 3 Replies
View Related
Dec 7, 2007
Hi
I'm trying to produce a database that will convert Ebay download files to a format more suitable for import into Sage Line 50. I have a table "Customers" that has all the customer information in it.
One of the fields is "Country", this lists the full name of the country the customer lives in. However, Sage requires countries to be listed as a two letter abbreviated identifier, GB, US etc.. I have another table "CountryCodes", with two fields, the countries full name and its abbreviated identifier. I need to be able to compare every "Country" field in the "Customers" table with with the "Country" field in the in the "CountryCode" table and change it's value accordingly. I also need the "Country" field to change to fixed value (ZZ) if the country is not found in the "CountryCodes" table.
Any help would be greatfully recieved.
View 1 Replies
View Related
Mar 12, 2008
I tried to revive an old post in the "General" section with this question, but it doesn't look like many people are looking at it. I figured it wouldn't upset the posting gods too much if I posted it here, since the the question is concerning queries.
I'm starting out with an excel sheet that looks like this:
Company Name..........Employee
Jones Electric............Kyle Brown
Jones Electric............Chris Ford
Jones Electric............Matt Daniels
Is there a way for access to make it look like this?
Company Name..........Employee..........Employee 2..........Employee 3
Jones Electric............Kyle Brown........Chris Ford............Matt Daniels
Thanks a lot, guys. I'll be sure to link the old post to this one.
View 6 Replies
View Related
Oct 17, 2007
Let me start by saying I am very, very new to Access, but have been assigned a report to create.
I have the following textbox in my report:
=[Location]+(Chr(13) & Chr(10)) & [callnumber]+(Chr(13) & Chr(10)) & [Online Availability]+(Chr(13) & Chr(10)) & [PF Subject]
I'm using this format so that my 4 fields appear in a list and don't leave blank lines if the one of the fields happens to be empty.
The problem is that I need to alter or substitute some text.
First, in the location field, I can have several different entries, but if the entry is 'Circ', I don't want that text to display and I don't want a blank line either.
Secondly, in the online availability field, if there is any value in the field, I want whatever text that is to change to something like 'Online - Yes'. If there is no value in the field, nothing should display.
I think the use of the Iif function should work, but I can't seem to write it correctly. Help!!
View 5 Replies
View Related
May 22, 2015
I have a database containing a mixture of upper and lower case input. I can make upper case appear on the screen by using > in the format box in the property section for each field. This works great but the same old uncorrected stuff remains on the database. How can I get the database to store the corrected upper case data after I have entered it in lower case?
View 11 Replies
View Related
Sep 19, 2013
I have a form that allows a user to complete a stock take. I would like to stop other users from receiving or despatching stock while a stock take is in progress.
Is there a way I can lock a table, or stop users adding or altering records that match certain criteria. i.e. don't let users receive or despatch stock from with a locationID of 'A'.
View 4 Replies
View Related
Mar 1, 2014
I am new to MS Access. So I am making a database for an automobile warehouse. When a new car arrives at the warehouse, it is allocated a specific parking slot (using a combo box available on the 'New Arriving Car' form).
However, once a specific slot has been allotted, the end-user should not be able to allot the same parking slot to other arriving cars, until the original car has left the warehouse so that the parking slot becomes available once again. Currently, the parking slot combo box shows all parking slots whereas it is supposed to display only unoccupied parking slots. How can I enforce this? (Note that there is a separate form called 'Exiting Car', which is filled when a car is leaving the warehouse).
Fyi, each car is referred to by a unique 17 digit code - so this is the primary key. Also, there are 120 parking slots available in all, with the slot identifiers ranging from A1, A2...A12 to J1, J2..J12 (10 x 12 = 120).
View 5 Replies
View Related
Dec 21, 2013
I have a series of unrelated access databases and I would like to have one 'super menu' where i can click a button to go to different databases.
View 3 Replies
View Related
Sep 2, 2014
I have a three-column query that tells me how many hours I have available per week for a given resource type (e.g. welders). I have a second three-column query that tells me how many hours of work I have planned per week for a given resource type.I'm hoping to produce a query (the source for a report) that will show resource types in rows and twelve months in 24 columns. the first column for each month will show how many hours I have available for all my resources, the second column for each month will show how many hours I have allocated.
How do I produce a query that will combine the other two queries, inserting zeroes where necessary considering that for any given week I might have allocated work to a resource that isn't available (because the inconsiderate buggers think they are entitled to holiday) or I might have a resource that has no work allocated (because I'm incompetent)?
View 8 Replies
View Related
Oct 18, 2005
Hi...
I have a query that when I run it normally (just click on it) then it runs fine. (It is a union query, getting it's data from 8 other queries (who has their dependancies)
But when I want to run a report from it, Access gives me an error saying "query is too complex".
I am flattered, but I would prefer access to work than say I write stuff that is too complex for it. :cool:
Any ideas?
I am confused by the fact that it runs when I double click the query, but the report bugs it out.
-Reenen
View 1 Replies
View Related
May 28, 2005
Hi all,
Member Table
attribute: Name,Date Join,Email
* the format of my date is 23-Mar-04
How do i exact the 'month' from the date. eg 23-mar-04, the month will be mar.
The poutput of my report should be as follows, therefore i need to exact the month of the date, is there a way in access that can perfrom this. THANLK FOR THE HELP.
Member Report
Month : Mar
Name: Alan
Date Join: 23-mar-04
Name: Tom
Date Join: 26-mar-04
Month: Arp
NAme: Mike
Date Join: 05-Arp-03
Name: Wilson
Date Join: 23-Arp-05
View 1 Replies
View Related
Aug 21, 2006
I have a table that contains transaction info including the date of the trans and the date of the order. Some orders do not have a transaction date yet. I'd like to have a report that shows a total dollar amount on all the orders and also shows the total dollar amount of orders that do have a transaction date.
It seems like I would somehow need to be able to have the report use two different queries, but I don't know how to do this.
Can anyone tell me how to do that?
Thanks.
View 2 Replies
View Related
Jun 16, 2007
I am new to Access and appreciate advise to design a report showing six months of transactions that will rollup the ending balance of January to February's beginning balance and then from February to March, etc. I design a query and a report but not able to proceed any further. Attached is a zip file with the Access file and a spreadsheet of the required report. Thanks in advance for any help.
View 2 Replies
View Related
Sep 3, 2007
I have many queries in my DB and would like to run a query to ascertain if some are unused and so can be deleted. Is there a query that can do this?(Clearly - if there are forms/reports with dependancies on some of these queries this needs to be accounted for).
View 3 Replies
View Related
Nov 3, 2007
I’m trying to write a query for a monthly report.
The table that I’m using has the following fields; CASE_ID, DISTRICT_OFFICE, TOTAL_ARREARS_ON_CASE.
I want the monthly report to tell me how many cases (count) in each DISTRICT_OFFICE have arrears (1) between 100-5000 (2) between 5001-10000 (3) 10001-15000.
Can this be done in one query and can you please show me how to write it?
View 4 Replies
View Related
Aug 5, 2005
How would I got about with this problem. What would the code be for this command button. I have got two combo boxes that contain Month(cbo_Month) and Year(cbo_Month) and located in them are month names and month number e.g. Jan 1 Feb 2 etc and year 2002 02 2003 03 etc in 2 columns. These are on a form that the user selects the month and year they want to show the expiry date of that item and then clicks on a command button that will look at a query with the expiry date column to show the results. For example there is a item that expires and the end of June 03. So the user goes to the form and selects Month Jan and Year 03 from the combo boxes and then clicks on the command button and this will then show a report with this item. I have tried everything with this and have ground to a halt. Hope this can be solved or guided. Have tried dateserial. Also what happens when they want the month Feb when it is a leap year. Any examples would be a help. Cheers
View 4 Replies
View Related
Nov 22, 2004
I have created a dispatch log that records the calls we receive. I need to create a report/query that calculates the number of calls for each call type (IE: Disorderly) and during which shift it occured on. These numbers are later figured into the department statistics.
I can query the duplicates but I cannot figure out how to sum each entry and seperate them by shift.(6a-2p, 2p-10p,10p-6a)
Any help would be appreceated. The access box the IT department has does not explain what I need to do with any clarity.
Dave
View 2 Replies
View Related
Feb 10, 2005
I have about 60 Query completed, each with its own statistical
percentages. Hoy can I make all these Query apear in one report.
Can I make the querys link and create and update a table or query?
Im stumpt?
View 1 Replies
View Related
