Short form Date format problem

[Imranis]

I am using following code to open a report for the people who have already been entered with the help of date of birth. But the format of date dd/mm/yyyy is automatically converted into mm/dd/yyyy, even my regional setting of the computer is not that of US. will appreciate if the code is amended to show the short format of date as dd/mm/yyyy. thanks.

Private Sub dob_BeforeUpdate(Cancel As Integer)
If DCount("*", "maintable", "[dob]=#" & Me.dob & "#") > 0 Then
MsgBox "There are other Employees with that dob.  Here is a list.", vbOKOnly, "Multiple Employees Same DOB"
DoCmd.OpenReport "maintable", acViewPreview, , "[dob]=#" & Me.dob & "#"
End If
End Sub

 

[omnibaer]

Easy, and always a good thing to validate. I learned over the years that you can't count on regional settings, so always validate your date formatting.

Private Sub dob_BeforeUpdate(Cancel As Integer)
On Error Resume Next
If DCount("*", "maintable", "[dob]=#" & Me.dob & "#") > 0 Then
MsgBox "There are other Employees with that dob. Here is a list.", vbOKOnly, "Multiple Employees Same DOB"
DoCmd.OpenReport "maintable", acViewPreview, , "[dob]=#" & Format(Me.dob,"dd/mm/yyyy") & "#"
End If
End Sub

 

[smig]

When you format it into dd/mm/yyyy you convert the date to text.

You can use "short date" to format it as short date, but the way it looks will be system dependent

 

[spikepl]

No it does not "convert" - it interprets it as US-formatted date unless that is impossible. In SQL you always need to use the US mm/dd/yyyy format, because that is what SQL is born with/assumes. US users are fortunate in that in their environment implicit cponverison to string produces US-formatted dates. That is not so for the remainder of us.

Read Allen Browne's comments: http://allenbrowne.com/ser-36.html

 

[omnibaer]

Good point @spikepl, I guess the format statement should be Format(date, "mm/dd/yyyy"). This will give you a string in the format that SQL expects, regardless of your system settings. Cheers.

 

[Galaxiom]

I prefer to include the date delimiters in the Format string because it is tidier.

Format(somedate,"\#mm\/dd\/yyyy\#")

The backslashes escape the subsequent character indicating it is to be interpretted literally. Otherwise a forward slash can be interpreted as a placeholder for the local date separator.

 

[Imranis]

I prefer to include the date delimiters in the Format string because it is tidier.

Format(somedate,"\#mm\/dd\/yyyy\#")

The backslashes escape the subsequent character indicating it is to be interpretted literally. Otherwise a forward slash can be interpreted as a placeholder for the local date separator.

Thanks for reply, will appreciate if you kind amend the the whole code with your above format.

 

[Imranis]

Easy, and always a good thing to validate. I learned over the years that you can't count on regional settings, so always validate your date formatting.

Private Sub dob_BeforeUpdate(Cancel As Integer)
On Error Resume Next
If DCount("*", "maintable", "[dob]=#" & Me.dob & "#") > 0 Then
MsgBox "There are other Employees with that dob. Here is a list.", vbOKOnly, "Multiple Employees Same DOB"
DoCmd.OpenReport "maintable", acViewPreview, , "[dob]=#" & Format(Me.dob,"dd/mm/yyyy") & "#"
End If
End Sub

Thanks for reply, but even with this code, the problem persists.

 

[namliam]

try

Private Sub dob_BeforeUpdate(Cancel As Integer)
On Error Resume Next
If DCount("*", "maintable", "[dob]=#" & Format(Me.dob,"mm/dd/yyyy") & "#") > 0 Then
MsgBox "There are other Employees with that dob. Here is a list.", vbOKOnly, "Multiple Employees Same DOB"
DoCmd.OpenReport "maintable", acViewPreview, , "[dob]=#" & Format(Me.dob,"mm/dd/yyyy") & "#"
End If
End Sub

If that doesnt work, is dob a text field or date field? (in the field properties, not in the value that is entered)

 

[Imranis]

try

Private Sub dob_BeforeUpdate(Cancel As Integer)
On Error Resume Next
If DCount("*", "maintable", "[dob]=#" & Format(Me.dob,"mm/dd/yyyy") & "#") > 0 Then
MsgBox "There are other Employees with that dob. Here is a list.", vbOKOnly, "Multiple Employees Same DOB"
DoCmd.OpenReport "maintable", acViewPreview, , "[dob]=#" & Format(Me.dob,"mm/dd/yyyy") & "#"
End If
End Sub

If that doesnt work, is dob a text field or date field? (in the field properties, not in the value that is entered)

Fantastic and it worked perfectly. Many thanks.