|
| |
|
|
box
Posts: 167
Joined: 12/10/2002
Status: offline
 |
Appointments! - 8/24/2009 12:01:45
My Table has the following fields:
ID “Auto number”
firstname “text”
lastname “text”
ptnumber “text” unique number
nextapt “date/time”
Nextapt field contains dates that could be old or new dates for the same person. I want to find a way using DRW, who does not have an appointment! Meaning not today or in the future! Any ideas please?
|
|
|
|
box
Posts: 167
Joined: 12/10/2002
Status: offline
|
RE: Appointments! - 8/25/2009 13:06:58
Thanks for reply! I need a function that check all the ptnumber to see if the nextapt is not greater than current date and then display that ptnumer this way I get only people without appt! any idea about this function please?
< Message edited by box -- 8/25/2009 13:13:57 >
| |
|
|
|
swoosh
Posts: 1535
Joined: 5/18/2002
From: Beaver Falls, PA
Status: offline
|
RE: Appointments! - 8/26/2009 8:37:17
SELECT firstname,lastname
FROM TableName
WHERE NextApt<Date()
GROUP BY lastname;
Does that give you what you want?
< Message edited by swoosh -- 8/26/2009 8:46:39 >
_____________________________
Swoooosh
Just Do It!
| |
|
|
|
box
Posts: 167
Joined: 12/10/2002
Status: offline
|
RE: Appointments! - 8/27/2009 0:12:33
fp_sQry="SELECT firstname,lastname FROM apt WHERE (nextapt < #::todaydate::# ) GROUP BY lastname "
Database Results Error
Description: You tried to execute a query that does not include the specified expression 'firstname' as part of an aggregate function.
Number: -2147217887 (0x80040E21)
Source: Microsoft JET Database Engine
Any Idea?
| |
|
|
|
swoosh
Posts: 1535
Joined: 5/18/2002
From: Beaver Falls, PA
Status: offline
|
RE: Appointments! - 8/27/2009 9:23:25
Use ORDER BY instead of GROUP BY.......does that fix it?
My mistake, Group by does not sort them, Order by will.
SELECT firstname,lastname
FROM TableName
WHERE NextApt<Date()
ORDER BY lastname;
< Message edited by swoosh -- 8/27/2009 10:21:49 >
_____________________________
Swoooosh
Just Do It!
| |
|
|
|
box
Posts: 167
Joined: 12/10/2002
Status: offline
|
RE: Appointments! - 8/27/2009 13:48:43
Hi there, this returns all the appointments! If someone has many old appointments, will display it all! Also the same person might have a future appointment as well! I just want to display one name that does not have today or future appointment! Any idea please
| |
|
|
|
box
Posts: 167
Joined: 12/10/2002
Status: offline
|
RE: Appointments! - 8/28/2009 2:24:12
fp_sQry="SELECT DISTINCT firstname,lastname, ptnumber, enrolled FROM appt WHERE ((enrolled = 'AC') AND ('nextapt' NOT BETWEEN (#::todaydate::#) And (#::todaydate::#)+365 )) ORDER BY lastname ASC"
fp_sQry="SELECT DISTINCT firstname,lastname, ptnumber, enrolled FROM appt WHERE enrolled = 'AC' AND 'nextapt' NOT BETWEEN #::todaydate::# And #::todaydate::#+3650 ORDER BY lastname ASC"
Both syntax return same results!!!
For some reasons it returns people with future appointments as well! New appointments wont go more than max 2 months! Is the syntax correct?
< Message edited by box -- 8/28/2009 2:36:13 >
| |
|
|
|
swoosh
Posts: 1535
Joined: 5/18/2002
From: Beaver Falls, PA
Status: offline
|
RE: Appointments! - 8/28/2009 7:40:59
Try taking the single quotes away from 'nextapt' and have it as just nextapt
_____________________________
Swoooosh
Just Do It!
| |
|
|
|
swoosh
Posts: 1535
Joined: 5/18/2002
From: Beaver Falls, PA
Status: offline
|
RE: Appointments! - 8/30/2009 0:48:05
why do you have:
::todaydate:: instead of date()
_____________________________
Swoooosh
Just Do It!
| |
|
|
|
box
Posts: 167
Joined: 12/10/2002
Status: offline
|
RE: Appointments! - 9/3/2009 0:25:42
quote:
Try doing a response.write on your sql statement
It should be very EZ to do! but I'm lost tonight! how can I do this pls?
| |
|
|
|
box
Posts: 167
Joined: 12/10/2002
Status: offline
|
RE: Appointments! - 9/3/2009 15:01:31
<%
fp_sQry="SELECT DISTINCT firstname,lastname, ptnumber, enrolled FROM appt WHERE ((nextapt NOT BETWEEN #::todaydate::# AND #::todaydate::# +365) AND (enrolled = 'AC')) ORDER BY lastname ASC"
fp_sDefault=""
fp_sNoRecords="<b>No Appointment Found!</b>"
fp_sDataConn="pt"
fp_iMaxRecords=10000
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=8
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<%Response.Write(fp_sQry)%>
and I've got this:
SELECT DISTINCT firstname,lastname, ptnumber, enrolled FROM appt WHERE ((nextapt NOT BETWEEN #::todaydate::# AND #::todaydate::# +365) AND (enrolled = 'AC')) ORDER BY lastname ASC
Then the same results!!!
by the way nextapt is date/time field. Thanks
| |
|
|
|
box
Posts: 167
Joined: 12/10/2002
Status: offline
|
RE: Appointments! - 9/3/2009 23:12:54
I put it Further down! the only change is the dates!
SELECT DISTINCT firstname,lastname, ptnumber, enrolled FROM appt WHERE ((nextapt NOT BETWEEN #09/03/2009# AND #09/03/2009# +365) AND (enrolled = 'AC')) ORDER BY lastname ASC
| |
|
|
|
box
Posts: 167
Joined: 12/10/2002
Status: offline
|
RE: Appointments! - 9/4/2009 1:56:54
I tried different possibilities but this doesn’t want to work I guess!
#todaydate#
Database Results Error
Description: Syntax error in date in query expression '((nextapt NOT BETWEEN #todaydate# AND #todaydate# +365) AND (enrolled = 'AC'))'.
Number: -2147217913 (0x80040E07)
'todaydate'
Database Results Error
Description: Data type mismatch in criteria expression.
Number: -2147217913 (0x80040E07)
::todaydate::
The same mixed result!
| |
|
|
|
box
Posts: 167
Joined: 12/10/2002
Status: offline
|
RE: Appointments! - 9/4/2009 16:04:31
todaydate
Database Results Error
Description: No value given for one or more required parameters.
Number: -2147217904 (0x80040E10)
Date()
SELECT DISTINCT firstname,lastname, ptnumber, enrolled FROM appt WHERE ((nextapt NOT BETWEEN date() AND date() +365) AND (enrolled = 'AC')) ORDER BY lastname ASC
| |
|
|
|
box
Posts: 167
Joined: 12/10/2002
Status: offline
|
RE: Appointments! - 9/5/2009 1:21:09
<%
fp_sQry="SELECT DISTINCT firstname,lastname, ptnumber, enrolled FROM appt WHERE ((nextapt NOT BETWEEN date() AND date() +365) AND (enrolled = 'AC')) ORDER BY lastname ASC"
fp_sDefault=""
fp_sNoRecords="<b>No Appointment Found!</b>"
fp_sDataConn="pt"
fp_iMaxRecords=10000
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=8
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
Even I tried the following still I'm getting the same mixed results! what is causing this? I'm confused.
fp_sQry="SELECT DISTINCT firstname,lastname, ptnumber, enrolled FROM appt WHERE enrolled = 'AC' AND [nextapt] NOT BETWEEN #" & request.form("StartDate") & "# And #" & request.form("EndDate") & "# ORDER BY lastname ASC"
SELECT DISTINCT firstname,lastname, ptnumber, enrolled FROM appt WHERE enrolled = 'AC' AND [nextapt] NOT BETWEEN #09/05/2009# And #09/05/2010# ORDER BY lastname ASC
The point is even if this query works right but still will show the appointments prior of starting date as well! In this case "NOT BETWEEN #09/05/2009# And #09/05/2010#" will show still appointments prior 09/05/2009!
< Message edited by box -- 9/5/2009 22:43:29 >
| |
|
 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 
