A Form With Critera For A Query
Oct 19, 2004
I am trying to create a form that searches the criteria entered and opens the query filter by the info entered.
Example:
Form opens, you type the last name parameter of client
Last Name: Smith
Click a button named Search- opens the query and filters it by the criteria (last name). You see the query with all records with the last name Smith. I may want to add the first name in the form along with the last name. Does anyone no how to do this?
thanks
Biz
View Replies
ADVERTISEMENT
Oct 26, 2004
Hi! I have a query setup that feeds off of three combo boxes on a form. It is setup to allow a user to search and calculate revenue for a selected time period by company, then department, then page. If all three or just company are selected, there are no issues running the data. However, if only company and department is selected I receive an 'ODBC--Call Failed' message. The issues seems to be stemming from the department field as I can link the department table to the main table and the query will run without error. The problem with that bandaid is that the query takes more than 15 minutes to run that way as opposed to about 2 minutes to pull more data at the company level.
Any suggestions on why this is happening or a fix that can be put in place? Any help is greatly appreciated.
I can provide more details if needed. Thanks!
View 6 Replies
View Related
Mar 15, 2005
Hello,
I first want to thank the owners of this forum for keeping it up and the bright users who post replues I have gotten a lot of useful info thus far. I have a small issue I've been working on for a few days now. I just can't seem to find the correct syntax to accomplish it. Any I have a table which has customer contact info as well as sales numbers. I have a form which my users can search contact info. However I've been unable to allow the users to search for sales dollar volume. Basically the form sends the critera to the query. I've tried getting the between to work but I'm doing something wrong. Below is my VB code attached to the search button. The red bold line is the problem. If I just take the line out then open the query manually and type between "0" and "1000" it works fine. But if I type my form txt box names I get a message saying type mismatch in critera expressiong. So I think it's my syntax.
Option Compare Database
Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
'Constant Select statement for the Query definition
strSQL = "SELECT tblCONSOLIDATED.ACCOUNT1, tblCONSOLIDATED.COMPANY_NAME, tblCONSOLIDATED.ADDRESS1, tblCONSOLIDATED.ADDRESS2, tblCONSOLIDATED.CITY, tblCONSOLIDATED.STATE, tblCONSOLIDATED.ZIP, tblCONSOLIDATED.CONTACT_NAME, tblCONSOLIDATED.TELEPHONE, tblCONSOLIDATED.FAX, tblCONSOLIDATED.CURRENT_YTD, tblCONSOLIDATED.PRIOR_YTD, tblCONSOLIDATED.PRIOR_TOTAL, tblCONSOLIDATED.YEAR2_TOTAL, tblCONSOLIDATED.YEAR3_TOTAL, tblCONSOLIDATED.YEAR4_TOTAL " & _
"FROM tblCONSOLIDATED"
strWhere = "WHERE"
strOrder = "ORDER BY tblCONSOLIDATED.COMPANY_NAME;"
'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.txtCSONME) Then '<--If the textbox txtCSONME contains no data THEN do nothing
strWhere = strWhere & " (tblCONSOLIDATED.COMPANY_NAME) Like '*" & Me.txtCSONME & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
If Not IsNull(Me.txtCSOSLD) Then
strWhere = strWhere & " (tblCONSOLIDATED.ACCOUNT1) Like '*" & Me.txtCSOSLD & "*' AND"
End If
If Not IsNull(Me.txtCSOARN) Then
strWhere = strWhere & " (tblCONSOLIDATED.CONTACT_NAME) Like '*" & Me.txtCSOARN & "*' AND"
End If
If Not IsNull(Me.txtCSOAD1) Then
strWhere = strWhere & " (tblCONSOLIDATED.ADDRESS1) Like '*" & Me.txtCSOAD1 & "*' AND"
End If
If Not IsNull(Me.txtCSOSSM) Then
strWhere = strWhere & " (tblCONSOLIDATED.ADDRESS2) Like '*" & Me.txtCSOSSM & "*' AND"
End If
If Not IsNull(Me.txtCSOCTY) Then
strWhere = strWhere & " (tblCONSOLIDATED.CITY) Like '*" & Me.txtCSOCTY & "*' AND"
End If
If Not IsNull(Me.txtCSOST) Then
strWhere = strWhere & " (tblCONSOLIDATED.STATE) Like '*" & Me.txtCSOST & "*' AND"
End If
If Not IsNull(Me.txtCSOZIP) Then
strWhere = strWhere & " (tblCONSOLIDATED.ZIP) Like '*" & Me.txtCSOZIP & "*' AND"
End If
If Not IsNull(Me.txtSLCYYD Or Me.txtSLCYYD2) Then
strWhere = strWhere & " (tblCONSOLIDATED.CURRENT_YTD) BETWEEN '*" & Me.txtSLCYYD And Me.txtSLCYYD2 & "*' AND"
End If
'If Not IsNull(Me.txtSLLYYD) Then
'strWhere = strWhere & " (tblCONSOLIDATED.PRIOR_YTD) Like '*" & Me.txtSLLYYD & "*' AND"
'End If
'Remove the last AND from the SQL statement
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qrySALESDATA")
qryDef.SQL = strSQL & " " & strWhere & "" & strOrder
'Open the Query
DoCmd.OpenQuery "qrySALESDATA", acViewNormal
End Sub
View 3 Replies
View Related
Apr 7, 2006
Hi,
I am trying to set the criteria in a query, referencing a control on a form. The control is a combo box and I am trying to use the second column to filter data in the query.
EG. Forms!FromName!ComboBox.Column(1)
However the query doesn't accept it. I get an error saying:
Undefined Function 'Forms!FromName!ComboBox.Column' in expression.
How do I reference the control properly please.
Thanks
Red
View 2 Replies
View Related
Nov 15, 2007
im tryin to get this query to get data from current weeks inputs. ive googled and tried everything with no luck so far. so here i am. can someone tell me what im doing wrong? keep in mind i dont know what im doing.....
heres what i put for criteria under my date column.
Between Date() And Date()-7
doesnt work.... anybody? thanks in advance...
View 10 Replies
View Related
Mar 17, 2008
Hi everyone
I have a database that I built myself and use on a daily bases for keeping track of customers and there service records for a small sized plumbing and drainage firm. The database has a function that is designed to automatically search for upcoming boiler services that are due or over due which works fine, However we use acronym's to describe some routine jobs such as B/S (Boiler Service) or H/R (Heating repairs) which speeds up data entry. The problem is that Access refuses to search for the acronym's, I have tried basic text search critera's which brings up full words fine such as "Smith".
Is there a way of searching for the acronym's like B/S?
Thanks in advance for any help.
View 4 Replies
View Related
Nov 16, 2004
Please advise, how do I set up a form for this:
Based on one particular report style, have multi select criteria to produce user selected criteria
based on selections from a. Interest, b. State, c. Type
Report 1. sorts on Interest, 2. sorts by State, 3. sorts by Type ... and each report includes selections of the other 3 selections plus basic data common to all.
Plus, the Interests have a table where the main interest data is stored in a ContactInterest table, while the specific interest selections, per contact, are stored in the Contact table as Interest_1 through Interest_20.
In the Report, when it is sorted by Interest, while I have a concantated output for 5 of the selections, they also, want to sort alphabetically by the interest selections (which changes per Contact).
There is already a form for printing reports that are more static, and I wanted to add these selections on that form, perhaps as a subform, to allow printing of the reports, mentioned, and associated mail labels to the targeted addresses per group.
Any help, direction, guidance, or redirection would really help and is MUCH appreciated. I've tried many options, and as a newbie, have not yet found a solutions- or solutions.
Thank you so much. (time is of the essence and my skills are small so far.)
__________________
View 1 Replies
View Related
Oct 19, 2004
What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?
I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.
So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.
Thanks in advance
Todd
View 1 Replies
View Related
Jul 23, 2015
I have a form which will be used as the basis to print a label.
It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.
What I want to do is to update the form/query without updating the underlying tables to the query.
View 14 Replies
View Related
Jun 15, 2013
Is there an easy way of entering a value in a text box, passing to a query to do a count function and then return the value of count function in to anther text box?
View 5 Replies
View Related
Aug 17, 2014
I have a big table, EquipmentDetails and separately I have job plans.Job plans can have many pieces of equipment. I store the relationship in EquipmentDetails_JobPlanDetails, but am feeling frustrated as I can't seem to get Access to build the SQL query from this in the way I want.
I am generating a List Box in a form which is populated from a query.The query calls on a table which lists ItemID from EquipmentDetails and JobID and in the current form (where the list box is) I want to pick up and display the ItemIDs associated with that JobID. The current JobID reaches my form correctly (I've proven this by displaying it in a text box) from the previous form.
The problem I have is that I can't seem to get the SQL query to only get ItemIDs that are linked to the current JobID. No matter what I try, it either gets all of the ones in that table, or I can't compose one.What kind of SQL do I need to only grab the ones relating to the current JobID please?I've tried building it in the design view but it says that it can't do it because the outer joins are ambiguous.
View 2 Replies
View Related
May 13, 2005
First, I am a beginner with Access. I am a graphics designer that has been assigned to cover for a db programmer that quit!
Here is my problem:
I have a database that we use to hold customer information. There are 22 fields in each record, and we are now well over 3000 records.
Once upon a time, to find a specific customer, we would just go to the bottom of the page, and use the arrow buttons to scroll through them all. This is no longer possible as the size is too big to manually search.
What I would like to do:
Upon Access startup, display a form that has a single input field and a button titled "Search". The input field is titled [UserName], as this field is the unique key identifier for the record. When the user types in the UserName and clicks the SEARCH command button, another form appears to display JUST THAT RECORD in the easy to read form!
If this is really basic, I appologize. I have 20 years experience with commercial illustration and only 7 days experience with Access. I have purchased books, and hit the forums, but I am not a VB programmer, and my skills this area are REAL weak!
My resourse books include the following:
Wiley - Access 2003 Bible [Prague, Irwin & Reardon]
Osbourne - How to do Everything with Access 2002 [Anderson]
O'Reilly - Access Cookbook [Getz, Litwin & Baron]
Thanks in advance for any assistance.
View 1 Replies
View Related
May 13, 2005
First, I am a beginner with Access. I am a graphics designer that has been assigned to cover for a db programmer that quit!
Here is my problem:
I have a database that we use to hold customer information. There are 22 fields in each record, and we are now well over 3000 records.
Once upon a time, to find a specific customer, we would just go to the bottom of the page, and use the arrow buttons to scroll through them all. This is no longer possible as the size is too big to manually search.
What I would like to do:
Upon Access startup, display a form that has a single input field and a button titled "Search". The input field is titled [UserName], as this field is the unique key identifier for the record. When the user types in the UserName and clicks the SEARCH command button, another form appears to display JUST THAT RECORD in the easy to read form!
If this is really basic, I appologize. I have 20 years experience with commercial illustration and only 7 days experience with Access. I have purchased books, and hit the forums, but I am not a VB programmer, and my skills this area are REAL weak!
My resourse books include the following:
Wiley - Access 2003 Bible [Prague, Irwin & Reardon]
Osbourne - How to do Everything with Access 2002 [Anderson]
O'Reilly - Access Cookbook [Getz, Litwin & Baron]
Thanks in advance for any assistance.
View 3 Replies
View Related
Sep 6, 2004
HI
I have a form for member details that comes from the member table and I want to
include a button on the form that will remove the member details from the member
table to a member history table and then delete the details from the member table.
When moving the record I also want to include details from another table called 'promotion',
this table is linked to the member table via the member ID (Foriegn Key).
Firstly I want to know if this is possible and if so how I would achieve this. I am having
trouble getting the SQL query to identify the current record that the form is displaying so I can
select additional information and move it into the Member History table.
Any information would be greatly appreciated.
Thanks.
View 2 Replies
View Related
Aug 30, 2005
I have a query that displays results in a form, but if the query is null, I want to display a different form, or just an error message that says something like "your query returned no results" (right now it will display the form with no fields)
I am a beginning Access/VBA user and have searched and browsed the forum for combinations of null/query/form, but haven't found what I need. Can anyone point me in the right direction?
Thanks for any help.
View 5 Replies
View Related
Sep 22, 2006
:confused:
I have a form displaying individual contract information. This form is a continuous form and on each record I have a button that activates a dialog box with two text boxes to facilitate NOTES entry for the current record on the main form. When I close the dialog form - I need the NOTES text entered to be updated in the corresponding text fields on the continuous form.
I have tried the following :
1. After the dialog has closed - set the main forms Bookmark property/ Requery the form and then reset the forms Bookmark.
Problem with this solution is that occasionally I get ' Not a valid bookmark' error message.
2. In the Unload event of the dialog - I have tried Requery calls for each of the text field controls on the constinuous form prior to the dialog closing. This works fine for records that already exist in the table and the data does reflect the changes correctly. For brand new records, the updates are not reflected on the main form and the data only appears when the main (continuous) form is closed and re-opened.
Does anyone have any ideas on how to do this properly? Has someone done something similar?
Thanks
Regards
GUIDO
View 1 Replies
View Related
Jan 10, 2007
Hi All
I have a query that runs based on the contents of a date entered in a form (I will call that Query1).
I need to take the data from Query1, and then have Query 2 analyse the data (I want it to tell me where the Count of First Day is more than 5).
When I use the query design grid, I include the fields want from Query 1, and put the criteria in to say CountofFirstDay >=5; but when I run it, it asks for the data for the form (relating to Query 1)
What can I do? thanks!
Maria
View 1 Replies
View Related
Mar 20, 2013
I have a form which uses a parameter based query to present an individual senior doctor with a list of names for of individual juniors to provide an assessment report on. When the first form opens the user enters their RespondentID.
Once senior has decided which names to comment on I have another form which has the questions to be answered which is opened by clicking a button on the first form.
How can I pass a parameter from the first form to the second so that only the records relevant to that senior doctor are displayed? The underlying table has 60 senior doctors and 20 junior doctors. The senior doctor is identified by the field RespondentID in the first form. I've tried putting a WhereClause in the FormOpen command but I still get a dialogue box asking for the parameter RespondentID when the second form is opened.
View 2 Replies
View Related
Jul 25, 2007
I have a query form which user use to search for records according to their conditions they selected from the dropdowns.
Now when there is result for the conditions specified it works fine but when there is no record it pops up message saying no record and as one hit okk it gives below error:
"Run Time error 7874 eta database cant find the object 'xxx-0725'"
this is some table gets created everytime we search something.
and if there is no result this is not created somehow and it gives error.
View 3 Replies
View Related
Aug 7, 2005
I have these queries (QRY1 and QRY2) made in vba which are used to fill 2 multiboxes (ListIncluded and ListNotSelected) in a form. The left multibox (ListIncluded) shows all the people that attented to a certain class, the right box shows the ones that werent present. The table "tblPersoneel" is used to find the names of the people. Basicly I use these tables: tblPersoneel (all the people) tblTrgRegAlgemeen (general data about the lesson like date etc) and tbltrgRegDetails (details, uses TrID to relate to a record in tblTrgRegAlgemeen and PersID to link it to the ID from tblPersoneel). I use buttons to move around the people.
The following code is used:
Dim SQL1 As String
SQL1 = "SELECT [tblPersoneel].[ID], [tblPersoneel].[Achternaam] & ', ' & [tblPersoneel].[Voorletters] & ' ' & [tblPersoneel].[Tussenvoegsel] As MyName, [tblPersoneel].[Rang], [tblTrgRegDetails].[TrID], [tblPersoneel].[Regnr], [tblPersoneel].[Toegang] " & _
"FROM tblPersoneel LEFT JOIN tblTrgRegDetails " & _
"ON ([tblTrgRegDetails].[PersID]=[tblPersoneel].[ID]) " & _
"WHERE ([tblPersoneel].[Toegang] = " & Me.ListToegang & " " & _
"AND tblTrgRegDetails.[TrID] = " & Me.TrainingID & ") " & _
"ORDER BY [tblPersoneel].[Achternaam];"
Forms!frmTrgInvoer!ListCurrent.RowSource = SQL1
Dim SQL2 As String
SQL2 = "SELECT [tblPersoneel].[ID], [tblPersoneel].[Achternaam] & ', ' & [tblPersoneel].[Voorletters] & ' ' & [tblPersoneel].[Tussenvoegsel] As MyName, [tblPersoneel].[Rang], [tblPersoneel].[Regnr], [tblPersoneel].[Toegang]" & _
"FROM tblTrgRegDetails RIGHT JOIN tblPersoneel " & _
"ON [tblTrgRegDetails].[PersID]=[tblPersoneel].[ID] " & _
"WHERE [tblPersoneel].[ID] NOT IN (" & SQL1 & ") " & _
"ORDER BY [tblPersoneel].[Achternaam];"
Imo this would result in the following: at the record i am viewing in the form i have people in the box which uses SQL1 and the other people are in the right box using SQL2. Any person cant be in both (one cannot be in a lesson and not be there).
I am pretty sure this is a SQL problem and not VB and I really hope someone can help me with this.
Thanks in advance!!!
View 1 Replies
View Related
Nov 25, 2005
I have a form with 4 pulldown boxes. What I want to do is basically query a query? But I am having trouble. I am basically looking for an AND/OR type clause.
If I choose any of the 4 combo's or some, or all. I want all the selections made in the pulldowns to be considered in the query. I have to consider some pulldowns will have not been selected. For that matter I must consider none were selected.
Basically having trouble passing this info from the form to the query and getting the query to do anything with it. I am not talking opening the query, that can be done with a button with an event handler. I am talking actually getting the query to give me the results I asked for. Basically I can get the query to take the data in the form of "AND" or "OR" but not to consider both.
Maybe I am talking in circles but I want clear my desire. If I chose 2 pulldown boxes, and select data. The query should consider both that I chose, and since the other 2 remaining boxes have not been selected ignor those NULL values as if nothing was in the criteria.
Any help would be appreciated.
View 3 Replies
View Related
Jan 9, 2006
I have a form that has a bunch of unbound fields on it. I have a query that is tied to the fields of the form. The purpose is the form is a filter and when you type something in it goes through the query and then creates a report. The form is not bound to anything. Is there a way to have a list box in the form that has a list of information that people can choose multiple records from the list box and have it go through the query?
View 5 Replies
View Related
Feb 13, 2006
I created a form with several combo boxes on it. When data is chosen within the combo boxes, a query/report can be run and it retrieves all of the information in accordance with the combo boxes.
The functionality that I need to build in is the option to leave any number of those combo boxes blank in order to retrieve ALL information from the field linked to that combo.
Example:
I have three combo boxes. One is named team and it lists all team names. The second combo box is priority (1-2-3-4 etc). The Last combo box is day of the week. As it stands now, I can input data into each of these boxes and my report functions fine. However, I want to be able to leave one or more of them blank and it pull back info. Such as....if I dont choose a specific team, but I choose a specific priority and a specific day of the week, it should pull back ALL teams that have that priority and day of the week.
Does this make sense?
I was given a link to a site by the name of Brinkster to include an ALL function on my combo box, but that will not work for some reason. I know what I want to do is possible, because I have incorporated it into another database of mine. However, I cant make heads or tails of my notes or how that database is setup.
Help?
need more info?
View 6 Replies
View Related
Mar 8, 2006
I tried a search but wasn't able to find what I was looking for. I would like to be able to use a form to run a query in my db. I need to use name, begin date and end date and then have a button to run my query. My problem is that I can't get the unbound text boxes (for the dates) to use a date format. Have I taken the wrong approach?
View 1 Replies
View Related
Sep 12, 2006
I have a table with a lot of columns. I have two columns i want to prepair to be used in a tablesheet form. One with sizenumber and another with productnumber. I have a datasheet form where I want to use the data from sizenumber and productnumber writen as one ex. "3-345767" (first number is size and the second is productnumber). Well I succsesfully done this in a query table but I can't get it to work or sort in the form. Its greyed out. Any suggestion? Can I do this on another way?
I gonna get greyhaired of this.. :confused:
Any suggestions would be help full!
View 1 Replies
View Related
Dec 15, 2006
I am trying to set up a database to set up a referral system. I have set up a form where I can enter the details of the booking. I have set a form so that I can enter the date of booking, for example booking for the future. The results are nicely produced in a datasheet/spreadsheet style. But I want the results to be displayed as a form, in a table style. So that I can edit and then enter the outcome of that booking.
I am a novice on Access, can anyone help.
View 4 Replies
View Related
