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