UK & US date format problem

[Mik]

I apologise if this post seems a little basic, but I am a newbie and
have NO access knowledge.
I have downloaded the Accounts Ledger from the Microsoft Website.

It allows the user to review a report of transactions between a Start &
End date.
The Tables and forms i believe are UK format "dd/mm/yyyy",

However, when I click on "Review Reports" and enter the date as UK
"dd/mm/yyyy", it returns nothing.
If i enter the date as US "mm/dd/yyyy" it works fine.

I have changed all Date Fields to UK format, but still not working.

What could I be missing?

Thanks in advance for any assistance.

[Rick Brandt]

"Mik" <mh*****@safetysystemsuk.comwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...

>I apologise if this post seems a little basic, but I am a newbie and
have NO access knowledge.
I have downloaded the Accounts Ledger from the Microsoft Website.

It allows the user to review a report of transactions between a Start &
End date.
The Tables and forms i believe are UK format "dd/mm/yyyy",

However, when I click on "Review Reports" and enter the date as UK
"dd/mm/yyyy", it returns nothing.
If i enter the date as US "mm/dd/yyyy" it works fine.

I have changed all Date Fields to UK format, but still not working.

What could I be missing?

Date literals in Access queries must be either US format or a non-ambiguous
format. That ensures that the same physically entered literal produces the same
result regardless of regional settings.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Mik]

Rick Brandt wrote:

"Mik" <mh*****@safetysystemsuk.comwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...

I apologise if this post seems a little basic, but I am a newbie and
have NO access knowledge.
I have downloaded the Accounts Ledger from the Microsoft Website.

It allows the user to review a report of transactions between a Start &
End date.
The Tables and forms i believe are UK format "dd/mm/yyyy",

However, when I click on "Review Reports" and enter the date as UK
"dd/mm/yyyy", it returns nothing.
If i enter the date as US "mm/dd/yyyy" it works fine.

I have changed all Date Fields to UK format, but still not working.

What could I be missing?

Date literals in Access queries must be either US format or a non-ambiguous
format. That ensures that the same physically entered literal produces the same
result regardless of regional settings.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Thanks...
Do I assume there is no solution?

[Rick Brandt]

"Mik" <mh*****@safetysystemsuk.comwrote in message

Thanks...
Do I assume there is no solution?

If you create a "hard" date using the CDate() function then pass that to the
query or report filter it would allow you to enter a date using your regional
format since CDate() assumes regional setting format as long as that results in
a valid date. However that doesn't force the option to enter in regional
format, it only allows for it. If a user were to deviate and enter the date in
some other format then they could still get unexpected results.

Examples:

SELECT * FROM TableName
WHERE DateField = Forms!FormName!TextBoxName

The above will always assume the format of the entered date is US format unless
that would result in an invalid date (then it will try others).

SELECT * FROM TableName
WHERE DateField = CDate(Forms!FormName!TextBoxName)

This query will have CDate() take the text entered and make a DateTime value of
it and CDate will assume the entered format agrees with the Regional settings in
Windows unless that results in an invalid date (then it will try others).

The second option "sort of" solves your problem, but both have the issue of
"unless that would result in an invalid date". What is best is to format the
entry on the form in a non-ambiguous format like ISO (yyyy-mm-dd), which is
always interpreted exactly the same.

Dates are basically a royal PITA and that is why you see so many GUIs that use
calendar pop-ups and/or drop-lists for date entry. It is always an issue when
you let the user enter dates via the keyboard.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Mik]

Rick Brandt wrote:

"Mik" <mh*****@safetysystemsuk.comwrote in message

Thanks...
Do I assume there is no solution?

If you create a "hard" date using the CDate() function then pass that to the
query or report filter it would allow you to enter a date using your regional
format since CDate() assumes regional setting format as long as that results in
a valid date. However that doesn't force the option to enter in regional
format, it only allows for it. If a user were to deviate and enter the date in
some other format then they could still get unexpected results.

Examples:

