MySQL Forums
Forum List  »  Microsoft Access

SELECT on INT (and other) intermitantly fails
Posted by: Joelle Tegwen
Date: November 01, 2005 09:25AM

So last week I had a problem (I thought) with updates not happening, but really the problem was that the WHERE clause was returning no records.

After a fair bit of trial and error testing I found out that the problem was with the integer column. So say the table looks like this:

TableID VARCHAR(255)
UserID INT(10)
InputID INT(10)
Answer LONGTEXT
Updated DATETIME
WorksheetID VARCHAR(255)

IF I run: SELECT TableID FROM myTable WHERE WorksheetID='myWorksheetID' AND InputID=5
I get no rows returned even though that record is in the table.
If I ask for a different record: SELECT TableID FROM myTable WHERE WorksheetID='myWorksheetID' AND InputID=1 then I do get a record

So I did a test and I created a new column
temp INT and I copied the data from InputID into that column. Then I ran
SELECT TableID FROM myTable WHERE WorksheetID='myWorksheetID' AND temp=5
and it returned the desired record.

So then I tried changing the InputID field to type INT and running the query and I still get no records.

I tried changing the data type in a new import and I wasn't able to do so. (or figure out how)

Why is this a problem? My understanding is that the (10) is just a mask, it doesn't actually affect the data.
Is this a bug? How do I make the import tool migrate these long ints correctly?

I couldn't find anything on this but searching for "data type" and "access" on the web is a mostly futile endeavor.

Thanks
Joelle

Options: ReplyQuote


Subject
Views
Written By
Posted
SELECT on INT (and other) intermitantly fails
2975
November 01, 2005 09:25AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.