Major problem with DATES, its back to front!

[JacobLondonUk]

i have a table with a date field in it, set as short date, and a field for a NAME
then i have a form with a command button to change the date for a new date which is the beginning of the next week.
i used the following code:
dim TodayDate As Date
dim StrSql as String
TodayDate = Format((Date - Weekday(Date) + 8), "short date")
StrSql = "Insert into MyTable ([DateField], [Name]) Values ( #" & CDate(TodayDate) & "#, '" & Me.Name & "' )"
docmd.runsql StrSql
When i check in the table the date has been changed from the old date to the new date, but the date appears the wrong way around.
if next sunday will be 27/Nov/2011, instead of 27/11/11, it puts in 11/11/27
i tried everything, i am going bizark on that, can anyone help me, will appreciate, thanks

 

[Kiwiman]

Howzit

It probably has something to do wioth SQL requiring the US Date format. Try

TodayDate = Format((Date - Weekday(Date) + 8), "mm/dd/yyyy")
StrSql = "Insert into MyTable ([DateField], [Name]) Values ( #" & CDate(TodayDate) & "#, '" & Me.Name & "' )"

 

[vbaInet]

You are inserting into your table, no need to format it:

StrSql = "INSERT INTO MyTable ([DateField], [Name]) VALUES (Date(), '" & Me.[COLOR=Red][[/COLOR]Name[COLOR=Red]] [/COLOR]& "');"

Plus Name is an Access/VBA reserved keyword and hence, it should be avoided. So you can see that it needs to be enclosed in square brackets or else VBA will think you are referring to the name of the form.