SELECT * FROM TableName
WHERE DateField = Forms!FormName!TextBoxName

The above will always assume the format of the entered date is US format unless
that would result in an invalid date (then it will try others).

SELECT * FROM TableName
WHERE DateField = CDate(Forms!FormName!TextBoxName)

This query will have CDate() take the text entered and make a DateTime value of
it and CDate will assume the entered format agrees with the Regional settings in
Windows unless that results in an invalid date (then it will try others).

The second option "sort of" solves your problem, but both have the issue of
"unless that would result in an invalid date". What is best is to format the
entry on the form in a non-ambiguous format like ISO (yyyy-mm-dd), which is
always interpreted exactly the same.

Dates are basically a royal PITA and that is why you see so many GUIs that use
calendar pop-ups and/or drop-lists for date entry. It is always an issue when
you let the user enter dates via the keyboard.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

I appreciate this assistance.

I kind of understand the logic behind your theory,
but have No Idea where i should be entering this information.
Remember i am a complete beginner to access.

Should i put this code in the "control source" or is it VBA?
If VBA, where in the code does it enter?

Below is the code assigned to the Preview Report Button, should it go
here somewhere?

****************************
Private Sub Preview_Click()

If IsNull([Beginning Trans Date]) Or IsNull([Ending Trans Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Trans Date"
Else
If [Beginning Trans Date] [Ending Trans Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Trans Date"
Else
Me.Visible = False
End If
End If
End Sub

[Rick Brandt]

"Mik" <mh*****@safetysystemsuk.comwrote in message
news:11**********************@16g2000cwy.googlegro ups.com...

I appreciate this assistance.

I kind of understand the logic behind your theory,
but have No Idea where i should be entering this information.
Remember i am a complete beginner to access.

Should i put this code in the "control source" or is it VBA?
If VBA, where in the code does it enter?

Below is the code assigned to the Preview Report Button, should it go
here somewhere?

****************************
Private Sub Preview_Click()

If IsNull([Beginning Trans Date]) Or IsNull([Ending Trans Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Trans Date"
Else
If [Beginning Trans Date] [Ending Trans Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Trans Date"
Else
Me.Visible = False
End If
End If
End Sub

This can't be all the code in that event as there is nothing in that code that
opens a report.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Mik]

Rick Brandt wrote:

"Mik" <mh*****@safetysystemsuk.comwrote in message
news:11**********************@16g2000cwy.googlegro ups.com...

I appreciate this assistance.

I kind of understand the logic behind your theory,
but have No Idea where i should be entering this information.
Remember i am a complete beginner to access.

Should i put this code in the "control source" or is it VBA?
If VBA, where in the code does it enter?

Below is the code assigned to the Preview Report Button, should it go
here somewhere?

****************************
Private Sub Preview_Click()

If IsNull([Beginning Trans Date]) Or IsNull([Ending Trans Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Trans Date"
Else
If [Beginning Trans Date] [Ending Trans Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Trans Date"
Else
Me.Visible = False
End If
End If
End Sub

This can't be all the code in that event as there is nothing in that code that
opens a report.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Sorry Rick,

There is a little more code as below ....

**************************
Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
Me.Caption = Forms![Report Date Range].OpenArgs
End Sub

****************************
Private Sub Preview_Click()
If IsNull([Beginning Trans Date]) Or IsNull([Ending Trans Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Trans Date"
Else
If [Beginning Trans Date] [Ending Trans Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Trans Date"
Else
Me.Visible = False
End If
End If
End Sub

[Rick Brandt]

"Mik" <mh*****@safetysystemsuk.comwrote in message
news:11*********************@j72g2000cwa.googlegro ups.com...

Sorry Rick,

There is a little more code as below ....

[code snipped]

What you posted still contains nothing that would open a report or cause a
report to be filtered.

WHAT opens your report? Also, does the report have code in it's Open or Load
events? If so post that.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Mik]

Rick Brandt wrote:

"Mik" <mh*****@safetysystemsuk.comwrote in message
news:11*********************@j72g2000cwa.googlegro ups.com...

Sorry Rick,

There is a little more code as below ....

[code snipped]

What you posted still contains nothing that would open a report or cause a
report to be filtered.

WHAT opens your report? Also, does the report have code in it's Open or Load
events? If so post that.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

The Report is opened via a button on a Form.
I can't see any other applicable code related to the View Report
function.

It may be easier to identify from the "Accounts Ledger Database" on the
Microsoft Website.

I have not changed any of the code.

Thanks again.

[Rick Brandt]

"Mik" <mh*****@safetysystemsuk.comwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...

>
Rick Brandt wrote:

>"Mik" <mh*****@safetysystemsuk.comwrote in message
news:11*********************@j72g2000cwa.googlegr oups.com...

Sorry Rick,

There is a little more code as below ....

[code snipped]

What you posted still contains nothing that would open a report or cause a
report to be filtered.

WHAT opens your report? Also, does the report have code in it's Open or Load
events? If so post that.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

The Report is opened via a button on a Form.
I can't see any other applicable code related to the View Report
function.

Okay, what is the code behind THAT button?
(it is not what you posted)

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Mik]

Rick Brandt wrote:

"Mik" <mh*****@safetysystemsuk.comwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...

Rick Brandt wrote:

"Mik" <mh*****@safetysystemsuk.comwrote in message
news:11*********************@j72g2000cwa.googlegro ups.com...
Sorry Rick,

There is a little more code as below ....

[code snipped]

What you posted still contains nothing that would open a report or cause a
report to be filtered.

WHAT opens your report? Also, does the report have code in it's Open or Load
events? If so post that.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

The Report is opened via a button on a Form.
I can't see any other applicable code related to the View Report
function.

Okay, what is the code behind THAT button?
(it is not what you posted)

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

The Button has a "On Click" procedure which is:-

Transactions Macros.ViewReports : On Click

When I open "Transaction Macro.ViewReports" in design mode, it simply
has an action stating Open Form, while the Action Arguments at the
bottom of the screen show the Form Name to open "View Reports".

this opens a form and asks what report you wish to view,
and gives the user the option to view transactions between
a start and end date.
It is this particular date field that is US format.

[Rick Brandt]

"Mik" <mh*****@safetysystemsuk.comwrote in message
news:11**********************@f1g2000cwa.googlegro ups.com...

The Button has a "On Click" procedure which is:-

Transactions Macros.ViewReports : On Click

When I open "Transaction Macro.ViewReports" in design mode, it simply
has an action stating Open Form, while the Action Arguments at the
bottom of the screen show the Form Name to open "View Reports".

this opens a form and asks what report you wish to view,
and gives the user the option to view transactions between
a start and end date.
It is this particular date field that is US format.

But...we still have not identified any piece of code or macro that is actually
opening the report. The form named "View Reports" must have a button (or
something) that you click on after you have chosen a report and entered the
dates. THAT something that you click on must have a code or macro associated
with it that opens the report in question. Can you find what that is and post
it?

Are you sure that the macro you looked at doesn't have additional steps it runs
after the one that opens the form?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Mik]

Rick Brandt wrote:

"Mik" <mh*****@safetysystemsuk.comwrote in message
news:11**********************@f1g2000cwa.googlegro ups.com...

The Button has a "On Click" procedure which is:-

Transactions Macros.ViewReports : On Click

When I open "Transaction Macro.ViewReports" in design mode, it simply
has an action stating Open Form, while the Action Arguments at the
bottom of the screen show the Form Name to open "View Reports".

this opens a form and asks what report you wish to view,
and gives the user the option to view transactions between
a start and end date.
It is this particular date field that is US format.

But...we still have not identified any piece of code or macro that is actually
opening the report. The form named "View Reports" must have a button (or
something) that you click on after you have chosen a report and entered the
dates. THAT something that you click on must have a code or macro associated
with it that opens the report in question. Can you find what that is and post
it?

Are you sure that the macro you looked at doesn't have additional steps it runs
after the one that opens the form?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Rick,
Please forgive my lack of knowledge,

the actual Open Report Button has the following link:-

cmdOpenReport : On Click

and the action arguments show:-

=[Forms]![View Reports]![lstReports]

with the view as "Print Preview"

and the "Where Condition" shows:-

=[Forms]![View Reports]![txtReportFilter]
Don't spend much more time on this issue.
I feel sorry for not informing you thoroughly.

It may be easier for you to view the TEMPLATE "Accounts Ledger
Database" on Microsofts Website if you are to see this through to the
end.

[Terry Kreft]

Mik,
This is a typical Mickey Mouse Microsoft sample.

To fix this:-
1) Go to the database window and choose Forms
2) Open the "View Reports" form in design mode
3) Select the txtReportFilter texbox
4) Change the text in the textbox to the following (watch out for line
wraps)
=Choose([grpFilterOptions],"[TransactionDate] Between #" & Forms![View
Reports].[Beginning Trans Date] & "# AND #" & Forms![View Reports].[Ending
Trans Date] & "#","")
5) Close and save the form.

You can now enter dates in dd/mm/yyyy format and it will work.

--

Terry Kreft
"Mik" <mh*****@safetysystemsuk.comwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...

I apologise if this post seems a little basic, but I am a newbie and
have NO access knowledge.
I have downloaded the Accounts Ledger from the Microsoft Website.

It allows the user to review a report of transactions between a Start &
End date.
The Tables and forms i believe are UK format "dd/mm/yyyy",

However, when I click on "Review Reports" and enter the date as UK
"dd/mm/yyyy", it returns nothing.
If i enter the date as US "mm/dd/yyyy" it works fine.

I have changed all Date Fields to UK format, but still not working.

What could I be missing?

Thanks in advance for any assistance.

[Terry Kreft]

Rick,
I downloaded the example file to have a look at and it's a typical cruddy MS
example, actually no it's not typical, it's probably the worst one I've ever
looked at.
--

Terry Kreft
"Rick Brandt" <ri*********@hotmail.comwrote in message
news:GB******************@newssvr29.news.prodigy.n et...

"Mik" <mh*****@safetysystemsuk.comwrote in message
news:11**********************@f1g2000cwa.googlegro ups.com...

The Button has a "On Click" procedure which is:-

Transactions Macros.ViewReports : On Click

When I open "Transaction Macro.ViewReports" in design mode, it simply
has an action stating Open Form, while the Action Arguments at the
bottom of the screen show the Form Name to open "View Reports".

this opens a form and asks what report you wish to view,
and gives the user the option to view transactions between
a start and end date.
It is this particular date field that is US format.

But...we still have not identified any piece of code or macro that is

actually

opening the report. The form named "View Reports" must have a button (or
something) that you click on after you have chosen a report and entered

the

dates. THAT something that you click on must have a code or macro

associated

with it that opens the report in question. Can you find what that is and

post

it?

Are you sure that the macro you looked at doesn't have additional steps it

runs

after the one that opens the form?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Rick Brandt]

"Terry Kreft" <te*********@mps.co.ukwrote in message
news:t4*********************@eclipse.net.uk...

Rick,
I downloaded the example file to have a look at and it's a typical cruddy MS
example, actually no it's not typical, it's probably the worst one I've ever
looked at.

I was coming to that conclusion even without seeing it :-)

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Terry Kreft]

I'm guesing the hint was the phrase:-
"I have downloaded the Accounts Ledger from the Microsoft Website."
--
Terry Kreft
"Rick Brandt" <ri*********@hotmail.comwrote in message
news:60*******************@newssvr14.news.prodigy. net...

"Terry Kreft" <te*********@mps.co.ukwrote in message
news:t4*********************@eclipse.net.uk...

>Rick,
I downloaded the example file to have a look at and it's a typical cruddy
MS
example, actually no it's not typical, it's probably the worst one I've
ever
looked at.

I was coming to that conclusion even without seeing it :-)

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com