Hi, first post here - may be a simple question, but it's been a bit of a while since I used Access, so my skills are getting rusty...
I have a form with a number of checkboxes writing to Yes/No fields in the underlying table. I then want the data to populate a report via a query. However, in the report, I would like to show only those fields where values equal TRUE/YES. In other words, for each record, only the fields where the user has checked the boxes should be displayed in the report. If possible, this should involve variable height for each record in the report, but this is not a must if too complex. It is, however, imperative not to show fields where the value is FALSE/NO. In other words, if all the fields has to go into each record in the visual layout of the report, is there a way to make the fields with negative values not visible? Any ideas how I can achieve this? I'm not sure if it can be done with just a simple Query, or with the SQL builder, or if it has to be done with VBA scripting (which would perhaps be applied to the report object rather than in the query?).
PS. If you know the answer, but are wondering how much you may need to dumb it down for my benefit, I have used Access a bit in the past, but not professionally. I have never bothered to learn VBA for Access, but have coded in VB and VBA for Excel, though, and know a bit of SQL and Java, which may give you a bit more of an idea what level to pitch it at :o).
I have a field countyID which is a code field and a lookup table County
I want to show the text for county on the datasheet, should I base the datasheet on just the company table and use a combo to get the county text - or should I have query that brings in the county text.
Could the experts tell me the "correct" way of doing this?
What are the performance issues of the two methods. Is which method I choose dependent on how many records are in the lookup table?
I have an on click event to mail a report which works. I want to change the text to include data from a table.
I changed the code to include the field 'office' from the table 'Checks' but get an error saying 'Object required'.
Code is : Private Sub cmd_mailreport_Click() Dim office As Object Set office = Checks.office DoCmd.SendObject acReport, "checks", "PDFFormat(*.pdf)", _ "info@company.com", "", "", office & " Daily Check - " & Date, "Attached is the report for the office", _ True, "" End Sub
Access 2007-10 Listbox created: List62 (I know I need to rename it, but for now) Multi-select: Extended Row Source Type: Field List Row Source: qryFieldList Open Query: qrySelectedFields (I added primary key to first column, just to have at least one destination field).
Goal: to select multiple fields within listbox, click on command button to open query "qrySelectedFields" with selected fields from list box.This is the code I have on the command_click:
Dim varItem As Variant Dim strSQL As String If Me.List62.ItemsSelected.Count = 0 Then MsgBox "Please select one or more fields.", vbExclamation, "Healthcare REIT" Me.List62.SetFocus End If
[code]....
It does absolutely nothing - doesn't add the fields to "qrySelectedFields", doesn't open the query, notta.
In my tables i have used calculated fields. one of the fields is to "total expenses." In a report, i need to show the sum of all the "total expenses", the filed populates in the report but the cents are missing. for example if the amount is 6080.40 it shows as 6080. how can i get around this? I have tried changing the decimal point value to 2 at which point the value turns to 6080.00 when it should be 6080.40 (i am a beginner at this i am assuming the answer will probably involve c++ or visual basic's, two concepts i am not familiar with.)
i am quite a begginer at this so sorry for the simple question
I am running a access query from a vba code. IN the vba code I am writing the sql command.
I am getting the criteria for the sql code (WHERE command) from a number of combo boxes in a form. The whole thing works quite well when I have something selected on all combo boxes but it does collapse when one of the combo boxes has the default value which is "" nothing.
for instance when I have WHERE domicile.IdDomicile = 5 works fine. 5 comes from the combo box
when there is nothing selected in my combo box the sql code would be WHERE domicile.IdDomicile =
and then I get an error msg saying the query is wrong.
Question,
what is the command in sql that instruct the query to return all records. I tried using "*" when the combo box was not selected but then I dont get the records that are blank, only the ones with something writen in it.
Is there something I can write in to a query which will tell me whether the 1 April 2006 appears in between two dates (which are entered by a user?)
For example, if they put the first date (which is one field) as 25/3/06 and then they put the end date (which is another field) as 4/4/06 - I just want the query to produce the word "yes"?
I have set up a Form to report Client acitivity by department Code for a given month. Users have the option within the form of selecting a Department Code.
These department codes are derived from a department Code Table and set accordingly within my Row Source properties.
If I wanted to select multiple departments for a particular report. how can i allow for wildcard entries i.e if several department code begin with letters OD*?
I have an application which includes a process that creates an excel sheet with some data. For this I have set reference to the excel object library. I am now packaging a demo of the application with Office Developer and including Access runtime. The question is what to do about this reference, since when I look at the file that it uses, it shows Excel.exe. I don't suppose I can legally be sending that file along with my program. Is there a different reference that I could set that would give me what I need? I noticed this in testing because it is the only reference file that wasn't automatically included in the package.
I am using DoCmd.TransferDatabase to transfer 2 tables from one database to another. This works fine, however, the user permissions for those tables do not carry over with the table and I have to go into the database and set them as they were in the original database.
Simply, I want to allow complete access for all users to the tables in the second database.
I have a table with a field QuotationRef containing a series of values which include a year indicator (eg Q11101/06, Q11102/06, Q11103/06, Q11105/07, Q11106/07, Q11108/07) - where the characters after the / denote the year a Quotation was started. I need to be able to setup a criteria filter expression in my query design view that will allow me to select just the values in the QuotationRef field that relate to the current year. I can set up a query that picks up this year's Quotations by using "Like "*/07"" but that will only solve the problem for this year and I need to setup a query that will change when the year changes. How could I do this, I have tried incorporating "Year(Now())" in the expression but this would filter on /2007 rather than /07. Any advice please?
basically from what i can understand, there are 3 options.
1. Where join field from both tables are equal 2. All records from table 1 and only those from table 2 where they match 3. All records from table 2 and only those from table 1 where they match
how do i go about having all records from both tables showing?
I have a site, written in ASP that works perfectly on my test server (IIS 5.0). My pages find the include file containing the database string several directories removed from the file calling the database and displays all results correctly. I have uploaded to my public server, and placed the correct database connection string for that server into the include file yet only some pages display correctly. The problem appears to be linked to the directory depth of the file calling the database, although i've never had this problem before.
e.g c:/dir1/dir2/dir3/file.asp when linked to c:/dir1/dir2/dir3/include/path.asp with <!--#include file = "./inclide/path.asp"--> I get a connection and all results displayed!
however, when file,
c:/dir1/dir2/dir3/file.asp is linked to c:/dir1/dir2/include/path.asp with <!--#include file = "../inclide/path.asp"-->
I get a connection error, (and because this is on my subscribed server I dont have access to error logs.)
I would appreciate any help in helping me understand this problem.
along with data in each row, i want to include the record number in the ouput of a query. is it possible?
once i run the query and view the ouput, i can see the standard access database record counter at the bottom or if i select a row, i can see the row number at the bottom.
how can i include that counter in each row of output from the query?
I have a table with a yes/no field. It's linked to a form with a yes/no box, but the outcome I want is that if the form box is unchecked, the query returns all the "yes" records - which is trivial; however I want to return ALL the records if the form box is checked.
I am creating a PDF version of a Report so that I can send it as an attachment. I use the following code to do this:
Private Sub EmailWorkOrderQuote_Click() On Error GoTo Err_EmailWorkOrderQuote_Click
Dim stDocName As String Dim mFilename As String Dim mEmailAddress As Variant Dim mCompanyName As String
[Code] .....
I first create the report with a specifically generated name and save it to a folder.
I then do a SendObject to send the actual email. Unfortunately, it generates its own name for the file that it attaches and I don't think it is possible to specify an attachment.
Anyway, how to include the name of the PDF report in the actual report preferably in the footer.
I want to include a validation on a form. I have an auto number field called ID and a field called Nature Of Incident. I only want to have the auto number generated if the Nature of Incident is NotNull.
I have a report "AFR" that includes workorder fields. I would like to also include the subform information for the parts and labor.I was able to put the subforms on the report but it will only display the first part and not all parts from the parts subform.
I have a report that is based off a query that has several hundred lines with values that I sum up. I also have another query that has manual adjustments. I would like to add the total adjustments for a particular customer to the end of the report and use it to calculate the final value.
for example: 1 20 2 10 3 07 Total for customer 37 Total manual adj 13 Total after adj 50
I dont want to include it in the original query because i dont have a value for every line of the query.
I have a Continuous form where I am trying to add a calculated feild that allows us to see how many days a client was in hospital. There is a start and end date. I want to see the following: If start date and end date are the same, this should show client in hospital for 1 day, not the default of 0. If start date 5/1/13 and end date is 5/3/13 it should show 3 days in hospital. If start date is 5/1/13 and there is no end date, this should show 7 dates (from start date to today's date or current date). Currently I have
A new query I am building, after entering an expression, gave the " You tried to execute a query that does not include the specified expression.." error. The new query is based on and uses expressions from another query. The error message listed the expressions from the query upon which this query was based. Copying in all the fields referenced in the error message did not work.
Here is the SQL code, the offending expression in red:
SELECT [Test Grade Results].[Class Number], [Test Grade Results].StudentNumber, Count([Test Grade Results].[Test Number]) AS [CountOfTest Number], Avg([Test Grade Results].[41Grade]) AS AvgOf41Grade, Sum([Test Grade Results].Wghtd41) AS SumOfWghtd41, [GPA]=Sum([Test Grade Results]![Wghtd41])/[Test Grade Results]![CuumWeightDivisor] AS Expr1FROM [Test Grade Results] INNER JOIN [Test Parameters] ON [Test Grade Results].[Test Number] = [Test Parameters].TestNumberGROUP BY [Test Grade Results].[Class Number], [Test Grade Results].StudentNumber;
Wghtd41 is the expession that is included in the error message.
Is it possible to include a bound OLE object in Access in a mail merge using a Word document? If so, can someone tell me the procedure? When I include the OLE object as a merge field, and complete the merge, only a code shows, not the document it is linked to.
Each month I use Access to import text files and export cleansed data to Excel.
The information contains (i.e. forecast data) that has 6 months of data. Each month, the data is rolled over to include the new month and delete the oldest month of data.
Is there a query(s) or module that can automate this process?
