I have a couple of questions which are sort of related. They both revolve around Parameters with a Query/Report.
Here is a bit of background. The source Table is formatted in a way similar to this:
Code--Account#--Expense Description--Jan--Feb...
When running the Query, and associated Report, the user is prompted to input an Account# via a Parameter that I set up. The dialog box pops up with the word "Account#" and a box for the user to enter by what account that they want to restrict the Report. [Question 1] - Is there a way to make this dialog box contain a drop-down box with a list of predetermined accounts instead of the aforementioned entering of an account number? If not via this dialog box, is there another way?
Also, multiple accounts roll up into a single P&L line item. For example, let's say that accounts 1234 and 5678 both make up "Equipment". [Question 2] - Is there a way to run the Report in such a way to return all accounts which roll into a line item? I want to have the ability to run by either an individual account or the P&L line in total.
it also prompts twice when i try printing or try to save the current page as a pdf and also when i switch record? i think its a problem with the subform as it always is trying to fetch data. would there be a way to make it so that the subform and the form are linked so that the parameter query prompts once and the information stays there rather then having to prompt again when printing or saving as pdf?Two more things - on my total value box i wish to be able to always show 2 decimal places
The code already in place is :
Net : =Sum([TotalValue]) VAT: =Sum([TotalValue])*0.2 Gross: =[Text8]+[Text10]
(Net is Text8 and VAT is Text10)
i have already specified that the decimal place value is 2 instead of auto but it still doesnt work - if the number is a whole number i still wish to see .00 at the end of the field.One last thing - Would their be a way to carry over the information from my delivery note to my invoice note? which would save a lot of time retyping the parameter query.
I this is a simplifed version a saved query called "qryTest"
PARAMETERS [gUserID] Long; SELECT [Id],'Complaint' AS IncidentType FROM tblComplaints WHERE tblComplaints.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Complaint');
UNION ALL SELECT [Id],'Fall' AS IncidentType, FROM tblFalls tblFalls.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Fall');
how to i call the result from code or from the query manager and include the parameter so there is no prompt that comes up?
like Me.ListNew.RowSource = "qryTest " & gUserID &";" or Select * from qryTest , 31 - where 31 is the value fed to the parameter
??? does anyone know? I know it can be called in APS like this:
Dim Conn Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open strConn ' strconn is my connection string set rsP=createobject("adodb.recordset") conn.qryTest 31 .....
I can't believe I can't do it from the query string! Heeeellpppp!
I had an issue with writing LIKE statements in query criteria yesterday [URL]....
The answer they gave worked perfectly when I only used a single table in the query. But as soon as I did an INNER JOIN with two other tables, now I get parameter value prompts when I open frmSearch, and instead of seeing ALL my records when the controls are left null, I get only the first record in the table.
Here's the SQL of the query, can you point out what I messed up? NOTE that this SQL was 'written' by Access.. as I used the Query builder to set all the 'Like or Is Null' statements, then clicked SQL and sorta formatted the code so I can see what I'm looking at (instead of superthick wall-o-code):
Code: SELECT tblPeople.name, tblPeople.num FROM (tblPeople INNER JOIN tblAddresses ON tblPeople.name = tblAddresses.name) INNER JOIN tblPets ON tblPeople.name = tblPets.name
[code]....
Basically, this is a searchable database of participants in a pet-adoption program, along with the participants' pets history and address history (hence the linked tables as opposed to additional columns in one single table for pets and addresses... there are more than one in some cases). The frmSearch allows a person to run quick searches based upon ANY item in the database, such as name, pets, addresses, pet age, pet type, county of residence, etc. I need to be able to pick ANY field on frmSearch and type a value, and have the qrySearch return records for ANY record's related column wherein any part of it matches what I typed.
The statements as written worked PERFECTLY right up until I added the INNER JOIN. Now I get a set of parameter value prompts for every field on frmSearch that's referenced in the SQL for EACH table I linked to tblPeople, and if I leave everything null and click Search, I want to see EVERY person, but I'm only seeing the very FIRST person in tblPeople.
In trying to make a certain job more simple, I need to be able to allow a user to click on a shortcut to a query and then enter several parameters. Two of them will be Start Date and End Date. I understand how to do that portion; however, the users must also be able to type in multiple page numbers and display the records where those page numbers appear (one field). For instance, ordinarily, I would just go in and type "46" or "48" or "50" and it would display all of those records where the field contained one of those 3 numbers. I would like the Parameter Query to prompt the user to "Enter Page Numbers" and maybe even give a format; however, when I try this, it doesn't work no matter what syntax I use to enter the page numbers.
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?
On a pop up form I have a list box. The row source for the list box is
Code: SELECT tblWebOrder.WebOrderId AS [Web Order No], tblWebOrder.CustomerWebOrderNumber, Format([WebOrderDate],"dd/mm/yyyy") AS [Order Date], tblCustomer.CustomerName AS [Customer Name], tblArea.Area FROM (tblCustomer LEFT JOIN tblArea ON tblCustomer.[PhysicalAreaId] = tblArea.[AreaId]) INNER JOIN tblWebOrder ON tblCustomer.CustomerID = tblWebOrder.CustomerId WHERE (((tblWebOrder.Processed) Like [Forms]![frmSelectWebOrderToOpen]![txtOrderStatus]) AND ((tblWebOrder.WebOrderDate) Between [Forms]![frmSelectWebOrderToOpen]![dteStartDate] And [Forms]![frmSelectWebOrderToOpen]![dteEndDate]+1)) ORDER BY tblWebOrder.WebOrderId DESC;
I have the following "On Click"event on the list box
Code: Private Sub lstWebOrder_Click() lngWebOrderId = Me.lstWebOrder.Column(0) DoCmd.Echo False DoCmd.Close DoCmd.Echo True End Sub
When I click on some of the records on the list box the form closes. On some of the records I get a parameter prompt to enter:
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:]) Site - (criteria = Like "*" & [Enter Site:] & "*") Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
I would like to display the value of my parameter query into the title of the report.How could I do this on Access 2007?So far I have made another field in the query and called it ParaDate: [JobDate]
I posted this within the Reports as well, but since this has a little bit of VBA decided to ask this here as well.
I have a report that is created from the following query (qryTotalProjectHours). What I am trying to do is get the total hours spent on Tasks within a given time period. A given Task can we worked on by multiple individuals and hence I need to find a way to aggregate the hours spent.
Code: SELECT TasksEntries.Project, TasksEntries.Task, Sum(TimeTracker.WorkHours) AS TotalHours FROM TasksEntries INNER JOIN TimeTracker ON (TasksEntries.EmployeeId = TimeTracker.EmployeeId) AND (TasksEntries.TaskID = TimeTracker.TaskId) GROUP BY TasksEntries.Project, TasksEntries.Task
I accept the start and end dates in a form and pass it like shown below. WorkDate is a column in the TimeTracker table and is not present in any other table.
When the report is invoked, I get a box where it says "Enter parameter value" for Workdate..
Is there anyway I can get rid of the prompt? I never thought you needed the column name in the SELECT statement to be able to run this.
I should add the I tried the query with the WorkDate hardcoded in there and it worked fine and returned the correct results
I have attached the cut down version of the database that I am working on.Open up the frmManagerReport Form Leave the Employee drop down empty Enter the start and end dates ( I have used 4/1/2015 and 4/30/2015) Select the 3rd option "Generate Tasks by Total Hours" click on Run When you do that you will see the prompt come up. Enter any date and you will see the report. The report generated uses the results from the query and does not filter on the date selected in the form. This is what I have been struggling to fix since yesterday but have reached nowhere..
I have a report that is created from the following query (qryTotalProjectHours). What I am trying to do is get the total hours spent on Tasks within a given time period.
Code:
SELECT TasksEntries.Project, TasksEntries.Task, Sum(TimeTracker.WorkHours) AS TotalHours FROM TasksEntries INNER JOIN TimeTracker ON (TasksEntries.EmployeeId = TimeTracker.EmployeeId) AND (TasksEntries.TaskID = TimeTracker.TaskId) GROUP BY TasksEntries.Project, TasksEntries.Task;
I accept the start and end dates in a form and pass it like shown below. WorkDate is a column in the TimeTracker table and is not present in any other table.
When the report is invoked, I get a box where it says "Enter parameter value" for Workdate..
Is there anyway I can get rid of the prompt? I never thought you needed the column name in the SELECT statement to be able to run this.I should add the I tried the query with the WorkDate hardcoded in there and it worked fine and returned the correct results
I have a parameter query for looking up a specific lot number in my database. The lot number table has 4 related material tables that deal with material issued, reworked, and rejected for that lot number. They lot number table is in a one to many relationship with the related tables.
I have a report based on this parameter query with 4 subreports. I want the report to display the lot number data and sub reports to display the related data in the material tables (all linked by the lot number defined in the parameter)
Is it possible to put a combo box with a drop down list in the Enter Parameter Values of an update query so that the user selects from the list instead of typing the required value?
Normally I would run a query like using 'CurrentDb.Execute' so that no user prompt occurs, but how do I avoid prompts when I need to run an update query?.
I'm using DoCmd.OpenQuery "qry_UpdatePW" with no joy!
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
I have a database called Tables.mdb with a Make Table Query called Qmak_Tables that I want to run nightly.
I have set up a Scheduled Task to run Tables.mdb every night. I have created a Form that loads on start-up. I have created a Macro that runs the Make Table query when the form loads. I have also de-selected the confirm record changes/document deletions/action selections in Tools > Options > Edit/Find menu so the query won’t be stopped by a prompt that needs manual input. Here’s the catch:
After the query runs, it displays the prompt, “The existing table will be deleted. Do you want to continue?” How do I disable this prompt so my task can continue to run?
Between [Enter Beginning Date] And [Enter End Date]
Run and enter 07/01/2014 and 06/30/2015
The return one record dated 07/01/2014
Run again enter 07/01/2014 and 12/31/2014
Return all records between those dates.
when I put in dates for 2014 to 2015 it does not return all records between those dates only the records for the beginning date, is there something I am missing asking for records from one year to another?
I have this query that finds people based on a given id number that they own. The ID number is in the format of (0000-0000) The tables have input masks so that when entering the ID number all the user has to worry about is entering the numbers. I have a report for that shows every customer in the database and i want to be able to copy and paste the (0000-0000) portion of the ID number but the query is only able to find customers by (00000000) format is there a way that i can get the query prompt to ignore the dash in between the numbers or would it be easier to remove the input mask and enter everything manually?
