Parameter Query Using Various Search Methods
May 22, 2006
Hi
I have a table in sql holding telephone data. the starttime column and endtime column are both in "10/05/2006 12:55:56" format.
I want to run a parameter query that lets me search by agent name and then by date. The agent name is no problem but when running parameter query by date it brings back no data. I think this is because the time is also in the same field as the date. Ideally I would like to be able to run the query between certain dates, e.g. find data for agent1 between 2 different dates.
Any help would be more than appreciated!!
Thanks
Ben
View Replies
ADVERTISEMENT
Feb 11, 2015
I have 2 tables: Table A contains [ID] (and other columns) AND Table B contains [Request ID] (and other columns).
However, [Request ID] contains several number separated by comma. For example: 10, 15, 20 etc.
I created a query with parameter with : Like "*" & [ID] & "*" under [Request ID] criteria.
Here is my problem:
if [Request ID] = 44, 60 then it is returning [ID] 4 records (4, 6, 44, & 60). Is there a way to show only 2 records? 44 & 60?
View 4 Replies
View Related
Nov 18, 2014
I have a parameter query in which consists of a search statement as below:-
Search: [Model] & " " & [FiscalYear]
It works very well. I am wonder if there is any options available to affect the search results. As far as I understand, when I enter the key word into the pop up box, it will search the fields sequentially and populate the result. Can I enter different key word for each field? If so, what is the syntax for entering the key words.
View 2 Replies
View Related
Nov 10, 2006
hi,
If I just want to display a relationship between two tables or more with a form and a subform, and I don't want the users to edit/delete/add the data - just view it. What is the best way - queries, bound forms etc? I created a form with a subform, but then ran into problems because I don't want them to be updated, only viewed.
Thank you !!!
View 2 Replies
View Related
Jun 24, 2013
I need to create a form to input records (or modify records) however im unaware of the methods that could be used to actually put the data into the tables..my current method is using:
Code:
Dim dbs as DAO.Database
Set dbs As CurrentDB
Dim InsertSQL As String
InsertSQL = "INSERT INTO [Table] (Columns) VALUES (Values)"
dbs.Execute InsertSQL
I am not sure if this is the most effecient or best or if it has issues that other methods or ways to use but doing this seems to do the trick.
View 3 Replies
View Related
May 1, 2006
Hello everyone. I'm a little new to the Access scene. I have a strong background in C, C++, Java, and assembly. Seems all the access books out there fail to mention the need to know vbs, heh.
Anyhow, I have a form ([WrkR]) based on a table ([ORDERS]) that has a customer's name ([Tables]![ORDERS]![name]), total payments to visa ([Tables]![ORDERS]![visa]), and total payments to cash ([Tables]![ORDERS]![cash]), and total payments to debit ([Tables]![ORDERS]![debit]). My form also has three rows, each containing a text box which holds how much a person has paid, and a combo box ([combo1], [combo2], [combo3]), that allows the user to choose what payment type was used. I have included a screen cap to demonstrate this.
I need to be able to create a function or macro, anything that will sum up the appropriate totals and store them in the corresponding columns in my table. Like in my example, there are 2 visa costs and a single cash cost that should be added up and stored in my table in the appropriate fields.
View 6 Replies
View Related
May 30, 2006
Hello,
I have got a serious problem with parameter searches. What I want are multiple field parameter searches in one query. I created a new query, and set the criteria to Like "*" & [...] & "*" for at least 5 fields. The problem now is, that the number of records get less the more parameter searches i create for different fields.
I tried to put the parameters into the "or" criteria but it still does not work!
please give me a hint.
Thank you!
John
PS please ask me when you did not understand my question.
View 1 Replies
View Related
May 30, 2006
Hello,
I have got a serious problem with parameter searches. What I want are multiple field parameter searches in one query. I created a new query, and set the criteria to Like "*" & [...] & "*" for at least 5 fields. The problem now is, that the number of records get less the more parameter searches i create for different fields.
I tried to put the parameters into the "or" criteria but it still does not work!
please give me a hint.
Thank you!
John
PS please ask me when you did not understand my question.
View 1 Replies
View Related
Aug 5, 2013
In criteria of a Query I have this SQL: Like [Forms]![FSearch].[qb1] & "*"
This SQL is to write in a textbox one parameter at a time to search in a table.I need to search for more than one parameter at a time and I have been trying to add to the SQL the operator OR or AND but does not work.
Like [Forms]![FSearch].[qb1] & "*" OR Like [Forms]![FSearch].[qb1] & "*"
Like [Forms]![FSearch].[qb1] & "*" AND Like [Forms]![FSearch].[qb1] & "*"
How can I search for more than parameters at a time?
View 11 Replies
View Related
Apr 8, 2013
I have attached a (slimmed down) Access 2007 database used for Asset Management and fault reporting. It opens on the main form which has a number of subforms and one sub-subform.
To make the form as user friendly as possible I am placing a number of unbound text boxes on the FormHeader to be used for search queries. The AfterUpdate event for each text box executes the necessary query, searching on the contents of the text box.
This works absolutely fine for the 'Colloquial Name' text box and associated field - this field is in the main table, RTI_Assets. The main form frmRTI_Assets is bound to this table. The query executed is called 'Colloquial Search'.
I cannot however get the 'Fault Reference' search to work on the field Supplier_Reference in the RTI_Fault table (the subform subfrmRTI_Fault is bound to this table). The query executed is called 'Fault Reference Search'.
There are 15 records in the assets table and 2 records in the faults table - with supplier references 555 and 101010.
I get prompted with Enter Parameter Value for the field RTI_Fault.Supplier_Reference if I enter 555 in the text box. Whatever I enter in this prompt I get returned to the first record.
View 4 Replies
View Related
Sep 10, 2014
I have searched for bits of code to copy other people better than I, and had some success but I am failing to complete, so it is now time for me to visit you all. I am trying to open a PDF from a button on a subform. The location of the PDF is specified in a field. I want to open the PDF and search for a Member ID criteria also listed in a field on the Form.This is my code:
Code:
Private Sub Text12_Click()
Dim strPath As String
Dim Searchmem As String
strPath = [Notetxt]
Searchmem = [MemID]
[code]....
Adobe Reader reports "There was an error opening this document. The file cannot be found." but then opens the file (that's something).So it's the "search" bit that is the problem, and I cannot for the life of me get my search parameter to be used.
View 5 Replies
View Related
Oct 9, 2013
We have an ActiveX grid control, 10Tec iGrid, written in VB6. One of its method, Group, used to group rows, raises the AfterAutoGroupRowCreated event so the developer can adjust the look and contents of every group row created during the automatic process of grouping.
When our ActiveX grid is hosted on an MS Access form and we call the Group method while populating the grid in the form's Open event, the AfterAutoGroupRowCreated event isn't triggered. But this event definitely works as expected in other development environments, and even in MS Access if we call Group from the form's Load event.
Is it a well know issue of MS Access, when any events of ActiveX controls aren't triggered while "executing" the Open event
View 1 Replies
View Related
Jul 31, 2014
I have a Main form, and a subform which lists client details. On the Main form I have an unbound field. I want to be able to type a word into this unbound field and have it display all company names that have this word in them. ie. I type "Ltd" into the unbound field and it displays all companies with "Ltd" in the title.
I have created a query that does exactly this (Like ("*" & [Enter Word] & "*")), it displays a dialog box and I type in "Ltd" and it displays all relevant companies.
I have tried everything I know to make this work when I use the unbound field on the Main form, but I've had no luck.
View 3 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
Aug 5, 2014
I have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.
View 2 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
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
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
Jan 18, 2006
I want my msgbox to tell my user that this projectId does not exist in our db if the sql search returns an empty value. I get errors that i can't run this action query. please see red text. any ideas?
Private Sub Project_Quick_Find_Click()
On Error GoTo Err_Project_Quick_Find_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim Ssql As String
Ssql = "Select [projectInformation].[projectId] from [projectInformation]" & _
"where [projectInformation].[projectId] = " & Me![ProjId]
'DoCmd.SetWarnings False
DoCmd.RunSQL Ssql
'DoCmd.SetWarnings True
stDocName = "Project Status - Full Details"
stLinkCriteria = "[projectId]=" & Me![ProjId]
If Ssql = "" Then
MsgBox "A Project with this number does not exist in our database", vbExclamation, "Cannot find project"
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Exit_Project_Quick_Find_Click:
Exit Sub
Err_Project_Quick_Find_Click:
If Err.Number = 3075 Then
MsgBox " Please enter a Project ID to find! ", vbExclamation, "Empty Field"
Else
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"
Resume Exit_Project_Quick_Find_Click
End If
End Sub
View 1 Replies
View Related
Feb 6, 2006
Hi, I'm a complete novice to Access and wonder if anyone can help me with this one please? I have a column in a query that is the output from a barcode scanner called WhenScanned from a table tblScans. The data in the column is in the format of the date and time for example: 20/01/2006 21:30:00 I want to be able to interrogate the data in the query by selecting a range of a date(s) and times for example from 20/01/2006 05:30:00 to 20/01/2006 13:29:00, date and time range from 13:30:00 to 21:29:00, date and time range from 21:30:00 to next day 05:29:00. I can achieve this by typing the required range into the criteria row of the query column in design view e.g. >=#20/01/2006 13:30:00# And <=#20/01/2006 21:29:00# for each range but I want to make it more user friendly so that a user can select type in the appropriate date and select the time range from a drop down list or something without having to edit the query using syntax. Thanks in anticipation.
View 2 Replies
View Related
Mar 16, 2006
hi all,
im trying to do this query, and in it i am using fields from 1 table, and i have created 2 fields using the expresion builder (these come up as Expr1, Expr2).
these field (expr 1&2). & iwant 2 do use a parameter on Expr1, but when i go to view the query, it says i need to enter a paremter value for Expr2.. if i click OK then the parameter i need appears and when i enter the value i want no records appear.
i uses the followin expressions:
Expr1: DateAdd("d",+[Expr2],[Date of Joining])
Expr2: IIf([Membership Type]="Family - 1 Year","365",(IIf([Membership Type]="Family - 6 Months","183",(IIf([Membership Type]="Adult - 1 Year","365",(IIf([Membership Type]="Adult - 6 Months","183",(IIf([Membership Type]="Child - 1 Year","365",(IIf([Membership Type]="Child - 6 Months","183",(IIf([Membership Type]="Student - 1 Year","365",(IIf([Membership Type]="Student - 6 Months","183")))))))))))))))
thnx in advance, chia
View 2 Replies
View Related
Mar 21, 2006
Hey guys
I was wondering how I would go about making it so that a query will run or not run based on the input from a combo box.
I have a form with several combo boxes. And a couple of these controls a query that I would like to be optional. Right now it runs no matter what the input in the combo box, but I would like it to run based on the selection of the box (ie. "Yes" or "No" type input)
Your help on this is much appreciated
Nathan
View 13 Replies
View Related
Jun 20, 2006
I am trying to create a query for a database for a company I'm interning for. The query is supposed to bring up an individual machine from a column labeled Machine ID. This column contains 20 or so abbreviated Machine labels. When you run the parameter query, and you select the individual machine ID I get multiple results or it brings up a list, but it is incomplete. The machines sometimes share parts, so in the machine ID column for a part there may be up to 5 machine IDs separated by commas. I want to know if there is a way of isolating a machine coded K from a part that has K,SIM12,C40 listed for the machines that it goes to.
View 1 Replies
View Related
