I am currently working on an excel program that obtains data from a data entry worksheet to a display worksheet using vba and macros. The majority of the data can transfer properly, except for the Time field.

To give an example the data in Time field is written like this: "12:00 PM" I'm using custom format of h:mm AM/PM.

To link it to the display worksheet I use a SQL string to call for the data in the data entry work sheet and added a button to execute the code. However the data for Time in display worksheet became: "0/1/1900 12:00:00 PM"

What's more, after executing the code the time in the data entry which I set the format initially became the same as the one in display worksheet.

The display worksheet functions using 3 SQL commands as seen below.

select * from EntryTable

select A.*,[Date + Time] from Summary1 A left join (select [Die No], max(Date + Time) as [Date + Time] from Summary1 group by [Die No]) B on A.[Die No] = B.[Die No]

select * from Summary2 where [Date + Time] = Date + Time 

Can someone please share with me if this is a SQL execution error or something else entirely?

  • I'm using custom format of h:mm AM/PM. This custom format is applied on Excel before the import? – Vylix Jun 1 '17 at 8:17
  • Yes. I applied it on "Format Cells" for both Excel sheets. – hjh93 Jun 1 '17 at 8:26
  • Can you apply it again after the import? It might just be Excel override the custom format. Applying the format after the import should work. – Vylix Jun 1 '17 at 8:28
  • I applied the custom format after and it can change. However, having to change it everytime I click Execute kinda defeats the purpose of the program. Is there a way for me to apply the custom before the import? Because there are 2 date fields in my program as well and those work perfectly. – hjh93 Jun 1 '17 at 8:34
  • unless if there is a way to force the columns in the display file to show the data in my desired format. Is there a VBA code that can do that? – hjh93 Jun 1 '17 at 9:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.