John
Sorry for the misunderstanding: Your function did work.
You are right about the table, but this is the way I inherited it.
Thanks again for your help.
Sarah
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Sarah-
>
> Are you saying my function didn't work? The Array function creates an array to
> pass to the PatientDaySched function, and it does a simple index into the array
> to return the correct answer.
>
> By the way, you have a table design problem. This would be so much easier if
> you had one row per day instead of a list of repeating fields that violate First
> Normal Form.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> --------------------------------------
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of novice012000
> Sent: Friday, March 30, 2012 9:17 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] help changing query
>
>
>
>
> Thank you so so much. I wrote a public function and called it from my query, but
> I had to-do it a little different, because my fields are not in an array, they
> are individual. So I tested each field in the function and returned a true or
> false value to the query.
>
> Thank you again.
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> >
> > Sarah-
> >
> > Not directly in the query. You can get the day name using the Format function,
> > but then you can't reference a field in a table dynamically. You could write a
> > public function that you can call from the query:
> >
> > Public Function PatientDaySched(datSched As Date, varDateTrue As Variant) As
> > Boolean
> > Dim intI As Integer
> > ' Get the day number as an offset into the array
> > intI = WeekDay(datSched) – 1
> > ' Return true or false from the array
> > PatientDaySched = varDateTrue(intI)
> > End Function
> >
> > Now try this in your query:
> >
> > INSERT INTO sdtemp ( [schedule id], [shift date], [patient id], [sdetail id],
> > arrival, departure, private, insrcd )
> > SELECT sdetail.[schedule id], Schedule.[shift date], sdetail.[patient id],
> > sdetail.[sdetail id], sdetail.arrival, sdetail.departure, sdetail.private,
> > Patient.insrcd
> > FROM Patient RIGHT JOIN (Schedule RIGHT JOIN sdetail ON Schedule.[schedule id]
> =
> > sdetail.[schedule id]) ON Patient.[patient id] = sdetail.[patient id]
> > WHERE (((Schedule.[shift date]) Between [Forms]![Billing]![bill from] And
> > [Forms]![Billing]![bill to]) AND ((sdetail.private)=True) AND
> > ((Schedule.generated)=True) AND ((Patient.[hold billing])="N") AND
> > ((sdetail.billed)=False) AND ((sdetail.attended)=True) AND
> > ((sdetail.hold)=False)) AND
> > PatientDaySched(Schedule.[shift date], Array([Patient].[Sunday],
> > [Patient].[Monday], [Patient].[Tuesday], [Patient].[Wednesday],
> > [Patient].[Thursday], [Patient].[Friday], [Patient].[Saturday])) = True
> > ORDER BY Schedule.[shift date], sdetail.[patient id];
> >
> > I tested the function, and it works. But I'm not sure if you can call the
> Array
> > function like that from a query.
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> >
> > ----------------------------------
> >
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of novice012000
> > Sent: Thursday, March 29, 2012 1:18 AM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] help changing query
> >
> >
> > I have the following query:
> >
> > INSERT INTO sdtemp ( [schedule id], [shift date], [patient id], [sdetail id],
> > arrival, departure, private, insrcd )
> > SELECT sdetail.[schedule id], Schedule.[shift date], sdetail.[patient id],
> > sdetail.[sdetail id], sdetail.arrival, sdetail.departure, sdetail.private,
> > Patient.insrcd
> > FROM Patient RIGHT JOIN (Schedule RIGHT JOIN sdetail ON Schedule.[schedule id]
> =
> > sdetail.[schedule id]) ON Patient.[patient id] = sdetail.[patient id]
> > WHERE (((Schedule.[shift date]) Between [Forms]![Billing]![bill from] And
> > [Forms]![Billing]![bill to]) AND ((sdetail.private)=True) AND
> > ((Schedule.generated)=True) AND ((Patient.[hold billing])="N") AND
> > ((sdetail.billed)=False) AND ((sdetail.attended)=True) AND
> > ((sdetail.hold)=False))
> > ORDER BY Schedule.[shift date], sdetail.[patient id];
> >
> > I would like to make the following change: I have in the 'patient' table a
> field
> > for everyday of the week, sunday,monday etc that is true or false. If the
> > 'schedule.shift date' which is a short date field dd/mm/yyyy is not a true
> field
> > in the patient table , then i dont want to add it to the sdtemp table. for
> > example if the shift date is 3/27/2012 which is a Wednesday and Wednesday in
> the
> > patient table is false and the field insrcd is not empty, I dont want to
> insert
> > into SDTEMP.
> >
> > Can I do this as part of the above query ?? If yes, how?
> >
> > As always all help is greatly appreciated.
> >
> > Thanks
> >
> > Sarah
> >
>
Tidak ada komentar:
Posting Komentar