Using "Or" In A Parameter Query Prompt
Aug 1, 2005
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.
Any ideas?
View Replies
ADVERTISEMENT
Aug 27, 2014
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.
View 6 Replies
View Related
Jan 4, 2013
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.
View 14 Replies
View Related
Jun 29, 2005
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!
View 3 Replies
View Related
Mar 29, 2015
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.
View 8 Replies
View Related
Jul 15, 2014
I have a form using a query as a control source so that is prompts the user for a parameter when they open the form.
I also have a listbox that is calling the same query.
The issue I am having is when I open the form it will prompt me twice to enter the same parameter.
Tables:
tblship
tblconsignee
tblbill
tblinvoicedetails
Query
qinv
Form
frminvoice
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?
View 3 Replies
View Related
May 8, 2013
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:
Forms!frmSelectWebOrderToOpen!txtOrderStatus
Forms!frmSelectWebOrderToOpen!dteStartDate
Forms!frmSelectWebOrderToOpen!dteEndDate
I inserted the echo commands to prevent this, but this has not worked!
Why am I getting the prompts? How do I prevent them?
View 4 Replies
View Related
Aug 12, 2014
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.
View 14 Replies
View Related
Jul 20, 2005
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?
Tony
View 10 Replies
View Related
Feb 17, 2006
:confused: I have to build a query to do the following
my table consists of 7 fields
cg
price
gross
open
IM
state
sdate
I want to build a query that will prompt the user for 3 of those fields:
price , gross , IM
BUT:confused: when prompted I want the user to have the capability of changing the operators ie: > , < , = etc.
one time they may want
price > 100000.00 , Gross = 50000.00 , IM < 8
the next time they run it they may want:
price < 50000.00 Gross > 45000.00 IM = 4.5
Is this possible
View 14 Replies
View Related
Jun 29, 2006
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!
View 2 Replies
View Related
Jul 15, 2014
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?
View 9 Replies
View Related
Jan 6, 2007
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?
View 3 Replies
View Related
Jul 27, 2015
Query Date Field
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?
View 3 Replies
View Related
Mar 11, 2014
I want to add a parameter to a query that lets the user enter a number to filter on the 'Top Values' section of the query.
I.E the query shows sales by product, the user enters 50 and it shows the top 50 records.
View 1 Replies
View Related
Jul 17, 2013
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?
View 1 Replies
View Related
Mar 28, 2015
I need to do a update query with prompt to input a percentage.
View 11 Replies
View Related
Apr 11, 2014
I have a table of data going back to 2007 that needs to be looked at on a monthly/quarterly/annual basis. I am able to filter the data when running a normal query by using
Between [Start Date] and [End Date]
in the Criteria section of the Date field. I now need to apply this same idea to a crosstab query. My current set up is:
[Gender]- Group By / Row Heading
[Plan Type]- Group By / Column Heading
[Pmt Amt]- Sum / Value
[Date]- Where / Criteria = Between [Start Date] and [End Date]
I am getting an error message that says:"The Microsoft Office Access database engine does not recognize '[Start Date]' as a valid field name or expression."Am I setting something up incorrectly or is it impossible to use input prompts in a crosstab query like this?
View 1 Replies
View Related
Aug 22, 2006
i have a subform on a form with Source Object="Query.myquery", my form is generating a sql string dynamically and assigning this sql string to the RecordSource property which works fine. But now if the user changes the width of the columns in the subform and close the form , a prompt is thrown asking if the "myquery" needs to be saved. I dont want to save the settings of "myquery" but at the same time avoid this modal dialog. If i use DoCmd.SetWarnings=False , i dont see the warning anymore BUT the query gets saved.
I am using "myquery" to just display column names in the subform when the forms loads and also without it i cant directly use RecordSource property in my code.
any suggestions?
View 1 Replies
View Related
Jul 12, 2005
I have a form whose data source is a select query, q3, that is built from 2 other select queries. I'll call them q1, q2, and q3. q1 is a parameter query where I enter a "Cutoff Date" that the 3 queries manipulte and generate the desired results that appear in the form. The problem is that I don't know how to capture the parameter "Cutoff Date" from q1 to display on the form.
View 2 Replies
View Related
Nov 9, 2006
Hi,
I have a query that requires a Start-Date and an End-Date to be input by user for the Where clause. It is asking for both over and over. I've had it ask from 1 up to 4 times! :eek: Shouldn't it store the input and only ask for it once? I'm thinking that the way my query is arranged may be causing it to have to loop through that section more than once to find the data, but that's just my theory. Any help would be great!
Here is my code (abbreviated slightly):
SELECT DISTINCTROW C1.*, C2.*
FROM Pen AS C1 INNER JOIN Jobs AS C2 ON C1.subno=C2.[Jobs Acct]
WHERE ((C1.typ="SS" Or C1.typ="CC" Or C1.typ="PP" Or C1.typ="TT") And C1.stdate>=[Enter Start Date] And C1.stdate<=[Enter End Date] And C2.[Type]<>"EE" And C2.[Type]<>"QQ" And C1.entdate<=C2.[ChangeDate]+60);
I'm selecting rows from "Pen" and "Jobs" that have the same subno/Jobs Acct numbers (text), then there are criteria for "Pen" types, user inputs criteria for date range (Start Date and End Date) and there are criteria for "Jobs" types. Finally, there's a cross-table criteria based on a date field ("Pen" entdate should not be more than 60 days past the "Jobs" ChangeDate). Tables are in quotes in my explanation here.
So running the above, it asks for user input "Enter Start Date", then again for "Enter End Date"...but then it asks for each again...and again...and sometimes again!
Help! :confused:
P.S..I didn't notice this repeating until I made it user input (parameter query) because it was using whatever dates I hard-coded in there before.
View 2 Replies
View Related
Dec 1, 2005
After I do a "DoCmd.RunSQL "Update ..."" I am asked if I really want to perform the 'Update'. Does anyone know if there is a way to get rid of the prompt and just carry out the query?
Thanks
View 1 Replies
View Related
Jul 28, 2005
Hi All,
How can I check the Parameter query interval is out of range?
Details:
I have a table with Date column. With parameter query(by Date field) I extract records between two dates. How could I make a check when taping in Inputboxes, am I or Not Between the Last and the First Date?
Thanks!
View 1 Replies
View Related
Aug 31, 2005
I'm writing a query which when run will prompt the user to enter criteria. In this case it is a workers id (like 000UA or 000UB)
My problem is I would like to have the ability to enter more than one criteria. What I want to do is to have the ability to enter several worker id's in the criteria field, like 000UA, 000UB, 000UC, 000UD, 000VA, 000VB, 000VE, 000VF, 000WA, 00WB
Is this possible and can someone explain to me how to do it?
Thanks
View 2 Replies
View Related
Sep 2, 2005
I am trying to run a parameter query that will prompt the user for "facility", but instead of displaying the results in table format, I want a form to display the results. I have already developed the form. I would also rather for the users to search by a list box instead of typing the facility. I cannot seem to figure this out. Please help.
Thanks.
View 1 Replies
View Related
Oct 19, 2005
Dear All,
I am having problems finding a solution to this problem. I am working on 3 databases at the moment..and am at the final hurdle to complete it.
I have built an Access database. I would like to generate a
number of reports which have to be exported directly into excel
individually.
I have a parameter query with 14 columns. One of the columns
is called GROUPS. I have a form with a button.
When you click on the button the parameter query asks for the
parameter value.
The parameter value must be a GROUP. i.e BURR, WIEN.
After you enter the group the parameter query generates results for
that particular group.
I have a number of groups which i would like to generate results
for at the click of the button on the form.
There are around 30 groups. but i only need to generate reports for
about 15 groups. The groups are stored in one of the tables.
So for instance after i click a button on the form
the results for each requested "GROUP" are exported individually into
excel.
Each group result must be in one excel file.
How can i achieved this. Do i need to use VBA? Please can you help.
I need a solution then i can apply this to a number of databases
Many, Many thanks
View 1 Replies
View Related
