I am trying to set up a create table query using parameter called Year. For each year I input , I need to create a new table with the suffix of the year. for example, if i input year 2011, i want to create a table called budget_2011, if i input year 2012, i want to create a table called budget-2012...
View RepliesHi,
I have a field in one table that needs the sum from fields in another table.
How do I go about doing that?
Thanks,
Trena
I am new at this and have been struggling for days trying to solve this problem.
I created a database, using the wizard, to keep records of my orchids.
This resulted in a table. I keyed in the information on each plant.
Then I created a form, using the form wizard. Everything worked great. I could switch between the form and the table views.
The other day, the form no longer contained the information from each of my records. When I tried to switch to datasheet view (from form view), there was no longer a datasheet listed.
When I go directly to the datasheet (when starting Access), I can see all of the records (186 of them), but if I try to switch to form view - no form is listed.
Please help.
I'm sure this is a simple issue, but so simple I can't find the answer.
I have a recipe DB and have calories and fat and fiber fields, I want to have another field which will display a point count for a formula (calories/50+Fat/2+fiber/5) but the catch is if the fiber number entered is over 4 I only want the calculation to use 4 max.
Not sure how or where to create this formula to populate a field in the DB.
As you can tell I'm new to all this and any help would be appreciated.
Thanks in advance
Mike
I have a parameter that I need to get a table contents between dates. In the query:
Birthdate: XXTable: criteria as follows: Between [Enter Start Date] and [Enter End Date:]
When run it gives me the Error - ! This expression is type incorrectly, or its too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables
I have tried almost everything. The formatting of the Birthdate is x/x/xxxx or shortdate. Will this affect the input thus affecting the outcome of the query.
I'm using Access 2003...I have a query that searches a parts table by description:
Like "*" & [Enter in Part Description to search] & "*"
I need to add another search to this query, I added another field to the parts table call manufacturer. I add this field to the above query and added this parameter to it:
Like [Enter in Manufacturer] & "*"
I then added a manufacturer to one of the fields for test purposes. For some odd reason this doesn't work. If I take out the manufacturer parameter and don't enter in anything into the part description the query returns all the records. When i type a description into the part description field the query returns the proper records. When I add back the manufacturer parameter and enter through both parameters only the one record returns showing the test record instead of all of them. If I put a part description in and enter through the manufacturer parameter no records show when they should.
I've tried adding a test field to a different table and tried a different query using the like parameter. The parameter works in a field already in the table but won't work with the new field I added. I've done compact and repair.
I have a list (table) that I've created in sharepoint 2010.I link to the sharepoint table with Access 2010 to update mass amounts of items at once. Some of the queries have no problem updating the sharepoint items, but other queries require me to "Enter Paramater Value."
In this particular queries; I'm trying to populate field A with dates from field B, when field A is null.
---------------------
UPDATE Table 1 SET Table.[FieldA] = [FieldB]
WHERE (((Table 1.[FieldA]) Is Null));
--------------------
When I run the above, I receive the "Enter Parameter Value" input box.All records have Field B populated (it's actually the created date.)
The goal is for field A to be populated with the values in Field B, without the query asking for parameters.
Note; I can go in each individual record and update them via access, one at a time. But it's the running of the update query that failing.
Edit: Removed spaces in table and field names.
I have table which store set of number
table: parameter
field: Branch
550
660
770
880
I want to use enter query criteria so that it can filter all record from parameter table, How can I do? or any VBA code can serve same purpose?
I have a crosstab queries which uses the date query parameters. However, when I go to my Export command (code is below), it ask me to enter the date parameters (start date and end date) twice. What do I have to do so that the system will ask me to enter once only?
Code:
On Error GoTo Err_cmdTest_Click
'Must 1st set a Reference to the Microsoft Office XX.X Object Library
Dim dlgOpen As FileDialog
Dim strExportPath As String
Const conOBJECT_TO_EXPORT As String = "qryEXPORT"
[Code] .....
I have 5 queries that I am running. The first query has the date range parameters set in the field area that I need to run and each additional create table query is based off the results of the previous query.
1. Which is better to use to run all of the queries in one simple step? A macro or a form? I am exporting the final table to excel so that I can make some additional adjustments off of it.
2. How would I setup the date range parameters for the first query if I were to use a macro without going into the query itself and updating the date field? I tried setting up a macro to run the queries by using the OpenQuery action for each of the 5 queries, but I cannot figure out how to do the date range.
I'm trying to perform a DELETE query at run-time. The criteria for deleting records is somewhat complex due to various one-to-many dependancies between the tables, overlapping of date fields etc., so in order to restrict the DELETE to only those records which qualify, I've set up a load of smaller queries to produce the list of eligible records.There is only one parameter required - a 'CutOff' date which is arbitrarily chosen by the user and is available in a textbox control on an open form when the code is triggered.
Here is the main DELETE query :
Code:
DELETE DISTINCTROW [tblComments].*
FROM [tblComments]
INNER JOIN [qryCommentsToPurge]
ON [tblComments].[CommentID] = [qryCommentsToPurge].[CommentID]
WHERE [tblComments].[CommentID] = [qryCommentsToPurge].[CommentID]
I've put qryCommentsToPurge and all the cascading sub-queries below - have highlighted in red where the parameter value is required (it is used in the queries to identify the records which must be retained, i.e. not deleted)Each of the individual sub-queries works fine and correctly (they return the correct population of records that they were designed to)If I run the sub-queries manually, I am prompted to provide the CutOff date, as you would expect - and the recordset returned in each case is correct for that date.
If, however, I run the DELETE at run-time (with the form containing that textbox open and containing a valid date value) I get a 3061 error :Suggesting that the queries can't pull the parameter value from the textbox on the form. But I've stuck a watch in the debugger on [Forms]![frmArchive]![txtCutOffDate] and I'm getting the correct date value? The form is open and available at the time.So why can't the query see it? I use these kinds of control references elsewhere in other queries and they work fine. I don't understand why it's a problem here?
Code:
SELECT [tblComments].*
FROM [tblComments]
INNER JOIN [qryIssuesToPurge]
ON [tblComments].[IssueID] = [qryIssuesToPurge].[IssueID]
ORDER BY [tblComments].[CommentID];
[code]...
How do I attach a zipped copy of my database when asking a question?
thanks
I want to attach a pdf file at the end of my word document by clicking a button from form.
View 3 Replies View RelatedIs there a way that I can attach a database that I am building/working on, to an email, so that I can send it to myself and work at home or at work. My work computers do not allow media.
View 2 Replies View RelatedThis may seem to be a couple of strange questions, but nevertheless, I have not been able to find anything that have answered my question, so, please help me:-).
I am currently working in Access 2003 (nop, an upgrade to 2007 is not an option:-/) and I am making a database containing my companys employe, their workplace, plans for the future etc etc.
I would really really really like to be able to attach a picture of the employes in in this database, but I really canīt find a simple way to do this, not even in MS help.
Furthermore, I would like to be able to attach files, such as documents, so that I cah doubleclich on them, whereafter they open in etc Word.
Is this possible, (in a simple way) or do I have to go deeper into Access.
Thanx from a desperat administrator:-)
I have a report (a contract) which I can send as a pdf file. However, on the printed version of the report, I have the "terms and conditions" on the reverse side. The "terms" are already printed; the report is merely printed on the reverse side.I've saved the "terms and conditions" file as c:AccessFiles erms.pdf. What I would like to do is when I execute the code to send the contract as a pdf attachment via email, the "terms.pdf" file is combined with the report as one pdf file. Is this possible?
View 4 Replies View RelatedI have been asked to set up a parameterized query using 4 fields (ID Number, Surname, Christian Name and Treating Dr/s). When I search Microsoft On-line under parameterized queries it gives a tutorial which basically set out a simple query with "prompt" parameters in the Criteria field. When I search under the Access Help it gives me a totally different explanation - ie. setting up a cross-tab query first and then defining parameters. Is this a loose use of terminology or is there indeed a difference. Help please!
View 1 Replies View RelatedI have searched for this and have not come up with an answer as yet.
I have query that is accessed by entering criteria in a text box and then the user can click a button which will use the criteria entered in the query and then export it to an excel file.
Is there any way i can get the option to export the file and then open up a new email and attach the excel file
I have been trying to figure out a best way to attach files to specific records. Up until this point have been using Hyperlink to link files that have been stored in shared folders. This seemed to be a very good option for quiet sometime; but some users wish to play god ! Since I have to give them RWX permission on the folder; they go into the Shared folder and either rename the file or move it around or worse, DELETE the file permanently. Thus messing up the hyperlink.
Now I am planning to use the wretched Attachment data type in Access. I know it will bloat the DB, but this seems to be a safer option. As I can control DELETES/EDITS. So I was thinking to use a stand alone DB, just used for Attachment tables. As these records will not be accessed everytime.
My problem is, I remember vaguely using two linked backend DB files, put a lot of strain on the front end. Thus making the connection very slow. Sometime even upto 10 seconds to simply load the login form.
My idea is to establish a persistent connection to the two DB files on front end opening. Thus the connection is always there so it might not be that slow !
Or is there something I could look into to manage the Files/Attachments more efficiently, other than Hypelinking?
Is there a way to label a parameter in a query?
I need to enter a start date for a query - the parameter - and the end date is always six months later.
Currently, I use;
Between [Enter Start date:] And [Enter End date:], but this is not idiot-proofed.
How can I make the second parameter be a calculation based upon the first; i.e., [First Param] + 180 days?
Thanx for any help!
Hi there.
I am looking to rationalise some of the queries I have in my access application.
Let's say I have two forms that query the same table based on a value they have in a combo box.
Currently I would have a separate query for each form and set the criteria to the name of the control on the respective form.
My question is whether I can use just 1 parameter query and use this for both forms, passing the necessary criteria - similar to how you would with an SQL Server stored procedure.
I have done this in the past programatically but wondered whether it is possible to do this within the GUI?
One solution is to not have any criteria in my query and then define an SQL statement in the rowsource of my form/control that queries my original query i.e.:
"SELECT FirstName, LastName from Qry_Customers WHERE QryCustomers.CustomerID = ????"
This works but it not as easy to maintain as having separate queries (same reason why I prefer to use stored procedures rather than definining my SQL statements within my code)
Also are there any negative performance impacts in defining the rowsource in this way (opposed to just setting to an existing Table/Query)?
Thanks,
I have a query field with the criteria: [enter state]. I would like to be able to enter multiple states (ie: IL or WA or AZ) but the query only functions when one individual state code is entered. Any ideas how to fix this? Thank you.
View 1 Replies View RelatedI'm new to this forum and am looking for help in the following:
I'm trying to create a parameter query (using Access 2003) and would like to display just the data that are not null. I tried to insert "and Is Not Null" after the [parameter name] under "Criteria", but it still list null data. Any help will be greatly appreciated (please note that I am a basic Access user and do not do visual basics or anything other than the quick command buttons or the wizard.
Many thanks in advance
I've created a custom ribbon and it works great however I want to create a separate ribbon and attach it to the report. On this particular ribbon, I'd like the export to excel, export to word and print functions to appear ONLY for reports. Not all reports will need to be exported; it depends what the user does. I've created this XML and I'm not receiving any error message. It loads as expected but my group is blank/empty. Still confused.
View 2 Replies View RelatedI have hp scanner whose twain driver has the option to scanning in the pdf document.
Microsoft WIA 2.0 have save scanned document only to graphical format.
I want automated (on click any button control) to scan multipage document from glass source or ADF to pdf document and attach it to attachment field.
I'm found VB Module for accessing TWAIN compatible scanner but it is call twain software and scan to bmp format with assigned name of file.
Me need it also only use the pdf format .
I have a form where I need a button that will export the current record being viewed to a TXT file and attach to email, I want to specify the To, CC, Subject and Body of the email, if possible putting a flag on it also.
I'm not doing very well, so far I have
Code:
Private Sub MailExec_Click()
On Error GoTo Err_MailExec_Click
Dim stDocName As String
stDocName = "Executive Incidents"
[Code] ....
This now doesn't seem to be working, if I remote email.To= then it does however this only creates a blank email with attachment.