Queries :: Checking For Special Characters In Descriptions

Mar 12, 2015

I want my to detect special characters in my descriptions that are not found on keyboard and display as error.
Tried using the following

Description check: IIf([Common Description] Is Null Or [Common Description] Not Like "*[!a-z0-9@=.^_$%!#&'`(){|}*?~[]/-]*" And [Description Local] Is Null Or [Description Local] Not Like "*[!a-z0-9@=.^_$%!#&'`(){|}*?~[]/-]*","<<Error Desc>>","OK")

but when i tested it using some data, it shows all as <<Error Desc>>

some of the special characters i want to check for are βuΩ etc.

So if my description contains characters that are not: a-z OR 0-9 or any of the following ~!@#$%^&*()_+=-`][';/.,<>?:"{}|~

it will return <<Error Desc>>

View Replies


ADVERTISEMENT

Queries :: Search Field For Special Characters

Oct 22, 2014

I am trying to query on a field for any that contain special characters. How can I accomplish this? I do not know what special characters could be in this field, so I would want to query for ANY special character.

View 2 Replies View Related

Queries :: Possible To Query A Field For Special Characters In A String?

Oct 11, 2013

Is it possible to query a field for special characters in a string? I want to find any records that have characters other than A-Z, 0-9

View 9 Replies View Related

Queries :: Removing Special Characters Where Original Data Length Varies

Mar 1, 2014

I thought I had solved my initial problem of removing the apostrophe character for each zip code field. Most of the fields have data that reads '12345-1234'. I want to remove each (') character and the "-1234" so I end up with a zipcode of 12345.

I created 2 queries for this process. I first run a query with the following statement "Left([ZIP],Len([ZIP])-6)". That gets rid of the "-1234' ".

The I run the second query with this statement "Right([ZIP],Len([ZIP])-6)".

These 2 queries work perfectly if the original zipcode is " '12345-1234' ", but if it is " '12345' ", the entire zipcode is deleted.

I have attached 3 (.jpg)'s to show you what I am talking about.

View 7 Replies View Related

Special Characters

Jul 23, 2007

Hi guys,

I have a question regarding special characters that access doesn't like to see.

When passing a string to be absolved via html, if I set myHtmlBody = "<font color="#000FF"></font> it will throw a syntax error because of the # character.

How can I solve this? Thx for any help.

View 2 Replies View Related

Replacing Special Characters

Feb 20, 2007

I am exporting an Access table as an xml file and need to be able to identify and replace '&', '(' and ')' as they are not accepted in xml.

Does anyone know how a query can be built which will find these characters in fields and update to ' and' or just remove?


Thanks

View 1 Replies View Related

Help With Special Characters In Access

Jun 14, 2006

Hi,

I'm not sure whether i'm in the right forum or not but i'll tell you my problem and if i'm not you can point me in the right direction.

I have a form with a text area which allow's the user to submit a comment to an Access database.

I get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

but this only seems to happen when the following characters are used " . / * : ! # & - ? " in the message.

Can the settings in Access be changed to correct this problem
or is it a coding problem??

Any help would be greatly appreciated.

Thxs dinivan

View 1 Replies View Related

View And Print Special Characters

Feb 7, 2007

I faced a dilemma over the weekend in that I needed to preview and then print the ascii character 219 (A black block) on a report. It overlayed a paper calendar form that we use to print temporary access passes for where I work. The black blocks would indicate the days for the pass. Unfortunately, using Chr(219) did not work - so I searched the forums here and had a bit of luck, but still the solution remained elusive. Finally (and I should have done this first :)) I went to Help in access and found the answer.

To view or print any special character go to the start button on the computer, then navigate to programs, then accessories, then tools, then character map. Once there, choose whatever character you want and write down the Unicode number that applies. In my case, 2588. Convert that to decimal (Because it is in Hex) to 9608 and use an SQL statement Yourfield = ChrW(9608) to view or print it. The W is added to let the program know that this is a Unicode character and not ascii.

I read a lot of ingenious solutions in the forum, but this one seems to be the most straight forward.

View 4 Replies View Related

People's Names With Special Characters

Jul 31, 2006

My search has been unsuccessful due to the search function refusing to play nicely with me and the fact that it's mixing up the problems with using special characters with field names, which isn't what I want.

My problem is I have comboboxes that looks up people's name and has a NotInLIst event to allow addition of new person. It uses split function and concentating query to keep data normalized while displaying the full name.

Access trips over, very hard, whenever there is a name that uses special character, which for obvious reasons, causes confusion. Example:

Mike O'Leary
Thomas O'Calloway
Janet Smith-Johnson
Mary-Ann Johnson

Can anyone point me to a snippet I could use to trap for those names and help Access deal with it accordingly?

Thanks in advance.

View 2 Replies View Related

