VBA code - clash of the date formats;-) (1 Viewer)

[lilminx]

Hi there,

The problem I have is that somewhere in my database its is getting confused between UK and American date formats. I use a lot of autofill dialog data entry forms using - Date(). Basically I have one table that holds all the information on a dialog form that pops up by clicking on a record in a subform. I’ve checked the date formats in the table and the query that the subform form run to and they are all set to Medium Date in the format or properties. I use the code below to display the dialog data entry form which runs direct from the table:

Code for display dialog form:
Private Sub CustCode_Click()

If Not Me.NewRecord Then
DoCmd.OpenForm "frm_dialog_IndividualContact", _
, _
, _
"[CustCode]='" & Me.[CustCode] & _
"' And [DelSeqCode]='" & Me.[DelSeqCode] & _
"' And [ContactDate]=#" & Me.[ContactDate] & _
"# And [ContactTime]=#" & Me.[ContactTime] & "#", _
, _
acDialog
Else
DoCmd.OpenForm "frm_dialog_IndividualContact", _
, _
, _
, _
acFormAdd, _
acDialog
End If

End Sub


Basically when a contact is stored on a date prior to the 13th of the month (with the exception of the same value for both – 01/01/2009, 02/02/2009….) if I click on the record it brings up a completely blank dialog form (not even showing fields). All other dates work fine and bring up the correct record.

Please help, as I have tried everything I can think of on this and I can’t get it to work.

I am working in Access 2003. Let me know if you require anymore information. Thank you in advance for your help.

Thanks Jodie

 

[namliam]

Jodie,

The only thing I can think of is that your Me.[ContactDate] is not a date field but a text field. This is then used for data entry in the euro format of dd-mm-yyyy.
Access requires that to be MM-DD-YYYY as access itself works with US dates.

You can confirm this by trying to enter the US format into your ContactDate, see if that works or not...

Regards & Good luck !

 

[lilminx]

Ok so now I've tried converting all the dates into format mm/dd/yy and it still isn't working.

What else could I try?

Thanks Jodie

 

[WIS]

Hi there,

The problem I have is that somewhere in my database its is getting confused between UK and American date formats. I use a lot of autofill dialog data entry forms using - Date(). Basically I have one table that holds all the information on a dialog form that pops up by clicking on a record in a subform. I’ve checked the date formats in the table and the query that the subform form run to and they are all set to Medium Date in the format or properties. I use the code below to display the dialog data entry form which runs direct from the table:

Code for display dialog form:
Private Sub CustCode_Click()

If Not Me.NewRecord Then
DoCmd.OpenForm "frm_dialog_IndividualContact", _
, _
, _
"[CustCode]='" & Me.[CustCode] & _
"' And [DelSeqCode]='" & Me.[DelSeqCode] & _
"' And [ContactDate]=#" & Me.[ContactDate] & _
"# And [ContactTime]=#" & Me.[ContactTime] & "#", _
, _
acDialog
Else
DoCmd.OpenForm "frm_dialog_IndividualContact", _
, _
, _
, _
acFormAdd, _
acDialog
End If

End Sub


Basically when a contact is stored on a date prior to the 13th of the month (with the exception of the same value for both – 01/01/2009, 02/02/2009….) if I click on the record it brings up a completely blank dialog form (not even showing fields). All other dates work fine and bring up the correct record.

Please help, as I have tried everything I can think of on this and I can’t get it to work.

I am working in Access 2003. Let me know if you require anymore information. Thank you in advance for your help.

Thanks Jodie

In place of Me.[ContactDate] try Format(Me.[ContactDate],"dd-mmm-yyyy")

 

[gemma-the-husky]

its sql statements that confuse it.

assuming you have uk regional date format,

then whereever you use dates in sql statements, you should format it explicitly as

format(mydate,"long date")

or

format(mydate,"dd/mm/yyyy")

----------
the problem is that if you dont access [always] treats it as a US date, if its a legal US date, and only a UK date otherwise

so

1/4/09 becomes Jan 4th 09, not 1st April 09

but

13/4/09 can only be 13th april 09, as you have obseved

 

[WIS]

format(mydate,"dd/mm/yyyy")

When I have used the above, unbelievably it still shows dates with days, months below 13 as US. That's why I use "dd/mmm/yyyy" or "dd-mmm-yyyy".

 

[gemma-the-husky]

yes you are right

dd/mm/yy is still numbers. so will fall foul of the problem

dd/mmm/yy or long date , both format the month as string, so there is no ambiguity

 

[WIS]

yes you are right

dd/mm/yy is still numbers. so will fall foul of the problem

dd/mmm/yy or long date , both format the month as string, so there is no ambiguity

Thanks for the explanation - knew it worked but not why it worked.

 

[lilminx]

That's wonderful it all works perfectly now, thank you very much for your help. I knew it was to do with the American date format but not how to fix it.

Thanks Jodie