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?
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.
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?
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???
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.
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?
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.
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
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)
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.
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"?
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?
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.
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.
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.
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?
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.
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?
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.
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];
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.
