Error With Trim Function

Nov 1, 2005

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:

fullname: trim([last]) + ", " + trim([first]) + " " + trim([middle])

I then used
fullname: trim([last])

I got the same error. Just using the field last does not generate an error.

Any ideas? I created the DB in Access XP and am getting the error in 2003. I tried the changes in 2003 but still got the error.

thanks. :)

View Replies

Mid Or Trim Function?

Dec 10, 2007

a maybe simple question:

With the mid function one can make sure that a part of a text is token to display on a report, output or whatever.

But how to do the following:
"John Isaac"
"William Bill"
"Michael Robert"

In the report the following has to be visible:
"John"
"William"
"Michael"

The use of mid is i think possible, and starting with the first letter as well, but how to determine when the space is coming?

Please advise!

View 3 Replies View Related

Trim Function

Jan 8, 2008

Hi there

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.

Thanks

View 9 Replies View Related

Trim Function In Query

Sep 5, 2006

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 ?

Thanks in advance,
Mitch....

View 4 Replies View Related

Trim Function In Access?

Mar 30, 2005

Dear all:

Is there a trim function that exists in access vb so I can apply to a text box to remove irregular spaces in a textbox field?

I am importing an excel file and a column has names with irregular spaces in it. Hopefully this trim function can solve that issue.

Many thanks,

Regards,

Dion

View 2 Replies View Related

TRIM() Function In Query Help Needed

Mar 14, 2005

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.

Help please

View 13 Replies View Related

Queries :: Using Trim With StrConv Function

Oct 6, 2014

I'm having a problem using Trim with StrConv functions, when I use this code Trim works.

Code : FullName: Trim([dbo_sponsor]![spn_chair_person])

but when I add StrConv, Trim stops working. Below is the code that doesn't work.

Code : FullName: Trim(StrConv("Dear " & [dbo_sponsor]![spn_chair_person],3) & ":")

Going to try to Trim first then then concatenate.

View 3 Replies View Related

Reports :: TRIM Function For Numbers

Jun 13, 2014

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

=Trim("Last Bloods: " & [dtmBloodsLast] & " Hb " & [intHb] etc.....

However When I try to Bracket out any fields that do not have a value in the table I get a #Type! Error

e.g: Trim("Last Bloods: " & [dtmBloodsLast] & (" Hb "+[intHb]))

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".

View 4 Replies View Related

Queries :: Trim And Replace Function?

May 31, 2014

I need to remove 1 final - from my string. Here is my sql.

Quote:

SELECT tblmaintenance.sernu, Right([sernu],10) AS m10digit, InStr([m10digit],"-") AS [Space]
FROM tblmaintenance;

View 3 Replies View Related

General :: Trim Function - Can't Find Project Or Library

Dec 3, 2012

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.

View 1 Replies View Related

Trim Text Box Causing Error?

Jul 6, 2006

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.

Thanks,

Matt.

View 6 Replies View Related

Left Trim / Right Trim

Sep 29, 2006

Hello,

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,

Peter Vav

View 2 Replies View Related

Forms :: SUM Function Produces Error From Calculated Function

Jan 30, 2014

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.

=SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))
#Error

View 2 Replies View Related

Error Msg - On SUM Function??

Dec 21, 2004

why am i receiving the followign error:
"Sub of function not defined"

on the following statement:

me.txtTotal = Sum(lstbox.Column(7))

I'm so stumped.. this is a built-in function, no?

Thanks!

- Jenn

View 1 Replies View Related

WHY Am I Getting This Error? (Date Function)

Aug 12, 2005

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:

Sorry about the poor image quality.

View 1 Replies View Related

If Function To Remove Error

Oct 29, 2006

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

View 5 Replies View Related

#Error With Function In Query

Nov 11, 2006

Hi All,

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.

Brad

View 5 Replies View Related

Function Sequence Error

Oct 30, 2007

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.

View 1 Replies View Related

Query Function With #error

Mar 15, 2008

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.

Thanks
Jared James

View 2 Replies View Related

Aggregate Function Error

Apr 3, 2008

Hi,

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?

View 2 Replies View Related

There May Have Been An Error Evaluating The Function

Nov 27, 2006

Hello,

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 attached the file with the two forms

View 2 Replies View Related

Undefined Function Error

Mar 5, 2006

Hi,

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???

Thanks in advance

View 4 Replies View Related

ODBC Error 1 'Undefined Function'

Jun 8, 2006

I am trying to run a query in an Access Database from another program using ODBC.

The query contains a user defined function and works fine when I run it in Access.

When I try to run the same query externally via ODBC I get the following error:

"ERROR 1 - [Microsoft][ODBC Microsoft Access Driver] Undefined function 'replace_chr13' in expression"

If I replace my user defined function wit a standard Access function such as LCase, the query can be accessed externally fine!

Can anyone suggest a solution?

View 1 Replies View Related

ODBC Error 1 'Undefined Function'

Jun 8, 2006

View 7 Replies View Related

Error 3122 (You Tried..... Aggregate Function)

May 1, 2007

Hello,

I have the following query:

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.

Thanks in advance,

Nico

View 8 Replies View Related

Recall - Aggregate Function Error

Dec 14, 2004

I'm trying to do a simple sum in a query to add up 3 costs to give me an overall cost. I've put the sum in as follows:

Total:sum(nz([Cost1],0)+nz([Cost2],0)+nz([Cost3],0)).

But I'm coming up with an error as follows:

"You tried to execute a query that does not include the specified expression 'AccountNum' as part of an aggregate function".

Where am I going wrong?

Cheers,
Recall.
Find attached an example.

View 1 Replies View Related