Problems after Update Query

[NeilPattison]

I have a problem with a database I'm maintaining after I have carried out an update query.

I have a macro that imports data from an excel spreadsheet onto a temporary table, which then runs the update query to my main table.

This all works fine and the data is updated correctly.

The problem occurs when someone tries to carry out a search on the date fields in the main table (i.e. it comes up with no results).

I know the queries that run the searches on said date fields are all correct as, if the data is manually typed in, they all work fine.

I thought it may have been something to do with the formats of the data coming in from excel but they are all the same (dd/mm/yyyy).

Does anyone have any idea why I'm having this problem?

Any help would be greatly appreciated.

 

[randy700]

One possibility that comes to mind right away:
Do the values in the Excel spreadsheet include extra spaces?
This could affect your queries - " THIS VALUE " won't be returned when searching for "THIS VALUE".

You could use LIKE in your search queries or (better yet) use the TRIM function in your update query.


Randy

 

[NeilPattison]

Thanks for your reply, however I have now sorted this.

I thought the issue was more complicated than it actually was. The issue was actually in the source data coming from the excel spreadsheet, so my database was working correctly all the time.

 

[ClulessChris]

MS Access prefers to work with the US date format 'mm/dd/yyyy' this could be the route of your problem. take a look at the updated records an check that the date are correct as you'd expect.


Never knock on Death's door: ring the bell and run away! Death really hates that!