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.
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?
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.
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 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?
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()
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
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.)
I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.
I have created a cross tab to extract pipeline and sales for Q1 2014, Q2 2014, Q3 2014 & Q4 2014... the user can select the quater from a multivalued text box...
Now for the final output, have created another query which pull the above four quarter in each column from the cross tab...now the problem arises when i change the quarter to Q2 2014, Q3 2014, Q4 2014 & Q1 2014..it gives an error "Microsoft office Access database does not recognizes "Query name" as a valid field name or expression".
The error is because the second layer of query does not identifies Q1 2014.
How do i make access change the column automatically when the Q1 changes to Q2...
I am looking to add a column in a query that will give a Y or No to previous column data if it contains TEXT or NUMBER (It could read "TEXT" or "NUMBER" or even Y for text or N for number).
In the organisation that I work for employees get paid every 2 weeks on a Saturday. So for this financial year the pay period end dates have been 08/07/2006, 22/07/2006, 05/08/2006 etc
I have a column in an Access table listing various dates. I want the next column to be populated with the next pay period end date after that date.
So if DATE is 05/07/2006 I want PAY PERIOD END to be 08/07/2006 and if DATE is 09/07/2006 I want PAY PERIOD END to be 22/07/2006 etc
I need to input a string into a column named "EventType". The code should first check if the column "Agent Name" contains any strings. If there is none, it will input "IBM Director" into the EventType column.
Once it has looped through the agent names, the code will then loop through the Details column and input into EventTypes based on what is displayed within the string.
These are the codes that I am using to achieve this, however nothing is being input into the EventType column.
Code: Private Sub Command11_Click() Dim dbs As DAO.Database Dim rst As DAO.Recordset
Set dbs = CurrentDb Set rst = dbs.OpenRecordset("Final")
[Code] ....
I think the problem lies with the code that checks the agent name. When I removed it, it managed to populate the EventType column based on the details. But I still need to find out how to check the agent name too.
i have a columns as 1. contactname, 2. firstname 3. lastname 4. email and in this columns some emails are not matching with the contactname or some time firstname or some time lastname so i need the to find out the un matched contacts from the database.
I'd like to add a new column in access query (see attached) that within the same batch if there is a "f" in the "passOrFail" column, then in a new column put a "f" for the whole batch, otherwise put a "p" for the whole batch.
Normally, with a multiselectquery you type in several criteria(postal code) like; 3311 aa 1245 bb 1234 bc
which is all very well, but what if you have 100 postal codes (dutch postal codes and without a logical order). In this case it would be nice if you could use a table with one column (the postal codes) to use as criteria.
im wanting to reference a value of a dropdown which is already serving a function - the dopdown already has it's bound set to 3 but i want this new function to refercne the second column-
im tyring:
"Tutor = '" & Me.ComboStaff.Value(2) & "'"
which isn't working- how do i reference the second column of a dropdown if its bound to its third?
Hi. I have a question I'm hoping someone can help me with. I would like to take data from multiple columns and put the data into one column. Additionally, I do not want to exclude any data (union all) and I would like to group the resulting union by another field. For example:
So far I'm using the following SQL. What do I need to add or change to get my desired result of grouping the unioned depths by the 'sample event' field?
I appreciate any help anyone may have to offer. Thank you.
SELECT Depth1 AS Depths FROM Depth_Velocity_Substrate_Correct Union all SELECT Depth2 FROM Depth_Velocity_Substrate_Correct Union all SELECT Depth3 FROM Depth_Velocity_Substrate_Correct Union all SELECT Depth4 FROM Depth_Velocity_Substrate_Correct Union all SELECT Depth5 FROM Depth_Velocity_Substrate_Correct
I have this table ("people") and an example of possible rows:
id(key) COL 1 COL2 department country name 1 xx yy KPP USA John 2 zz kk KPP USA John 3 ss ff TLL USA John 4 ww qq PPO Italy Marco 5 jj uu PPO Italy Marco
I have to count the number of distinct DEPARTMENT for each NAME; so, for John should be 2 (KPP and TLL) and for Marco 1 (PPO).
I have tryed in this way:
SELECT COUNT(DISTINCT department) AS NumberOfDifferentDepartments FROM people GROUP BY name;
I have a Access 2003 file and I want to filter anywhere where there last name is "expired" and change the column first name to say "no". How do I do that?
I have a big table bringing Access up to almost 2G limit. I need to add a column to that table using a query. I can’t make another table because of the volume.
How can I create a column in a query that reflects automatically the table name? Example: Table_ABC. has fields: ID, Material, Qty. I want to create automatically a column/field in the Qy_ABC with "ID", "Material" and "QTY", and in additon into it, next to "Qty" a filed/column is called "Name" and filled with "ABC". Thanks.
I'm still a little new to this, so please bear with me. I've searched to no avail for what I am looking for, so if there is an existing thread and you would rather point me in it's direction as opposed to answering my question, that would be fine.
I want one of the parameters of my delete query to be only the first column of my "ProdList" listbox, but don't know how to specify this. This is what my query looks like so far... Can someone tell me how do i need to change it?
DELETE ProductionLog.Username, ProductionLog.Date, ProductionLog.OrderNumber FROM ProductionLog WHERE (((ProductionLog.Username)=fOSUserName()) AND ((ProductionLog.Date)=[Forms]![Production]![TheDate]) AND ((ProductionLog.OrderNumber)=[Forms]![Production]![ProdList]));