Working with dates in VBA (1 Viewer)

[matg]

Hi all

I'm fairly new to Access and VBA so am learning as I'm going along.

I have a form that records progress reviews for learners and have used the following code on the 'add new record' button:

MLEARN_ID.DefaultValue = Me.MLEARN_ID
NORVW.DefaultValue = Me.NORVW + 1
PRVRVW.DefaultValue = "#" & Me.ACTRVW & "#"

MLEARN_ID is the learner's unique ID
NORVW is the number of the review
PRVRVW is the previous review date
ACTRVW is the actual review date

All of the dates I enter are in the DD/MM/YYYY format, when this code is executed however, the date in the new record in the PRVRVW field is in the MM/DD/YYYY format. I want this to be in the same format as the previous record.

I've found some info on formatting this using an SQL string but I'm not 100% sure what I'm doing.

Would appreciate some help.

Many thanks

 

[CameronM]

I tested your code but couldn't get the same result, but to force to format of you default dates you could try;

    MLEARN_ID.DefaultValue = Me.MLEARN_ID
    NORVW.DefaultValue = Me.NORVW + 1
    PRVRVW.DefaultValue = Format("#" & Me.ACTRVW & "#", "dd/MM/yyyy")

 

[matg]

Hi Cameron

Sorry, what couldn't you make work?

I have tried the code you suggested but that didn't seem to work.

Many thanks

 

[CameronM]

I could not reproduce the date format switching. I have mine set to dd/mm/yyyy and when I use the code you have, the new record has dd/mm/yyyy also.

Where are you placing the code? I tested using a button that also created the new record. My full code is below:

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click
    MLEARN_ID.DefaultValue = Me.MLEARN_ID
    NORVW.DefaultValue = Me.NORVW + 1
    PRVRVW.DefaultValue = Format("#" & Me.ACTRVW & "#", "dd/MM/yyyy")
    DoCmd.GoToRecord , , acNewRec
Exit_Command4_Click:
    Exit Sub
Err_Command4_Click:
    MsgBox Err.Description
    Resume Exit_Command4_Click
 
End Sub

 

[matg]

Hi Cameron

The code you pasted is exactly the code that I am using on my button.

Could the error be with the PRVRVW txtbox? Perhaps it's lifting the ACTRVW value in dd/mm/yyyy format but copying it in mm/dd/yyyy format?

Thanks

 

[CameronM]

I was able to recreate the error when using dates early in the month, for example using 6th Feb 2009 (06/02/2009), I also got 2nd June 2009 (02/06/2009).

In the past I have been able to tweak these kind of this by splitting the date into its various parts as shown in the code below. This might be worth a shot.

"#" & Day(Me.ACTRVW) & "/" & Month(Me.ACTRVW) & "/" & Year(Me.ACTRVW) & "#"

 

[matg]

This doesn't seem to work either - the ACTRVW date is 07/01/2009 but the next PRVRVW date is still coming up as 01/07/2009.

Appreciate your efforts.

Thanks

 

[darbid]

Hi Matg, please understand that I am a beginner too but I too have dealt with dates. If you read nothing else on this including what I write here I strongly suggest you read this from Allen Browne http://allenbrowne.com/ser-36.html

I will also explain in my simple terms what I have found out about dates. Access does not store your date in any format, it is just a number. When it comes time to do something with it Access will display that number based on certain things. I cannot give you all those things because I am not an expert, but I can tell you the ones I know about.

First how a date will appear in any textbox of yours or label or the format of a date variable is based on what you have in your regional settings. eg if you have a variable such as mydate and it is a date variable then no matter how you give it a date you will always get it back in the format of your regional settings.

You can try this with your debugger. set a variable, give it a date and then hover your mouse over it, and the format will be based on your regional settings.

This means that every time you want to display a date you have to tell access how you want to see it. For example I deal with date formats from 4 countries

Japan YYYY/MM/DD
China YYYY-MM-DD
ME DD/MM/YYYY
US MM/DD/YYYY

so no matter how someone enters the date if i have to later display this I use the following

me.mytextbox = Format(mydate, "dd\/mm\/yyyy")

NOW mytextbox will always show me dd/mm/yyyy

If I were to just say

me.mytextbox = mydate

Then access would format the date based on the regional settings.

Allen Browne is going to help you to understand when it comes time to find dates using filters or SQL.

edit: You need to especially take note of what Allen says when you use "#" around dates as access always uses an American format.

 

[matg]

Hi Darbid

Perhaps I'm doing something wrong.

I used the Format you suggested in several places but this still doesn't work.

Perhaps you could advise where in my code I would place this.

Many thanks

 

[darbid]

I am not sure I understand the above posts, at the risk of asking you to do the same thing twice, could you give me your example. I will then try.

I assume that you are dealing with bound forms?

 

[matg]

Hi darbid

It's a rather large database, but the part I'm having problem with is as follows:

I have a form that records progress reviews for learners and have used the following code on the 'add new record' button:

MLEARN_ID.DefaultValue = Me.MLEARN_ID
NORVW.DefaultValue = Me.NORVW + 1
PRVRVW.DefaultValue = "#" & Me.ACTRVW & "#"

MLEARN_ID is the learner's unique ID
NORVW is the number of the review
PRVRVW is the previous review date
ACTRVW is the actual review date

The above code is part of the 'add new record' button code as per Cameron's post above.

These are bound txtboxes and forms.

If there's anything else you need to know please ask.

Many thanks

 

[darbid]

PRVRVW.DefaultValue = "#" & Me.ACTRVW & "#"

so if we focus this above line is your problem.

You are asking access to display the value of Me.ACTRVW AND telling access that it is a date with the "#" in the textbox PRVRVW. This will be displayed based on the computers regional settings as far as I can see.

 

[matg]

We also tried the following line:

PRVRVW.DefaultValue = Format("#" & Me.ACTRVW & "#", "dd/MM/yyyy")

This didn't work either.

Can you suggest how this might be resolved?

Thanks

 

[matg]

I've noticed this on the website you recommended - I'm not sure how this works or where I would put it though?

Function SQLDate(varDate As Variant) As String
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function

 

[darbid]

if your regional settings are a US date system then I do not know anything you can do.

You could trick access by using this

Me.PRVRVW = Format(Me.ACTRVW, "dd\/mm\/yyyy")