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 Chriss Miller 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
Joined
Nov 9, 2001
Messages
98
Location
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
Thank you both very much. That is extremely helpful.
 
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 Question
fso Question
Replies
3
Views
473
Offera
Offera
leifoet
  • Locked
  • Question Question
ASP Classic - SELECT query does not work
Replies
11
Views
1K
guitarzan
guitarzan
leifoet
  • Locked
  • Question Question
How to compare dates in a SELECT query
Replies
12
Views
801
guitarzan
guitarzan
leifoet
  • Locked
  • Question Question
How do I save a list of the screen created with a select query
Replies
1
Views
324
ChrisHirst
ChrisHirst
leifoet
  • Locked
  • Question Question
Selection query : problem with 'ORDER BY'
Replies
4
Views
442
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top