Why is Access converting my date format?

[bryanuk]

Hi,

I am new to access and have only been messing about with it for a few weeks, so forgive me if my code sample is not efficient or elegant. I am tidying it up as I learn more!

My problem is that, depite variable ConvertedDate (it is a Date variable)being in the correct format (I think), of dd/mm/yyyy, whenever I write it to the table Membership, it gets stored as mm/dd/yyyy. I have tried everything to understand this problem but I am lost now!!

Any help/pointers would be appreciated, so I can move on to the next challenge!!

This is the code:

Private Sub SaveReturnToRenewMemClick_Click()
'Get info from Input form
Dim RenewalDate
Dim ConvertedDate As Date
Text19 = Forms!RenewMembership!Text2 'Text2 = start memNum
Text22 = Forms!RenewMembership!Text4 'text4 = end menenum
Text11 = Forms!RenewMembership!Combo7 'combo 7 = renewal fee
RenewalDate = Forms!RenewMembership!Text10 'text10 = renewal date
DoCmd.Close acForm, "RenewMembership", acSaveNo
'set up recordset
DoCmd.OpenTable "Membership"

Dim UpdateCommand As String
Dim MemState As String
Dim YearCol

Text22 = CInt(Text22)
Text19 = CInt(Text19)

ConvertedDate = CDate(Left(RenewalDate, 2) & "/" & Mid(RenewalDate, 4, 2) & "/" & Right(RenewalDate, 4))
'calculate which date column for save data to
If DatePart("yyyy", RenewalDate) = 2012 And DatePart("M", RenewalDate) >= 9 Or DatePart("yyyy", RenewalDate) = 2013 And DatePart("M", RenewalDate) < 9 Then
YearCol = "2012"
GoTo Update
End If
If DatePart("yyyy", RenewalDate) = 2013 And DatePart("M", RenewalDate) >= 9 Or DatePart("yyyy", RenewalDate) = 2014 And DatePart("M", RenewalDate) < 9 Then
YearCol = "2013"
GoTo Update
End If

'Set up correct recordset query
Update:
If YearCol = "2012" Then

Do
UpdateCommand = "UPDATE Membership SET [Fee Paid] = " & Text11 & ", 2012 = 'Renewed', [2012 Date Paid] = #" & ConvertedDate & "# WHERE [Membership Number] = '" & Text19 & "'"
CurrentDb.Execute UpdateCommand
Text19 = Text19 + 1
Loop Until Text19 > Text22
GoTo GetOut
End If
If YearCol = "2013" Then

Do
UpdateCommand = "UPDATE Membership SET [Fee Paid] = " & Text11 & ", 2013 = 'Renewed', [2013 Date Paid] = #" & RenewalDate & "# WHERE [Membership Number] = '" & Text19 & "'"
CurrentDb.Execute UpdateCommand
Text19 = Text19 + 1
Loop Until Text19 > Text22
GoTo GetOut
End If

'clean up and return to renew form
GetOut:
DoCmd.Close acTable, "Membership", acSaveYes
DoCmd.Close acForm, "ConfirmMultipleRenewMembership", acSaveNo
DoCmd.OpenForm "RenewMembership"

End Sub

 

[Solo712]

My problem is that, depite variable ConvertedDate (it is a Date variable)being in the correct format (I think), of dd/mm/yyyy, whenever I write it to the table Membership, it gets stored as mm/dd/yyyy. I have tried everything to understand this problem but I am lost now!!

Hi there,
Access uses Windows' Regional Settings to set the short and long date formats so you might want to check there first.

Best,
Jiri

 

[bryanuk]

Jiri,

Thanks. I have checked that setting and it is set to UK and short date dd/mm/yyyy, so that is not the problem.

If I navigate to the entry in my database that has been written incorrectly and select the date picker that appears, if I then select the same date, it is written in the field correctly (ie dd/mm/yyyy), so that to me indicates some problem with my code (with the Update statement?)

 

[Brianwarnock]

Access expects dates to be in US format in SQL and VBA read Allen Brown's web

http://allenbrowne.com/ser-36.html

Brian

 

[spikepl]

