Negative Numbers In Query Are Not Sortin Correctly.
Sep 26, 2007
I have a table with data that is similar to this:
HCP1 DATE (Fields)
-.1 7/19/2007
-.2 6/14/2007
0 9/9/2007
1.2 4/23/2006
-.3 4/4/2007
2.4 3/7/2007
0 2/5/2006
1.1 2/6/2007
Etc. There are 0's, negative numbers and postive numbers. Some with at least one decimal point. I am trying to sort the data by the highest number in HCP1 field. When I run the query in Access it looks great. But when run through ASP on the server it is only giving me the negative numbers, no 0's no positve numbers. It would return -.1 in this sample data as the first record.
Anyone know what might be going wrong on the server side of things. The query statement looks the same on both sides. (server and access)
Howdy Folks, I have an equation that will often return negative numbers in an update query. I need to be able to change any negative to zero, and update the table with that zero.
So far, all I've been able to do is ignore negatives with only positive numbers updating.
We have some programmers creating text files from our mainframe. We will then import these text files into Microsoft Access. We have some negative numbers in the text files that the programmers have formatted this way for example, $100.00-. When I import the file into Access, it ignores the negative sign. I told our Lead Analyst that the programmers should format the negative numbers this way, -$100.00 in order for it to import correctly. She doesn't believe me and thinks I'm not importing the file correctly.
Does anyone have an literature on this so that I can show her so that we can move forward? Or can anyone confirm that what I'm saying is correct, it needs to be formatted with the negative sign in front of the number, not behind it.
I try to modify an access 2010 template from the MS website.The problem is that the template as is programmed does not accept as input negative prices. I need to use negative prices for offering discounts on my invoices. In my country the discounts are only displayed as products with negative prices/values. We do not use % discounts.
i try to create an products with negative price and to use it as an discount on the invoice. Both the products input form and the invoice input form give me the same error : "please specify an price list that is not negative" or "please specify non zero positive prices" ( sometimes it allow me to enter but when the software try to validate the form/invoice i just entered it give me those error/messages)
I changed all the field from currency in general numbers Where do I have to change in order to remove those formatting/condition..? The template has a lot of macros.. I an not able to change those macros if there are some formatting conditions in them. If it is allowed I will post the link to the free template i use to be more conclusive..Or I can give/ upload the template I use. the link to my data base ( dropbox)
I need to create a query in Microsoft Access to enter 1 or 0 in a field based on whether numbers in another field is positive or negative. Here are the details.
1 entered in a "late" field if "TotalHoursAheadOrBehindScedule" field has a positive number 0 entered in a "late" field if "TotalHoursAheadOrBehindScedule" field has a negative number
I have a field on my report that contains positive and negative numbers. I'm trying to find a way to Sum the column twice for two different fields (one for positive sum and the other negative) To make this easy let me give a small example with explanation of the issue I am having. 14 rows of data containing positive and negative numbers in a field called [Rooms]:
The positive numbers represent confirmations, the negative numbers represent cancellations. I need 2 fields to separate these. The first field's control source is Sum([Rooms]). No problem there. Access does the math and comes up with "8" confirmations which is correct. Then in the second (Cancellation field) I try something like Sum([Rooms]<0) to get the negative numbers which should add up to -7 (-1+-1+-5), but instead I get -3 because it's not summing the values, it's really returning the number of rows that has a negative number. Is there any way this can be done at the ControlSource Property of a report field? Or if I could do a "Where" clause in a ControlSource I could acheive it another way, but I don't that is possible. Is this just something I'm missing with the way I'm stucturing the ControlSource or is it not possible. Or alternatively, is there a way to separate it in the query and have a separate query field for positive and negative going into the report. I can do one or the other in the query but I can't seem to do both without showing values of each type in both query fields.
Ok i know negative numbers can be a bugger in calculations:
Code: Function FEV1pred(height As Double, ClientID As Double) As Double
Dim a As Double Dim Age As Integer Dim DOB As Date Dim TestDate As Date Age = DateDiff("yyyy", DOB, TestDate) + CInt(Format(DOB, "mmdd") > Format(TestDate, "mmdd")) 'In this case the clients Age is 39 a = -0.0244
'Now for the fun stuff...
FEV1pred = a 'returns -0.0244 so i know the dim a holds the neg number FEV1pred = (a * Age) 'returns 1.708 (which is incorrect, how this calculation is reached.) FEV1pred = (a * 39) 'returns -0.9516 (which is correct) End Function
So something about the dimming of Age seems to be doing something to the equation but i can't work out what?
I created a database for a survey. There are a lot of number fields and a few yes/no fields. I did a query to give an average for the number fields and a sum of the yes for the yes/no fields. I get the right number for the yes/no fields, but the number is negative. What do I need to do so that the number is positive?
I have a query that I have an expression in that is returning a text value instead of a number value. The field in my table is a number value but when I use the expression it returns it as text. I have done several searched but can't seem to find anything that matches formatting an expression as a number. Here is the expression I am using, the reason I am using it is because I need to return a zero if no records matched the criteria.
I have a field in my DB called "Category". Users are allowed to imput any text in that field. I have a particular problem in which the LIKE command isn't working as I think it should with the limited help files of Access explanation of use. The category field has multiple entries, for example - HEQ, PBH5, PBH4, SWA, SWA2, ALL - etc. I'm trying to pull the records from that field with specifics.... in other words I may need to get All the records that have "PBH5" in them. However, if if PBH5 isn't the FIRST entry in that field the LIKE criteria doesn't work. I have tried Like "PBH5*" LIke "PBH4* etc to no avail. Any ideas? It can't be that difficult, but I cannot find anything in any book I've purchased on it, or any of the knowledge base information at Microsoft.com.
I have a table that gets updated by a query(For Invoices).When I query that Invoice table for the last record, I do not get the last one written. It stops at number 12. I am using the Totals option with Invoice number field and setting to "Last".is there another way I can get the last record to query correctly. I am trying to populate an invoice report.
I have a query that I am using in VBA to select the number of days equipment is past due for service. I have an equipment table with a last maintenance field (tbleqDP with field DPLstMnt) and another table (tblmaintdue) that defines the maintenance interval. I have a query (qrynumdays) that finds the number of days from tblmaintdue for the equipment type based on a value in cboMaintEquipType combobox. The VBA then generates a report based on which type of equipment is selected using a select case statement. An excerpt of the VBA is below:
Select Case cboMaintEquipType.Value Case "DP" Set qdf = db.CreateQueryDef("qryoverdue", _ "Select DPTag, DPLstMnt, DateDiff(""d"",DPLstMnt,Now())-NumDays AS Overdue from tbleqDP, qrynumdays where Overdue > 0") DoCmd.OpenQuery "qryoverdue" DoCmd.OpenReport "rptDPmntovrdue", acViewPreview
I am having problems with the "where overdue > 0" statement. It is asking me for parameter value for overdue. How can I get this to automatically filter out any values that are negative - in other words, don't report on equipment that is not past its maintenance interval due date.
Also, I think there is a way around how I am naming my query in the createquerydef function because later I just delete the query after the report is run. Is there a better way to do that.
I'm trying to get a public function to work within the design view of a query. The function is defined as:
Public Function Percentile_(fldName As String, _ tblName As String, p As Double, _ Optional strWHERE As String = "") _ As Double... ( I can put the whole code if u need it)
Any thoughts about how I can make the Percentile function calculate values according to a GroupBy statement and not for whole data set?
When I open one of my tables or queries and look at the customer Id, the displayed info is a single digit. On my customers table my primary key is the customer Id with auto number with the format like this: "ID"000. This is great however, when you look up the customer id in other tables it only displayes the single digit. I want it to show up like ID001 or ID002. In the customers tables it looks just like that, but if you click in that cell/field it takes away the ID and just shows the 001 or 002. I changed the format in my other tables under customer id to "ID000" but that didn't seem to make a difference. I would like to be able to do a search lets say by customer ID in one of my products table and when I type ID005 or ID012 it takes me to those records. But right now I would have to enter 5 or 12. Datatype in my other tables on field customer ID is set to text. Do I have to do a validation rule? How can I get it to show up the way I want it?
I get a negative value when i run the below expression.
The expression picks the month from the field txtmonth in form "frmYearToMonth" and search's the crosstab query "ctbYearToMonth" for the corresponding value in txtMonth.
The time difference in access query works very fine in 18:00 (Short Time) format , but when i export to excel it came out with "########" and the value is -0.14679132479
How to solve this problem , i keep trying but it doesn't work ....
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:]) Site - (criteria = Like "*" & [Enter Site:] & "*") Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
Ok so in excel I have some numbers that are stored as text. The reason being that they are zip codes and some begin with 0 and excel doesn't want numbers to start with 0....so when I import these into an access field that has an input mask for zip codes...will it convert these correctly since the field is a text with input mask?
I have 2 fields that I would like to automate if possible
One field is called "p/o number" and another field called "line no"
These fields are part of an ordering database
Let say I have 200 items to purchase form 10 suppliers
And form example 20 items from each supplier
What I do at present is put the order number on each line item and the line number
example
p/o number line no
1 1 1 2 1 3
2 1 2 2 2 3 2 4
What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.
When i sort values, for some reason the negatives are at the top, followed by the highest positive values. Anyway to turn this off? Tried google and searching these forums.
