Queries :: Text Box Values Used In Query Contain Brackets
Sep 3, 2013
I have a query which uses values in two hidden text boxes, in order to populate a sub form.Unfortunately some times the data in the text box contains brackets within it as follows:
'120/60 ZR17 (55W)'
When this occurs the query returns no data, even though records with a matching code exists in the table I am working with where Speed is 'FR'.
Code:
WHERE (((stockdyn.SPEED)="FR") AND ((stockdyn.DESCRIPN) Like '*' & [forms]![frmSearch]![Text10] & '*'))
How can I get around this, as surely if I used quote marks it would take "[forms]![frmSearch]![Text10]" as the value I am searching for.
I have a column containing records of the timestamp of an event. I need to extract the date out of each of these records and put them in a separate table. The date and time of each record is contained within a bracket.
I want to prompt the user to enter a merchant name, but want the results to return close matches.I know how to use the wildcard in the Criteria field of the query, but I want to use brackets.I know that "*Southwest*" Will return Southwest Airlines.So I tried *[Southwest Airlines]* and it treats the criteria like a text string.
I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:
Code:
AcctNum FoodType FoodClass ------- -------- --------- A123 Apple Fruit A123 Apple Fruit A123 Grape Fruit A456 Potato Vegetable A456 Potato Perishable A789 Carrot Vegetable A001 Banana Fruit
For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:
It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').
I have a question regarding counting of text values base on their status and using that result to a calculation.
Say, I have a table of Demand of Positions, wherein, I have a specific Job Title for a certain Department that have number of workers needed (demand quantity) and a table of candidates for that job title and their status, say, Arrived, Visa Processing, Visa Applied, Visa Issued, and With Ticket.
What I would want is to make a summary out of the two tables, where the query will count how many candidates are there in that specific job title and have a field of status say, field of Count of Arrived, Count of Visa Processing and etc., and a field where I can add all of the count of candidates per status and deduct the result to the demand quantity where that field would be named Balance.
The problem is that the status varies on every candidate on that specific job title because the status field is used to track the progress of each candidate and this scenario will make the query blank because there would be no such record due to their status.
I tried making a summary following my requirement and you will see that in the attached file together with the SQL code of that query that the balance field value is blank.
I have one field where string contains several words separated by semicolon and my goal is to be able to remove duplicates within the string and keep only unique values. Here is an example:
I have a Volunteer Database that has teams. There is only one team leader but some of the teams have two or three assistants. Currently I have a query that lists all the assistants for each team vertically.What I want to do is list the Assistants horizontally under headings like Assistant 1; Assistant 2; and Assistant 3 for each team on one line.
I am currently developing a database to provide a friend with an auction tracking and ordering system.
I will have a number of questions the first is related to the Home Page/Dashboard/Summary form I am creating. I basically have a few sections, one of which is a combo box offering the user to select an auction to view in the summary section. This summary section contains the list of lots but I also hope to to expand on this and create multiple text boxes, each containing the answer to a number of queries (totals etc).
I have written all the queries and can see the results however as the form does not have a record source I would like to know how to make each text box populate with the result of different queries (ideally in vb - I am using ms access 2010)?
I have tried a few things, control source doesn't apply as I have no record source (i guess). I investigated Dlookup/Dcount but am unsure if these apply for the same reason. I understand I am likely to need a recordset etc in vb and have already tried a few things but unfortunately none of them work.
This is almost a cross-forum post. I started a post in the reports forum to see if it were possible to sort/group the data by field values. Everyone knows this can be done row-by-row, but I was looking for a way to do it horizontally as well.
It should look something like that where the years are values of [year] field and the seasonal quarters are values of [quarter] field.
I have been searching for something that will help me do this when I ran across information on the "crosstab" query. This, in principal is what I am looking for BUT I am not dealing with numeric data here. Is there still a away to make a crosstab query/report with text fields where there would normally be numeric data?
I a trying to search some product from a search button and two combo boxes text values ,and on serch the vba code is :
Code:
Private Sub Command4_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim varRecords As Variant
[code]...
I just dont know,the fuile is created on click of button but with headers only,dtaa is not coming but wheni debug ,in immediate window,data is oming but just not coming in excel file.
In Access 2010 I have a Data Entry Form on which I have an unbound textbox in the header that the user can put a default date in. In the body of the form is a bound textbox that records the date and the default value is set as =defaultdatestat (obviously the name of the box in the header).
Problem: The default date shows up perfectly until a value is put in any of the other text boxes.
For further info : If you put values in text boxes default value disappears; if you then push escape the default value reappears when the values in the text boxes disappear.
I have a table Pmt in Ms Access with following information:
Table Pmt Cust no Inv no T no Date Amt 123 ABC T1 20110131 1000 123 ABC T2 20130228 1000 123 ABC T3 20130331 1000 789 XYZ A1 20130131 2000 789 XYZ A2 20130228 2000
Fields "Cust no", "Inv no" and "T no" are in Text format, whereas "Date" and "Amt" are in Number.
My question: how can I concatenate row values using a query. The query results that I wanted are as follows:
Cust no Inv no T no Date Amt 123 ABC T1, T2, T3 20130131, 20130228, 20130331 3000 789 XYZ A1, A2 20130131, 20130228 4000
I Want to Create an Query to Find Different Values in A Table/ Query
Like Month Year Name School Post Jan 2012 Ankur School 1 Post 1 Feb 2012 Ankur School 1 Post 1 Mar 2012 Ankur School 2 Post 1 Apr 2012 Ankur School 2 Post 2 May 2012 Ankur School 2 Post 2 June 2012 Ankur School 3 Post 2
Now i want in result of Query is only like
Month Year Name School Post Jan 2012 Ankur School 1 Post 1 (Starting Ledger) Mar 2012 Ankur School 2 Post 1 (Change in School) Apr 2012 Ankur School 2 Post 2 ( Change in Post) June 2012 Ankur School 3 Post 2 (Change in School)
I need to use square brackets as part of the criteria in a LIKE SQL statement. The criteria is:
[ABC123][SR]
ie, find all records which have that as part of a longer text string. How can I amend the SQL statement below to "escape" the square brackets and treat them as part of the string?
SELECT * FROM p_Overall WHERE (((p_Overall.Assigned_Dept)="ICONS_IMAC")) OR (((p_Overall.Brief_Description) Like '*[ABC123][SR]*'));
I recently MERGED two columns in a database file using the following code:
SELECT "(" & RESULT & ") " & QUALIFIER AS MERGED FROM SOIL_STL3_MERGE AS SOIL_STL3_MERGE;
The RESULT field was a number which I had to change to a TEXT field since my QUALIFIER was a TEXT field. Anyway the result was the field called MERGED which depicted the data as follows:
(100) U (500) U ...... ....... I use the Find and Replace twice to get rid of the first ( and run it again to get rid of ) resulting in a field that looks like this:
100 U 500 U
My question is can I solve this through an UPDATE query statement through SQL or some other way with a function.
I would rather automate the removal of the ( ) or change my original code to NOT put brackets around my result.
I have created a query that counts the AppSizerPerUD for every User Drive (UserDriveID). Now I want to assign these values to the other query (UserDrive Usage) for every UserDriveID. So I want to create a new column in the UserDriveUsage query that will contain the AppSizePerUD. In this case in the first two rows must be 23.6, then one row 18.38, the third one 45,39 etc.I need something like a VLOOKUP in Excel. I have tried DLOOKUP but probably I am not uisng it correctly.
I have a table that has a list of tasks and checkboxes attached to them to be checked once the task is completed. I need to run a query on the table that will only bring me back the tasks with a completion that is false.
Everything that I read online indicates that this is a difficult task for access. Maybe I can accomplish this in SQL view instead of design? If I put false is all of the yes/no fields, the query brings back nothing.
I am trying to create a query to append new records from my NEW database into my old excel database.....
The old DB has 4-5 extra tabs that the NEW database does not have so when I append, in those extra columns the new database will just have blank records since the column doesn't exist.
Usually I do a append query in design view. but sometimes it gets funny because it creates duplicates...
How would I go about it, so its quicker and persistent like creating a macro excel.
I have a form that has a dropbox list and I want the values of the form to change according to the value in the dropbox.For example:
When the dropbox contains the value A, The form would display x=1, y=2, z=3.
When the dropbox contains the value B, The form would display x=4, y=5, z=6.
I managed to create a query that opens the QUERY's results according to the value in the dropbox but I can't managed to run the query in the form, return the values according to the query and display them in the form.
I searched the internet and found many options including the Dcount command but I don't think it fits my case..I know I have to write something in the AfterUpdate field of the dropbox but I just can't figure out what it is...
modify the code below to Show the LocationName in the Schema Column instead of the MPID? I attached a pic showing the relationship between the two tables which contain the data I'm trying to query.
SELECT [Locations Query].LocID, Qry_MPLoc.MPID AS Qry_MPLoc_MPID, [Locations Query].Location, [Locations Query].Schema FROM [Locations Query] INNER JOIN Qry_MPLoc ON [Locations Query].[LocID] = Qry_MPLoc.[LocID];
I have employee attendance in Excel i.e Emplpyee Number, Day1, Day2, Day3....Day31
I have a table in access that have columns Employee Number, Day1, Day2, Day3...Day31, Presents, Annual Leaves etc
Excel file data needs to be transferred in Access table but the number of Presents, Annual Leaves etc needs to be calculated in Access during transfer not in Excel.
Now due to limited knowledge I do not understand how to resolve this issue and how to start. How to calculate the number of Present and Leaves.
