Numerical query criteria and quotation marks

 I have a query that uses a criteria of >"269.9".  I realized it was not 
working because the format of the field being queried was text.  I 
successfully altered the format of the source field to double. Access will 
not allow me to remove the quotation marks from around the "269.9" criteria.  
It puts them back as soon as I tab off the field.  If I create a new query it 
works, but why won't it let me modify the existing one?

Thanks,
-- 
PRD
0
Utf
11/20/2009 9:50:01 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
999 Views

Similar Articles

[PageSpeed] 49

In query design view try deleting the field, save, and then put it back.
-- 
Build a little, test a little.


"PD" wrote:

>  I have a query that uses a criteria of >"269.9".  I realized it was not 
> working because the format of the field being queried was text.  I 
> successfully altered the format of the source field to double. Access will 
> not allow me to remove the quotation marks from around the "269.9" criteria.  
> It puts them back as soon as I tab off the field.  If I create a new query it 
> works, but why won't it let me modify the existing one?
> 
> Thanks,
> -- 
> PRD
0
Utf
11/20/2009 11:18:05 PM
Reply:

Similar Artilces:

Numeric currency to words
I would like to use a user created function to read a cell containing a numeric currency value and put it into words, ie. $103.40 is One Hundred Dollars and Forty Cents. Any help? Thanks! Here is one way (you would want to use "Dollar" as the second argument to the function)... Go into the VB Editor (Alt+F11) and add a Module to the Workbook (Insert/Module from the VBA menu) and then paste in all of the code appearing after my signature into the Module's code window. You can call the function from your work sheet like this =NumberAsText(A1) where A1 is assumed ...

Excel Query #4
Hello, I have a list of around 50 values of which around 40 of them make up a specific value. Is there a way that I can find which cells make up this value? ie. A1 5 A2 4 A3 3 Value = 7 Therefore value is made up of A2 + A3. Thanks in advance Lesley I can' find the link, so will have to post the code. You may have to play with this to match your setup (Dim statement), but in order for it to run do the following: In B1, enter your target amount In B2, enter the # of numbers to use In B3 thru B#, enter your numbers from which to select the combination. Leave Col C open for a he...

Query Criteria 02-10-10
I have a query in which I only want the top 10 stores for item velocity for multiple groups. I have used the top value and max criteria but it only brings back the top or max for the entire dataset, not for each group within the data. What am I missing??? Post your table and field names with datatype that you would use in the query along with sample data (need not be real but workable). -- Build a little, test a little. "gabbyccf" wrote: > I have a query in which I only want the top 10 stores for item velocity for > multiple groups. I have used the top...

Database text converted to numeric
I Imported data from a SQL Database, it is numeric and alpha numeric but mostly numeric numbers (Serial numbers). I need to compare this list to another Excel list containing the same information but with some numbers missing. I pull this into Access and when I try and compare them the error is that the format has to be the same. I changed the format of the list I imported to Numeric (most of the numbers is only numbers) but now I have to press F2 and enter on every single number (database is only 227500 lines) to convert it to a Number. I thought it was because it still saw itself as a Dat...

Queries fromm ACC97 to ACCXP
I have many queries like 'select foms!form1!datestart as Expr1, ...' which worked fine in access97. When I do the same in AccessXP I can see nothing in field Expr1. Or sometimes I can see 'Chinese' characters. The problem is only when 'foms!form1!datestart' is datetime data. Why it doesn't work in AccXP? When I try to add 'foms!form1!datestart' to parameters it works only when its datatype is string. Any suggestions welcome Thanks, Grzesiek Hi Grzegorz, I guess the first thing to do is to check the date/time formats and regional settings on your comp...

auto numbering in a query
Hi all, I need to build a query that have in a field auto numbering and in a certain formating for example: 0001 0002 ....... 0012 ....... is it posible? Is this to produce data that will be viewed in a report, by any chance? "thread" <yaniv.dg@gmail.com> wrote in message news:1175794888.467011.108490@w1g2000hsg.googlegroups.com... > Hi all, > I need to build a query that have in a field auto numbering and in a > certain formating > for example: > 0001 > 0002 > ...... > 0012 > ...... > is it posible? > On Apr 5, 2:42 pm, "BruceM&q...

LDAP query help
What would be the correct syntax for an LDAP query that shows all members of a particular distribution/security group? This would be under users and computers --> View --> Filter Options --> Create Custom --> Advanced. Thanks in advance! Never mind, i just figured it out.. "jim" <jim@NOSPAM.com> wrote in message news:%232LsZf9jGHA.1204@TK2MSFTNGP02.phx.gbl... > What would be the correct syntax for an LDAP query that shows all members > of a particular distribution/security group? This would be under users > and computers --> View --> Filt...

