I wont to import a excel into my db, the problem is that the excel has the dates in uk format, dd/mm/yyy, hh,mm, ss and my db date format is us format short date mm/dd/yyyy. How can do fix that?
I wont to import a excel into my db, the problem is that the excel has the dates in uk format, dd/mm/yyy, hh,mm, ss and my db date format is us format short date mm/dd/yyyy. How can do fix that?
Your DB should be in the same format.
The only issue is if you use the dates in string with VBA.
if you create a query in design view and add a date criteria, even if the the date is 01/12/2023 1st Dec 2023, Access will convert that in SQLview to 12/01/2023
I used to used this for queries where I built the criteria in VBA
Public Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Needed for dates in queries as Access expects USA format.
I used to use it as
debug.Print Format(date,strcjetdate)
#12/03/2023#
Other option is in yyyy-mm-dd format which is unambiguous.
Last edited by Welshgasman; 12-04-2023 at 01:44 AM.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
The best method I've found is to convert your dates to a long value, then you can format it however you want.
e.g. UK 04/12/2023 = 45264.00
Code:Dim dDate as DateDim lngdate As Long lngdate = CLng(CDate("04/12/2023")) ' converts to 45264 dDate = Format(lngdate , "mm/dd/yyyy")
However, US dates - pure hellish nightmarish pain - don't do it! I just want to burst into tears thinking about it!
Reconsider!
Best I can suggest is to save Excel as CSV (assuming dates will retain UK structure) then import/link CSV and force date field to be treated as text. Then in Access run INSERT SELECT using text manipulation in query to parse date string and rebuild in U.S. style to add records into destination table.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Have you even tried importing and specifying it is a date field?
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
Welsh, I tested import/link with Excel and CSV - date of 13/1/2023 just errors (#Num!) when imported as date/time type. External data wizard does not offer opportunity to specify field type with Excel import/link - can with text file.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Well I know they are just numbers as well in Excel. As I have copied and pasted, and have had to format the destination cell as a date, as the initial paste, just produced a number.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
Probably works because your Excel data and your system are both UK.
My system is U.S. and just doesn't handle the 13/1/2023. Excel is not reading this as a valid date and treats it as text even when other values in same column are recognized as date.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Using my old diabetes workbook.
Column A has my dates in UK format.
Column O has a value of =A2 and copied down the sheet.
Then I formatted column O as US short date format.
As I mentioned, the dates are stored as numbers, so the dates are still correct, even in US format.
If this fails, then just unformat the dates and format them as numbers, with no decimals.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Well no. You would put the date in in your region format. Them change it to UK format. The numeric number is going to be the same, just like Access stores the date as a number.
It is just a format after all.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
And I get impression that OP is receiving data that was entered as UK date and import is failing because of that. That is what I attempted to emulate.
We need to hear from Fredo on whether formatting has made a difference.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
We also need to know the method of importing, as I am sure an import spec would solve this. Or even change the format to US as I did. The value is not changing, just the format.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
AFAIK, cannot build import spec with Excel, can with CSV.
I assumed someone is typing into this sheet in UK structure: 13/1/2023. If their system is actually storing value for 1/13/2023, then yes, makes sense OP changing format should work. Apparently, I cannot replicate this and would need the actual sheet causing issue in order to evaluate.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
I can send you that diabetes workbook, if you like?
PM me if you do.
In fact no need for PM. Here it is.
I had to add a .txt extension to get it uploaded. Just remove that extra extension.
The import wizard allows me to specify type of fields or skip, though it recognises both the UK and USA columns fine by itself.
EDit: I think you should have enough on the Daily sheet, but if not there is more data on the Old Daily sheet. You would need to add the extra column with the =A2 formula and format of USA date yourself in that case.
Last edited by Welshgasman; 12-04-2023 at 04:44 PM.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba