US to UK Dates displayed in report 1

[Stevehewitt]

Hi Guys,

I have a third party Access DB which currently stored dates in the US format of MM/DD/YYYY. This is fine to be stored like this, (the web interface - GenericDB - displays the data and entry as UK, but stores as US) however I need to create some reports which will be based on the date crieria.

Instead of the guys entering in the US format for the criteria, I would like them to continue to use the UK format - and also for the UK format to be displayed on the reports.

Anyone give me some advice regarding this?

Cheers,



Steve.

"They have the internet on computers now!" - Homer Simpson

 

[RoyVidar]

As long as it is stored as Date/Time, and not text, a date is acutally stored as a number. Displaying it as a date, is just a formatting issue, which access retrieves from the regional settings, or explicitly assigned formats.

This means:
[ul][li]if you use form controls, formatted as date, and use as critaria in the query grid, it will be OK without any tweaking[/li]
[li]if you use some kind of dynamic SQL, as for instance when using the wherecondition arguement of the openreport method, you will need to format the date(s) to an unambiguous format - because when the Jet engine receives a SQL string, it doesn't know anything about datatypes, but the hash (#) tell's the Jet engine to interpret the criterion as Date/Time, and then the "order of" month/day will need to be in unambiguous format (US;-) or ISO), to make Jet understand wich date you rally mean[/li]
[li]then there's also the [Enter date:] thingie you can use in queries, I don't know how they work, cause I don't use them - they allow no datavalidation, and I prefer that the users receive a message, or are in other ways informed that their selection isn't valid in stead of giving a blank report[/li][/ul]
For the second method, using some standard formatting should do:

[tt]docmd.openreport "myrpt",acviewpreview,,"mydate = #" & format$(me!txtDate.value, "yyyy-mm-dd") & "#"[/tt]

Roy-Vidar

 

[Stevehewitt]

Hi RoyVidar,

Thanks for your reply - worked a treat. Thank you!

Cheers again,





Steve.

"They have the internet on computers now!" - Homer Simpson