Query criteria - porting from excel, a bit lost!

[RCheesley]

Hi all,

I have a query I'm using which I want to do something funky with but I'm not sure how!

Basically I am currently using a very complex excel spreadsheet to maintain a line listing of patients with a certain condition, which automagically calculates certain fields.

I'm attempting to incorporate this into an access database with varying levels of success, as I'm quite new to Access and databases in general.

I have a query pulling from a table with the following fields:

SpecimenID (PK)
PatientDetailsID (FK from tblPatientDetails)
SpecimenDate (date of sample)
SpecimenOrganism (because this db will manage several organisms in the future)
SpecimenLocation (which lab it came from)
SpecimenPreviousDischarge (patient's most recent discharge date)
SpecimenCurrentAdmission (patient's admission date if admitted this episode)

I've got this query working perfectly in a number of situations, however I want to be able to automagically calculate as my spreadsheet does, whether the sample is classified as:

Acute (taken day of admission + 2 days or greater)
Pre-48h (taken on day of admission or following 2 days)
Community (taken by GP hence no admission date for this episode, or admission date is after sample date)

The way I accomplish this on the spreadsheet is using the following formula:

=IF(AND((G2-I2)<=2,G2>=I2),"PRE-48h",IF((AND((G2-I2)>2,G2>=I2,I2<>"")),"ACUTE","COMMUNITY"))

Where
G2 = Specimen Date
I2 = Admission Date

I then have a separate field which identified in further detail the hospital association:

=IF(AND(G2<=(I2+2),(K2>31)),"CO-HAP",IF(AND(OR(K2>=63,(AND(ISBLANK(J2),N2=1))),N2=1),"CO-CA",IF(AND(K2>=32,K2<=64,N2=1),"INDETERMINATE",IF(AND(G2>=I2+2,N2=3),"HO-HA",IF(AND(OR(ISBLANK(I2),K2>=1,K2<=31,)),"CO-HA")))))

Where
G2 = Specimen Date
I2 = Admission Date
K2 = Calculated field of days since discharge using =IF(ISBLANK(J2),"N/A",(DATEDIF(J2,G2,dcell)))
J2 = Previous Discharge Date
N2 = Automatically populated location code using =IF( M2="Community", 1, IF( M2="Pre-48h", 2, IF( M2="Acute",3)))

My question is (well done for making it this far!) what is the best way to calculate these fields in Access? The Spec/Admission/Discharge dates are all input via a form frmAddSpecimens so is it possible to have a field which calculates the appropriate values somehow based on the above equations?

Ruth

 

[RCheesley]

An update, I've tried porting this equation:

=IF(AND((SpecimenDate-AdmissionDate)<=2,SpecimenDate>=AdmissionDate),"PRE-48h",IF((AND((SpecimenDate-AdmissionDate)>2,SpecimenDate>=AdmissionDate,AdmissionDate<>"")),"ACUTE","COMMUNITY"))

using the Expression builder to get:

=IF(AND(([SpecimenDate]-[SpecimenCurrentAdmission])<=2,[SpecimenDate]>=[SpecimenCurrentAdmission]),"PRE-48h",IF((AND(([SpecimenDate]-[SpecimenCurrentAdmission])>2,[SpecimenDate]>=[SpecimenCurrentAdmission],[SpecimenCurrentAdmission]<>"")),"ACUTE","COMMUNITY"))

I also tried IIF.

I'm getting an error which says "The expression you have entered contains invalid syntax. You may have entered a comma without a preceding value or identifier".

Anyone able to shed light on what I'm getting wrong with the syntax? Assuming access handles things differently and hence the syntax is different, but don't know where.

Ruth

 

[DCrake]

What you need to do is to create public functions which take in the parameters which are needed to evaluate the answer. These are stored in a module and can be referrenced in your queries as calculated fields.

Hence your first example:

Excel version:
=IF(AND((G2-I2)<=2,G2>=I2),"PRE-48h",IF((AND((G2-I2)>2,G2>=I2,I2<>"")),"ACUTE","COMMUNITY"))

Access version

Public Function Location(SpecDate As Date, AdmitDate As Date) As String

If DateDiff("d",SpecDate,AdminDate) < = 2 And SpecDate > = AdminDate Then
   Location = "Pre-48h"
ElseIf DateDiff("d",SpecDate,AdminDate) > 2 And SpecDate>=AdmitDate Then
   Location = "Acute"
Else
   Location = "Community"
End If

End Function

The logic has not been tested but is shown to give you a flavour of how to proceed.

In your query you would have

Loc:Location([SpeciminDate],[AdmissionDate])

David

 

[RCheesley]

Hi David,

Huge thanks for your help with this - I have not really delved into modules but this seems to accomplish my needs, with one issue.

If the patient is not admitted then the cell is blank - this returns #error when I want it to return COMMUNITY.

What would the syntax be for entering an ISNULL or ISBLANK statement?

Ruth

 

[RCheesley]

Also none of the acute cases are flagging as acute, all are "Pre-48h"

Ruth

 

[Kiwiman]

Howzit

There may be a slight error in Davids answer - the items in bold should be the same variable - AdmitDate?

Access version

Public Function Location(SpecDate As Date, [B]AdmitDate[/B] As Date) As String

If DateDiff("d",SpecDate,[B]AdminDate[/B]) < = 2 And SpecDate > = [B]AdminDate[/B] Then
   Location = "Pre-48h"
ElseIf DateDiff("d",SpecDate,[B]AdminDate[/B]) > 2 And SpecDate>=[B]AdmitDate[/B] Then
   Location = "Acute"
Else
   Location = "Community"
End If

End Function

The logic has not been tested but is shown to give you a flavour of how to proceed.

In your query you would have

Loc:Location([SpeciminDate],[AdmissionDate])

David

 

[RCheesley]

I changed them to Admitdate from Admindate - assumed it was a typo

 

[RCheesley]

Option Compare Database
Option Explicit

Public Function Location(SpecDate As Date, AdmitDate As Date) As String

If DateDiff("d", SpecDate, AdmitDate) <= 2 And SpecDate >= AdmitDate Then
   Location = "Pre-48h"
ElseIf DateDiff("d", SpecDate, AdmitDate) > 2 And SpecDate >= AdmitDate Then
   Location = "Acute"
Else
   Location = "Community"
End If

End Function

In the query:
Loc: Location([SpecimenDate],[SpecimenCurrentAdmission])

 

[RCheesley]

Ahar I think I might have figured out one part - the datediff had the two dates the wrong way round:

Option Compare Database
Option Explicit

Public Function Location(SpecDate As Date, AdmitDate As Date) As String

If DateDiff("d", AdmitDate, SpecDate) > 2 And SpecDate > AdmitDate Then
   Location = "Acute"
ElseIf DateDiff("d", AdmitDate, SpecDate) <= 2 And SpecDate >= AdmitDate Then
   Location = "Pre-48h"
Else
   Location = "Community"
End If

End Function

Seems to work now, just need to figure out how to put in an isblank/null statement

 

[Kiwiman]

Howzit

The problem I think is the difference in the return value of the datediff function to your excel formula

If the first date is before the last date the value returned will be positive, and where the first date is after the first date the value returned is negative,

This pretty much does the opposite of what your excel formula is doing.

The Elseif part will never be true in this case as the Datediff function will always return a negative value if the first date is after the last date - and as the AND stmt requires all parts to be TRUE, it will always fail.


Try changing the signs around or the dates around.

 

[Kiwiman]

Howzit

I see you solved it. Well done.

For the blanks you can try

Option Compare Database
Option Explicit

Public Function Location(SpecDate As Date, AdmitDate As Date) As String

if isnull(specdate) or isnull(admitdate) then
   Location = "Community"
elseIf DateDiff("d", AdmitDate, SpecDate) > 2 And SpecDate > AdmitDate Then
   Location = "Acute"
ElseIf DateDiff("d", AdmitDate, SpecDate) <= 2 And SpecDate >= AdmitDate Then
   Location = "Pre-48h"
Else
   Location = "Community"
End If

End Function

 

[RCheesley]

Already tried that but it doesn't seem to work

 

[DCrake]

Ok

Try

Public Function Location(Optional SpecDate As Date = 0, Optional AdmitDate As Date = 0 ) As String

Then test for specDate and/or AdmintDate = 0

 

[RCheesley]

Had a go at this but am now getting query errors - assume I've got the wrong end of the stick:

Option Compare Database
Option Explicit

Public Function Location(SpecDate As Date, AdmitDate As Date) As String
Public Function Location(Optional AdmitDate As Date = 0) As String

If AdmitDate = 0 Then
   Location = "Community"
ElseIf DateDiff("d", AdmitDate, SpecDate) > 2 And SpecDate > AdmitDate Then
   Location = "Acute"
ElseIf DateDiff("d", AdmitDate, SpecDate) <= 2 And SpecDate >= AdmitDate Then
   Location = "Pre-48h"
Else
   Location = "Community"
End If

End Function

 

[DCrake]

You did not read the reply correctly

Public Function Location(SpecDate As Date, AdmitDate As Date) As String
Public Function Location(Optional SpecDate As Date = 0, Optional AdmitDate As Date = 0) As String

You have got it in twice. You need to replace the first line with the second line


David

 

[RCheesley]

Hi David,

I had tried that and no joy, still getting #error

Option Compare Database
Option Explicit

Public Function Location(Optional SpecDate As Date = 0, Optional AdmitDate As Date = 0) As String

If AdmitDate = 0 Then
   Location = "Community"
ElseIf DateDiff("d", AdmitDate, SpecDate) > 2 And SpecDate > AdmitDate Then
   Location = "Acute"
ElseIf DateDiff("d", AdmitDate, SpecDate) <= 2 And SpecDate >= AdmitDate Then
   Location = "Pre-48h"
Else
   Location = "Community"
End If

End Function

 

[RCheesley]

Not sure if it's relevant, but a date field can't =0 .. is this the same as saying isnull/isblank or not? Am not sure if this is causing the problem.

Ruth

 

[DCrake]

Have you stried stepping through the code to see what is causing the error?

Go to the immediate window (after setting a breakpoint on the function) and enter

?Location(Date)

Se what happens using F8

 

[Kiwiman]

Howzit

I get the error message as well. Still trying to get around the Optional parts of Davids solution.

I have got this to work


The query to look like

Loc: Location(nz([datein],#01/01/1900#),nz([dateout],#01/01/1900#))

Public Function Location(SpecDate As Date, AdmitDate As Date) As String


'Debug.Print SpecDate
'Debug.Print AdmitDate

If SpecDate = #1/1/1900# Or AdmitDate = #1/1/1900# Then
    Location = "Community"
ElseIf DateDiff("d", SpecDate, AdmitDate) >= 2 And SpecDate >= AdmitDate Then
     Location = "Pre-48h"
ElseIf DateDiff("d", SpecDate, AdmitDate) < 2 And SpecDate >= AdmitDate Then
   Location = "Acute"
Else
       Location = "Community"
End If

End Function

 

[DCrake]

Using the Optional command in a function allows you pass Null or Empty values to a function where ordinarily it would be expecting a value. In this case a date. However if no date is passed to the function then use 0 as the substitute date. As 31/12/1899 = 0 and 00:00:00 also = 0 it means that the function will operate normally.


Place a breakpoint on the Public Function line

Test 1
From the immediate window type in

? Location(Date)

as the breakpoint appears hover the mouse over the SpecDate and AdmitDate arguments. The specdate will have today's date and the AdmitDate will be 00:00:00.

Press F8 to step through the remaining code

Test 2
From the immediate window type in

? Location(,Date)

as the breakpoint appears hover the mouse over the SpecDate and AdmitDate arguments. The specdate will be 00:00:00 and the AdmitDate will be today's date.

Press F8 to step through the remaining code

In test 1 the second argument was missing
In test 2 the first argument was missing


David