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