Modules & VBA :: Remove Special Characters

Feb 25, 2015

I have a Memo field that is used to be a description of a document. I don't know what the user will put in (could be anything), but it is eventually passed in a string to a query, etc.I discovered during testing that if an apostrophe is entered (Ex: This document explains how to deal with Joe's pleasant disposition.), it breaks my code. I then realized that all reserved words and characters would cause this problem.

I can get around this one just by using Replace(str, "'", "''"). However, I know there are others that will cause problems, and I don't want to end up with a string of replace statements just to fix them.

View 2 Replies View Related

Modules & VBA :: Find And Replace Special Characters

Dec 18, 2013

I have a table called: "tb_special_characters" with a field name [character]. In this table are values that I would like to remove from fields in another table ("tb_data", field name [Title]

The values in the "tb_special_characters" are thinks like
.
@
}

I'd like to run a query on tb_data.title that would replace any matching characters in tb_special_characters.character with nothing (I remove and close any spaces).

View 2 Replies View Related

Forms :: Special Characters In Data Values

Aug 12, 2015

In my database there is one value that requires the use of a /. (This is not as a name of anything...just a value stored in one of the fields.) I have a form which functions beautifully in all other regards, but it produces an error about syntax of the subquery in the expression if I try to use this value as a criteria for a search/filter (screen shot of error message attached).

I've tried using double quotes and square brackets around the / and a in front of it to no avail. I don't have the option of changing the value...it is defined by this multi-billion dollar project. Again, this is just a piece of data in a field in a record which also needs to be a criteria in some searches/filters.

View 5 Replies View Related

Reports :: Include Special Characters In Export

Apr 29, 2015

I'm trying to export either a report or a query to excel with a field name that has special characters "()". I wouldn't normally ever do this (everyone knows not to use special characters) but we're using this to import the excel document to a website, and the field name MUST be what they specified in order for the import to be successful. Is there any way to rename the field name at export, since I can't use special characters on the query or report itself?

The field name is currently DepType, but it must be
"
*Dependent Type
Spouse/Partner or Child/Dependent
(Required for Dependents only)
"
EXACTLY or it will not import.

View 1 Replies View Related

General :: Special Characters In Table Values

May 1, 2013

My database has several tables (and queries) that have fields that contain people's names. Some names, like O'Neil, contain apostrophes. Other fields contain couple names, like Tom & Laura Jones. Both the ' and the & prevent queries, forms, and reports from working correctly.

View 3 Replies View Related

Data Validation - Check For Special Characters

Jul 31, 2013

In vba I need to check a specific field (ID) for special characters used in that field. The characters to check for are:

' " ! * # & ^ % $ " : ; | < > ? @ { } [ ]

The code I have is:

strSQL = "SELECT ......... "

Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst

Do While Not rs.EOF
rstTest.AddNew
rstTest("ID").value = rs![ID]
rstTest.Update
rs.MoveNext
Loop

I need to know what I should put after "SELECT

View 11 Replies View Related

Memo Field Converts Everything After Char 255 To Special Characters

Jan 15, 2008

Hello,
I have an unbound form where the user enters feedback, usually > 255 chars. When they hit the "Submit" button, an append query adds this to a memo field on a table. A memo field should be able to hold 65,000 some characters however everything after 255 turns into a special character, mostly boxes. Any ideas on how to retain the text?

Thanks for any suggestions!
Stone

View 2 Replies View Related

Modules & VBA :: Remove Special Characters From File Name (when Saving)

Feb 20, 2014

I use the below code as part of a database which creates documents and then saves them using data from the form fields.

Every now and again one of the form fields contains a "special" character such as /,@,& and this prevents the file from saving.

Is there a simple addition to the below code to remove special characters from the file name if there is one present?

It causes all sorts of issues as many people don't realise that there has been a problem until it is too late.

Code:
objWord.ActiveDocument.SaveAs2 FileName:="C:UsersPublic" & Forms![Front Page]![Site 2 Name] _
& " " & Forms![Front Page]![Combo79] & " " & "O2" & ".doc"
objWord.ActiveDocument.Close

View 9 Replies View Related

Modules & VBA :: Asset Database - Special Characters In DLOOKUP Expression

Jul 29, 2013

I have an asset database I am designing to manage our computer inventory and assets.

I am trying to get a DLookup to work with one of my forms that will auto-populate some of the fields depending on what is entered in to the ProductID field. For instance, Make, Model, Asset type...

My problem is that the string that returns contains special characters, specifically "#" and gives me the error message -

Run-time error '3075':
Syntax error in date in query expression 'productID=EN371UA#ABA'.

My expression is definitely working, it just looks like it things it has something to do with date/time which it does not. Unfortunately, most HP equipment contains a # in the Product ID number.

Here is my expression -

