Date time query problem

[andyspeak]

Hello
Im sure this is a common problem but cant find an answer in the forum.
I want to merge a number of excel files which all share a date/time column in the format dd/mm/yy hh:mm. The range of of dates and times differs between excel files because the data is from monitoring equipment which were started and stopped at different times but have the majority of dates/times in common.

The query runs but then all the columns in the resultant table, while having correct column headings, are all empty. I ran a test with a subsample of the data which all shared the same date/times and it still didnt work so im guessing it isnt due to the fact that each excel file has different ranges of date/times to match up.

Attached is an example of one of the imported excel files.

 

[JohnLee]

Hi,

You need to split the time part out from the date part of your date time field, because the time part of the date time field is not the same for every instance of 30/06/2012 for example:

30/06/2012 00:10
30/06/2012 00:20
30/06/2012 00:30

these are not seen as being the same, because as you can see the time part is different for each example above and therefore whatever your criteria in the query is, is not producing your desired results.

When you import your data into your table you should have a date field specifically for your date part and a time field specifically for your time part for Example:

dtmDate and dtmTime

When importing your data from your spreadsheets ensure that only the date part goes into the dtmDate field and only the time part goes into the dtmTime field.

Ensure that your dtmDate field is set to short date and your dtmTime field is set to Short Time to match 00:10 [hhnn (hours and minutes)] or Long time to match 00:00:10 [hhnnss (hours, minutes and seconds)].

You should then be able to create a query to meet your requirements.

Regards

John

 

[Brianwarnock]

I don't understand what the problem is, what the query is trying to do and how it is failing. I no longer have Access so posting the DB. Won't help me, I am posting to say it is not only not necessary to split date time into two fields but normally advised against as it is easy to do any separation when required but more difficult to recombine.

Lookup DateValue and TimeValue

Brian