dd/mm/yy format on textbox

[Mark A. Sam]

Hello,

A Canadian client uses dd/mm/yy/ format. I set up a report dialog box as
Shortdate for the date textboxes but the report wants mm/dd/yy format or it
will display all of the results on the report. I tried setting the format
of the textboxes to dd/mm/yy but that produced errors on the report. How
can I get the report to accept the dd/mm/yy format. I am using the standard
OpenReport method with a whereclause:

DoCmd.OpenReport lstReports, acViewPreview, , "[TransactionDate] >= #" &
[Date1] & "# And [TransactionDate] <= #" & [Date2] & "#", acDialog

Thank you for any help and God Bless,

Mark A. Sam

 

[Allen Browne]

Mark, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

The article explains how you can just set the Format property of the unbound
text box to "Short date" or similar, but you must explcitly format the
criteria string to use the US format as JET expects.

(This advice comes from 16 years of using Access in a dmy country.)

 

[Maverick]

Is the data being stored as a date or text? If it is stored as a date, then
the format shouldn't matter. If it is text, then you could create an unbound
textbox and parse the date into 3 separate values using and reorder them by
putting the following into the Control Source of the unboud textbox:

=Mid([TextDate],4,2) & "/" & Left([TextDate],2) & "/" & Right([TextDate],4)

 

[Mark A. Sam]

Maverick,

Thanks for the response. These are unbound textboxes looking for user
input. I can't put a forumula into the controlsource becuase it won't
allow entries.

God Bless,

Mark

Is the data being stored as a date or text? If it is stored as a date,
then
the format shouldn't matter. If it is text, then you could create an
unbound
textbox and parse the date into 3 separate values using and reorder them
by
putting the following into the Control Source of the unboud textbox:

=Mid([TextDate],4,2) & "/" & Left([TextDate],2) & "/" &
Right([TextDate],4)

Hello,

A Canadian client uses dd/mm/yy/ format. I set up a report dialog box as
Shortdate for the date textboxes but the report wants mm/dd/yy format or
it
will display all of the results on the report. I tried setting the
format
of the textboxes to dd/mm/yy but that produced errors on the report. How
can I get the report to accept the dd/mm/yy format. I am using the
standard
OpenReport method with a whereclause:

DoCmd.OpenReport lstReports, acViewPreview, , "[TransactionDate] >= #" &
[Date1] & "# And [TransactionDate] <= #" & [Date2] & "#", acDialog

Thank you for any help and God Bless,

Mark A. Sam

 

[Mark A. Sam]

Mark, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

The article explains how you can just set the Format property of the
unbound text box to "Short date" or similar, but you must explcitly format
the criteria string to use the US format as JET expects.

That is the problem. The user is use to entering dates using dd/mm/yy. She
will do the US format, but I am trying to make it easier on her. I'll try
Mavericks method, but by coding it in to the where clause.

God Bless,

Mark

