advice on access query

[teng asprer]

i'd like advice on my problem. i created a service record
table on my access database. i has a "DATE TO" and a "DATE
FROM" field. we extracted data from an excel file to this
field and we have an error. some of the months ending in
31 was erroneously encoded as ending in 30 (ex. -
12/30/2003 instead of 12/31/2003. this problem would
create a 1 day gap in the service. how can i get all the
months ending in 31 and filter those we encoded ending in
30. how can i update/filter those so the months ending in
31 will be updated/append to the correct ending day?

 

[Dale Fye]

How did you do your "extraction" from the Excel file? I've seen
Access and Excel do some strange things but this appears to be a bug
I've never seen? If I understand you correctly, Access converted a
12/31/02 or something like that to 12/30/02?

You cannot simply write an update query, since you will most likely
already have an entry for 12/30/02. You could create a find
duplicates query where the Month(DateTo) IN (1, 3, 5, 7, 8, 10, 12),
but I'm not entirely sure what that is going to accomplish.

--
HTH

Dale Fye


i'd like advice on my problem. i created a service record
table on my access database. i has a "DATE TO" and a "DATE
FROM" field. we extracted data from an excel file to this
field and we have an error. some of the months ending in
31 was erroneously encoded as ending in 30 (ex. -
12/30/2003 instead of 12/31/2003. this problem would
create a 1 day gap in the service. how can i get all the
months ending in 31 and filter those we encoded ending in
30. how can i update/filter those so the months ending in
31 will be updated/append to the correct ending day?

 

[John Spencer (MVP)]

To identify the records try

SELECT [Date To]
FROM YourTableName
WHERE [Date to] is Not Null AND
Day([Date To]) <> Day(DateSerial(Year([Date to]),Month([Date to])+1,0))

If you want to update then you could even skip the criteria and do:
UPDATE YourTablename
SET [Date to] = DateSerial(Year([Date to]),Month([Date to])+1,0)
WHERE [Date to] Is Not Null