Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query Question (beginner)

Status
Not open for further replies.
LongFeiFengWu

LongFeiFengWu

Technical User
Nov 9, 2001
98
US
I have 2 forms that post to an Access DB. i have a query setup that displays the sum of "total" and the "date". What I need is a way to display the query results based on today's date. basically, show the "total" from the query where "date"=today. wow that almost looks like a real script. :0)
 
Sort by date Sort by votes
Hi.
If for example your recordset name be RS and your table name be myTable and a field in your DB which contains the date is fldDate then you will have :
RS.Source = "SELECT * FROM MyTable Where fldDate = " & Date()

TNX.
E.T.
 
Upvote 0 Downvote
If u want the number of records with today's date, use:
SELECT Count(*) FROM Table WHERE DateField = " & Date()

If you want the total value of a field where date is today, use:
SELECT Sum(AField) FROM Table WHERE DateField = " & Date()

eg.
RecordNum AField Date
[red]1 3 06/09/02[/red]
2 1 04/09/02
3 2 05/09/02
[red]4 8 06/09/02[/red]
[red]5 4 06/09/02[/red]

In this case the query will return 15 (the sum of AField for records with today (sept 06)). [Thanks in advance|Hope I helped you]
Exodus300
World-Wide Alliance of Evil and Twisted People
[red]"Experimentation by Example is the best learning tool" - Exodus300[/red]
Code: 
if (pop_tarts == 0)
{
   tantrum = 1;
}
[pc3]
 
Upvote 0 Downvote
<html>

<head>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=windows-1252&quot;>
<meta name=&quot;GENERATOR&quot; content=&quot;Microsoft FrontPage 4.0&quot;>
<meta name=&quot;ProgId&quot; content=&quot;FrontPage.Editor.Document&quot;>
<title>New Page 1</title>
<meta name=&quot;Microsoft Theme&quot; content=&quot;zero 111&quot;>
</head>

<body>

<%

set objcon=createobject(&quot;adodb.connection&quot;)
set objrec=createobject(&quot;adodb.recordset&quot;)

UserID=request.cookies(&quot;ECT&quot;)(&quot;UserID&quot;)

objcon.open &quot;dsn=Tool&quot;,&quot;&quot;,&quot;&quot;

SQL = &quot;SELECT SUMOFTOTAL FROM [Away Data Query] WHERE DATE=#&quot; & DATE & &quot;# AND UserID=&quot;& UserID

objrec.open SQL,objcon,3,3

Response.write &quot;Total Time Away FromEmails Today Is: &quot;
response.write objrec(&quot;sumoftotal&quot;)

%>

</body>

</html>

This script gives me this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'DATE=#9/6/02# AND UserID='.

/nuzbitz/Joe's Project/ToolRightBot.asp, line 24

HELP!!!!
 
Upvote 0 Downvote
The UserID variable is empty! Check the content of it before executing the SQL query:

if len(UserID) = 0 then
'Put here some code that is executed when UserID is empty.
End if

This line:
UserID=request.cookies(&quot;ECT&quot;)(&quot;UserID&quot;)
should fill the UserID variable. Check if the parametes are correct. Or try to separate the two cookie values:
UserID=request.cookies(&quot;ECT&quot;) & request.cookies(&quot;UserID&quot;)

Regards,
Balazs
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
fso Question 1
Replies
3
Views
203
Offera
Offera
leifoet
  • Locked
  • Question
ASP Classic - SELECT query does not work
Replies
11
Views
446
guitarzan
guitarzan
leifoet
  • Locked
  • Question
How to compare dates in a SELECT query
Replies
12
Views
346
guitarzan
guitarzan
leifoet
  • Locked
  • Question
How do I save a list of the screen created with a select query
Replies
1
Views
162
ChrisHirst
ChrisHirst
leifoet
  • Locked
  • Question
Selection query : problem with 'ORDER BY'
Replies
4
Views
197
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top