Appointments! (Full Version)

All Forums >> [Web Development] >> ASP, PHP, and Database



Message

box -> 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?



TexasWebDevelopers -> RE: Appointments! (8/24/2009 12:14:35)

How far in the future?
1 years ...10 years?

This gives you the folks who have an appointment between today and you year from today.

SQL= "SELECT * FROM [table_name] WHERE nextapt BETWEEN Date() And Date()+365"

If they don't have an appointment would there be no data in the nextapt column? so it would just return a null value?



box -> 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?



TexasWebDevelopers -> RE: Appointments! (8/25/2009 14:23:18)

If they don't have an appointment would there be no data in the nextapt column? so it would just return a null value? Or are you defaulting the data to a value like zero?



box -> RE: Appointments! (8/26/2009 1:46:08)

Here is my table! "see the attachment" As you can see one person can have more than one appointment! "old or new" I want to sort who does NOT have a new appointment. In my case the query should display the following:

People without appointment are:
Kelly K
Nancy N

The function should check the nextapt of each person to see if it’s older than current day then disply it! Any idea ?


[image]local://upfiles/6745/E66A0EC468154E558209F766E05290B6.jpg[/image]



swoosh -> 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?



box -> 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 -> 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;



box -> 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



TexasWebDevelopers -> RE: Appointments! (8/27/2009 14:14:58)

SELECT DISTINCT firstname,lastname
FROM TableName
WHERE nextapt NOT BETWEEN Date() And Date()+3650
ORDER BY lastname;



swoosh -> RE: Appointments! (8/27/2009 14:21:57)

Not sure how far into the future your dates are referenced. But TWD's example above using DISTINCT that covers ten years into the future and that should be plenty I would presume[:D]



box -> 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?



swoosh -> RE: Appointments! (8/28/2009 7:40:59)

Try taking the single quotes away from 'nextapt' and have it as just nextapt



box -> RE: Appointments! (8/28/2009 12:42:00)

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"

The only difference was I've got 2 records less compare to 'nextapt'! but still displays mixed appointments people with old and new! [:o]



box -> RE: Appointments! (8/29/2009 23:24:10)

Any idea....[&o]



swoosh -> RE: Appointments! (8/30/2009 0:48:05)

why do you have:

::todaydate:: instead of date()



box -> RE: Appointments! (8/30/2009 1:30:16)

it's a function for date to get this format mm/dd/yyyy, even I have used date() but I've got exact the same result! how weird! I dont know realy what to do![:-]

todaydate=<%response.write(fixTheDate(Date()))%>



box -> RE: Appointments! (9/2/2009 0:57:56)

There is no solution for this?[8|]



TexasWebDevelopers -> RE: Appointments! (9/2/2009 8:05:43)

Try doing a response.write on your sql statement and show us what the result is...it's hard to give advice when there are so many different problem areas. For instance, variables, Access column formatting for Date/Time, etc.



box -> 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?



TexasWebDevelopers -> RE: Appointments! (9/3/2009 8:15:53)

Directly under your sql statement put:

Response.Write(fp_sQry)



box -> 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



TexasWebDevelopers -> RE: Appointments! (9/3/2009 18:20:29)

No, darn it...I'm looking to see what the query looks like after the variables are stuck into it....I can't remember what the full code looks like in FP. Try moving the response.write down the page a bit to someplace after the record set is created. Maybe someone else reading this can help by telling you exactly where to put it...but trial and error works just as well for now...you can't bust anything.



box -> 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




TexasWebDevelopers -> RE: Appointments! (9/4/2009 1:04:23)

OK the result you want is -- BETWEEN 09/03/2009 AND 09/03/2010
Try removing the # from the sql statement and if that won't work then keep the # and remove the ::
Play with it.



box -> 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!



TexasWebDevelopers -> RE: Appointments! (9/4/2009 12:56:10)

So quit using the function fix...it's already in the correct format...use Date() instead of todaydate...and there is something you didn't check...todaydate without any punctuation around it



box -> 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



TexasWebDevelopers -> RE: Appointments! (9/4/2009 17:44:50)

post the page code



box -> 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!



Page: [1] 2   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.109375