Used VB Code in Query - - Does Not Work 2

[dmon000]

The following VB code works great in an on-load event:

IIf (CDate(DFirst("dssdate", "programs")) & " " & CDate(DFirst("dsstime", "programs"))) > (CDate(DFirst("dssdate", "CRMH Current Patients")) & " " & CDate(DFirst("dsstime", "CRMH Current Patients"))),[CRMH CURRENT PATIENTS]![ward id],[PATIENTS]![ward id] Then

I want use the same logic in an update query in the "Update To" line, but Access keeps telling me that the syntax is wrong. This is how it is typed in the query:

IIf (CDate(DFirst("dssdate", "programs")) & " " & CDate(DFirst("dsstime", "programs"))) > (CDate(DFirst("dssdate", "CRMH Current Patients")) & " " & CDate(DFirst("dsstime", "CRMH Current Patients"))),[CRMH CURRENT PATIENTS]![ward id],[PATIENTS]![ward id]

Any suggestions?

Thanks Again !!

 

[SkipVought]

You IIF syntax seems incorrect. What the Then doing??

IIF syntax is
[tt]
IIF(expression,true expression,false expression)
[/tt]

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[dmon000]

There is no then in this statement. The statement that I am trying to replace is this, and it works fine without a "then" as it is in the "Update To" line of an update query.

IIf([Forms]![CCREST DIET SLIP MAIN MENU]![txtProgramsDSSdate] & " " & [Forms]![CCREST DIET SLIP MAIN MENU]![txtProgramsDSStime]>[Forms]![CCREST DIET SLIP MAIN MENU]![txtCRMHdssDate] & " " & [Forms]![CCREST DIET SLIP MAIN MENU]![txtCRMHdssTime],[CRMH CURRENT PATIENTS]![ward id],[PATIENTS]![ward id])

This is what I'm replacing it withwhich does not work)
IIf (CDate(DFirst("dssdate", "programs")) & " " & CDate(DFirst("dsstime", "programs"))) > (CDate(DFirst("dssdate", "CRMH Current Patients")) & " " & CDate(DFirst("dsstime", "CRMH Current Patients"))),[CRMH CURRENT PATIENTS]![ward id],[PATIENTS]![ward id]

Thanks!

 

[PHV]

Are you aware that the DFirst domain aggregate function is highly UNRELIABLE ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

As I stated before your SYNTAX is incorrect and you did NOTHING to correct it in order to adhere to...
[tt]
IIF(expression,true,false)
[/tt]
You are missing a closing parentheses. So it might look like this...
[tt]
IIf
(
CDate(DFirst("dssdate", "programs")) & " " & CDate(DFirst("dsstime", "programs")) >
CDate(DFirst("dssdate", "CRMH Current Patients")) & " " & CDate(DFirst("dsstime", "CRMH Current Patients"))
,[CRMH CURRENT PATIENTS]![ward id],[PATIENTS]![ward id]
)
[/tt]

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[PHV]

One more thought:
why storing a SINGLE DateTime value in TWO fields (dssdate,dsstime) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dmon000]

Thanks again for your input. I saw that I was making this far more complicated than necessary; that is I was trying to make the query use the same code as a line in VB that I am using for an on-load event.

My simpler solution is here:

UPDATE [CRMH CURRENT PATIENTS] INNER JOIN PATIENTS ON [CRMH CURRENT PATIENTS].mpi = PATIENTS.MPI SET PATIENTS.dssdate = [CRMH CURRENT PATIENTS]!DSSDATE, PATIENTS.[WARD ID] = [CRMH CURRENT PATIENTS]![WARD ID], PATIENTS.[LAST NAME] = [CRMH CURRENT PATIENTS]![LAST NAME], PATIENTS.[FIRST NAME] = [CRMH CURRENT PATIENTS]![FIRST NAME], PATIENTS.dsstime = [CRMH CURRENT PATIENTS]!DSSTIME, PATIENTS.asdate = [CRMH CURRENT PATIENTS]!ASDATE
WHERE ((([CRMH CURRENT PATIENTS]![dssdate])>[PATIENTS]![dssdate])) OR ((([CRMH CURRENT PATIENTS]![dssdate])=[PATIENTS]![dssdate]) AND (([CRMH CURRENT PATIENTS]![dsstime])>[PATIENTS]![dsstime]));

It's simpler only because I didn't have to type the SQL; Access did. I just figured out the criteria. But it works.
Thanks again for your imput, Guys. It really helps!!