Query For Characters Not In "IN Statement"
Jul 10, 2007
I have a table with close to 1 million records. Somewhere in this table are a handful of records that have characters we consider "illegal". I'm trying to write a query that will display these records.
The "Legal" characters are as follows:
0 1 2 3 4 5 6 7 8 9 - /
Here is a query that I've been playing around with.
SELECT * FROM tblMonthly WHERE
(Instr(1,tblMonthly.RecID,(NOT IN ("0","1","2","3","4","5","6","7","8","9","-","/")), 0));
I'm receiving the following message:
"Syntax error (missing operator) in query expression '(Instr(1,tblMonthly.RecID,(NOT IN ("0","1","2","3","4","5","6","7","8","9","-","/")), 0))'.
Is what I'm trying to do possible, or am I waisting my time?
Thanks,
Crhodus
View Replies
ADVERTISEMENT
May 12, 2006
Hi Folks,
Apologies if this has already been covered, but I couldn't find anything...I've got a form in Access that checks a table for a combo boxes values. If the user adds a value to combo box that isn't in the table I want that record to be added to the table. The code that I've written is:
Private Sub cmbClient_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
Dim db As Database
Dim ssql As String
Set ctrl = Me!cmbClient
If MsgBox("This isn't a recognised client. Do you want to add it?", vbOKCancel) = vbOK Then
Response = acDataErrAdded
ctrl.RowSource = ctrl.RowSource & ";" & NewData
ssql = "Insert into tblclient (Client) Values ('" & NewData & "');"
Set db = CurrentDb()
db.Execute (ssql)
Else
Response = acDataErrContinue
ctrl.Undo
End If
End Sub
When I test the code it appears to work, but I get an error message saying that "Characters found after end of SQL statement"
Can anyone shed any light? I can't see anything obviously wrong with the code, but I must be missing something.
Cheers.
View 1 Replies
View Related
May 8, 2015
i'm trying to execute an insert statement from an ASP.Net form page such as:
insert into documents (Name,NameFrench,Message,MessageFrench) values('info','information','See details ','Voir l'affiche pour des details');
I get an exception on the insert statement.
If I copy the statement directly into a test query in the database, I get the following error: Syntax error (missing operator) in query expression "Voir l'affiche .... "
The insert dies on the " l'affiche " word. I need the those french characters in the database as they are pulled from those fields and are required to be displayed with the proper accent characters.
know a way to escape the French special characters?
View 3 Replies
View Related
Feb 19, 2007
Hi
I want to ask something simple but because i am new i am dont know it!!! If you try to make queries in Access, untill how many characters you can put in each query???
View 14 Replies
View Related
Sep 8, 2005
In MySQL I can use the LEFT(3, field_name) command, but I essentially want access to pull all records from a table where the the first three digits of a number field match those pre-selected by a form drop down.
This database is in Access 97. Is there a way to compare the first x number of digits using SQL only, or do I need to run each line through code first, and then check it (I really don't want to do that)?
example numbe rin field:
123456789
SQL checks to see if 123 matches value selected on a form.
View 1 Replies
View Related
Aug 30, 2006
How do a write a query to find those rows in a field that have only 5 characters?
View 2 Replies
View Related
Jan 5, 2007
I have this test file I am using. I want to figure out how to make a query( using the wizard or other functions are fine) . In this stream it always has Invoice Number: 385152769: I want to query to get 385152769 for every line. Is this possible?
View 2 Replies
View Related
Aug 16, 2007
I have field called [LOCATION] that varies in length. I need to remove any alpha characters at the end, if there are any. Listed are some examples.
[LOCATION] [NEEDED]
PA 109S 55W 2N 1A PA 109S 55W 2N 1
WC 155A WC 155
PB4 68N 1UNH PB4 68N 1
B 3E 1P B 3E 1
XB1 203S 14W 2N 6PU XB1 203S 14W 2N 6
PB1 6 PB1 6
Thanks!
View 5 Replies
View Related
Nov 1, 2007
I need to query a field for any records where this field has less that 10 characters. Any suggestions? The reason is depending on the type of customer the field can be 10 or 11 digits. IF there are less than 10 but not blank they need to be pulled for correction.
View 1 Replies
View Related
Apr 11, 2008
Just curious to know. When I run certain query from A SQL Table in Access, the result is what looks like japanese characters. Anyone encountered this? Is this an error(it has to be, right)? This is an an email field
View 5 Replies
View Related
Sep 15, 2005
Maybe it's the day's 'brain drain', but I need to set a criteria in a query whereby it will select answers in a field that are a specific number of characters in length.
i.e., answers that are 5 digits long (without knowing any of the digits)
Russ
View 2 Replies
View Related
Jan 24, 2007
Hello,
I have created a query using the query builder by concatenationg several fields using the expression builder. Once concatenated, the total number of characters is greater than 255, and is therefore truncated. Since this is a query field and not a table field, there must be a way around this limitation. I can't even create the query and dump the results into a memo field because the dump will still only contain 255 char.
I could probably create the query using VBA, which creates a table containg a memo field, which is then populated by a variable containing the concatenated fields, but I would like a simpler solution.
Any ideas on how I can generate a query field that contains more than 255 char? The query is used to populate a report.
Thanks in advance.
Ken
View 6 Replies
View Related
Jun 5, 2013
I am trying to replace a # character and all characters after in a text field.
U set up a Select query and used the following expression:
Expr1: Replace([Part Number],"[#]*","")
It doesn't change any of the records. Is this possible to do in Access 2007?
View 11 Replies
View Related
Sep 5, 2013
I have a field in Access 2010 called Section Number which generally holds two characters. The characters can be numbers, letters, or a combination. Examples are:
01
X1
KA
40
Sometimes this field holds three characters, such as:
01a
02b
I want to write a query that returns only the Section Numbers that contain two characters. The instances where the field holds three characters are too numerous and changeable to use a parameter that says, for example: not "01a" and not "02b"
Is there a way to write a parameter that means "not Section Numbers of three characters" or "not Section Numbers of more than 2 characters"?
View 2 Replies
View Related
Jan 1, 2015
I have over 270,000 records in my database with 4 fields. And for each field I need to make sure they dont go over a certain number of characters for example field1 can't go over 20. Is there a way I can make sure that they don't go over that? When I go to design view and go to the Data Type of each field I change the filed size to 20, but if something is imported into that field that is over 20 characters long will I get a message saying it won't import? Or can I write a query to check the whole field to make sure that nothing is over 20 characters?
View 3 Replies
View Related
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
Sep 5, 2014
I have a query that is linked to a data table. The values entered in the data table are fine and display as expected. When I run the query, the results of one of the columns show Chinese like characters instead of the actual value. But, when I filter that column by choosing 'does not contain...' the actual cell contents display. I have 2 other identical queries reading from the same table and they do not display funky.
View 3 Replies
View Related
Apr 11, 2013
memo fields chopping the content when appending to another table memo field.I have a query that takes in a couple of tables and then appends the content to one larger table.In each of the smaller tables there is a memo filed that when I check it has the entire type content in above 255 without a problem.
When the query runs and appends it to the larger table the content in the memo filed is chopped down.Both tables fields are set to memo I have double checked this.I have seen some comments about unique values in the query properties but these are set to NO as advised but it still does this.
View 4 Replies
View Related
Jun 26, 2013
I have a current file with GBP 12.00. I wish to remove the GBP in a make table query when ran to print off.
View 2 Replies
View Related
Dec 3, 2013
I have a query that pulls a "fromdate" and a "todate" from a form, and runs a series of queries then a report. The first query is an append query. If I choose to just view the data, the two date fields in the query have chinese characters in them. There is no memo field, there is no Totals. I have reduced the query down to 3 fields: from, to, and "ticket number." Both the from and to fields are blank, until I click in one of them and 2 chinese characters appear. I have done a C&R, I have decompiled, I am completely stumped.
I imported two excel spreadsheets that I was going to have to do some quick work on (separately), call them A and B. I used A first, made the queries and the report and it worked great. Instead of redoing all of the code to look for B, I renamed A to Ax and renamed B to A. Everything broke then. I renamed them back to the original, I deleted them, nothing fixed it.
View 2 Replies
View Related
Feb 12, 2014
I have the following Select Statement:
SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],
[code]....
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
View 2 Replies
View Related
Sep 22, 2006
Hi all..
Here is what I have:
I have a query that is running a search form. The query is made up of three tables:
1.tblMain
2.tblSpouse
3.tblChildren
When I have the query looking at just tables 1 and 2 it works fine. But when I put in table three my query doesn't work correctly.
Here is what it is doing. On my search form I have a main window that lists all of the entries into the database. I then have two subforms one for spouse and one for children. The spouse now shows this:
Last Name
Washington
Washington
Washington
Washington
Washington
Washington
First Name
Janine
Janine
Janine
Sally
Sally
Sally
(two wives, 1 current, 1 past)
And the children show this
Last Name
Washington
Washington
Washington
Washington
Washington
Washington
First Name
David
David
Mines
Mines
Foy
Foy
And the one entry that does not have any children is no longer showing up in the main window with the other entries.
Here is the sql of the query:
SELECT tblMain.EntryNumber, tblMain.DriversLicNumber, tblMain.EmpNumber, tblMain.FName, tblMain.LName, tblMain.MInitial, tblSpouse.LName, tblSpouse.FName, tblSpouse.MInitial, tblSpouse.DriversLicNumber, tblChildren.LName, tblChildren.FName, tblChildren.MInitial, tblChildren.DriversLicNumber, *
FROM (tblMain INNER JOIN tblChildren ON tblMain.EntryNumber = tblChildren.EntryNumber) INNER JOIN tblSpouse ON tblMain.EntryNumber = tblSpouse.EntryNumber
WHERE ((([tblMain]![EntryNumber] And [tblSpouse]![EntryNumber] And [tblChildren]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.EmpNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.EntryNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.EntryNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*"))
ORDER BY tblMain.EntryNumber, tblSpouse.EntryNumber, tblChildren.EntryNumber;
Sorry for the long post but I wanted to make sure most if not everything was covered.
Thanks
R~
View 14 Replies
View Related
Mar 29, 2007
If I had a Form 'FrmForecastReview' Based on a Query 'QryForecastReview' I am wondering whether i can dispense with having a query and just use SQL statement in the properties of the form to query and retrieve the data i am looking for?
If yes would this be a technically and/or faster way for Access to produce datasets?
Thanks in anticipation of your pearls of wisdom
View 8 Replies
View Related
Apr 24, 2007
This is my first post and would like help with the following:
I have a warranty database that I have to create adjustment claims. There a re two pricing structures depending on the customer type. One customer type is SH and the other is DD.
I need to create an expression within the query that will allow me to add 1.0639 to the SH pricing (SH*1.0639) for all customers = DD.
So, when I pull a DD customer 1.0639 (6%) will be automatically be added to the List Price.
When I pull a SH customer the price will be the regular list price already housed in the database.
What type of statement will I use here. IF, SELECT or what? and if so, what would it be?
I hope this is clear and appreciate any help or feedback that I can get on this.
Thanks
View 2 Replies
View Related
Dec 5, 2007
I am stumped and need some help I have a simple select query used in a combo box to filter what is shown. As shown below.
SELECT [Task Type Drop].[Task Type Id], [Task Type Drop].[Task Type], [Task Type Drop].[Task Category], [Task Type Drop].[Task Type Definition]
FROM [Task Type Drop]
WHERE ((([Task Type Drop].[Task Category])=[Forms]![Task Category and Area Look up]![Combo158]))
ORDER BY [Task Type Drop].[Task Type];
Is is possible to only have it use the criteria if a nother field [Combo158] is not null?
Simply put I would like it to be if [Combo158] is null
SELECT [Task Type Drop].[Task Type Id], [Task Type Drop].[Task Type], [Task Type Drop].[Task Category], [Task Type Drop].[Task Type Definition]
FROM [Task Type Drop]
ORDER BY [Task Type Drop].[Task Type];
If not null
SELECT [Task Type Drop].[Task Type Id], [Task Type Drop].[Task Type], [Task Type Drop].[Task Category], [Task Type Drop].[Task Type Definition]
FROM [Task Type Drop]
WHERE ((([Task Type Drop].[Task Category])=[Forms]![Task Category and Area Look up]![Combo158]))
ORDER BY [Task Type Drop].[Task Type];
View 2 Replies
View Related
Dec 18, 2007
I have a database where I list prices for products. The prices are based on the type of customer.
I have a table that lists
Customer
Customer Type (can be DD or SH)
My other table with is the tire table has the following:
TireID
ITem Number
Description
Tire Price
Sh Price
List PRice
If the customer type is a DD - I need to use the List Price
If the customer type is SH - I need to use the SH Price
Here is the statements I've tried, but none of them are working..
Tire Price: IIf([customer type]=[dd],[SH Price],IIf([customer type]=[sh],[List Price]))
Tire Price: iif ([customer type]=”dd”, [SH Price], iif([customer type]=’’sh”,[List Price]))
Tire Price: IIf([Customer Type]=[DD],"[SH Price]")
I was using this IIF statment in my query but it is giving me a parmeter box with dd and then pulls up another box with sh before running the query. The query is not working.
I'd appreciate if someone can help me fix this issue.
Thanks
View 3 Replies
View Related
