Too Many Brackets Query Sql Statement
Mar 1, 2006WHERE (((zEnquiries.Enq_Forname) Like '*' & Forms!Enquiry_Search!Search2 & '*'))
this statement im sure has too many brackets.. can someone correct it for me
ADVERTISEMENT
Using Square Brackets As Criteria In A LIKE Statement
May 1, 2008I need to use square brackets as part of the criteria in a LIKE SQL statement. The criteria is:
[ABC123][SR]
ie, find all records which have that as part of a longer text string. How can I amend the SQL statement below to "escape" the square brackets and treat them as part of the string?
SELECT * FROM p_Overall WHERE (((p_Overall.Assigned_Dept)="ICONS_IMAC")) OR (((p_Overall.Brief_Description) Like '*[ABC123][SR]*'));
Help!!!!
Thanks in advance :-)
Queries :: Text Box Values Used In Query Contain Brackets
Sep 3, 2013I have a query which uses values in two hidden text boxes, in order to populate a sub form.Unfortunately some times the data in the text box contains brackets within it as follows:
'120/60 ZR17 (55W)'
When this occurs the query returns no data, even though records with a matching code exists in the table I am working with where Speed is 'FR'.
Code:
WHERE (((stockdyn.SPEED)="FR") AND ((stockdyn.DESCRIPN) Like '*' & [forms]![frmSearch]![Text10] & '*'))
How can I get around this, as surely if I used quote marks it would take "[forms]![frmSearch]![Text10]" as the value I am searching for.
Where To Put The Brackets?
Aug 29, 2005Greetings all,
Continuing to work on my problem and I am now getting and error of '3075' - syntax error (missing operator) in query expression
the error line is
strSQL = strSQL & "HAVING (tbl_data_DispatchDetails.TradingName) = " & varVal2 & " AND (tbl_Data_DispatchLineitems.WineNumber) = " & VarVal0
(I have to admit query language is not my strength)
Thanks in advance.
rbinder
Calculate Percentage Within Currency Brackets
Jan 7, 2008Hey,
I'm unsure of how to do this one, any help would be appreciated.
The database I'm working on has around 2000 records within the parent table. These reflect projects around the world.
the child table has records on how much these projects cost.
I would like to calculate the % of projects cost less than £50,000, then, the % of projects between £50,000-£100,000 and so on.
i'm not entirely sure how to do this, anyone have any ideas?
Thanks
How Do I Eliminate Brackets In MERGED Column
Nov 4, 2004I recently MERGED two columns in a database file using the following code:
SELECT "(" & RESULT & ") " & QUALIFIER AS MERGED FROM SOIL_STL3_MERGE AS SOIL_STL3_MERGE;
The RESULT field was a number which I had to change to a TEXT field since my QUALIFIER was a TEXT field. Anyway the result was the field called MERGED which depicted the data as follows:
(100) U
(500) U
......
.......
I use the Find and Replace twice to get rid of the first ( and run it again to get rid of ) resulting in a field that looks like this:
100 U
500 U
My question is can I solve this through an UPDATE query statement through SQL or some other way with a function.
I would rather automate the removal of the ( ) or change my original code to NOT put brackets around my result.
Any and all help is most appreciated.
I thk you all in advance.
the raven man
Question On Printing A Negative Number In Brackets: -5 -> (5)
Sep 7, 2004I want to print negative numbers in brackets without the negative sign:
-5 beccomes (5)
I am doing this on a control that I am summing on. Currently this is what I am doing in the "Control Source" field.
IIF(Sum([field]) < 0, "(" & -Sum([field]) & ")", Sum([field]))
This works, however, I would assume it is running the sum 3 times (I do not know access internals, I may be wrong and would like to be corrected).
Is there a way to do this using the "Format" field??
Thank You
newbie and learning
Modules & VBA :: Extract The Text Within The Brackets From String?
Nov 23, 2014I have a column containing records of the timestamp of an event. I need to extract the date out of each of these records and put them in a separate table. The date and time of each record is contained within a bracket.
E.g.
ES~1~1412179200(Oct 02 00:00:00 2014)~1~ITM_W64MQMONITORLOG00~
0~0~ES~1~1412179203(Oct 02 00:00:03 2014)~1~ITM_Log_Entries~
0~0~ES~1~1412179204(Oct 02 00:00:04 2014)~1~ITM_Log_Entries~
As you can see, the number of characters of each record are different, so I am unable to do the Left$ count thingy.
Is there any other way to do it? I only need the date and time contained in the brackets.
Queries :: Wild Card Searches With Brackets
Jun 26, 2014I want to prompt the user to enter a merchant name, but want the results to return close matches.I know how to use the wildcard in the Criteria field of the query, but I want to use brackets.I know that "*Southwest*" Will return Southwest Airlines.So I tried *[Southwest Airlines]* and it treats the criteria like a text string.
View 1 Replies View RelatedHow To Split Letter&number Field Into 2 Column And Skip Brackets?
Dec 2, 2005HI!
I have big problem I have telephone number field like this
Tel(XX)XXXXX - X are numbers
I have to split it into two columns and skip brackets like this
Column1 - Tel
Column2 - XXXXXXX
Is it possible?
I have no idea how to manage it...I'd be very thankful for any
examples ? Or Help....I'm beginner
Thanks a lot
in Advance
Tables :: Showing Negative Numbers In Brackets Without Selecting Currency Format
Jul 7, 2014Is there a way to show negative numbers in brackets without selecting the Currency format?
I want to show (75,000.00) and not -75,000.00
Queries :: Update Statement For A OUTER JOIN Select Statement
Feb 12, 2014I 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?
Need Help With Query/sql Statement
Sep 22, 2006Hi 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~
Query V SQL Statement
Mar 29, 2007If 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
Help With Statement In Query
Apr 24, 2007This 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
If Then In A Query Statement
Dec 5, 2007I 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];
Help With IIF Statement In Query
Dec 18, 2007I 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
Query Or SQL Statement
Jun 3, 2005I want to show cell values in a form from a table the form is not sourcing data from where the primarykey of both tables commensurates and tried do this thru a query, but id doesn't work. what would be the expression in the text box or the SQL statement for this.
thanks in advance
Help W/a Statement In A Query
Apr 2, 2008I need to know how to take the following statement:
TOTAL: Sum(Nz([TblPurchases]![Qty])*Nz([TblProducts]![PointValue]))
and modify it so that any records in TblPurchases where the field called "ProductID" is =42 is excluded from the calculation.
If anyone can help me out, I would sure appreciate it!!
Tax Band Query Statement
Jul 25, 2005I am trying to convert an excel formula into a new access query statement.
The statement itself will refer to a tax percentage column within the same query.
Example of excel formula:
=IF(Tax Percentage>22,40,IF(Tax Percentage>10,22,IF(Tax PercentageN2>0,10,0)))
The output I hope to achieve is:
If Tax Percentage is greater than 22 then = 40
If Tax Percentage is greater than 10 but less than 22 should equal 22
If Tax Percentage is greater than 0 but less than 10 should equal 0.
Hope this makes sense?
thanks
Update Statement Or Query From VBA
Dec 24, 2004I am trying to update the fields of a table from comboboxes and textboxes on a form. I don't want all the fields to receive data from the objects on my form directly. Some of the bound controls are not visible and data would be entered into them from visible comboboxes instead. I am not sure exactly how to do that. I tried to devise a query that would take the values from the comboboxes and equate them to those of the hidden controls, but that didn't work. I tried inserting an update command in code but that came up with an error. If some one could help me I would be thankful.
View 7 Replies View RelatedIIF Statement Is Acting Up In A Query
Aug 12, 2005I have a query which is based on 2 tables (Log2000 & Customers) and criteria is entered by the user via a form called 'SearchMarkets'.
In concept, if the user chooses a 'Market' on this form, the query uses that value, otherwise it runs everything. My SQL looks like this:
SELECT Log2000.[Call ID], Customers.ID
FROM Customers INNER JOIN Log2000 ON Customers.CustomerNumber = Log2000.Customer
WHERE (((Customers.ID)=IIf([Forms]![SearchMarkets]![Market] Is Null,([Customers].[ID]) Like "*",[Forms]![SearchMarkets]![Market])));
For non-null values it works grand but the ([Customers].[ID]) Like "*" part isnt returning anything. However this piece of SQL does work when used on its own like this:
SELECT Log2000.[Call ID], Customers.ID
FROM Customers INNER JOIN Log2000 ON Customers.CustomerNumber = Log2000.Customer
WHERE ((([Customers].[ID]) Like "*"));
Can someone please help me overcome this issue?
Thanks in advance!
If Statement In A Query Not Working?
Oct 9, 2005hi im trying to do the following if statement in a query but its not working, anyone know what im doing wrong?
any help would be great.
-------------------------------------------------------------------------
New: IFF [(TABLE-SALES]![PERIOD]<=[TABLE-AVERAGE]![ACTUALS_TO_PD] then [TABLE-SALES]![ACTUALS_TO_PD]) else
[TABLE-UPDATE]![FORECAST_1_BASE_VALUE]
------------------------------------------------------------------------
im trying to say if the field "period" in the table: "TABLE SALES" is the same or less than the value in the field "actuals to pd" in the table :"TABLE-AVERAGE" then the value displayed in this field ("new") should be the value: "actuals to pd" in the table "table-sales" otherwise the value in the field "forecast1 base value" in the table "table update"
Iff Statement For Query Criteria
May 30, 2006A database I'm creating contains records of events within an academic year. There will be many events in the table, and I want to create a query to show me only those events in the current academic year.
I figure that I need to use some sort of iff statement to make this happen; something which says:
IF the current date is before 31-Aug-CurrentYear
THEN display records in the range 01-Sep-LastYear and 31-Aug-ThisYear
ELSE display records in the range 01-Sep-ThisYear and 31-Aug-NextYear.
I've had a go at writing this, but can't get my code to work. Can anyone help?
Thanks in advance,
Gary
Help With Search Query Statement
Oct 16, 2006Hi,
I would like to display records on a webpage and am unsure of the query statement and would like to seek some advise.
I have a lists of contact numbers(home, mobile) and they are from various Sports groups and different teams. Captains from the different sports group will be able to view the details of their teammates of the respective sports they belong to.
An example of what should be returned is shown in the attached file
Database Info:
Surname
GivenName
Sports
Team
HomeNum
MobileNum
Thank you.
Query Help Using A Iff Statement And Relationships
Jun 12, 2007Here I have three tables.
Control type – has a field control – 1 row
Table1 – has fields: Acct (text), table 1(text), table 3(text) – 8 rows
Table2 – has fields Acct (text), table 2(text) – 4 rows
What I am looking for is to combine Table1 and Table2, replacing table 1 with table 2 if the control type is 4. if its not then it uses table 1.
I was able to do that with the query test except I lose that data in table 3 that doesn’t match table 2.
I would like to have the information kept on Table1.[table 3] and if nothing matches [table 2]
It sounds confusing so I am attaching the database.
The Query test shows it working but losing the extra data from Table1.[table 3]
The Query test2 gives the SQL Err I received when it runs the way I thought It should.
Any help or suggestions would be welcome.
What I am looking for is a way to set a stactic varable that only changes when I set it. and is available for 4 or 5 different queries. run under the same macro.