Weird Query Results
Feb 6, 2008
I have a simple query based on two fields that says
Component4 total = no of hours sleep/no of hours in bed * 100
where no of hours sleep = 9
no of hours in bed = 10
therefore component 4 total should = 90 instead it equals 89.999999
How can this be?
For all the other records the answer is correct down to the very last decimal place.
When I right click on the query for this component 4 total and change the format from general number to standard it will correctly respond to 90 but only because the numbers are being rounded up.
PS. THe numbers are definitely being stored and recalled as 9 and 10 and arent being rounded up because I inputed it as 9.0 and 10.0.
View Replies
ADVERTISEMENT
Sep 7, 2007
I have a software package that uses a DBF file. I am linking to that file in Access 2002. Most of the fields are fine- however- at some point some of the field sizes are cut short. A text field that should have a size of 255 comes in as being a size of 44. This causes weird results in my data- shifting the data that's cut off into other fields and whatnot.
I have tried importing instead of linking- but get the same results. Same w/ copy/paste. The software package runs fine- and doesn't display these odd results. Obviously I can't go in and modify the software package (and I'm not sure that's where the problem is to begin with).
If I EXPORT out of the software package- I have to create an HTML file (I know- LAME!), then import it on into Excel- where, I can put in a dummy record row so that Access declares the right type of field to use for the tables upon importing (PAIN IN THE @$$!)- but- it doesn't cut the fields off. So, I know the problem is in Access linking to this DBF file, and not the DBF file itself.
Does anyone have any solutions here?
Thanks
View 3 Replies
View Related
Dec 1, 2007
Hi guys & gals,
I recently came across this really strange behaviour and was wondering if anyone else knows this bug or knows a better workaround than mine.
I've attached a little Events DB, which has a search interface. I've included an export button, which runs a macro that does an OutputTo (xls) of the search query.
Now if you do an export once, everthing is fine. The bug (at least I think it's a bug) occurs if you export a *second* time and *overwrite* the file you created earlier. The search query (which sits under the "Queries") tab is now empty and you get an error message.
My workaround idea was to dump the query to a new table every time you export and then do an OutputTo with this table. Not very elegant and you also have to do the whole error handling (e.g. user presses cancel) manually.
Does anyone have a better idea? Or am I doing somethng completely wrong and this is actually "by design"?!
Thanks in advance for any ideas!
View 2 Replies
View Related
Sep 7, 2005
I enter values into a table with 2 decimal places.
I have a query that applies percents to the values - I have set the results to have 2 decimal places.
I have another query that summarises data from the previous query, however the results I get are not right, the decimals differ from the actual result, if I make a sum of the values by hand.
It seems as if the query I have made to apply percents only DISPLAYS data with 2 decimals instead of actually rounding it, so that the other query summarises data with more than 2 decimals, and thats where the difference int the end result comes from. I don't know how to fix this however.
Can someone please help.
Thanks!
View 1 Replies
View Related
Nov 13, 2006
Help!
I have one table that deals with project details. Then I have one table that deals with project history. Obviously each project has a bunch of unique details and several related records detailing the project history.
My goal is to be able to read details of any project along with the last history entry.
1-I have one query that gives me the last history comment. It creates a table.
2-Then I have another query that gives me the details of the project along with all the project history. It creates another table.
3-Then I have a select query that joins the first table and the second table comparing the project history comments and only showing the records that have the same comment. Therefore I get exactly what I need; The details of a project along with the last history comment.
The problem: I get the correct records selected, however 1) if the first entry in the project history table in one or several records is one word, then the query results include those records repeated a random numebr of times. 2)If the first entry in the project history table in one or several records is two words, then the query results include those records repeated a random number of times, but less times. 3) If the first entry in the project history table in one or several records is three words, then the query results include those records repeated a random number of times, but only a few times. 4) If the first entry in the project history table in one or several records is three words or more (on average), then the query results include only the record I wanted once.
What is going on?!??!?!?!
It almost looks like the query is runing once and again until a certain number of words are compared....
This is weird.
Anyone has any idea?
thanks
mafhobb
View 2 Replies
View Related
Aug 23, 2007
Hey guys (again :o)
I've encountered another weird error when applying an append query through a button. Here's the code:
Private Sub cmdSaveChanges_Click()
On Error GoTo Err_cmdSaveChanges_Click
'Saves the new Inspector information into the information table.
'Adds the two references created by adding a new inspector into the XREF_FILE_INSPECTOR table.
'This is the case that the references are formed by adding a completely new inspector.
If (IsNull(cmbInspector) Or Me.cmbInspector = "") Then
'Saves Inspector information
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'Adds references
DoCmd.RunSQL "INSERT INTO XREF_FILE_INSPECTOR" _
& "(FILE_NUMBER_CD, INSPECTOR_NUMBER_CD) VALUES " _
& "(" & [Forms]![GeneralFile].[txtGeneralFileNumber] & "," & [Forms]![AddInspector].[txtInspectorNumber] & ");"
Else
'This is the case where the user chooses an inspector from the provided combo box.
DoCmd.RunSQL "INSERT INTO XREF_FILE_INSPECTOR " _
& "(FILE_NUMBER_CD, INSPECTOR_NUMBER_CD) VALUES " _
& "(" & [Forms]![GeneralFile].[txtGeneralFileNumber] & "," & cmbInspector.Column(0) & " );"
End If
Exit_cmdSaveChanges_Click:
Exit Sub
Err_cmdSaveChanges_Click:
MsgBox Err.Description
Resume Exit_cmdSaveChanges_Click
End Sub
The problem is, when I run this I get a "Syntax error in INSERT INTO statement". This seems to only be the case for the first part of the IF statement, as the second part's append query works fine.
Help!
View 2 Replies
View Related
Apr 1, 2008
I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items.
How can i make 3rd query which will give me all but taken items from query1?
(of course items from query 1 are in query2)
thx in advance
View 7 Replies
View Related
Oct 12, 2007
Access 2003 -
I have many queries in a DB. they work fine, but *sometimes* some of my queries are wiped - when i try to run them they say "query must contain at least one cell" or something..
when i check the query then, its empty!
I have saved out the SQL code into a word doc because this has happened a few times now.. so i just copy and paste the query back in - but this is very inconvenient! what is going on?
View 1 Replies
View Related
Oct 25, 2004
I have 2 unbound boxes in the footer of my continuous form. Both total up separate sets of fields using DSum and do it correctly. I have used Conditional Formatting on Total1 to make the numbers turn bold and red whenever Total1 is greater than Total2.
Here comes the weird part. It will always work whenever Total1 is greater than Total2, but sometimes when Total2 is bigger, Total1 is still red, even after I attempt to modify it and it requeries, or I go to a different record and come back. Any thoughts??
Thanks
-Jason
View 6 Replies
View Related
Jan 24, 2006
I have a weird issue going on...I'm running a "Find Duplicates" query on one of my tables, I'm only checking for duplicate values in 2 fields and outputting a third. now this runs fine, unless I use the total's button to group them. When I group them that access instance stops responding. The reason I'm doing this is the third column can have duplicate values, for my purposes it doesn't matter, but I need to be able to see it.
I'm running this against 120k records.
Anyone help a poor man out?
View 2 Replies
View Related
Aug 14, 2006
Ok, a friend of my was on my laptop and he crashed the hardrive. I was able to get the database I have been working on off the computer but when I use my computer at work to try to open it, it opens but says
"Function is not available in expressions in tabel-level validation expresions"
on all the date-time functions. I can't save anything and my queries are not running because of this date/time thing. Anybody know what is going on?
View 1 Replies
View Related
Sep 11, 2006
I modified a form in an existing split database, by simply adding several fields. Now, I get various messages such as " not Access database "
or if it opens, the following message when I try to close it:
"Your last change may not have been changed because an internal buffer was locked by another user... "
Plus, when I do get out, I cannot delete the .ldb file which it says is open by Administrator. The thing is I ma only one to try this new database and I set no permisasions or... ?
Please, any ideas?
Russ
View 1 Replies
View Related
Feb 5, 2007
sorry,
there are too many requests and not enough information to easily create the required reports for my customers.
I want to copy a table/query with yes/no fields and paste into excel with the
boxes for the yes/no fields. . .
I can't remember or figure out how to do it. . .
I know it can be done as I have done it before, how, i can't recall. . .
thanks
sportsguy
:insert idiot emoticon:
View 5 Replies
View Related
Aug 22, 2007
Hi, I have a table with two fields: CustomerName and OrderNumber.
Some order numbers have dashes, for example:
123-4, 123-5, 123-6 etc.
I wrote a query to strip those dashes from these ordernumbers, but only for 3 specific customers: Mark, Mike and Jane.
This is the query:
SELECT CustomerName, OrderNumber,
IIf(InStr(1,[OrderNumber],"-")<>0 And InStr(1,[OrderNumber],"-B")=0,
IIf([CustomerName] Like '*mark*' Or [CustomerName] Like '*mike*' Or [CustomerName] Like '*jane*',
CStr(Mid([OrderNumber],1,InStr(1,[OrderNumber],"-")-1)),
CStr([OrderNumber])),
CStr([OrderNumber])) AS NewOrderNumber
FROM Test
GROUP BY CustomerName, OrderNumber,
IIf(InStr(1,[OrderNumber],"-")<>0 And InStr(1,[OrderNumber],"-B")=0,
IIf([CustomerName] Like '*mark*' Or [CustomerName] Like '*mike*' Or [CustomerName] Like '*jane*',
CStr(Mid([OrderNumber],1,InStr(1,[OrderNumber],"-")-1)),
CStr([OrderNumber])),
CStr([OrderNumber]));
This query works great when I run it from Access. However, if I run it from Visual Basic .NET, it does not strip any dashes for any customers. I am using the following connection string:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C: est.mdb
And the following query:
SELECT * FROM [Strip] ORDER BY NewOrderNumber
(Strip is the query name in Access)
Did anyone encounter a problem like this before?
Thank you,
Nader
View 2 Replies
View Related
Jul 18, 2005
hi all,
i have .adp projects.I am facing a problem for which i dont find any reason.whenver i open 2 .adp projects they hang up.I have to kill the msaccess.exe
It okay if i m running 1 project but i have to logout to open the second project.I can not run both at the same time.
Any help on this pls..
View 1 Replies
View Related
Sep 2, 2005
I am creating a switchboard to access a few data entry forms and to preview reports before printing. It seems that whenever I add a button to preview a certain form(the 4th button on the form, all others work fine), Access goes crazy. I get several errors, including:
"You have entered an expression that has an invalid reference to the propery MaxRecButton. "
and
"The Open Form action has been canceled"
Im also getting crashes to desktop and low memory errors.
Now I am getting "referenced memory" "memory could not be wrriten" errors? Is this an Access problem or a "need a computer upgrade" error?
Any clue why this might be happening for this form?
View 1 Replies
View Related
Nov 7, 2005
hi,
got a weird question.
Im working on a form atm (duh)
I have a form that exists in more tables.
Now you have 2 solutions: I can use a query and generate the form, or i can make various subforms.
what is the best option? or doesnt it make much diffrence?
View 2 Replies
View Related
Oct 1, 2004
I am creating a database. There are two main tables - Table 1 is created via a Make Table query, which pulls from an Oracle db, which is the db for our department's customer service system. Table 2 is populated via a form. Table 1 has personal customer information and Table 2 has order information. The two tables share a Customer ID. Table 2 contains data that was imported from an Excel sheet (what the users had been using up until this db was created). For this example, let's say each customer will only ever have one order so there will be a record to record match. Also, since Table 1 has to be manually refreshed using real-time data, it may lag behind or be ahead of Table 2.
Here's my problem:
I want the user to see the customer's name when he/she types in the Customer ID. I got the DLookup to work for NEW records - that is, for records that exist in Table 1 but have not yet been entered into Table 2 - but it will NOT work for the 300+ records that I imported from Excel. For those records, the Name textbox remains blank or if I type in the Customer ID, gives me an error about entering duplicate values.
Furthermore, if Table 1 is lagging behind Table 2, I want the user to just go ahead and enter all the order information and when he/she goes back to update the order at a later date (after Table 1 has been refreshed), the name to just pop up.
Can anyone help or offer any thoughts?
Thanks.
View 4 Replies
View Related
Aug 14, 2006
Ok, a friend of my was on my laptop and he crashed the hardrive. I was able to get the database I have been working on off the computer but when I use my computer at work to try to open it, it opens but says
"Function is not available in expressions in tabel-level validation expresions"
on all the date-time functions. I can't save anything and my queries are not running because of this date/time thing. Anybody know what is going on?
View 5 Replies
View Related
Oct 13, 2006
Hi,
I have this querry but when I execute on a button click it dosen't give me any error....but no data is saved in the table, why????
strSQL = " UPDATE DISTINCTROW projet set id='" & txt_id.Value & "',code_NOP='" & txt_code_NOP.Value & "',client='" & txt_client.Value & "',libelle_projet='" & txt_libelle_projet.Value & "',id_clarity='" & txt_id_clarity & "',technologie_projet='" & txt_technologie_projet.Value & "',em='" & txt_em.Value & "',date_debut=#" & txt_date_debut.Value & "#,date_fin=#" & txt_date_fin.Value & "#,site_mandataire='" & txt_site_mandataire.Value & "',charge_totale=" & txt_charge_totale & ",ca_total_vendu=" & txt_ca_total_vendu.Value & " WHERE id='" & Form_projet.id.Value & "';"
View 7 Replies
View Related
Dec 14, 2007
This outputs smth
SELECT Proj_Name & '-' & Proj_ID as Emp_Pr from Emp_Prj where trim(Proj_Name) like trim('Dot')
------
But not this...why ??? I am using ms access
SELECT Proj_Name & '-' & Proj_ID as Emp_Pr from Emp_Prj where trim(Proj_Name) like trim('%Dot%')
View 2 Replies
View Related
Feb 21, 2008
Okay guys. This one may not be able to get answered. I made a report in Landscape view. However, when you look at it on a different computer, it shows portrait. I know what your thinking, but I checked the page setup and everything and it says it's in landscape view. Even when I print it, it prints in landscape. Does anyone know why this one computer refuses to show landscape? Has anyone ever seen this problem before? I don't even know where to start to fix it. It's just weird!
View 2 Replies
View Related
Mar 24, 2013
I am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
View 3 Replies
View Related
Apr 2, 2013
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
View 3 Replies
View Related
Feb 7, 2006
have a table call 'ClinicTimeTable' there is a column call 'ClinicDate', another column called 'DrID', different row could have same 'ClinicDate' and 'DrID', so I wanna to count how many Drs in a particular day, I have following code, but it acts so werid, it onlys work for some dates! say if I put 02/02/2006 then I can get the number of DrID by observing 'count', however, if I put 01/02/2006 like below, count is 0! but there is some records with 02/02/2006 (I open the table directly in Access), how can this happening? how can it only partly works? it will make more sense if it don't work at all.
Dataset ds = new Dataset();
query = "SELECT DISTINCT DrID FROM ClinicTimeTable WHERE ClinicDate = #01/02/2006#" ;
OleDbDataAdapter myAdapter = new OleDbDataAdapter( query, oleDbConnection1 );
myAdapter.Fill ( ds );
int count = dds.Tables[0].Rows.Count;
any helps will be so appreciated! I am going mad at this problem!
by the way, i just tested that if any day in Jan 2006 is fine, say if i search 23/02/2006 or 22/02/2006 is fine... can't imagine why...
View 3 Replies
View Related
Dec 6, 2006
Hello,
I have simple invoice database, tyhe old one I made works fine, trying to make a newone always simple but now Access is not helping me for some reason,,,, Can you look at this? I create an invoice form using wizzard i join invoices and invoices details to it and from there I create my Invoive form that has the invoice info and the details.... I change some stuff for looks and easyness... Mostly change text boxes to combo boxes... I did this in office 2003 and had the same results than in office 2002, Why dont know... Once I create the invoice from and change the text boxes combos and ofcourse set everything else, once I pick a product from the detail form I get the product but I dont get its price. Now on my other older database made excatly the same way it works!!!! WHY IIIIIIII DONT KNOW!!! please help me:(
I have attached both db so you can look at them!!!
Thanks
View 2 Replies
View Related
