Numeric Formatting

C

Cayvman

Okay, let me try this again with a pared down sql script. I was able to do
the formatting on one of the my queries, however am still stuck on the first
query. So, here is the query that I still need help in sending the
information to Front Page to format the results of the Hours field.
The fields in the access database are ID,Employee Name,Project,Hours,WED
The Table name is WPLR.

</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM WPLR WHERE (Project LIKE '::project::%' AND WED =
'::WED::') ORDER BY ""Employee Name"" ASC"
fp_sDefault="Project=&WED="
fp_sNoRecords="<tr><td colspan=4 align=""LEFT"" width=""100%"">No records
returned.</td></tr>"
fp_sDataConn="ProjLab"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="WED"
fp_sMenuValue="WED"
fp_sColTypes="&ID=3&Employee Name=202&Hours=5&Project=202&Fiscal
Year=3&Period Number=3&Sub Period Num=3&WED=135&"
fp_iDisplayCols=4
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

The script for a second query that sums the results of the first query
follows. Though this script is more complex, I was able to get the format
syntax right. Which came as a shock to me.

SELECT *,
(SELECT sum(Hours) FROM WPLR WHERE (Project LIKE
'::project::%'AND WED = '::WED::')) as [Total Hours]
FROM WPLR WHERE (Project LIKE '::project::%'AND WED = '::WED::') ORDER BY
Hours ASC

Any suggestions will be greatly appreciated. I have over 16 hours into this
seemingly simple web-based reporting system.
Joe
 
D

David Berry

I'm not sure I understand. Do you want to format the time when you do the
insert into the database or do you mean when it comes out of the database?
 
C

Cayvman

David Berry said:
I'm not sure I understand. Do you want to format the time when you do the
insert into the database or do you mean when it comes out of the database?



Cayvman said:
Okay, let me try this again with a pared down sql script. I was able to do
the formatting on one of the my queries, however am still stuck on the
first
query. So, here is the query that I still need help in sending the
information to Front Page to format the results of the Hours field.
The fields in the access database are ID,Employee Name,Project,Hours,WED
The Table name is WPLR.

</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM WPLR WHERE (Project LIKE '::project::%' AND WED =
'::WED::') ORDER BY ""Employee Name"" ASC"
fp_sDefault="Project=&WED="
fp_sNoRecords="<tr><td colspan=4 align=""LEFT"" width=""100%"">No records
returned.</td></tr>"
fp_sDataConn="ProjLab"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="WED"
fp_sMenuValue="WED"
fp_sColTypes="&ID=3&Employee Name=202&Hours=5&Project=202&Fiscal
Year=3&Period Number=3&Sub Period Num=3&WED=135&"
fp_iDisplayCols=4
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

The script for a second query that sums the results of the first query
follows. Though this script is more complex, I was able to get the format
syntax right. Which came as a shock to me.

SELECT *,
(SELECT sum(Hours) FROM WPLR WHERE (Project LIKE
'::project::%'AND WED = '::WED::')) as [Total Hours]
FROM WPLR WHERE (Project LIKE '::project::%'AND WED = '::WED::') ORDER BY
Hours ASC

Any suggestions will be greatly appreciated. I have over 16 hours into
this
seemingly simple web-based reporting system.
Joe


When the data is displayed. It is formatted in Access as I want to see it, however, Front Page does not "respect" the formatting in the db and needs direction on how to format the data. I've looked at spiderwoman's webpage for direction and was able to utilize it on the second query that I've posted. However, in the first query I haven't quite master the syntax.
 
D

David Berry

Try

FormatDateTime(FIELDNAME,vbLongTime) or
FormatDateTime(FIELDNAME,vbShortTime)

Depending on how you want the time to appear. Replace FIELDNAME with the
name of the field coming out of the database in your recordset. Ex:

FormatDateTime(fp_rs("Hours"), vbLongTime)



Cayvman said:
David Berry said:
I'm not sure I understand. Do you want to format the time when you do
the
insert into the database or do you mean when it comes out of the
database?



Cayvman said:
Okay, let me try this again with a pared down sql script. I was able to
do
the formatting on one of the my queries, however am still stuck on the
first
query. So, here is the query that I still need help in sending the
information to Front Page to format the results of the Hours field.
The fields in the access database are ID,Employee
Name,Project,Hours,WED
The Table name is WPLR.

</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM WPLR WHERE (Project LIKE '::project::%' AND WED
=
'::WED::') ORDER BY ""Employee Name"" ASC"
fp_sDefault="Project=&WED="
fp_sNoRecords="<tr><td colspan=4 align=""LEFT"" width=""100%"">No
records
returned.</td></tr>"
fp_sDataConn="ProjLab"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="WED"
fp_sMenuValue="WED"
fp_sColTypes="&ID=3&Employee Name=202&Hours=5&Project=202&Fiscal
Year=3&Period Number=3&Sub Period Num=3&WED=135&"
fp_iDisplayCols=4
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

The script for a second query that sums the results of the first query
follows. Though this script is more complex, I was able to get the
format
syntax right. Which came as a shock to me.

SELECT *,
(SELECT sum(Hours) FROM WPLR WHERE (Project LIKE
'::project::%'AND WED = '::WED::')) as [Total Hours]
FROM WPLR WHERE (Project LIKE '::project::%'AND WED = '::WED::') ORDER
BY
Hours ASC

Any suggestions will be greatly appreciated. I have over 16 hours into
this
seemingly simple web-based reporting system.
Joe


When the data is displayed. It is formatted in Access as I want to see
it, however, Front Page does not "respect" the formatting in the db and
needs direction on how to format the data. I've looked at spiderwoman's
webpage for direction and was able to utilize it on the second query that
I've posted. However, in the first query I haven't quite master the
syntax.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top