so can the output work above based on N/A?
can the chart director recognise it or cannot recognise?
i try it but it gives error when it shows N/A in access
so how to resolve it when N/A is needed for value for Price..
or got another alternatives?
I have two access tables that I am joining. In the opty table I want all records that meet the WHERE clause and in the activity table I want any/all that meet the join clause. The opty table has a one to many relation to the activity table. The join works correctly, it is the display that I am having problems with. I want the output display as an opty then under that all the activities associated to the opty in ASC date order. My problem is I have a memo field in the opty table I want to display but if I put a GROUP BY clause in the sql then I only get part of the memo field (I know why this is happening). So, I tried to leave out the GROUP BY in the sql and use the OUTPUT GROUP BY in the display section. This works except if there are numerous activities associated to an opportunity then the opportunity (sometimes) will be displayed twice with different activities listed. Can some please help.
If I use the the ORDER BY, and put CDATE(acty_begin_dt), I get a NULL error. Which, I assume is because some of the opty's may not have an activity associated to it.
Below is the code I am using and it works fine except, the activities are not in the correct order. (I am using this query in a cold fusion application using access db) Thanks
SELECT opty_row_id, opty_name, opty_created_by, opty_stage, opty_type, acty_type, acty_created_by, acty_status, acty_opty_row_id, acty_desc, acty_comments, opty_primary, opty_create_dt, opty_close_dt, opty_est_value, opty_desc, acty_row_id, acty_begin_dt, acty_end_dt FROM opty LEFT JOIN activity ON (opty.opty_row_id = activity.acty_opty_row_id) WHERE (Trim(opty_primary) = '#form.rpt_user_name#' AND (CDate(opty_create_dt) between #get_first# AND #get_last#)) OR (Trim(opty_primary) = '#form.rpt_user_name#' AND (CDate(opty_create_dt) < #past_date# AND (Trim(opty_stage = '01 - Prospecting') OR Trim(opty_stage = '04 - Opportunity') OR Trim(opty_stage = '03 - Qualification')))) GROUP BY opty_row_id, acty_row_id, acty_begin_dt, opty_name, opty_created_by, opty_stage, opty_type, acty_type, acty_created_by, acty_status, acty_opty_row_id, opty_primary, opty_create_dt, opty_close_dt, opty_est_value, opty_desc, acty_end_dt, acty_desc, acty_comments ORDER BY CDate(opty_create_dt) Desc
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.
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.
Hi everybody. I got a query that displays object name and object type of access 2000 db. Unfortuenly it dose not display the linked tables objects(linked to tables in acccess 2000 db). could an expert tell me how i can fix this query so it displays linked tables object as well.Thanks
SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectType FROM MsysObjects WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1 Or (MsysObjects.Type)=5 Or (MsysObjects.Type)=6) AND ((MsysObjects.Flags)=2097152 Or (MsysObjects.Flags)=128 Or (MsysObjects.Flags)=0 Or (MsysObjects.Flags)=16)) ORDER BY MsysObjects.Name;
Hi everybody. I got a query that displays object name and object type of access 2000 db. Unfortuenly it dose not display the linked tables objects(linked to tables in acccess 2000 db). could an expert tell me how i can fix this query so it displays linked tables object as well.Thanks
Code:SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectTypeFROM MsysObjectsWHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1 Or (MsysObjects.Type)=5 Or (MsysObjects.Type)=6) AND ((MsysObjects.Flags)=2097152 Or (MsysObjects.Flags)=128 Or (MsysObjects.Flags)=0 Or (MsysObjects.Flags)=16))ORDER BY MsysObjects.Name;
I am trying to use a switch statement in a access query and i be leave i have the code right but for some reason it will only output a string i need it to output an long integer . (All the columns that are in the statement are Numbers).
This error occurs at the OutputTo line. This code is part of an user form with a button “save as PDF”. When this code is run, the report is visible as an example (as meant to be) on the screen. The code stops at the OutputTo line.
When I use DoCmd.OutputTo acOutputReport, StrReport, acFormatPDF, StrDocumentName,, False the problem still exists.
I tried to save the report manually by Preview > Export > PDF. The preview is OK, saving as PDF results in the same error 2501. This may lead to the conclusion that this problem is not caused by the VBA code.
I verified whether user write rights are OK, and whether a default printer is available. Both are OK. The report consists of 1 page. When this very same application is used with Access 2010, everything functions as expected.
Including check boxes (representing the query fields) in an interactive access form, in order to decide which fields should be visible.
I think the solution is building an invent in VBA for each check box, however I'm not an expert in Access VBA and don't know how to write the code.
In the example that I've uploaded, in the Form1, when I run the query, all fields are displayed, i.e. VENDOR, REGION, CUSTOMER and MATERIAL are displayed. How can I manage it in the form with a listbox to display only the REGION or MATERIAL for instance.
I have been tasked with creating a database to log employee suggestions and then automatically present reports around the site. I have developed the database and it works OK and can output *.PDF reports, but I'm having problems with how to display these reports. My idea was to output the reports automatically as *.TIFF or *.JPG files and have them stored in a folder that the My Pictures screensaver uses. The reports (in fact, any site report stored in this folder) can then be shown on any screen dotted around the site when it goes into screensaver mode. However I can't find any way to output/convert to a picture file.
The other option is to create a webpage that rotates through the saved PDFs, but this isn't ideal as the PC users will have to load the webpage to display the reports.
I have a sql string that is used to populate a form and now I'd like to export that set of records to a spreadsheet but I can't get 'OutputTo' to work. It wants a predefined query name - !? Any suggestions?
guys the below works a treat on a single qry -(pinched from samples) however I will have 20-30 qry that I want in this 1 workbook all on seperate tabs . Now I have also tried getting this done cross refer to "qryto excel automation" and have had some particual succes - but does any one know how to do this
I've created code to export data from a query to a spreadsheet and it works fien. My question is, is it possible to set up an SQL statement in code and export the results of the SQL to a spreadsheet instead of using the saved query?
Im reading Selecting the Data from Running Microsoft Access 2000, pp. 87-94. The confusing part is the input/output explanation. When I think of input I think of typing, keying, or entering raw data into the computer. Output to me is information displayed or printed.
Okay, here is what was printed:
A data item is an input for a task if you need to read it from the database (but not update it) to perform the task. For example, a customer name and address are some of the inputs needed to create an order. Likewise, data is an output for a task if it is new data that you enter as you perform the task or that the task calculates and stores based on the input data. For example, the quantities of items you enter for an order are outputs; the shipping address and phone number you provide for a new order are outputs as well.
The above is confusing me. Can anyone explain it better so this dummy can get it? Thanks!
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?!
If I open a query in Access and uses the "Export to Excel" button everything works out just fine. But when I want to make the export with a button using the code: DoCmd.OutputTo acOutputQuery, "qryToExcel", acFormatXLS
I only get 255 chars from the PM-fields to excel. Can anyone help me solve this problem?
I also want to make some filtering, with code string, to the query before I export it, could use some suggestions there also.
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.
Attached is a real simple database. What I want to be able to do is insert new payment details, and I have a couple of issues.
I have two clients and need to be able to select which client I want, select the payment details from the drop down list and enter in some appropriate amount.
Its been a while since I've created a db from scratch so this is obviously where I am having my difficulties. However I think I need to use a sub form.
How can I input the details as above, and how can I always get the combobox to default to the first entry in the list rather than defaulting to zero?
I've got a huge problem. Lots of questions I need answers to, but I'll start with this. I have a client I was doing some web design and basic Access work for, and now they want this sorta-complicated database and I'm getting lost.
It's a big customer complaint database for a big manufacturing company, a local branch. I have all this data in one table because that's what they want.
One of the items is whether or not the company has a debit with a particular complaint, and, if so, how much it is.
The thing is, this debit number (currency) might change (update), but they want a YEARLY and MONTHLY report on "how much money we've lost, altogether."
Where do I start with that?
Big thanks to anyone who can lead me in the right direction. I have some experience with Access, but I'm by no means an expert or programmer.
I have a control button on a form that outputs the current record to disk using an on click event. The user currently has to select the format (rtf) the path and the filename and I would like to be able to automate this process.
The format should be rtf, the path will always be the same and I would like the file name to comprise the unique record number (4 digits) followed by the current date string (format ddmmyy).
I think is just a matter of setting some parameters after the DoCmd code event that saves the report to file but I have had a go but can't get the syntax right.
Any ideas how to automate this process to eliminate the risk of user errors?
I am having a problem getting Access to output to an excel file.
I want to take a form that I have that has 20 or so records with 6 or 7 fields for each and put it into an excel file that is formated the way I need everythign to look.
I also need it to make the file name such as CCCAAAMMDDYYYY (3 letter company abbreviation,3 letter initials and then todays date) I am thinking that the company abbreviation will be pulled from a query, then initials will be a form that opens, and the date can be pulled from access' date function.
I tried using vb code and doing an output to comand but that did not work. I am confused someone please help if possible.
