|
| |
|
|
Colin Williams
Posts: 13
Joined: 9/6/2002
Status: offline
 |
Date Format Problem - 9/6/2002 11:46:32
Hi all, i' m new to all this so bear with me if i make any faux pas.
Using FP2000 I' ve created a form that posts to a database and subsequently displays the results in a table.
It all works OK except that the form insists on posting the date (sent as a standard " Additional field" ) in the mm/dd/yy format.
As i' m in the UK, and Windows is set to UK base, the standard format for dates is dd/mm/yy
This means that if i post the form today, the database records the date as 09/05/02, when it should be 05/09/02. This then displays as 9th March 2002 rather than 5th September 2002 in the results page.
I' ve looked in vain for a way to change the setting in FP, and it is correct if i post the results to a text file or e-mail rather than to a Database.
Any help appreciated before i tear my hair out in frustration.
Colin
|
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: Date Format Problem - 9/6/2002 13:21:03
Colin Williams,
Welcome to the forum
I think your looking in the wrong area. If it works everywhere but in the database itself, then you need to set a custom format for the Date/Time data type in the database itself.
If your using Access:
1): Go into the Access Help system.
2): Type in " Date" .
3): Search for " Format Property — Date/Time Data Type" .
This will show you how to change the date format in Access to match what you have in FP.
Good Luck.
LLL
_____________________________
| |
|
|
|
Colin Williams
Posts: 13
Joined: 9/6/2002
Status: offline
|
RE: Date Format Problem - 9/7/2002 3:34:45
Thanks Lune, but i' ve already tried that without success.
If I put 06/09/02 into Access direct into the table then it displays the date as 6th September 2002, which is what i want.
But the FP form passes the date and time as " 09/06/02 12:22:00" , which Access then formats as 9th June 2002, which is clearly not what i want.
I still think that the problem is with FP passing the date incorrectly, but i' m quite happy to be proved wrong!
On another tack, is there a way that I can create a field in my form that auto fills in the date based on the user' s computer date / time setting?
CW
| |
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: Date Format Problem - 9/7/2002 10:05:39
Colin,
No, I don' t think you can do that in FP 2000. The only other suggestion I would have is to create a date field and have it manually filled in. Or use JavaScript to assemble your own date prior to the form, then have it fill a date field in the form. Here is some info:
This page contains date scripts you might be able to use:
http://search.sprinks.about.com/library/dist/sprunder2.htm?PM=485_000575_spn&terms=perl&ref=000575
This page contains info on the date functions:
http://www.irt.org/xref/Date.htm
_____________________________
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: Date Format Problem - 9/8/2002 15:30:08
Hi Colin,
SOLVED
Go to your custom query in Frontpage, enter the following sql format:
SELECT Name, FORMAT (DateEntered,' dd/mm/yyyy' ) As MyDate FROM Dates
The SQL reads Select Field1, Fomat(DateField,' dd/mm/yyyy' ) From TableName.
It is working for me. Please check the URL below, which has a DRW from a Table with no query, to see the mm/dd/yyyy, and under it, you will see the same DRW but generated by the SQL above.
http://www.fmhs.uaeu.ac.ae/nml/DateFormat.asp
Regards
Hisham
| |
|
|
|
Colin Williams
Posts: 13
Joined: 9/6/2002
Status: offline
|
RE: RE: Date Format Problem - 9/11/2002 5:13:00
Thanks for your input Hisham,
Unfortunately, I still get the date displayed in US format.
This is because if i post today the form sends up the date in US format (mm/dd/yy) 09/10/02 (10th September)
The Access DB uses euro format (dd/mm/yy) but doesn' t transpose any of the numbers when it receives the data from the form. Therefore it believes the date to be 09/10/02 (9th October).
if i set the SQL to format the date as dd/mm/yy it displays as 09/10/02 (9th October) or if i set it to mm/dd/yy it displays 10/09/02 (October 9th) so i still don' t get the correct date.
Sorry to P on your bonfire
CW
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: Date Format Problem - 9/11/2002 5:41:36
Hi Colin,
What do you want to appear.
This is what I understood from your post. You want the date to display day/month/year.
How do you want it to appear?
Regards
Hisham
| |
|
|
|
Colin Williams
Posts: 13
Joined: 9/6/2002
Status: offline
|
RE: RE: Date Format Problem - 9/11/2002 6:10:53
I want it to appear as dd/mm/yy, which is what your SQL statement formats the date as.
The problem is that Access is receiving the date from my FP form as mm/dd/yy but assumes that it is in fact dd/mm/yy format.
So 09/10/02 sent from the Form is, to Access, 09/10/02 (9th October 2002)
Tomorrow my form will send 09/11/02 and Access will show it as 09/11/02 (9th November 2002.)
So when your SQL asks for the date to be pulled from the database and shown in dd/mm/yy format it shows exactly what Access has in its date column i.e. 09/10/02 and 09/11/02
The problem isn' t with formatting the results from the database, it is in the format of the date sent from the FP form.
CW
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: Date Format Problem - 9/11/2002 6:23:48
Hi Colin,
I' ve got it now. You mean the time and date field called TimeStamp right?
I created a form, entered information then formatted the TimeStamp that was entered automatically by the form as mm/dd/yy.
See new link:
-----------------
http://www.fmhs.uaeu.ac.ae/nml/DateTestResult.asp
Take this SQL:
SELECT Results.ID, Results.Name, Results.DateJoined, Results.Department, Results.Remote_computer_name, Results.User_name, format(Results.Timestamp,' dd/mm/yy hh:nn:ss' ) AS PcTime
FROM Results;
Hope it works
Hisham
< Message edited by hhammash -- 9/10/2002 6:52:45 AM >
| |
|
|
|
Colin Williams
Posts: 13
Joined: 9/6/2002
Status: offline
|
RE: RE: Date Format Problem - 9/11/2002 11:50:30
Hisham,
I' ve tried your latest suggestion on my server here with no success, so i then tried it on a remote hosted server where it worked perfectly.
Obviously there is some problem with our server or FP extensions set-up that is causing the form to post the date incorrectly.
Thanks for the help
CW
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: Date Format Problem - 9/11/2002 14:06:31
Hi Colin,
You are welcome.
Hisham
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
