database interface wizard - Date format changes

[Mick]

I have a working DIW, one of the fileds in the table is a date field, the
field is formatted to DD/MM/YYYY, no problem there. If I edit a record, and
not even change any of the data it makes no difference, then save the record,
that date field changes format to MM/DD/YYYY. If I open edit for the same
record, and again not change any of the data, and save it, it reverts to the
DD/MM/YYYY format. It does not seem to matter if data is changed or not, the
format for this date field just toggles back and forward at each change...
help please

 

[Jon Spivey]

Hi Mick,

Access behaves differently on the web to the desktop, to wit it ignores date
formats. You'd need to format the date in your sql

SELECT FORMAT(YourDateField,'dd/mm/yyyy') AS UkDate

will give dd/mm/yy - 06/09/2006

 

[Mick]

Hi Jon, thanks for the swift reply. Im afraid I am still learning here. Where
do I insert this code, what page please, sorry....Mick

 

[Jon Spivey]

Hi Mick,

Sorry, i didn't know how far you'd got. This needs to be part of a custom
query - in Step 2 of the Insert - Database wizard hit Custom Query. Then
type
SELECT Field1, Field2, Field3, FORMAT(YourDateField,'dd/mm/yyyy') AS UkDate
FROM YourTable
complete the wizard

 

[Mick]

Hi Jon, sorry to be a newbie, Im still in trouble.
Using the DI Wizard it produces a file called edit.asp is this the file I
should be editing? Looking at this file in normal mode the field look like
<%=FP_FieldHTML(fp_rs,"Datebookedontoclientdb")%>. If I try to make a new
file and insert the suggested code in step 2 of the wizard the fileds look
like <<Datebookedontoclientdb>>
So I tried using the existing edit.asp and inserted the code as follows
inthe purple area of the asp (following another thread in the forum) thus
SELECT * FROM clients WHERE ID = ::ID::"
FORMAT(Datebookedontoclientdb,'dd/mm/yyyy') AS UkDate
Ive tried most of the variations on this but to no avail. Can you please
help a little more..thanks Mick

 

[Mick]

Hi Jon, sorry to be a newbie, Im still in trouble.
Using the DI Wizard it produces a file called edit.asp is this the file I
should be editing? Looking at this file in normal mode the field look like
<%=FP_FieldHTML(fp_rs,"Datebookedontoclientdb")%>. If I try to make a new
file and insert the suggested code in step 2 of the wizard the fileds look
like <<Datebookedontoclientdb>>
So I tried using the existing edit.asp and inserted the code as follows
inthe purple area of the asp (following another thread in the forum) thus
SELECT * FROM clients WHERE ID = ::ID::"
FORMAT(Datebookedontoclientdb,'dd/mm/yyyy') AS UkDate
Ive tried most of the variations on this but to no avail. Can you please
help a little more..th

 

[Mick]

Jon you kindly attempted to help me, I write to confirm that Microsoft have
solved the problem, a glitch in the software. I am not ofter able to
contribute to the news group, noramlly the other way round, so here we go (I
dont pretend to understand it, but it works) Thanks to Microsoft Support
(Pradita, Bombay India Microsoft Support)

Solution Starts---------------------
FrontPage saves the column type in a string variable called fp_sColTypes. To
change the column type in general from adDBTimestamp to adDate the file
"_fpclass/fpdbrgn1.inc" has to be edited.

In the file fpdbrgn1.in search for the following line of code (should be
line 247):

fp_colType = ""

Insert a new empty line after the above line of code and enter the
following code line:
fp_sColTypes = Replace(fp_sColTypes, "=135&", "=7&")

3. 135 is the value of adDBTimestamp, 7 is the value of adDate.

Kind regards and thanks Jon