Hi All
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"?
Thanks
Maria
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
=DateDiff("d",[EStartDate],[EEndDate])-([EStartDate]=[EEndDate])
but it only shows 1 if the start date and end date is the same. I need it to include the start and end dates typed in its equation.
Hi
Bit of an Access beginner and am trying to sort something out for work - not sure why they've asked me!
I've created a query to search on a couple of items using drop down boxes on a search form I created. This bit of it works fine, I used this site http://www.fontstuff.com/access/acctut08.htm and copied what he had done. This is fine.
I now want to add a date search to the same query. I know I can use Between [..] AND [..] but if I leave the boxes blank it finds no records. I'd like it to search and include all.
Ideally I want to include 2 extra text boxes on my form that I can put a to and from date in (or not put a date in and it find everything).
Hope that makes sense, please can someone do me an idiots guide?
Many thanks
Phil
I have written a user defined function that calculates the end of the current month. This I named EndOfThisMonth. It works well as a function. Now I would like to use it as date criteria to include in a query. The function is included as such EndOfThisMonth().
The field on which this function is to enter as a criteria is another calculated date function called Due.
When I run this query I get an error message saying Undefined Function 'EndOfThisMonth' in expression.
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).
Thanks for any input!
Hi everyone,
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.
Any other suggestion for this
Many Thanks
K Regards
I am putting together a query-based form for an auction, that needs to include an image of each item retrieved.
One of the columns in the source table contains the name of the picture file (e.g. "pic001.jpg","nopic.jpg") to be displayed for each row retrieved.
How do I link this text with the presentation of that image on my form?
I have a pretty sound knowledge of SQL and I'm fairly comfortable with Access.
All contributions welcome!
I have a list of codes:
K101
K101A
K101B
K101C
K101D
K101E
L101
L101A
L101B
L101C
L101D
I want a query that displays all codes that dont have a letter on the right.
This may well be a simple question.
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*?
Thanks
Paul
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.
Thanks,
Tom
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.
Does anyone have a solution to this?
Thanks
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?
Hi,
i've got a query that is linking 2 tables.
i'm having problems with the join properties.
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.
I'm using an Access database.
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'm having a mental block on this one.
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.
View 5 Replies View RelatedI 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.
View 1 Replies View RelatedI 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.
If you copy a field from a table or query without highlighting the information. It automatically copy's the column name aswell.
This has proven to be very annoying for some of my co workers. So I was wondering if there is a way for access to stop copying the column name aswell.
So, Is there a way for us to copy field information, just by selecting the field it self. Without copying the column name?
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.
I am using a datasheet to show company info -
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?
Thanks Paul
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.
View 2 Replies View RelatedHi
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?
Would greatly appreciate any tips on this one!!
Lucas
I have a table with a string field for a street. It might have data such as:
Bleeker Street
or Bleeker St.
I want to create a query that will enable the user to search for a string by entering a parameter. Is there a way to construct a query using wildcards for a string so that if the user types in "Bleeker" both the above values will be returned?
Thanks.