Query to find all zip4's inside polygon
I need to find a way to run a SQL query to find all lat/long points in a table which are within a polygon which is defined by several lat/long points. For example, I have this table which includes ALL ~700,000,000 possible Zip+4 positions with the corresponding lat/long values. CREATE TABLE [dbo].[Zip4Coordinates]( [Zip4] [varchar](10) NOT NULL, [Latitude] [decimal](12, 9) NOT NULL, [Longitude] [decimal](12, 9) NOT NULL) And say I have 4 lat/long points (moving in clockwise pattern on map) which make up my polygon. In this case, it's 4 lat/long points, but could be ...

Query of items ordered by month
Using access 2002 I made a query in design view that lists all orders for a certain item over a few years. There are only 3 relevant columns although others were used to get this info: order number, quantity, date I'm interested in knowing the steps to alter this query so it sums up each month. Ultimately I want a report that shows a bar chart with the quantity sold per month (and maybe show the number of orders too) Here's my current query I made in design view SELECT tblOrderHdr.InvoiceNo, tblOrderDetails.Quantity, tblInventory.ISBN_B, tblOrderHdr.UserID, tblOrder...

List of where queries are used?
I have an access database which has many queries which are leftover from various tests and temporary data extract needs. Is there a tool which can tell me which queries are used in all reports, queries, macros, or modules in the database? Hi Joseph, You might want to take a look at the Total Access Analyzer (< http://www.fmsinc.com/MicrosoftAccess/BestPractices.html>). Or you can use Access's own Documenter. Run it on all the objects in Access with maximum reporting settings, and then export/save it to a file of some sort that can be searched (text, PDF, Wo...

Random Conversion of alpha/numeric text with letter "e" to exponen
OS - w2003, office 2003, desktop XP Pro/ w2000 Pro My company uses a six digit alpha - numeric string for customer account numbers. a few are all numeric, most are in the format: ##X###, where # = numeric and X = an alpha character. A few accounts, 101 out of 360, that contain an "e" as the alpha character are converted to an exponential by Excel. the other 259 are not. These 101 accounts where the first accounts created using "e", the subsequent 259 accounts are not converted by Excel. Anyone have a clue as to what is going on and how I can stop it? Thanks, --...

what is wrong with my query?
Hello everybody, I have been using this query: SELECT table1.Name, table1.activity, table2.wann FROM table2 INNER JOIN table1 ON table2.id = table1.id WHERE table2.week = False And myDate = DateValue(" 22.11.2007 ") And table2.morning=true and table2.done =false and table2.lfd_nr in ( select Lfd_Nr from persons where deaktiv =false and GroupID = 2) ORDER BY table1.Name; But now I get as the result also a person who belongs to group1 (GroupID=1) The problem must be in the part "table2.lfd_nr in ( select Lfd_Nr from persons where deaktiv =false and GroupID = 2...

Forecast Query
Hello, I have a lease database which I need to input monthly rents through the lease term based on an annual lease figure. Example: Lease 1 - Lease term April 1, 2005 through March 31,2008 Annual Rent: $120,000 through March 31, 2007 Increases: April 1, 2007 to $130,000 through March 31, 2008. Is there a query that can do this for me? Any help would be appreciated. Thanks in advance. On Oct 26, 12:19 pm, D. M. <D...@discussions.microsoft.com> wrote: > Hello, > > I have a lease database which I need to input monthly rents through the > lease term based on an annual l...

SUMIF with Mutiple Ranges & Criteria
I'm doing a sales report and have a single sheet with all my call/resul metrics on it. I want to use SUMIF (I think ;) ) to calculate the cells in particular column if a cell in a specific column has a specific tex entry as well as a different text entry in another column (the tw columns are region and district). I know how do do a singl range/criteria in a SUMIF statement, but I don't know how to ad multiple SUMIF range/criterias. So basically here is what I am looking for: Sum a particular column G if column F equals "East Enterprise" an column V equals "CSI"...

sorting alpha numerics
I have a list of documents that I need to sort by title. Example AFI10-229 AFI10-245 AFI10-1101 After I sort, the list looks like this AFI10-1101 AFI10-229 AFI10-245 How do I make it take the entire alphanumeric into account? Let A2:A4 house the sample to sort. In B2 enter & copy down: =--REPLACE(A2,1,SEARCH("-",A2),"") Sort A2:B4, with column A in ascending order and column B in descendin order. Sproove Wrote: > I have a list of documents that I need to sort by title. Example > > AFI10-229 > AFI10-245 > AFI10-1101 > > After I sort, th...

text form to numeric ?
E.g A cell contain 1233- but the value is actually a numric value whereby the "-" behind is a negative sign, wat function do I use to convert it to a numeric form causing it to to become -1233 ? One way, assuming that the values are mixed (some with/without "-" sign) and that the values are in colA, which is formatted as text; IF(RIGHT(Ax,1)="-",LEFT(Ax,LEN(Ax)-1)*-1,VALUE(Ax)) -- Regards; Rob ------------------------------------------------------------------------ "crapit" <littlecramp@yahoo.com> wrote in message news:OSAgKf$1DHA.2324@TK2MS...

Code for Criteria in a Pass Through Query
I have read all of the posts on this forum about Pass Through queries and I just do not get it. Does anyone have any sample code of how they used data on a form as criteria in a Pass Through query. Particularly for date ranges. I have a Pass Through query that looks like this. select [detail].[sd_term_cntry], [detail].[sd_datekey], [detail].[md_tran_amt1], [detail].[sd_key], [detail].[sd_resp_cde], [detail].[sd_mbr_num] from [detail] (nolock) where ((([detail].[sd_term_cntry]) <> '999') and (([detail].[sd_term_cntry]) <> ' ') and (([detail].[sd_...

Rules queries
Hello All I currently use Outlook Express for emailing, with Office 97, but I think I need to move to Outlook97 now because ... I regularly receive 3-5 important emails per week from each of around 110 (specified) people - so around 330-550 emails per week (plus about the same number again of spams and other miscellaneous emails). For all the emails from the 110 specified people I want to do two things: 1) Print them 2) Maintain an electronic copy of the email in a specific folder (different folder for each person - so 110 folders will be set up). I need these two things to happen automati...

Clear Unbound Text box used as input parameter to query
I've looked at everything I can find, tried all the suggestions but still can't get this to work. I have a form that opens blank. You input your parameter into an unbound text box then press a command button that performs a requery of the database and returns the results in a continuous form. When the results are returned, the parameter is listed as part of the detail in the form so I want the parameter box to be cleared out and ready for the next one. My command on the Search button looks like this: Private Sub SearchEPICode_Click() Me.Requery End Sub ...

Can multiple criteria be used for IF formulas?
I am using the below formula to calculate percent of change from one year to another year. =IF(F3=0,1,U3/F3-1) F3 being the amount spent in previous year U3 being amount spent in current year The question I want my formula to answer is: If F3 and U3 have a quotient greater than zero, then calculate. However, I also want it to yield a 100 answer if the F3 is zero AND I also want it to yield 0 if both F3 and U3 are zero. -- Thanks, Christine Please see responses to your later reposting. ----- original message ----- "CHaney" wrote: > I am using the b...

COUNTIF for two criterias
Hello, I would like to use the countif function but using two criterias. For example: I want to count the times the string X appears on column B for certain criteria in column A. A B 1 X 1 1 X 1 X 1 2 X In this case I would like to count the X's matching the value in colum A = 1, the result should be 3. Does anyone know, how to do this? Thanks in advance for any help -- Message posted from http://www.ExcelForum.com Use SumProduct(b1:B10,=x)*(A1:A10=1) >-----Original Message----- >Hello, > >I would like to use the countif function but using two crite...

Merge different queries
Hello all, I have two tables where I enter my stock trades as well as foreign exchange (FX) trades. The two tables contain different fields as the trading instruments require different entries. I then created two seperated queries in order to calculate the profit from each stock and each FX trade. I need two queries as the calculations are different, too. Now, I would like to merge the two queries in order to see my overall profit and losses no matter if the trade is a stock or FX trade. That is, in the final query I would like to see the trade ID (which is the primary key in eac...

convert alpha to a numeric value
I received data from a user where negative numbers are sent as : "31Neg" for a "-31". Can you suggest a way to create a real negative number. Thanks in advance for you help. You could use a macro Sub ConvAlpha() For Each c In ActiveSheet.UsedRange v = c.Value If Right(c, 3) = "Neg" Then c.Value = Left(v, Len(v) - 3) * -1 End If Next c End Sub Not very efficient though, so if the sheet is huge it will take time. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ...

Parameterized query in form
My form exports a query. The query needs to select some or all "MICAP"s and some or all "Project"s. My form has an option box [optMICAPS] and another option box [optProjects]. When the option radio button is cleared, a combo box is made visible for selecting a MICAP or Project (cbMICAPID, etc.) The underlying query has a "where" clause like: WHERE MICAPID = IIf([Forms]![frmLaborHistoryExtract]![optMICAPS],"*",[Forms]![frmLaborHistoryExtract]![cbMICAPID]) Similarly for the Project selection. The MICAPID is numeric, as is the ProjectID. When th...

Numeric -> Text conversion
I've got a date field, and have extracted the month using the MONTH function. I want to put the month into a text field, two digits in width. e.g. 07 for July, 09 for September, 12 for December. I don't want it 'formatted' so that it looks like two digits. I want the actual value to be two digits. Any quick solutions ?? Using Office 2000. Regards, Mike. =TEXT(monthnumber,"00") -- Best Regards Leo Heuser MVP Excel Followup to newsgroup only, please. "Mike Gorton" <micky@notthere.com> skrev i en meddelelse news:OBDPeA3SDHA.2280@TK2MSFTNGP12...