Private Sub ProductIDCombo_AfterUpdate()
Make = DLookup("Make", "productlist", "productID=" & [ProductIDCombo])
End Sub

Make is the field I am looking up from the ProductList table. The Product ID is the ID I'm looking up from the ProductList table to find the make. My problem is actually getting it to return the correct value of "HP or Dell or Lenovo". etc.

View 12 Replies View Related

Queries :: Running Query Based On Form Text Box Criteria With Special Features

Dec 18, 2013

I am trying to run a query and display the results in a report (the report side of it is childs play and not a problem). The problem I am having is that I have a search form which should allow the user to search any one of 6 fields (text boxes) or a combination of each.

If the user enters something into a field then that search criteria must match. I wanted to have it so if all fields are left blank then it will show all entries in the database (but it isn't, it shows a blank report). I also wanted it to allow partial completion of boxes.

So for instance if I have 5 customers (Jones, Jonson, Jonus, jimjonkins, Janis) and I type "Jon" into the name field then I would like it to show the first 4 records as they all contain "jon" somewhere in their name but its not, its only allowing exact matches.

I currently have '[forms]![Search_Customer]![Search_Name]'.

View 2 Replies View Related

Queries :: Checking If Type Is Present In Table

Mar 21, 2014

I have 3 tables:

- tbl_Positions {containing information on a specific job role}
- tbl_Office {containing information on an office}
- tbl_StaffDetails {containing information of all staff in the company, including a relationship with tbl_Office to say which office they are working out of}

I want to do a simple query saying what type of staff are working out of each office:

e.g.

If in Office 1 I might have a managing director, CEO and 2xOperations managers etc.

If in Office 2 I might have 5xAdmin, 3xHR, 6xIT Support etc.

What sort of query can I run to return just a list of job titles for those present in the office I choose.

View 2 Replies View Related

Queries :: Checking That All Matching Fields Are True

Mar 21, 2013

For my query I have 3 tables.

TEAMS. This contains TeamID / TeamName and TeamManagerName.
TeamAPPLinks. This is a many to many relationship and contains TeamAppID / AppID and TeamID (TeamID links to Teams.TeamID and AppID links to Applications/AppIS)
Applications. This contains AppID / AppName / Pass/Fail.

So teams can have many applications and applications can have many teams.

I only want to show teams that have all applications that have the Pass/Fail column as PASS. I have managed to do this for FAIL runing a query in a query but obviously its different for PASS as all associated applications have to be a PASS, not just one FAIL for a FAIL.

View 4 Replies View Related

Queries :: Checking Format (Identity Of Field)

May 28, 2013

I've got a database with an 'address 1' field. Sometimes if it's just a number, excel formats the field as a date. Is there anything to check the field to identify any that are showing as dates as opposed to just normal fields. For example identify all customers who have some variation of dd-mm-yyyy in the address-1 field.

View 10 Replies View Related

Queries :: Checking Query For Null And Setting Operations

May 30, 2014

I have been working on an application where I am collecting survey data in a database. There are multiple survey tools available to the user, and it's possible to complete multiple survey tools in the survey.My problem is, it's possible for the surveyor to complete some tools on one day and other tools on another day. I am having problems with trying to figure out how to add a tool that has not been previously added and keep in the same survey which is all held under a single Survey Number.

The first step in the function is to set a Record Number temporary variable based on whether or not the tool has been used (it's possible to use multiple instances of a survey tool, so need to know if the Record Number is '1', or the next number in the sequence.I've been trying to do this by checking a query for a Null and setting the temporary variable using something like:

IF ISNull("RecordNumber","qryRecordHeader") Then '1'
Else
DLast("RecordNumber","qryRecordHeader") +1
End IF

The second half works just fine, so if there is a previous record, it will add. But if it's Null, it doesn't work.I'm trying to avoid opening a temporary form to run the query and checking a field. Is there a way around that?

View 3 Replies View Related

Codes And Descriptions

Jul 25, 2005

Hi i have designed a database where the users use a combo box to find a particular code they want..for example dog_brown or dog_grey, on the report which i have produced this code for the type of dog shows up to. I have another table which has a column with the code and also the description...eg dog_brown...brown dog. My users have asked me if on the report which i have produced can the description be shown instead of the code. I dont know if this can be done...any help would be grateful. Thanx

View 3 Replies View Related

1 Code To Many Descriptions

Apr 21, 2008

Hi -

I have 4 tables - one is a master list of codes and the master descriptions.
I have 3 other tables (with more coming) that have some of these same codes listed, with different descriptions than the master list.

How can I pull a list of unique master codes, and only one instance of each of the other descriptions?

View 7 Replies View Related

Error Numbers And Their Descriptions

May 3, 2006

is there a place that lists all the error numbers and their descriptions?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved