incorrect time formatting

[Maven4Champ]

In my ongoing battle with Microsoft Access and it's usability via importing data from Excel - I have another question.

I am pulling data from a report in a phone application called CentreVu.

The specific data I pull is by agent id (someone's telephone extension) the day (06/11/04) and the to and from time. In this case, I am doing 10am to 7:30pm. Now in this application, I choose 10:00-19:30

When it exports, the from column is correctly done in AM/PM format. The From field is not. It appears as follows:

11:00 AM
12:00 AM
1:00 AM
2:00 AM

whereas the To field reports as follows

11:30 AM
12:30 PM
1:30 PM
2:30 PM

Is there a way to convert that time format correctly in the "From" field to report the AM/PM correctly? As it stands now, I am changing the AM to PM in EACH CELL in Excel then importing it over to Access.

Any help?

 

[Maven4Champ]

Hello,

Ok, here is my problem in a nutshell.
My supervisor asked me to create a database to handle reporting from a telephone agent application called CentreVu. In doing so, I am running into some problems.

For instance, there is a 'Time From' and 'Time To' field in the report
Here is how it looks in the CentreVu report:

From To
11:00 - 11:30AM
12:00 - 12:30PM
[color:red]1:00 - 1:30PM[/color]

Now here is my problem. I am exporting these reports to an .xls format and then importing them into Access. When I do this, it reports anything after noon (shown above in red) as AM in the 'Time From' field. Example: the 1:00 shown above to the right of 1:30PM actually shows up as 1:00AM.

Now normally, I figure if we are just running reports, I could change the 1:00AM to "Medium" time format to show 1:00 and no one would be the wiser - however, if you go to use search criteria and want to pull up all calls made between 1:00PM and 1:30PM, you would get nothing because 1:00PM doesn't exist - only 1:00AM does...

Make sense?

 

[bat17]

Have you tried opening this in Excel to see if the fault lays in The data being exported from CentreVu?

Another possibility to look at is to export as CSV which gives you more control on importing into Access.

HTH

Peter

 

[Abulafia]

Dates and Times from CentruVui are terrible. You have to perform extra calculations on them yourself - typically division by sixty.

 

[Elysyn]

Hi Maven4Champ

I have some experience in working with exporting/importing information from CentreVu - both in Access and Excel. Just a few questions: When you import into Access, (and I apologize if you have already stated this) are you importing all 3 fields from excel (i.e. [12:00] [-] [12:30AM]? If so, have you considered only importing the time that contains the AM/PM designation? (Then you could search based on an interval ending time...) Also, if you want to get the entire interval, (12:00-12:30AM) you can concatenate in Excel before you import into access. (=A1&B1&C1)

I'm still a novice extraordinaire when it comes to VB, but I believe there are also some things you can do in code to correct the times on import. One of my co-workers has done something similar - he set up his import module to convert the time into interval numbers. A 24 hour day would have 48 intervals.

I hope this helps a little... I know that dealing with time in Access/Excel can be a challenge!!

-Elysyn

 

[Maven4Champ]

Have you tried opening this in Excel to see if the fault lays in The data being exported from CentreVu?

Another possibility to look at is to export as CSV which gives you more control on importing into Access.

HTH

Peter

The fault does indeed lay within the data being exported from CentreVu - thats the problem in itself. I have to delete a row for TOTALS, delete a "-" column, etc.

I have figured out the time problem though. I used an "expression" in a TABLE UPDATE QUERY to modify the times after importing them into Access - so that essentially is fixed.

 

[Maven4Champ]

Hi Maven4Champ

I have some experience in working with exporting/importing information from CentreVu - both in Access and Excel. Just a few questions: When you import into Access, (and I apologize if you have already stated this) are you importing all 3 fields from excel (i.e. [12:00] [-] [12:30AM]? If so, have you considered only importing the time that contains the AM/PM designation? (Then you could search based on an interval ending time...) Also, if you want to get the entire interval, (12:00-12:30AM) you can concatenate in Excel before you import into access. (=A1&B1&C1)

I'm still a novice extraordinaire when it comes to VB, but I believe there are also some things you can do in code to correct the times on import. One of my co-workers has done something similar - he set up his import module to convert the time into interval numbers. A 24 hour day would have 48 intervals.

I hope this helps a little... I know that dealing with time in Access/Excel can be a challenge!!

-Elysyn

I have been able to correct the time using an expression. I don't have it on hand because I am at home. I will be at work at 10amCST. I can post it here for others to use if necessary.

You did speak about the "-" column. Thats my problem. I told my supervisor we might just have to open up each Excel spreadsheet and when it actually goes through the prompts, it gives you the option to NOT IMPORT certain columns. I select that column and choose not to export.

Because that column does not have a header after being exported from CentreVu, Access errors out when trying to import it - so I just delete it all together.

My other thing is, do you know the TOTALS row that it makes. It happens to be in the worste place. The column headers are in the first row, and the TOTALS are in the second row, but the word TOTALS is housed in the first cell below the "TIME" column so it can't read it.

So here is what I do. I choose DO NOT IMPORT on the "-" column. I then delete the Totals Row. I then Rename the two time columns as "From" and "To" as in FROM this time TO this time.

Granted that is just 4 small steps you have to do each time - but my supervisor would like it as seamless as possible - i.e. not have to do that at all. But Access just won't let me.

Know that I have figured out the time problem - I am faced with how to simplify the import process via eleminating the steps above.

THATS THE CHALLENGE!