Incorrect Information
Sep 9, 2005
I have set up 2 queries which are working correctly. The problem is when I try to combine them it brings back incorrect information. The 2 queries that work correctly are set up like this
Query1:
SELECT Projects.[Work Stream], Count(Poles.[New Pole No]) AS [CountOfNew Pole No], Sum(Projects.[Line Length]) AS [SumOfLine Length], Projects.Team
FROM Projects INNER JOIN Poles ON Projects.[Scheme No] = Poles.[Scheme No]
GROUP BY Projects.[Work Stream], Projects.Team
HAVING (((Projects.Team)=[EnterTeam]));
Query2:
SELECT Projects.[Work Stream], Sum([Material Cost]+[Labour Cost]) AS [Total Cost]
FROM Rates INNER JOIN (Projects INNER JOIN [Pole Work Instructions] ON Projects.[Scheme No] = [Pole Work Instructions].[Scheme No]) ON Rates.[Rate No] = [Pole Work Instructions].[Rate No]
GROUP BY Projects.[Work Stream];
Do you have any idea how I can combine these to get accurate results?
View Replies
ADVERTISEMENT
Nov 1, 2005
SELECT [FA Ctr], [SAP Co], [SAP Ctr], [GL Co],
Format (([SAP Co],"0000") AS NewField), (([cst ctr], "0000000000") as costcenterappended) INTO [Interim Table]
FROM Asset_Map;
View 1 Replies
View Related
Jun 30, 2005
I am getting the following error:
ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/province.asp, line 24
And this is my code please help:
<%
province=Request.Querystring("province")
Dim intRecordsPerPage
Dim intRecordLoopCounter
SET Conn = SERVER.CREATEOBJECT("ADODB.Connection")
Conn.OPEN "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("/db/dealers.mdb")
SET RS = SERVER.CREATEOBJECT("ADODB.Recordset")
strSQL = "SELECT * FROM tblDealers WHERE "
strSQL = strSQL & "ProvState = '" & province & "'"
Response.Write strSQL
strSQL = strSQL
RS.OPEN strSQL, Conn, 3, 3
RS.PageSize = intRecordsPerPage
If RS.EOF Then
Response.Write "<CENTER>There are no dealers in this state"
Response.Write "<br>Please check back later<P></CENTER>"
Response.End
Else
Response.WRITE "<CENTER><B>List of Dealers</B><BR>"
Response.WRITE "<BR><HR><BR>"
Response.WRITE "<table valign=top border=1 cellspacing=0>"
Response.WRITE "<TR>"
Response.WRITE "<center><TABLE valign=top border=1 cellspacing=0 BGCOLOR=#DFDCEE>"
Response.WRITE "<TR>"
Response.WRITE "<TH>Company Name</TH><TH>Street Address</TH>"
Response.WRITE "<TH>City</TH><TH>Province</TH><TH>Postal Code</TH>"
Response.WRITE "<TH>Phone Number</TH>"
Response.WRITE "</TR>"
For intRecordLoopCounter = 1 to intRecordsPerPage
If RS.EOF Then Exit For
Response.WRITE "<TR>"
Response.WRITE "<TD WIDTH=200><font size=2>"
Response.WRITE RS("CompanyName")
Response.WRITE "</FONT></TD><TD WIDTH=200><font size=2>"
Response.WRITE RS("StreetAddress")
Response.WRITE "</FONT></TD><TD WIDTH=150><font size=2>"
Response.WRITE RS("CityName")
Response.WRITE "</FONT></TD>"
Response.WRITE "</FONT></TD><TD WIDTH=150><font size=2>"
Response.WRITE RS("ProvState")
Response.WRITE "</FONT></TD><TD WIDTH=150><font size=2>"
Response.WRITE RS("PostalCode")
Response.WRITE "</FONT></TD><TD WIDTH=150><font size=2>"
Response.WRITE RS("PhoneNumber")
Response.WRITE "</FONT></TD></TR>"
RS.MOVENEXT
Next
End If
Response.WRITE "</TABLE></center>"
SET RS = NOTHING
Conn.CLOSE
SET Conn = NOTHING
%>
View 1 Replies
View Related
Oct 27, 2005
I tried this question a few weeks ago, but I'm trying again.
I have 2 Access 2002 files. One has a sort order of ascii and one has a sort order of international. I need them to both have the same sort order. It doesn't matter which one. Right now I get the above error message on one of them.
How can I change the sort order so that I can import Paradox files like I used to?
:eek:
View 11 Replies
View Related
Nov 22, 2006
Can somebody explain to me, how can happen mistake inside of table like a picture?
1. I don't understand, how can be change automatic number - see the picture
2. Why is there some japanese charakter.
If somebody knows, please tell me.
hurka.deltec@wo.cz
View 1 Replies
View Related
Sep 21, 2006
Have searched but could not find my solution. I have a bowling league database and I am doing averages based on games bowled. On certain averages the results are incorrect. Such as
Tot pins = 1169 divided by
tot games = 6
the result should be 194.83
but the result in my query is 196
have tried the Round function, Abs function and cLng function to no avail.
Thank you
View 10 Replies
View Related
Nov 23, 2007
Hi all
I have a query field doing a simple calculation: 874*(18,3/55,65*0,0592)
Access calculates this to: 17,0144948838454
Excel as well as my own calculator gets: 17,0144948787062
All table fields is defined as double.
If anyone can help me with an explanation as to why access doesn't seem to get this simple calculation right, I would be most thankful.
View 4 Replies
View Related
Feb 25, 2008
Thought this thing was working great.Seems this is happening:If the sum of credits exceed the sum of charges, the query doubles the sum of charges. The query-SELECT TblCustInfo.CID, TblCustInfo.Name, TblCustInfo.SrvAddr, TblCustInfo.SrvAmt, TblCustInfo.BLCAT, Sum([TblCharges.Chargeamt]) AS SumOfCharges, Sum([TblPayments.Creditamt]) AS SumofCredits, nz([SumOfCharges],0)-nz([SumofCredits],0) AS RunBalance, TblCustInfo.HerbieFROM (TblCustInfo LEFT JOIN TblCharges ON TblCustInfo.CID=TblCharges.CID) LEFT JOIN TblPayments ON TblCustInfo.CID=TblPayments.CIDGROUP BY TblCustInfo.CID, TblCustInfo.Name, TblCustInfo.SrvAddr, TblCustInfo.SrvAmt, TblCustInfo.BLCAT, TblCustInfo.Cancel, TblCustInfo.HerbieHAVING (((TblCustInfo.Cancel)="n"));When I run the query, the SumofCharges calc is the culprit since it shows in that column. I can't dup it in the SumOfCredits, but I would suspect it will also do it somewhere down the line since they are virtually the same.HELP !!!!
View 14 Replies
View Related
Dec 5, 2006
Hello,
This is probably pretty simple but i can't find any info on it on this forum:
Every time i open my form the cursor automatically selects a field halfway down the page. How do i get it to open with the cursor at the first field on the form???
Also, I would like the form to be maximised upon opening anyone know how to do this?
Thanks
View 6 Replies
View Related
May 6, 2014
I saved my form with a particular name and I have used that name all throughout my code and it still works fine. But when I open that form, the name I see on the top of the form is not that name at all. It's actually some value I gave a combo box a while ago.
View 2 Replies
View Related
May 19, 2014
i have a query that pulls a read-only SQL table, i have check in that table and the date field is set up as a Date/Time type, however the db was set up with the date and time in the same field. (1/1/2014 9:00:00 AM)
I have set up the SQL code below to pull out just the Date and not the time. however when i enter my criteria on the form, only the correct month and days load, it pulls in all other years with that month/day.
my Access level - on the lower end of medium experienced.
SELECT IIf(InStr([dbo_Rides]![ApptDatetime]," "),Left([dbo_Rides]![ApptDatetime],InStr([dbo_Rides]![ApptDatetime]," "))) AS [Date], IIf(InStr([dbo_Rides]![ApptDatetime]," "),Mid([dbo_Rides]![ApptDatetime],InStr([dbo_Rides]![ApptDatetime]," ")+1)) AS [Time]
FROM dbo_Rides
WHERE (((IIf(InStr([dbo_Rides]![ApptDatetime]," "),Left([dbo_Rides]![ApptDatetime],InStr([dbo_Rides]![ApptDatetime]," ")))) Between [Forms]![frmMain]![subTCTools].[Form]![snavHistory].[Form]![subnDriver]![txtFromDate] And [Forms]![frmMain]![subTCTools].[Form]![snavHistory].[Form]![subnDriver]![txtToDate]));
View 2 Replies
View Related
Dec 11, 2014
I have a POLEFFDATE field with dates in date/time format. A single record has POLEFFDATE equal to 12/15/2013. I calculated a simple field called EVALUATE as follows: IIF([POLEFFDATE]< 1/1/2014,1,0). EVALUATE should equal 1 for this record, but it equals 0. Why ?
View 3 Replies
View Related
May 29, 2012
The filter output from a recordset in the following code is 1. But this is incorrect: How can this be corrected?
Private Sub Command43_Click()
Dim curDatabase As Database
Set curDatabase = CurrentDb
Dim rs3 As Recordset
Dim t As Recordset
Set rs3 = curDatabase.OpenRecordset("Select * from [Courses under Programs]")
rs3.Filter = "ProgramCode = 'ANS.CT'"
Set t = rs3.OpenRecordset
t.MoveLast
t.MoveFirst
MsgBox t.OpenRecordset.RecordCount
End Sub
View 2 Replies
View Related
Jun 3, 2014
I am modifying an inventory database and the first change I made was to split it. The major forms for this is an intake form to enter the inventory and a work sheet to identify the open items to be worked including the fields used to close the items.After the split the intake form started changing a time field. To be clear on this; this is not a calculated field. The person entering the inventory manually enters the time in which the item was recieved. When the record is saved (or rather when the Add New Record button is pushed) the time field gets changed to something seemingly random.
Could this be some kind of conflict due to both forms being bound to the same table and being use simultaneously? These forms would never be used by the same user at the same time. Also, why would this become an issue only after splitting the database?
View 6 Replies
View Related
Feb 16, 2007
Hi,
I am using
Total_Time: Format([totaltime_mins]/60,"00") & ":" & Format([Totaltime_mins] Mod 60,"00")
To display the time as hours and minutes but this formula doesn't always work correctly.
for instance if the totaltime_mins = 90 then total_time is displayed as 2:30
also if totaltime_mins = 40 then thotal_time is displayed as 1:40
does anybody know why this is not working correctly ?
kind regards,
jamie
View 2 Replies
View Related
Jun 16, 2015
I am trying to sum a column on a subform named Cost of New if another column named Final = any of the values listed in my code. However, it is summing the Cost of New column regardless of the values of Final. It is summing for all values.What do I need to modify to make this correct or should I do it another way?
=IIf([Final]="RPR-RPR & RTN" Or [Final]="NFF-TST & NFF" Or [Final]="RTN-RETURN" Or [Final]="SCL-SCR LOCALLY" Or [Final]="SCR-SCR RETURN" Or [Final]="BER-BER RTN CST",Sum([Cost Of New]),Null)
View 3 Replies
View Related
Dec 16, 2014
I'm working on a database to track our program's performance under different grants. Services performed for each grant would be entered on a quarterly basis. I am tracking the number completed for each service during the quarterly period.
I want to create a report that shows progress against benchmarks depending on the most recent quarter completed. Rather than fooling with dates, I put a field in the table where the data is entered for the number of the quarter in which the service was performed. The field, 'Quarter', holds numerical values 1-4 corresponding with the quarter. The table holds entries from different projects, distinguished by the field 'GrantID'.
There will be more than one service entered for each GrantID.
I would like to be able to identify the most recent quarter in which services were performed for each GrantID.
I have tried using the MAX function in a query but it seems to be adding "1" to the highest quarter number in the field for each GrantID. The code is:
SELECT DevEntryQ.GrantID, Max(DevEntryQ.Quarter) AS MaxOfQuarter
FROM DevEntryQ
GROUP BY DevEntryQ.GrantID;
With this query, if the highest quarter number entered in the table for GrantID1 is 3, the query returns "4".
In Excel, I would use an array formula: = {MAX(IF(Table1[GrantID]=GrantID, Table1[Quarter])}.
I want to use the most recent quarter to retrieve that quarter's benchmarks and show the progress toward the benchmark rather than the overall progress toward the goal for that grant and service.
View 2 Replies
View Related
Jul 6, 2015
So I have to text boxes, one for month and one for year. Below is the control source for each box. When I go into Report View, I get January 1905....This is definitely NOT January 1905....I've checked the date on my clock, it says today's date...
=Format(Month(Now()),"mmmm")
=Format(Year(Now()),"yyyy")
View 4 Replies
View Related
Jul 3, 2012
I have installed a Runtime 2007 application on a user computer, but the computer's file associations appear to be missing/incorrect.
How and where do I change the file association for this .accdr file?
Where does the MS Access Runtime file actually get installed ... and should I be pointing the .accdr file at this?
View 10 Replies
View Related
Jan 9, 2014
Using Access 2010. I have a form on which I've placed a simple label. I try to set the label border color to black but it shows grey. I can set the border color to #000000 and it's grey. I set it to #000001 and it shows black as expected.
I'm thinking it has something to do with the themes, but shouldn't an exact color code show as expected? As a new member (<10 posts) I can't post an actual link, but here's the text of the location: [URL] ....
View 2 Replies
View Related
May 16, 2013
I have a form that lists evidence items 1, 2, 3, ect....
The form is listing them 1, 10, 11, 12, 13, 2, 3, 4, 5...
I have tried right clicking the evidence item # field and doing a sort a to z, and it is still listing them the wrong way.
View 10 Replies
View Related
Sep 30, 2004
I have a problem getting pages of an Access 2002 report to print in the correct order.
I have a report which prints one of four pages per record. The data source for the report was initially sql which retrieved records from 2 tables. The sql was working fine. The order of the records was set by the ORDER BY clause. ORDER BY ON is set to Yes. ORDER BY is set to a text field called SortKey. Because the report was not printing the pages in the correct order, someone suggested that if I create a table (I called it SortReports), make sure that the table is sorted by SortKey, and make the table the data source, it should work. But it does not.
The pages in the report still do not follow this sort order. I have also made SortKey the primary key field. No matter what I do, the report continues to sort by another field called SystemKey, as well as something else. I have been looking at Microsoft and other websites for a similar problem and solution, but no luck yet. Any ideas?
I would greatly appreciate any help, or a push in the right direction.
Thanks.
View 1 Replies
View Related
Apr 26, 2013
I have a query (that gets it's data from several other queries) with a column called "max." The data in the column is correct, but when I call on the query in VBA, it shows me incorrect data.Here is where I call the query:
Code:
Dim db As DAO.Database, qdf As DAO.QueryDef, rs As DAO.Recordset
Dim strReport As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("7-ErrorsReport")
qdf.Parameters(0) = Forms!frmmain!dt1.Value
qdf.Parameters(1) = Forms!frmmain!dt2.Value
qdf.Parameters(2) = Forms!frmmain!d2.Value
[code]....
The query looks at a table of employees and finds out if they have been issues a warning letter before, then prints out a corresponding report based on the "max" warning level they are at.The problem arises when an employee graduates from a 6 month probation period - all letters in that period should be ignored. As i said, they are ignored correctly when i run and view my query ("7-ErrorsReport") because they are filtered out at that point, but for some reason when this code runs, it somehow sees the previously issued letters which are stored in a table and likely in some of the other queries.
I am not sure if there is some issue with the query tree I have set up which is necessary to get the results I need, or if something is wacked with the was I am using it as a recordsource.
View 3 Replies
View Related
Mar 26, 2014
Front end access 2010, back end SQl-server 2008 R2.I have all talbes and views connected to the server and due to incorrect results I found out, that the views from the server deliver incorrect data.Of course I refreshed the views in the access front end I even deleted them and connected them for new, but the incorrect data, even a different field sequence (!) remain.in a certain field there can be two different values: 1 and 2.
If opening the view I can only see 1, on the server the view shows 1 and 2, in the records, whatever is the value.This is not a calulated field.When checking this in a query in access I can select on value 2 (although I cannot see it!!) and I get the correct records but in the field itself the values still show as 1 !!
Same with other values in other fields. When I take the same SQL from the view in a stored procedure and execute is from access I get the correct data!!On both, view and sp I have the same permissions on the server!why views show incorrect data?
View 2 Replies
View Related
Sep 5, 2012
I am trying to get the message Request added to show up when the new record command works.
The message "add button error" show if there are any errors, rather than just doing nothing and stopping.
However when it works I get both, I know I doing something very simple very wrong.
Private Sub bAddRecord_Click()
On Error GoTo errorhandler
RunCommand acCmdRecordsGoToNew
MsgBox "Request added "
On Error GoTo 0
errorhandler:
MsgBox "add button error"
End Sub
View 3 Replies
View Related
Sep 23, 2014
I am importing .txt files into Access table via VBA code (i.e., not via Saved Import Spec). Is there a way to trap the error if a particular field does not get imported due to incorrect format? When you import via Saved Import Spec and there are errors in formatting, Access generates an 'ImportErrors' table, which tells you which fields could not be updated.
Is there a way to generate a similar 'ImportErrors' table with VBA error checking?
View 4 Replies
View Related
