I have created a db with 10 linked tables from 10 db's.
I then build 10 queries associated to these linked tables.
The 10 queries filter data by start date and end date.
The data generated from each query is then exported to excel (each query has its own sheet in the woorkbook).
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"
Every time I run a query that I have created it asks me to input Expr1 and Expr2 in an enter perameter value pop up box. I don't enter anything, just click okay and the query runs as expected.
Is there a way to get rid of these? (and maybe more importantly, why do they appear?)
I am trying to mail merge from word 2013 to a query in access 2013. I initially had problems displaying the query until I came across a solution regarding connecting via DDE. I can now link to the query but it then asks me to 'enter parameter values' from the query. I have already run these values into the query via a form which ends up in a report. (incidentally I have left the report open). If I manually enter these parameters it works fine.
I cannot sort below query in descending order by absolute value. If I do not use sort, all works fine but as soon as I try to sort by absolute value I get message to 'Enter Parameter Value'. I tried to replace Abs([Variance (W2 - W1)]) with filed name AbsoluteValue and still the same result ;(
Code:
SELECT [Query Union].[Stock Code] AS SKU, [Query Union].[Pallet No] AS [Pallet No], [Query Union].[Batch No#] AS Batch, IIf(IsNull([qW1 SOH].[Physical stock]),0,CDbl([qW1 SOH].[Physical stock])) AS [W1 Qty], IIf(IsNull([W2 SOH].[Good Stock]),0,CDbl([W2 SOH].[Good Stock])) AS [W2 Qty], [W2 Qty]-[W1 Qty] AS [Variance (W2 - W1)], Abs([Variance (W2 - W1)]) AS AbsoluteValue
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.
'WHERE ((OperationalRiskEventTable.DateReported)>=Forms!U pdateForm!UDateBegin And (OperationalRiskEventTable.DateReported)<=Forms!Up dateForm!UDateEnd)'
in a query by form.
The problem is that you have to enter a date in the between values for results to show. If I don't enter information into a different field such as Full Name but I enter in 40 into Age then everyone that is 40 years old will show. On the other hand if I enter 40 into the Age field but I leave the Date Reported fields empty then no results will show.
How can I change it so that I don't have to enter dates into the date reported fields for results to show?
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.
Date From - >=[ENTER Start Date] Date Till - <=[Enter End]
The results work fine to show which vehicles are booked between these dates, the only thing is I want to be able to show the vehicles that are not booked for the given dates in the parameter query is this possible?
I am trying to create a parameter query to return dates that have 2 years remaining.
For example I have dates for when mortgages expire, and I want to recognise the dates that have two years remaining using a parameter query but I can't figure out if I use DateAdd or DateDiff.
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 have created a query so that two fields have a range in them i.e.
One Field called "Price" There are two text boxes on the seach form called "price1" and "price2" The query reads
Between [price1] And [price2]
A second field called "Date Ordered" There are two text boxes on the search form called "date1" and "date2" Field called "Date Ordered" Between [date1] And date2]
I have used the AND operator to join the first and second range. The user will fill in the criteria from a form with text boxes. This form is unbound but as soon as they click on the Search button, it will open up the query with the parameters in as above.
However, if the user were to type in a price but not a date the query shows no results. Results are only shown if a price and a date are inputted.
The OR operator will work but it is not what we need. We need to show results if a price is entered but not a date and vice versa. The fields are coming from one table.
Please can anyone advise? The database is a library management system so the price and date are the price of any books and the date that the book was ordered.
Is there a way to have one parameter in a query effect multiple fields?I have a table that details service calls on our equipment. There are columns in that table for each part of the equipment that get marked, depending on what needed serviced (i.e. software, printer, keypad, monitor, etc). I would like to be able to run a query that will show all the service calls where one part of the equipment was serviced multiple times without have to enter multiple parameters or write a union query (there would need to be 11 unions or 11 parameters for each part to be captured). For example, I would want to know how many pieces of equipment had 3 or more service calls on their software. I want to run the query without having to say that I don't care how many service calls were performed on the printer, monitor, or keypad. I would like to be able to say "Software >3" and have the query run on that information.
I have a DB that consists of Movie titles and the multiple dates ( as many as 10) on which they will be used in the coming year.I built a flat table with 10 date fields.Then tried to build a relational table with just movie title and dates linked to ID.I cannot work out an ability to SEARCH the Database for a SPECIFIC DATE and get returned a listing of ALL Movie titles that will air on that date,
When the user enters the invoice number it populates the ship, consignee, billing address information. I have the list box to display line items for that invoice.
Currently I just have them enter the invoice number twice.
Is there a way I can take the user input and apply it to my other query?
I have over 800 films which I've converted from DVD to iTunes format. I have added them to iTunes and extracted the data to Excel. From Excel, I have imported the data into MS Access.
So, now I have a movie database of my films. See attached fields for all tables and the Relationships.
As you can see, I have created a separate Genre table as this will be my main search parameters.
I can run a simple query which returns the search results for example 'Comedy'. My problem is, I am wanting to do a search for films with for example 'Comedy' and 'Drama'. Whichever way I try this, I don't get the right answer. I either seem to get duplicate records or no results returned.
I attach the query I am running which works for 1 Genre only!
I am not sure whether I have some fundamental problems with it and it's better to start from scratch?! I have the raw data from Excel which I could use to start again.
I have a query that is supposed to be showing todays data (as it is labeled) but it keeps pulling today's number date (i.e. the 1st, or the 17th) for every month, when i only want June 18th not including jan/feb/march etc. 18th.
I started running this query in may, that's why it only shows may and june's data.
I'm thinking it's definitely a problem with the criteria but check out the attachments and let me know
I have a list of dates and I want 1 query which counts the dates between numerous criteria. for example, colum 1; dates between 1/1/14 and 16/02/2014. Colum2 between 17/02/2014 and 15/04/2014.
I have a form and it is related with a query and by clicking ok I got the required results in Access 97. But now I have converted to Access 2002 and whenever i enter values in the form and click ok I get a Dialog Box "Enter Dialog Box". I read MKB article but it did not help me at all. Please help me out I am not getting any idea at all to solve this problem?
I have searched other threads and cannot find one that helps solve my problem. So, I hope someone can help.
I have a query which runs ok until it is filtered. I have tried using criteria to filter the info I need but this returns an 'Enter Parameter Value' message. Running the query without using criteria works okay but using a filter in the report itself returns a 'Data Mismatch' message.
The report filters fine on fields until it gets to one that has been changed from text to number. It also includes an Nz formula. The formula in the is field is: CLng(Nz([order_no],0)). Other fields related to this also appear to be affected.
I need the CLng formula as the field is needed to calculate other fields using a zero instead of a null which would otherwise cause an error. I was given the formula on this forum yesterday, (thanks Bob Larson and AlanJ10), which was great in solving the other issue but is this is what is affecting filtering, (ie. a confusion between text and number?!?!) I have also tried converting the result of the calculation I want to filter back into text using CStr but this does not make any difference.
I have checked the field names and also renamed fields that were 'Expr' (as in default names for calculated fields), and cannot see any problems.
I have a query and one of the fields/headings i have it create is a "cost" total where it does a dlookup from a defaults table.
I then have a "listprice" which is antoher field in the querry of which the data table sits.
I want to take the [listprice] and minus the [cost] without re-creating the dlookup function...
I need this to help with an APR calulction using the RATE function which if i have it do all the dlookups it says I have exceeded the limitiation and will be trumcated. So if i use the name [cost] it works but i have to hit enter because it says "Enter Parameter Value".
I have the following query that gives me this halt. If I enter without responding to it it opens in Datasheet view as intended. It wants me to enter Query2.EmpID. This is my SQL statement.
SELECT qryPolicyByDriverName.CWSPolicy, qryPolicyByDriverName.EmpID, qryPolicyByDriverName.DriverName FROM tblTrainingPoliciesNotCoveredDrivers INNER JOIN qryPolicyByDriverName ON tblTrainingPoliciesNotCoveredDrivers.CWSPolicy = qryPolicyByDriverName.CWSPolicy;
I don't know why this is happening. Thanks for looking at my problem.
ok im am not sure if this is in the right place but it seems to be a form problem. i have a form that has a textbox(text1) which is used to enter a supplier name, the user then presses enter and another form opens up with all the supplier details.ok. the code i have to do this is on the afterupdate of text1 which is unbound is DoCmd.openform"SupplierDetails",,,"[Supplier Name]=" & Supplier name when i use this code and the enter key is pressed an `Enter Parameter Value` dialog box appears with the supplier name under the blue title bar.if i then enter the supplier name again it opens the second form and displays all the details i want. If i use the code DoCmd.openform"SupplierDetails",,,"[Supplier Name]"= me.Supplier_name it opens up the second form without the dialog box but it doesnt display any details. is ther any way to adjust the first code so the dialog box isnt displayed and just goes straight to the second form and displaying the details. i have gone through as many posts as i can but cant seem to find a fix.
i'm creating a search form giving the end user a range of controls to use when filtering/searching data. See the image.But, i think my range search (using the textbox) to put in a lower and upper limit...is preventing this from working. In fact, when i put data into all the controls, no data pops up in my subform.
My query data source can also be seen...showing you how i've handled teh null entries. (i need to put in a null 'handler' for the two textboxes?)
