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