I have used the Trim function in a query and am getting an error. At first I was concatanating several fields but have cut it back to the most basic form but still receive the error. The error says that the trim expression failed. Here is the code I used:
I saw my question in an online tutorial once but can't find it.
I have about a thousand records and I'm just concerned about one field. I have to strip away part of the text.
Heres an example:
AFG-006/2/E AFG-006/E AFG-008/1 AFG-008/1/E
I have to strip away all of the left part from the ascii character (/)
so all the records would look like this:
AFG-006 AFG-006 AFG-008 AFG-008
I'm sure there's a way to do this with Trim, maybe Ltrim but I'm not sure how this would be done. Does anyone have any ideas. I have over 1000 records which means it wouldn't be practicle to do it manually.
Hi all, I have a local table that has data imported from a spreadsheet and we have discovered that one of the fields brings in trailing spaces. Since the Access DB has some querys to manaipulate the data I thought I would just create a qry that would trim the value of that field but I am getting error messages when I try to run the query. Basically it is a update query with the field value written as:-
Code: Trim([Code])
I am leaving the update field in the qry blank and it complains about it not having a destination field which is understandable so how do I go about trimming the value of a field in a query. Is it even possible ?
Hello, need help with the Trim() function. In a query I am using this SQL:
SELECT TRIM(tblAllMembers.First_Name), tblAllMembers.Middle_Name, TRIM(tblAllMembers.Last_Name), tblAllMembers.Address1, tblAllMembers.Address2, tblAllMembers.City, tblAllMembers.StateOrProvince, Trim(tblAllMembers.Postal_Code) FROM tblAllMembers WHERE (((tblAllMembers.City) Is Not Null) AND ((tblAllMembers.StateOrProvince) Is Not Null) AND ((tblAllMembers.Country)="USA"));
However when I run the query, the First_Name, Last_Name and Postal_Code doesn't trim leading and following blank spaces.
I have a report that contains several numeric fields for Blood tests. I am currently using the "TRIM" function to great success in the following format
Where intHb is a Number, field Size: Double, Format: General numberSimilarly I have one or two fields which have + - characters in their name e.g. intCa2+ (Calcium) which seem to throw a type error (inconsistently).how I can achieve "skip this field if no value in the table - but display with Indicator (e.g. Hb for Haemoglobin) if value present".
I am using VBA for MS Access 2003. I left this project since 4 years and I know back to it as mass needs forced me to use it again.
I use trim function. it raises the compile error: can't find project or library. I know that the solution is by adding the library in the References item from the Tools menu. Thus What are the minimum libraries that I should add so that these basic problems solved. By the way: I already added the Microsoft Visual Basic for Applications Extensibility 5.3.
Hi all, this is my first post, I'll try and make it a good one :-)
System: Windows 2000 Office 2000
I have a small network of PC's here which have access to our central database. I have a simple form created using the label wizard which is used to print address labels for mailing lists. On most of the PC's here it works fine but on one or two (which have no obvious difference in thier setup) I get an "ODBC--Call Failed" error and it won't run the report???? I have puzzled over this for a while when I decided that I would remove the Trim function from the lines of the address fields on the label, these are put in automatically by the label wizard. This stopped the error message and the reports now run. I don't know why this is happening but I am hoping someone will.
Does anyone know of a way that I can get rid of characters off a product code so all I am left with is just the characters greater than zero?
Example...
AA0000000652618... I only want to be left with 652618.
Would it be best to use a left trim or to use a right trim function? Unfortunately there is no standard for the product numbers... meaning that some numbers are 15 characters in length (as is above) but others maybe shorter or longer...
Also where I would add in the Right or Left Trim piece of code? Regards,
I have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total ----------------------- 2 | 1234 | 80.00 ------------------------ 1 | 43526 | 20.00 ------------------------ > | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer) Dim SPSelect As String SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE" SPSelect = SPSelect & " ItemID = '" & ItemID SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
I don't get it!!!! I use it all the time. <=Date() should get all rows with no future dates...but I get this error on some of my queries...I've never got this error before... :mad: :mad: :mad: :mad:
Hi - I am using the replace function in a query expression to remove spaces in a postcode field:Replace([POSTCODE]," ","")) However, this returns an error if the Postcode is left blank - as I am trying to match the postcodes from 2 queries the error is giving a type mismatch.(I presume its this anyway)I am thinking I need to use an Iff function deal with the instance of no postcode(i.e. NULL) but I can't get it to work.IIf([POSTCODE]=NULL,"No Code",Replace([POSTCODE]," ",""))Would be grateful if anyone could help me out.ThanksRuss
I made a function to calculate the difference in hours between two times.
Function TimeDiff(Time1 As String, Time2 As String) As Double 'This function is used to calculate the difference in hours between two times. 'It is capable of handling a finish time that is after midnight.
TimeDiff = 0
If Not IsNull(Time1) And Not IsNull(Time2) Then
If CDate(Time1) > CDate(Time2) Then TimeDiff = ((CDate(Time2) - CDate(Time1)) + 1) * 24 Else TimeDiff = (CDate(Time2) - CDate(Time1)) * 24 End If
End If
End Function I have a query where there are 2 sets of times in fields (Start1, Finish1, Start2, Finish2). When I add a calculated column with the following expression: Expr1: TimeDiff(Start1,Finish1) + TimeDiff(Start2,Finish2) it calculates in the query correctly.
However, when I made a new function that incorporated this function, it gave me the #error result in the calculated field. Function OrdinaryHours(Status As String, Start1 As String, Finish1 As String, Optional Start2 As String, Optional Finish2 As String) As Double
'Initialise OrdinaryHours = 0
'If employee is NOT a casual If Not Status = "CT" Then
If Not IsMissing(Start2) And Not IsMissing(Finish2) Then OrdinaryHours = TimeDiff(Start1, Finish1) + TimeDiff(Start2, Finish2) Else OrdinaryHours = TimeDiff(Start1, Finish1) End If
End If
End Function Does anyone know why this would #Error. I have the TimeDiff function initialising to = 0 before doing anything...I am stumped why it returns #Error when it should return 0 in the case of something going wrong.
I'm running an update query and I get this error - Function Sequence Error. (#0)
I have an excel spreadsheet that I imported into a table. I am trying to update the part table with date from the spreadsheet. The part id is unique and thats how the tables are joined. I only need to update 5 fields. The table I'm trying to update is a linked table.
I can't figure out what I need to do to make the necessary updates.
Hi, i know ive asked this in another forum, but i just read a thread on a query function which may be able to deal with this so thought id post it here:
the function i just saw:- Expr1: IIf([Sold],="","0",[Sold]) this does nothing to help my problem though so any ideas?
i have three fields; [boughtin], [sold] and [instock]. [boughtin] comes from one query and [sold] from another.
[instock] = [boughtin] - [sold]
because [sold] only has a value once an invoice has been created for that item, it means that if you try to view an item that hasnt been sold yet, you see how many have been bought in, but no sold value and then because of this, no stock value. (you just get an '#error' value in the stock box.
basically what i need is this: if both [boughtin] and [sold] are present, then the calculation is done and a [stock] value is shown. if only [boughtit] is present then the stock value simply shows the [boughtin] value.
[boughtin] is located on one subform (frm_stocksub) and [sold] is one another (frm_stocksubsub).
i need a code to put into the after update event of the main combo on the main form so that it detects this and then displays the approriate value once both queries have run.
a simple idea of what i want is: If [Sold] has no value Then [InStock] = [BoughtIn] Else [InStock]=[BoughtIn]-[Sold] End If
Obviously there is far more detail in terms of locating the particular text boxes, but i hope it gives you an idea of what i want.
I am working on a marketing database. I have two tables that are in use called tblListInfo and tblResults.
In tblListInfo the fields I am using are DropDate, ListName and Marketcode. In tblResults the fields I am using are JoinDate and Marketcode.
I am attempting to write a query that the end result will display the number of records within the 1st week of the promotions drop date. For every combination where the ListName value AND the DropDate value are unique, I want to count the number of records in tblResults that have JoinDate BETWEEN DropDate AND DropDate + 6.
I tried writing the code for this in pieces starting with:
SELECT DISTINCT tblListInfo.[ListName], tblListInfo.[DropDate] FROM tblResults INNER JOIN tblListInfo ON tblResults.[Marketcode] = tblListInfo.[Marketcode]
This gave me my first ideal part of the output I was seeking. I listing of all the possible marketing lists used with duplicate names occuring only when there were different drop dates. for example:
Listname Drop Date List - 1 1/1/08 List - 1 1/15/08 List - 2 1/1/08 List - 2 2/5/08
From there I wanted to modify the process to include a third field that included the # of records in tblResults that have the JoinDate field BETWEEN tblListInfo.[Drop Date] AND (tblListInfo.[Drop Date]+6). The ideal output would be like this:
Listname Drop Date Week 1 List - 1 1/1/08 15 List - 1 1/15/08 8 List - 2 1/1/08 32 List - 2 2/5/08 12
My attempt at getting to this result is below:
SELECT DISTINCT tblListInfo.[drop date], tblListInfo.[ListName], count(tblResults.[join date]) AS [Week 1] FROM tblResults INNER JOIN tblListInfo ON tblResults.[MarketCode] = tblListInfo.[MarketCode] WHERE tblResults.[Join Date] BETWEEN tblListInfo.[drop date] AND (tblListInfo.[drop date]+6)
I receive an error at this point saying: "You tried to execute a query that does not include the specified expression 'ListName' as part of an aggregate function."
Can anyone offer suggestions/corrections to my approach and logic?
Hello I keep getting the error after I copied a form and saved under a different Name ++++++++++++++++++++ A problem occured while Microsoft Access was communicating with OLE Server or ActiveX control.
There may have been an error evaluating the function , event or Macro
+++++++++++++++++++++++++ Strangely, I keep getting this error when loading and clicking on a control in the ORIGINAL form while the copy seems to work without errors.
I have deleted some tex box controls from the first form and added a few new ones in the copy.
I have tried a copy of the database by deleting the New form, but still get the same error.
I have run DEBUG on both form codes and everything seems fine.
I have checked the refrences and find none missing.
Is there any way to resolve this issue. Or am I doing something fundamentally wrong by copying and modifying the form ?
I have used access to create a database for my web site which I use ASP to connect. I've written a function in Access which takes a product description and shortens it down...
Public Function GetShortDesc(strInput As String)
Dim x As Integer Dim tempstr As String Dim checkstr As String x = 100
tempstr = Left(strInput, x) If Right(tempstr, 1) = " " Then GoTo stringfound Else Do Until checkstr = " " x = x + 1 tempstr = Left(strInput, x) checkstr = Right(tempstr, 1) Loop End If
stringfound:
GetShortDesc = Left(strInput, x - 1)
End Function
And I have used this function in a query which works fine in Access, but when I go to my ASP page and try to return the field to my page I am getting an error...
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC Microsoft Access Driver] Undefined function 'GetShortDesc' in expression.
It looks like the ASP page isn't accepting my function, Can anybody help???
SELECT tblTempProject.project_number, tblTempProject.Project_name, tblTempProject.subsidiary, tblTempProject.division, tblTempProject.unit_number, tblTempProject.client_name, tblTempProject.resourcing_contact, tblTempProject.project_cat_type_code, tblTempJob.headline, tblTempJob.contact_name, tblTempJob.contact_staff_number, tblTempJob.contact_unit_number, tblTempJob.resourcing_team_email, tblTempJob.creation_date, tblTempJob.nominatio_date, tblTempJob.decision_date, tblTempJob.sar_status, tblTempJob.publication_level, tblTempJob.sar_number, tblTempJob.last_modified_timestamp, tblTempJob.last_updated_by, tblTempJob.requirements, tblTempJob.positions_filled, tblTempJob.closure_description, tblTempJob.other_inf, tblTempJob.description, tblTempJob.qual_exp, tblTempJob.Tag1, tblTempJob.Tag2, tblTempJob.Tag3, tblTempJob.Tag4 FROM tblTempJob, tblTempProject WHERE tblTempJob.project_number = tblTempProject.project_number AND tblTempJob.project_category_type_code = tblTempProject.project_cat_type_code AND tblTempJob.project_company_code = tblTempProject.subsidiary AND tblTempProject.subsidiary='NL' AND (tblTempJob.sar_status='FIR' Or tblTempJob.sar_status='HOL' Or tblTempJob.sar_status='PRO25' Or tblTempJob.sar_status='PRO50' Or tblTempJob.sar_status='PRO75') AND tblTempJob.project_company_code=[tblTempProject].[subsidiary] AND tblTempJob.project_category_type_code=[tblTempProject].[project_cat_type_code] AND tblTempJob.project_number=[tblTempProject].[project_number] AND tblTempJob.external_resourcing=-1 GROUP BY tblTempProject.project_number ORDER BY tblTempJob.sar_status;
When execute this query then I get the following error: "You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)"
When delete the line: GROUP BY tblTempProject.project_number, then it works. I don't know what I must do to get this right so that I have every project one time in the result.