The first thing to realize is that there are thousands and thousands of people who have experienced the same basic problems as you before you, and many have recorded their experiences and solutions on the web and on sites like this one. Make a small effort and you'll find a solution to 80 % of your problems in 5 or 10 minutes, beginner or not. Learn how to google - how to specify the important terms that describe the essence of your problem.

 

[bryanuk]

Brian,
Phew!!

It looks like the problem I have but I can't understand the code (I am not that experienced yet!!)
If possible could you give me a clue in simple language (or code) that would apply to my code? If not I will have to abandon the SQL method and try to work out how to use Query to get what I want.

Thanks

 

[bryanuk]

Spikepl,

Thanks for your unhelpful comments. You certainly know how to encourage a new user !!!

 

[jdraw]

There are a number of date functions from Pat Hartman at
http://www.access-programmers.co.uk/forums/showthread.php?t=238821
that may be useful to you.

 

[Brianwarnock]

Its years since I did this so although I might stumble through it I am not the best to give a detailed explanation, for your current needs use a format in your SQL string to force US fiormat, which will be converted to local format in the table


#" & Format(RenewalDate, "MM/DD/YYYY") & "#

BTW cannot understand this

ConvertedDate = CDate(Left(RenewalDate, 2) & "/" & Mid(RenewalDate, 4, 2) & "/" & Right(RenewalDate, 4))

as RenewalDate is a date although you did not declare it as such, why?

Also when posting code wrap it in the code tags to keep the formatting eg indents etc

Brian

 

[Brianwarnock]

Spikepl,

Thanks for your unhelpful comments. You certainly know how to encourage a new user !!!

Actually Spike's reply was both encourageing and helpful.
It let you know that you are not alone in having this problem and that help may be found by googling.
In the General forum, I think, there is a sticky telling you how to Google this forum. It gives a more specific search than the forum's own.

Google can be your friend.

Brian

 

[bryanuk]

Brian,

Thanks for the input, it worked a treat!!

Since I recieved your reply, I still can't find any reference to this solution either on this Forum or Google or in my refeernce book (The Missing Manual). Is it a well know fact that, although I am dealing with UK dates in Forms, when I use UPDATE I have to convert it to US for Access to Convert it back again?? (seems a bit silly to me!)

 

[bryanuk]

Brian,

As I said, your solution to the date format worked a treat, however, now that the VBA runs, I have another slight issue. Once fixed then my Form is finished!!

The section of code which does the update of the records is

If YearCol = "2012" Then
   Query = "SELECT [Membership Number],[Fee Paid],[2012],[2012 Date Paid] FROM [Membership] WHERE [Membership Number] = '" & Text19 & "'"
   Set RecordSet = CurrentDb.OpenRecordset(Query)
   
Do
   UpdateCommand = "UPDATE Membership SET [Fee Paid] = " & Text11 & ", 2012 = 'Renewed', [2012 Date Paid] = #" & Format(RenewalDate, "MM/DD/YYYY") & "# WHERE [Membership Number] = '" & Text19 & "'"
   CurrentDb.Execute UpdateCommand
   Text19 = Text19 + 1
   
Loop Until Text19 > Text22
   GoTo GetOut
End If

When I exit the Form which runs this code, I get an error/warning message:

Write Conflict
This record has been changed by another user since you started editing it.....


The records in the Table are OK.

Any idea what is causing this (it doesnt seem to happen all of the time) or how to get rid of it?

Cheers, Bryan

 

[Brianwarnock]

Bryan

It is nothing to do with doing an Update , as I said in my first post in VBA and SQL dates must be in US format.

Access actually stores dates as a double with the part before the decimal point being the date part and after the time element. Thus when you are using forms reports tables and incidentally the query design grid you van use the local format as set in windows and access converts that to the double, and will convert the double back to that format for viewing.

In a SQL. String such as yours a date must be in US format thus when you create the string you must ensure that this is so, it's a conversion from the double to the US format not UK to US , then when it is stored in your table it will be stored as a double but with the correct value, and when you view it it will show correctly in the local format.

You can see what your SQL. String looks like by coding
Debug.Print updatecommand Ie your name for the sqlstring
After you have created the string and selecting immediate window from the view menu.

Brian

 

[Brianwarnock]

I don't know why you are getting the error message, you may have to ask on a separate thread

Brian