Multiply Parameter Queries!
Apr 13, 2007
i have a Query where i have Criteria for two fields as the following:
Field :Date of Incident
Crirteria: [Forms]![frmSearchStudent&Date]![cboStartDate] And [Forms]![frmSearchStudent&Date]![cboEndDate]
Field: StudentSurname
Criteria: [Forms]![frmSearchStudent&Date]![Combo0]
Occasionally, staff may just want all incidents (records) to be shown for the selected StudentSurname and so, just select the surname and leave the date fields blank.
i tried the following:
Field :Date of Incident
Crirteria: [Forms]![frmSearchStudent&Date]![cboStartDate] And [Forms]![frmSearchStudent&Date]![cboEndDate]
Or: [Forms]![frmSearchStudent&Date]![cboStartDate] Is Null And [Forms]![frmSearchStudent&Date]![cboEndDate] Is Null
Field: StudentSurname
Criteria: [Forms]![frmSearchStudent&Date]![Combo0]
then when i tested this by leaving the date fields blank and selecting a valid student surname it simply returned all incidents (records) for ALL students not the selected one.
is it possible to achieve this? if so, could someone please help!
View Replies
ADVERTISEMENT
Sep 5, 2005
I am try to do all in one queries which has two queries imbedded into.
"SELECT Filter_ID, Channel, SpeedBands, Filter_Channel, Filter_type
FROM
(SELECT [Tbl_Lookup_Channels].[ID] AS Channel, [Tbl_Lookup_SpeedBand].[ID] AS SpeedBand
FROM Tbl_Lookup_Channels, Tbl_Lookup_SpeedBand)
LEFT JOIN
(SELECT [Site_ID] AS Filter_ID, [Channel] AS Filter_Channel, [Type] AS Filter_SpeedBand
FROM Tbl_SiteResults
WHERE Site_ID=" & ID &")
ON ((SpeedBand = Filter_Speedband) AND (Channel = Filter_Channel))
GROUP BY Filter_ID, Channel, SpeedBands, Filter_Channel, Filter_type
HAVING Filter_Channel Is Null AND Filter_type Is Null
ORDER BY Channel, SpeedBands;"
If is not work at all it is just asking that it is invalid join to the main query
Thanks
Sean
View 2 Replies
View Related
Apr 27, 2015
I have the time per item, and I would like to multiply that by the quantity, so I can tell the total time. But I cant find in access how to * data/time by a number.
View 3 Replies
View Related
May 21, 2005
How to multiply a group of records in an Table (eg. I have a table with tow fields Id and Value, how to multiply all Value records)
View 1 Replies
View Related
Jun 15, 2006
Does anyone know how to count a field and multiply the answer automaticaly.. so if i have a count function on number of names repeated in a table via nameID the result would be presented in the new query with how many times name repeated multiplyed by a defualt set number.instead of name repleated 3 times, it be 30 times..using a query. any help appreciated..thanks
View 5 Replies
View Related
Mar 17, 2008
Hi all, wondering if anyone can help me.
Im trying to a run a query to see the results of my table. but i am experincing problems, when use the SUM function.
I have constructed a query with three fields.
1) QTY.
2) Cost
3) Total.
what i would like is to be able to Multiply QTY & Cost to get the total.
so whan QTY Is 0 and Cost is £0.00. the query recongises it and changes the total should then be £0.00.
any advice on enabling the query is welcome.
View 12 Replies
View Related
Mar 22, 2005
Hi. I'm new to Access and I wonder if someone could help me with this problem I am having while constructing a database. We need to create a database of reservations to a program we offer. We will need to store the quantity of reservations per person. What we needed to do is multiply the quantity of reservations by the value of each reservation and get a 3rd field with the final amount. So we need a field with the result of the multiplication of the quantity of reservations by the value of each reservation, which is a fixed amount. I'm having problems getting access to do this, I get an "error" message on the resulting field. Can somebody help me with this?
Thanks.
View 3 Replies
View Related
Apr 27, 2005
Currently i have:
If CIS = False Then
CIS_Payment = "£0.00"
Else
CIS_Payment = "£20.00"
End If
which works ok but what i want to do but do not know the correct way/syntax is:
If CIS = False Then
CIS_Payment = "£0.00"
Else
CIS_Payment = "Labour_Total*18/100"
End If
Where CIS_Payment and Labour_Total are currency fields, and CIS is a checkbox updated using an update query with the same info as detailed in another table (Couldnt get the conditions to work referencing the checkbox in the other table, so duplicated the box in both tables and used a query to sync)
Any ideas???
View 2 Replies
View Related
Sep 10, 2013
I have a text box in a form that is producing a number from the query that the form is based on.In another Text Box i want to show the Value of the first Text Box multiplied by 3 because i am going to hide the first text box to only show the calculated number.
At the moment i have this in the control source of the text box [CountOfJobID]*3 but all i am getting is #####...I have set the format of the text box to General Number.
View 5 Replies
View Related
May 14, 2007
Hi, I have a problem, I want to split up form input into several rows but I don't know how. Alright here's the deal: I have a textbox in a form were I write down all the ipaddresses for one computer and all the addresses end up in the same row in the same field in the table, I want the addresses to end up in different rows but in the same field, how do I do?
I might also add that i'm pretty new to access.
View 2 Replies
View Related
Feb 24, 2015
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] .....
View 9 Replies
View Related
Aug 8, 2015
I'm trying to multiply the values of two number fields together, but it is not working. I used to be able to do this easily in earlier versions of Access. Here's the problem:
On the form, I need field A x field B to appear in field C. I've tried this as an expression in the build event function on the control, but it is not working. My code reads as follows: = (field A) * (field B).
View 3 Replies
View Related
Jun 24, 2014
My question is that can we do multiplication of data of two columns and result is automatically displayed in third columns in datasheet view.? Is it possible ?
View 1 Replies
View Related
Jun 19, 2015
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.
View 11 Replies
View Related
Sep 18, 2014
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]...
View 1 Replies
View Related
Nov 5, 2004
I 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 Related
Jun 29, 2007
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!
View 2 Replies
View Related
Nov 15, 2007
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,
View 1 Replies
View Related
Nov 22, 2004
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 Related
Dec 18, 2006
I'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
View 3 Replies
View Related
Jan 9, 2006
Hi everybody,
I have a report that is based on an underlying query.At the query level,I
want to define parameters of date data type so that records to be displayed
should fall between DateA and DateB. ie Between[Enter Date A:] And [Enter Date B:].
I want the DAteA and DateB to be only days and Months of a year.
Say my DateA = 01 Jan 06 and my DateB = 31 Dec. 06
But I want only the Parameter to be Between[Enter day and Month A:] And [Enter day and Month B:].
How can I write a query with the two parameter values?
Thank you
dfuas
View 1 Replies
View Related
May 24, 2007
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.
Thank you in advance for any help or advise.
Janet
View 9 Replies
View Related
Apr 25, 2013
I have a table that has a date column. I am assigning one group of students a test date of 4/29 and another group 4/30. I don't want access to change the column or ask for a parameter during the query, but no matter what I do, it keeps asking me for a parameter. I just want it to use the data that is already in the column.
I tried changing the column name in Excel to DOT from Date. That didn't work.I also tried changing the column to text, so that it wouldn't be a "date." That didn't work.
View 7 Replies
View Related
May 15, 2013
This statement returns a "date" each time the event occurs. I want to have a parameter between [Start Date] and [End Date] so I can pull events for a specified date range. I have tried this "Expr [EventDate]" Between [start date] And [end date] but it returns "null" results. I am using access 2010
EventDate: IIf([tblEventException].[EventID] Is Null,IIf(([qryEventCartesian].[PeriodTypeID] Is Null) Or ([qryEventCartesian].[PeriodFreq] Is Null) Or ([qryEventCartesian].[InstanceID] Is Null),[qryEventCartesian].[EventStart],DateAdd([qryEventCartesian].[PeriodTypeID],[qryEventCartesian].[InstanceID]*[qryEventCartesian].[PeriodFreq],[qryEventCartesian].[EventStart])),IIf([tblEventException].[IsCanned],Null,[tblEventException].[InstanceDate]))
View 2 Replies
View Related
May 21, 2013
Is it possible to have a parameter in the field row of a query? That way, the user could select the field they want to query against without going into the design view of the query.
View 9 Replies
View Related
May 29, 2015
I have a query that retrieves a list of all "Open" issues/projects that I'm working on. I've added a couple of columns in the query to calculate the age of each project (comparing Now() to Start Date), and using a comparison between the last time I updated the project to Now().
The problem I'm having is that the second calculation is based on the first calculation. As a result, the second calculation causes prompt box "Enter Value Parameter" to pop open whenever I open the form based on this query.
I would like to know how can I open this form without the prompt box popping up?
View 5 Replies
View Related
