ResultID
SampleName
Date
SampleDate
a few others not necessary in this
and tblXRFResultsConcentration
ResultID (many records linked to 1 record in tblXRFResults)
Concentration
CompoundName
What I need to set up is a query that will allow me to generate a report that will give an excel like format, with the column headings being the SampleName, followed by the CompoundName(s) from the other table and the "rows" will be corresponding sample name and concentrations.
I am sure this is pretty easy, but I am stumped!
I'm working with Access 2010 and am trying to use the transferspreadsheet command to output data in a query to an Excel 2010 format file. Here is the line of code:
It works fine and produces the output file but when I try and open it with Excel I get an error saying the format is incorrect. If I change the extension to .xls it opens with no problem but I need it to be an Excel 2010 format with correct extension.
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.
I have researched on here how to print the results of a query to a text file. I put the code in and I get an error on the openrecordset line. The error says "too few parameters, expected 4"
I tried the query in another report I export to excel and the query works.
I tried printing the whole table using "Select * from tbl_Customers" and it works????
Here is the code:
Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordset("qryBell1", dbOpenSnapshot) Dim fs, TextFile Set fs = CreateObject("Scripting.FileSystemObject") Set TextFile = fs.CreateTextFile(pathname, True)
[Code] ....
the query returns 6 fields for printing and can filter based on whether 3 fields are filled or not on the form - Date, Campaign, Status
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 written some code that will output to a spreadsheet in a given location:
how can I rework this code so that the excel output displays on screen rather than saving to a specified location:
Code Written:
Private Sub outputToExcel_Click() DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "C4C Period Final", "\uk.michaelpage.localdfsGroupDataNSCHElite Database ReportsGross Cash For Car.xls" MsgBox "C4C Period Final Exported"
I haven't worked with access in about 2 years, so I'm a bit rusty. I was wondering, what is the easiest way, if any, to output an image stored in an OLE object field to a file, like a bmp to be edited say in paint or something via VB code?
Hi all! I'm not sure if this is the correct area to post this in, but hopefully it is!
I have a Microsoft Access database, and I need to output the content of one of the tables in a specific format-the table contains a Name field, a Description field, a URL field and an Alt Text field, and I need it to end up in a text file in this format:
NAME|DESCRIPTION|URL|ALT|
Where each line of the text field is a different product, and the content of each field is separated by the | symbol (whose name escapes me right now).
I am using a where condition on printing records from a form to restrict output to the current record. I would also like to write the current record only to a file (rtf). Have set up an output to file button which works but writes all records and there is no code option for a where condition.
I can live with this as it is only for backup but a report per record would be a neater solution.
Hello first of all I have learned a lot from this forum so thanks you all who post questions & solutions. Ok I have a table which I use to query info and export to excel. My question is there a way to add sum totals to a few of the columns once it is in excel. I was thinking it would be easier to do a sum statement then just add it as the bottom row while it was exporting. Or would it be easier to do it once it is in excel. Here is my code:
Private Sub Command63_Click()
Dim strSQL As String, strOrder As String, strWhere As String Dim dbNm As Database Dim qryDef As QueryDef Set dbNm = CurrentDb()
If Not IsNull(Me.txtCSONME) Then strWhere = strWhere & " (tblCONSOLIDATED.COMPANY_NAME) Like '*" & Me.txtCSONME & "*' AND" End If
If Not IsNull(Me.txtCSOSLD) Then strWhere = strWhere & " (tblCONSOLIDATED.ACCOUNT1) Like '*" & Me.txtCSOSLD & "*' AND" End If
If Not IsNull(Me.txtCSOARN) Then strWhere = strWhere & " (tblCONSOLIDATED.CONTACT_NAME) Like '*" & Me.txtCSOARN & "*' AND" End If
If Not IsNull(Me.txtCSOCTY) Then strWhere = strWhere & " (tblCONSOLIDATED.CITY) Like '*" & Me.txtCSOCTY & "*' AND" End If
If Not IsNull(Me.txtCSOST) Then strWhere = strWhere & " (tblCONSOLIDATED.STATE) Like '*" & Me.txtCSOST & "*' AND" End If
If Not IsNull(Me.txtCSOZIP) Then strWhere = strWhere & " (tblCONSOLIDATED.ZIP) Like '*" & Me.txtCSOZIP & "*' AND" End If
If Not IsNull(Me.txtCSOSSM) Then strWhere = strWhere & " (tblCONSOLIDATED.REP_NUMBER) Like '*" & Me.txtCSOSSM & "*' AND" End If
If Not IsNull(Me.txtCSOM1) Then strWhere = strWhere & " (tblCONSOLIDATED.PROMOCODE) Like '*" & Me.txtCSOM1 & "*' AND" End If
If Not IsNull(Me.txtSLCYYD1) Then strWhere = strWhere & " (tblCONSOLIDATED.CURRENT_YTD) BETWEEN " & Me.txtSLCYYD1 & " And " & Me.txtSLCYYD2 & " AND" End If
If Not IsNull(Me.txtSLLYYD1) Then strWhere = strWhere & " (tblCONSOLIDATED.PRIOR_YTD) BETWEEN " & Me.txtSLLYYD1 & " And " & Me.txtSLLYYD2 & " AND" End If
If Not IsNull(Me.txtSLPYR11) Then strWhere = strWhere & " (tblCONSOLIDATED.PRIOR_TOTAL) BETWEEN " & Me.txtSLPYR11 & " And " & Me.txtSLPYR12 & " AND" End If
If Not IsNull(Me.txtSLPYR21) Then strWhere = strWhere & " (tblCONSOLIDATED.YEAR2_TOTAL) BETWEEN " & Me.txtSLPYR21 & " And " & Me.txtSLPYR22 & " AND" End If
If Not IsNull(Me.txtSLPYR31) Then strWhere = strWhere & " (tblCONSOLIDATED.YEAR3_TOTAL) BETWEEN " & Me.txtSLPYR31 & " And " & Me.txtSLPYR32 & " AND" End If
If Not IsNull(Me.txtSLPYR41) Then strWhere = strWhere & " (tblCONSOLIDATED.YEAR4_TOTAL) BETWEEN " & Me.txtSLPYR41 & " And " & Me.txtSLPYR42 & " AND" End If
If (Me.PROSPECTBX) = True Then strWhere = strWhere & " (tblCONSOLIDATED.CUSTOMER_TYPE) Like 'P' AND" End If
If Not IsNull(Me.txtSLCLS) Then strWhere = strWhere & " (tblCONSOLIDATED.SALESCODE) Like '*" & Me.txtSLCLS & "*' AND" End If
If strWhere = "WHERE" Then strWhere = "" Else strWhere = Trim(Left(strWhere, Len(strWhere) - Len("AND"))) End If
I have an Access db that schedules the sending of emails with attachments. I run a query on the table that returns all active users I then need to create a separate file for each active user - in the format as shown below. Each file is called "ddmmXX.epe" - where dd=day,mm=month,XX=sequential number starting 01. The file needs to be formatted ascii text file. The file extension must be .epe
I would like to print a report file from a database into PDF format.Currently I am using a macro with a "where condition" to select the particular file which currently outputs to the default printer. Converting the macro to VBA reads as follows:
DoCmd.OpenReport "Receipt - full pay new", acViewNormal, "", "[ConsultID]=[Forms]![Payment Record Type]![ConsultID]"
However, if I do an output to PDF file as below, I can't insert a "where condition" to pick the specific file. Instead, it outputs the whole unfiltered report which is 12,000 pages and 29 MB!
DoCmd.OutputTo acOutputReport,"Receipt - full pay new",acFormatPDF,"C:AccessReceiptPDF.pdf"
How to combine the 2, i.e. have the "where condition" to select the particular report page, but output to a PDF file in a selected directory.
I currently have an invoice report that runs through my database which is e-mailed to clients (automatically through vba), and then saved to a specific file path.
I'm currently having an issue with the saving part of the process with certain clients who have illegal characters in their names - for example, "Client / Other Information" where the '/' is causing the save to fail.
I'm trying to find a way to change the file name of the save file for clients with these illegal characters in their names. I would prefer not to take only part of the name (as some clients are listed as "Town/Village of...") but if there is no other way then there is no other way.
The problem that we are facing is that the after the execution of the statment the query ( qryAllPrems) gets wiped out....i.e the entire sql in the query gets wiped out clean ..
It's driving us crazy as to what could be the cause of the problem...and I would appreciate it if somebody could help us..in this regard.
I have a query that contains Invoices(usually 2 line items per invoice) Taxable and NonTaxable. I need to able to basically export it to excel on multiple worksheets when the Amount of unique invoice number reaches 15.
From all my scouring on the internet I can't find anything with this criteria.
I guess you've all heard this one before, but I'm relatively new to Access, and was wondering if there was a way to export data from a form to an already existing Excel spreadsheet via a form button. Is this even possible in MS Office 2007, or even at all?
Been hunting a round looking for some code that will output my access report to excel but more inportantly add a unique field from the report to the name of the file. example would be [FileName] & [ReferenceNo].xls.
Hi At the moment I am trying to send data ( a query) to a CURRENT Excel file, I do not want it to replace the current file but instead just update the cells of that file to the new values calculated in Access. Is it possible to do this?
Cheers all Bikeboardsurf :confused: :eek: :( :mad: :confused:
:confused: I've created a form within our company database which will track hardware/software requests. I'd like to create a macro, or add code to output the data entered into a notepad file. Nothing extraordinary. I'd like for it to be like this:
Ticket: XXXXX Employee: xxxxx Reason for Request: xxxxx Quantity:xxxxx Part Number:xxxxx Price:xxxxx Shipping:xxxxx Total:xxxxx
Those are the headings of the fields and x's denote entered data. I'd like to keep the headers and have the entered data as well. Is it possible?
Please Help i have been trying to figure this out for some time now and I am really stuck. I need to maniuplate data that is in an excel spreadsheet basically seperating out certain data and putting that information into seperate columns an example of what i am trying to do is this. in excel I have a numbers 117.865.65, 117.865.79, 117.865.60 associated with the Name "Cell Phone in Blue, Cell Phone in Red and Cell Phone in Green " what I am trying to get Access to do it take the part of the number that they all have in comon and put that into a column, then take each of the different numbers and put that into a column, and then put the Names of each item and put that into a seperate column. like this: 117.865 Cell Phone 117.865 .65 Cell Phone in Blue 117.865 .79 Cell Phone in Red 117.865 .60 Cell Phone in Green
anyone that know how i can get access to do this your help woudl be greatly appreciated.
I have a query that runs based on a parameter a user selects on a form.
I want to dynamically create the sendobject file name based on the parameter the user selects.
Example:
My form has a parameter named medicine. When the user checks the checkbox next to medicine it runs a query for records labeled as medicine but sends the e-mail output as the query name. I want something like medicine.txt etcc...
the following code saves my report in a folder called test as a pdf file with the name MyReport. I have a string variable called RepName. How can I make the report get saved under that name.
I tried DoCmd.OutputTo acOutputReport, "Estimate", acFormatPDF, "c:Test&MyReport&.pdf" but it dosent work.
Am trying to export an excel (97-03) file from a query I have. Some of the columns have exported as number values (as its linked via unique id's). I want it to export certain columns as the text columns. I have tried the lookup route but it doesn't seem to make a difference.
