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.
Thanks to all of you who answered and gave me suggestions on where to
look for my problem.
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.
*sigh*
I tried changing the data type in a new import and I wasn't able to do so.
Why is this a problem? My understanding is that the (10) is just a mask,
it doesn't actually affect the data.
How do I fix this? Do I really have to go through my entire application
and rename columns? We've got several development copies of this out
there so doing this for all of them would be a major pain, not to
mention increase the down time of our site when we do the live migration.
I couldn't find anything on this but searching for "data type" and
"access" on the web is a mostly futile endeavor.
Thanks
Joelle