(This advice comes from 16 years of using Access in a dmy country.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

A Canadian client uses dd/mm/yy/ format. I set up a report dialog box as
Shortdate for the date textboxes but the report wants mm/dd/yy format or
it will display all of the results on the report. I tried setting the
format of the textboxes to dd/mm/yy but that produced errors on the
report. How can I get the report to accept the dd/mm/yy format. I am
using the standard OpenReport method with a whereclause:

DoCmd.OpenReport lstReports, acViewPreview, , "[TransactionDate] >= #" &
[Date1] & "# And [TransactionDate] <= #" & [Date2] & "#", acDialog

Thank you for any help and God Bless,

Mark A. Sam

 

[Mark A. Sam]

This is how I solved it with Mavericks expression:

I removed the format on the textboxes and set an input mask to force a date
format.

99/99/00;0;*

Then I revised my code as follows:

Dim strDate1 As Variant
Dim strDate2 As Variant
strDate1 = Mid([Date1], 4, 2) & "/" & Left([Date1], 2) & "/" &
Right([Date1], 2)
strDate2 = Mid([Date2], 4, 2) & "/" & Left([Date2], 2) & "/" &
Right([Date2], 2)
DoCmd.OpenReport lstReports, acViewPreview, , "[TransactionDate] >= #" &
strDate1 & "# And [TransactionDate] <= #" & strDate2 & "#", acDialog

Mark, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

The article explains how you can just set the Format property of the
unbound text box to "Short date" or similar, but you must explcitly format
the criteria string to use the US format as JET expects.

(This advice comes from 16 years of using Access in a dmy country.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

A Canadian client uses dd/mm/yy/ format. I set up a report dialog box as
Shortdate for the date textboxes but the report wants mm/dd/yy format or
it will display all of the results on the report. I tried setting the
format of the textboxes to dd/mm/yy but that produced errors on the
report. How can I get the report to accept the dd/mm/yy format. I am
using the standard OpenReport method with a whereclause:

DoCmd.OpenReport lstReports, acViewPreview, , "[TransactionDate] >= #" &
[Date1] & "# And [TransactionDate] <= #" & [Date2] & "#", acDialog

Thank you for any help and God Bless,

Mark A. Sam

 

[Mark A. Sam]

Thanks for your suggestion. I used your expression in my code:

I removed the format on the textboxes and set an input mask to force a date
format.

99/99/00;0;*

Then I revised my code as follows:

Dim strDate1 As Variant
Dim strDate2 As Variant
strDate1 = Mid([Date1], 4, 2) & "/" & Left([Date1], 2) & "/" &
Right([Date1], 2)
strDate2 = Mid([Date2], 4, 2) & "/" & Left([Date2], 2) & "/" &
Right([Date2], 2)
DoCmd.OpenReport lstReports, acViewPreview, , "[TransactionDate] >= #" &
strDate1 & "# And [TransactionDate] <= #" & strDate2 & "#", acDialog

 

[John W. Vinson]

This is how I solved it with Mavericks expression:

I removed the format on the textboxes and set an input mask to force a date
format.

99/99/00;0;*

Then I revised my code as follows:

Dim strDate1 As Variant
Dim strDate2 As Variant
strDate1 = Mid([Date1], 4, 2) & "/" & Left([Date1], 2) & "/" &
Right([Date1], 2)
strDate2 = Mid([Date2], 4, 2) & "/" & Left([Date2], 2) & "/" &
Right([Date2], 2)
DoCmd.OpenReport lstReports, acViewPreview, , "[TransactionDate] >= #" &
strDate1 & "# And [TransactionDate] <= #" & strDate2 & "#", acDialog

This shouldn't be necessary! Try instead a criterion of

[TransactionDate] >= Format(CDate([Date1]), "\#mm/dd/yyyy\#") AND
[TransactionDate] <= Format(CDate([Date2]), "\#mm/dd/yyyy\#")

The user's entry into the unbound textbox will be translated from their
preferred regional date format (which CDate honors) into Access' American
format.

 

[Mark A. Sam]

This shouldn't be necessary! Try instead a criterion of

[TransactionDate] >= Format(CDate([Date1]), "\#mm/dd/yyyy\#") AND
[TransactionDate] <= Format(CDate([Date2]), "\#mm/dd/yyyy\#")

The user's entry into the unbound textbox will be translated from their
preferred regional date format (which CDate honors) into Access' American
format.

Thanks but too late. I already sent the update, but will keep that in mind
if I ever run into this again.

 

[Allen Browne]

Mark, I'm not keen on that solution, as you are not interpreting the value
the user typed according to their regional settings.

It would be better to follow the advice John Vinson gave (essentially what I
recommended.) Your Access program will then work in the UK (dmy) or in Korea
(ymd) or the US (mdy), regardless of what the user's local date settings
are.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

This is how I solved it with Mavericks expression:

I removed the format on the textboxes and set an input mask to force a
date format.

99/99/00;0;*

Then I revised my code as follows:

Dim strDate1 As Variant
Dim strDate2 As Variant
strDate1 = Mid([Date1], 4, 2) & "/" & Left([Date1], 2) & "/" &
Right([Date1], 2)
strDate2 = Mid([Date2], 4, 2) & "/" & Left([Date2], 2) & "/" &
Right([Date2], 2)
DoCmd.OpenReport lstReports, acViewPreview, , "[TransactionDate] >= #" &
strDate1 & "# And [TransactionDate] <= #" & strDate2 & "#", acDialog

Mark, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

The article explains how you can just set the Format property of the
unbound text box to "Short date" or similar, but you must explcitly
format the criteria string to use the US format as JET expects.

(This advice comes from 16 years of using Access in a dmy country.)

Hello,

A Canadian client uses dd/mm/yy/ format. I set up a report dialog box
as Shortdate for the date textboxes but the report wants mm/dd/yy format
or it will display all of the results on the report. I tried setting
the format of the textboxes to dd/mm/yy but that produced errors on the
report. How can I get the report to accept the dd/mm/yy format. I am
using the standard OpenReport method with a whereclause:

DoCmd.OpenReport lstReports, acViewPreview, , "[TransactionDate] >= #" &
[Date1] & "# And [TransactionDate] <= #" & [Date2] & "#", acDialog

 

[Mark A. Sam]

Hello Allen,

I understand, and agree, and in fact it is my intention to change it soon.
I apologise that I didn't go down to section 2 of your explantion. I see
now that it is the solution. I thought by the headings that only section 1
applied to my problem and didn't read past it.

Thanks and God Bless,

Mark

Mark, I'm not keen on that solution, as you are not interpreting the value
the user typed according to their regional settings.

It would be better to follow the advice John Vinson gave (essentially what
I recommended.) Your Access program will then work in the UK (dmy) or in
Korea (ymd) or the US (mdy), regardless of what the user's local date
settings are.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

This is how I solved it with Mavericks expression:

I removed the format on the textboxes and set an input mask to force a
date format.

99/99/00;0;*

Then I revised my code as follows:

Dim strDate1 As Variant
Dim strDate2 As Variant
strDate1 = Mid([Date1], 4, 2) & "/" & Left([Date1], 2) & "/" &
Right([Date1], 2)
strDate2 = Mid([Date2], 4, 2) & "/" & Left([Date2], 2) & "/" &
Right([Date2], 2)
DoCmd.OpenReport lstReports, acViewPreview, , "[TransactionDate] >= #" &
strDate1 & "# And [TransactionDate] <= #" & strDate2 & "#", acDialog

Mark, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

The article explains how you can just set the Format property of the
unbound text box to "Short date" or similar, but you must explcitly
format the criteria string to use the US format as JET expects.

(This advice comes from 16 years of using Access in a dmy country.)

Hello,

A Canadian client uses dd/mm/yy/ format. I set up a report dialog box
as Shortdate for the date textboxes but the report wants mm/dd/yy
format or it will display all of the results on the report. I tried
setting the format of the textboxes to dd/mm/yy but that produced
errors on the report. How can I get the report to accept the dd/mm/yy
format. I am using the standard OpenReport method with a whereclause:

DoCmd.OpenReport lstReports, acViewPreview, , "[TransactionDate] >= #"
& [Date1] & "# And [TransactionDate] <= #" & [Date2] & "#", acDialog