forcing date format when importing data

[megatronixs]

Hi all,

I have a form that is used to import data from excel sheet.
when it imports the data, it uses the date of import day (lets say today) in the table. we use the UK date format dd/mm/yyyy, but when we use it via the vba automation, it uses the US date format and works really bad.
this is the part from the SQL to update the table:

[COLOR=black][FONT=Segoe UI]#" & Me.Date_uploaded.Value & "#[/FONT][/COLOR]

any way this could be forced to use the dd/mm/yyyy ?

greetings.

 

[spikepl]

I think you've confused yourself. Dates are stored in the same way, no matter what country or format. That has been explained to you in some other post.

Format is "description for how to display a value" and is only concerned with how you look at dates, and when you do not input a Date variable but some text in which a date is written as text! A Double value like 1.1212121212 can be formatted to display as 1,1 but that does not change its actual value of 1.1212121212

Dim a as date

A= Forms!MyFormname!MyDateControlName

MsgBox A

this will show you the date A in the format set in your country locale for windows.

When you WRITE an SQL string, THERE you need to take into account how a date is formatted, because the meaning depends on format, and SQL requires the date to be in the US format of mm/dd/yyyy. That has NOTHING to do with how that date later will be displayed, or how it is stored, only how it is interpreted on input.

Dim strSQL

strSQL = "SELECT * FROM tblMyData WHERE MyDate=#" & Format(MyDate,"MM/DD/YYYY") & "#"

 

[arnelgp]

i think that's the way it is, en(us) in query. the display of date format depends on your system local setting, while "date" values area actually stored in db as numeric double.