Trimming Query Return Value
HI, i have a field in a query called [cost_type], these typically contains the values "principle works" or "additional works" how can i set the query up so that it doesn't return the "works" part of the record? Could i just return the first 10 characters of the result?
thanks
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Trimming Characters
Good Morning to all i would like to kindly ask if there is a pre defined function in the VBA SQL of access in which it will trim a certain field programmatically for example Old string = Manila-MSE Building - 30.26f New String = Manila-MSE Building Thanks for your help
View Replies !
View Related
Trimming An Address
I have a table that has about 5000 street addresses (ex. 1234 your st.). I want to get the all the characters until the first space. So for (1234 Your St.) I want to get 1234 for W1234 St I want W1234. Is this possible?
View Replies !
View Related
Trimming Data In Listbox
Another issue for my contacts database for work. I have a listbox on the edit contacts form that lists all contacts in the database. The listbox rowsource is SELECT [ContactID], [LastName] & ", " & [NamePrefix] & " " & [FirstName] & " " & [Business/Organization] FROM tblContacts ORDER BY [LastName] & ", " & [FirstName] & " " & [Business/Organization]; Some of the contacts that are businesses or organizations, do not actually have the first and last name filled in, just the business/organization name. So what happens in the list I get all of the entries that do not have FirstName/LastName at the top of the list, with the Business name following a few spaces. The way it looks is: , Stop & Shop Supermarkets But I would like to trim that beginning part if there is no FirstName/LastName so that Stop & Shop Supermarkets gets sorted with the S's, like this: Stabile, Lisa Stop & Shop Supermarkets Stott, Joan Is there something I need to put in the rowsource to accomplish this? I've searched these forums for an answer, and turned up no results. Attached, I have a screenshot of the form with the listbox. Thanks ScrmingWhisprs
View Replies !
View Related
Trimming Off First 7 Characters In A String
My DB is on SQL Server with an Access adp front end. SQL uses the Windows NT user name and password for security and also pulls in the user name into a field in every record so we know who has added and modified it. For one table we are actually using the user name field on the form and in a report for reasons other than security. The user name is filled in with first the name of the network group, then a slash, then the user name itself. So there are 7 characters I don't really care about. Is there a way in VBA to remove those first seven characters and only show what comes afterwards? Thanks for the help!
View Replies !
View Related
Is Trimming Field Size Where Suitable Good Practice?
If I have a DB with several tables containing thousands of records, and most of those records only ever need say 8 characters, does anyone know if changing the field size from default 255 chars to 8 chars will actually benefits the the DB? Potential benefits I'm thinking may occur are reduced filesize and maybe some speed?
View Replies !
View Related
Need Query To Return Same Record Twice
Each record in my table has six possible sale dates as a result of cancellations. My problem results when a record is sold twice during the queried time frame (SaleDate1 and SaleDate2). Currently my query uses an if statement and if they are in the same time period, it will only return SaleDate1. I would like to see the record returned twice (once for SaleDate1 and once for SaleDate2). Any suggestions?
View Replies !
View Related
Time Return Query
Hi Everyone, I have a database that I enter time for in the form of military time, I would like to make a query to count the number of entrys in the PM,AM, And NIGHT. For instance if a record has a time of anywhere between 700 or 2299 I would like it to be in the AM catagory, then I would like to total up the number of AM records. Any help would be great. Thanks. AM=700-1399 PM=1400-2299 NIGHT=2300-699
View Replies !
View Related
Query To Return Table Name As A Value?
Hello all, Is there a SQL function in Access that will allow it to return (as a field value) the name of the table where a particular record was found? The reason that I want to do this is because I have a single table (Table A) with building address records counted and broken down by state and by month - but some of the records belong to smaller groups (e.g. Zone 1, Zone 2, etc.) that aren't states, but that I want to count in the crosstab as if they were. There are building addresses on Table A that belong to each of these four groups, so I need to somehow match the records on Table A with the appropriate Zone, and place the name of that Zone in the "State" column of the query results instead of the actual state so that I can then count the results in the same crosstab. I had the intention of resolving this by creating three tables (with the listings of address information for each Zone #), then matching records in Table A to each Zone table in a query by the address field to get each record's appropriate Zone no.(if available), then using the results of this query in my crosstab - :confused: but I can't figure out how to get Access to create a calculated field that lists from which table a resulting query record came from, so I can't continue.:confused: Any suggestions, if able, would be appreciated!
View Replies !
View Related
Wrong Query Return
I placed ="G*" in a text field to get all names that begin with G and a blank return was issued. Maybe the manual I have has the wrong info. I have Access 2003 and a Access 2000 manual. Please help.
View Replies !
View Related
Query - Weird Decimals Return
I enter values into a table with 2 decimal places. I have a query that applies percents to the values - I have set the results to have 2 decimal places. I have another query that summarises data from the previous query, however the results I get are not right, the decimals differ from the actual result, if I make a sum of the values by hand. It seems as if the query I have made to apply percents only DISPLAYS data with 2 decimals instead of actually rounding it, so that the other query summarises data with more than 2 decimals, and thats where the difference int the end result comes from. I don't know how to fix this however. Can someone please help. Thanks!
View Replies !
View Related
Query That Return No. Of Records In Run Time
Hi all, May I know some to construct any functions or query string that can track the no. of records that mean certain criteria in the run time. Basically I want to track the records in a subform (in datasheet view). Should the no. of records is 0, then I can disallow user from saving it onto the table for better record keeping. Thanks !!
View Replies !
View Related
Blank Or Duplicate Return From Query?
I have searched for an answer and spent some hours but still not getting it. I have played about with the query joins and still nothing? I have never had this problem with my database in 5 years (well not that i have noticed). Some records are fine others are triplicated and quite a lot are blank? This is the SQL for the query. SELECT Employee.EmployeeID, Employee.FirstName, Employee.Surname, Employee.Address, Employee.[Town/City], Employee.Region, Employee.DateofBirth, Employee.Nationality, Employee.MaritalStatus, Employee.PassportNo, Employee.ExpiryDate, Employee.Nextofkin, Employee.Relationship, Employee.NOKAddress, Employee.ContactNo, Certificate.CertName, EmployeeCert.Level, Trade.TradeName FROM Certificate INNER JOIN (Trade INNER JOIN ((Employee INNER JOIN EmployeeCert ON Employee.EmployeeID = EmployeeCert.EmployeeID) INNER JOIN EmpTrade ON Employee.EmployeeID = EmpTrade.EmployeeID) ON Trade.TradeID = EmpTrade.TradeID) ON Certificate.CertID = EmployeeCert.CertID WHERE (((Employee.EmployeeID)=[Forms]![Employee]![EmployeeID]) AND ((Certificate.CertName)="IRATA")); Basically i have to solve this problem. I have a report that is based on this query and if the employee is not displayed correctly instead of the employees Name and Surname i am getting "#Error" ? Many thanks in advance
View Replies !
View Related
Query Doesn't Return Any Fields
I created a database that contains 2 tables. I then created a query that simply groups the data and calculates a row total. And it works as expected. However, when I try to create a report, using the query, I get, "The wizard was unable to generate fields from the record source you chose. Perhaps you chose a query that doesn't return any fields." What am I doing wrong? Screencaps attached. Thanks.
View Replies !
View Related
Query To Return Computers Without Allocation
I have 2 tables tblComputers & tblComputerAllocations tblcomputers has 85 records & tblComputerAllocations has 71. I would like a query that returns the 14 computers that have no allocations. I tried a union query on field ComputerID but i must misunderstand its use!! Thanks in advance, Phil.
View Replies !
View Related
Query Parameter To Just Return Weekdays
I am trying to write a query that looks at groups of records for averages and maximums but the records are only from Mon-Fri (Trading Days). I have a query that runs everyday and compares today's close to see if it is greater than the maximum close of the last 20 trading days. I want to use between date()-21 and date()-1. BUT this will include weekends where there is no data. QUESTION: Is there a way to say between the last 20 trading days(Mon-Fri)? I researched dateadd() and found one place were it says that "w" is used for weekday. http://www.database-design-2000.com/dateadd.htm BUT when I did a simple query to see if it would exlude the weekends it doesn't. I will have no data for the weekends but if I am doing an average of the last three days and this is run on Monday, it will only include Monday and not the previous Thursday and Friday. Thanks for your help:)
View Replies !
View Related
Query To Return Intermediate Values
Hi All, I need an Access query to return the rate at which a customer should be charged. A customer's rate can be set for a period of 1 to 6 months and the customer's rate table could look like this: ACCOUNT_ID........START_DATE........END_DATE...... ..COST_PER_UNIT 31308.................01-Apr-07................30-Jun-07.................6.195 31308.................01-Jul-07.................31-Aug-07.................6.304 31308.................01-Sep-07................31-Mar-08.................5.422 31308.................01-Apr-08.................31-Aug-08.................6.304 I need a query (preferrably SQL) that will return the rate a customer should be charged each month, e.g. 31308 / Aug 07 / $6.304 31308 / Sep 07 / $5.422 31308 / Oct 07 / $5.422 etc, etc I'm using Access 2003 and the rates table contains details of about 2500 customers. Any ideas would be welcomed. PS. Can someone tell me how to paste a table straight from Excel into the forum post body???
View Replies !
View Related
Return The Query Results On The Form
Hi, I am not much familiar with VB Coding.Can any body please help me with this Please:When i click on submit button,it has to show the query results on the Form.Please find the below code: Private Sub Command_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSql As String Set db = CurrentDb strSql = "SELECT Interface.[Interface ID], Interface.[Interface Name] from [Interface]" Set rs = db.OpenRecordset(strSql) Do While Not rs.EOF Debug.Print ("Interface ID: " & rs![Interface ID] & "Interface Name: " & rs![Interface Name]) rs.MoveNext Loop rs.Close db.Close End Sub ####################### It is not retrieving any thing.IF my code is wrong,Please advice me with the necessary steps to view the results on the form,when we clicl on the submit button. Thanks, Kanuri
View Replies !
View Related
Query To Return Record Set Where 1 Column Is Greater Than Another
Creating a query in Design View, how does one create a query in the "Condition" field that tells the Access to return the record set where the contents of one column is greater than the other? Table Name=SURVEY_RAW_DATA Column Name=TOTAL_EXP Column Name=TOTAL_NX There are obviously many more columns than those listed above in the table, but these are the two I want to compare. Also the two columns contain numbers. In SQL I would write it like this: Select * FROM SURVEY_RAW_DATA WHERE TOTAL_EXP > TOTAL NX; Thanks in advance, Ken
View Replies !
View Related
Automatically Return Parameter Query As A Report
I created a combo box that runs a parameter query based on the name entered in the box. Is there some way to return the results as a report instead of in datasheet view? I'm wondering if inserting some sort of code into the event procedure of the "OK" button (which launches the query) would work. Would greatly appreciate help---
View Replies !
View Related
Query Needs To Return Match And Unmatched Results
I have a query pulling data from several tables. In the middle I've got join between a part number in a BOM table and a part number in an inventory table. What I want is the query to return the inventory location when we have stock. When we have nothing in, then there would be no entry in the inventory table and it should return a blank or null in this and all the proceeding tables. Can this be done?
View Replies !
View Related
Query..not Return Double?? Tables - Oracle
Ok. I have tables vinculate to ORACLE, the primary key of Oracle is double, from Access of query return one number rounding 1,00000902026541907589E+20 this is the problem, in query i have this funtion: IIF(isnull([CAMPO1]);[CAMPO2];[CAMPO1]) this return one number ok field(CAMPO1) and other bad(CAMPO2 it's rounding). I need that return this query two doubles. example: bad - 1,00000902026542E+20 = 100000902026542000000 ok - 1,00000902026541907589E+20 = 100000902026541907589 help me...:confused: My english is bad bad bad... :D
View Replies !
View Related
Query Needed To Return Last, Or Next To Last, Value Based On Date...
I have the following tables: Rates and SelectedDates, which are structured as follows. Rates: RateID, EffectiveDate, Rate SelectedDates: Date. Table is bound to a combobox in which user selects one or more dates, mm/dd/yyyy, which is the same format as Rates.EffectiveDate. I need a query to return the first Rates.Rate for the following conditions. (there will be a minimum of one record in the Rates table) IF there is only one Rates.EffectiveDate for the SelectedDates.Date return the associated Rates.Rate ELSE IF count(Rates.EffectiveDate <= SelectedDates.Date)>=1 return the first Rates.Rate where Rates.EffectiveDate <= SelectedDates.Date ELSE return Rates.Rate associated to the Rates.EffectiveDate closest to the SelectedDates.Date. I hope this is clear, as it is late and I am tired...:confused: Thanks in advance, Rod
View Replies !
View Related
Return One Unique Instant Of A Field From A Query
Hi, I've been given a flat-file database to 'improve'. One field is simply titled 'Room' and refers to a room number in a school. I want to create a MakeTable query that will run through all the records and make a table containing one instance of each room number (for normalizing). Hope that makes sense! Any help much appreciated, Steve
View Replies !
View Related
Query To Return A Field To Determine Value For Report
I have a field on a report which I am struggling to produce the right query for. I have a table - tblTillDept which consists of a 4 fields. The first is the PK, which is the number of a till department. The second - fourth fields are names of 3 different branches of the small garden centre I am writing the database for. The values in these fields are the names of the corresponding departments. Not every branch has exactly the same name for each field. Prior to the report being is run, a small form, frmCentre pops up asking the user to select the Centre they are printing the labels for. They select the name of the branch from a combo box (cboCentre) bound to a field list of tblTillDept. When the report is formatted, the intention is that the value of this combo box selects the field from which the value for the department is taken. I can see that I need to set the control source of this property to some sort of query, but I can't quite work out how to do it. I've uploaded the database, written in Access 2000, here (http://www.moppy.co.uk/plants.mdb) as it's too big to post as an attachment.
View Replies !
View Related
|
TRACKER
