Nested IIf( with DMax and DateAdd not working correctly

[nightflight]

[SOLVED] Nested IIf( with DMax and DateAdd not working correctly

As the control source of txt_expdate I have the following


=IIf(([txt_coursedate]>DMax("certexpires","qry_personcert")),
DateAdd("m",36,[txt_coursedate]),
IIf(([txt_coursedate] Between DMax("certexpires","qry_personcert") And DateAdd("m",-3,DMax("certexpires","qry_personcert"))),
DateAdd("m",36,DMax("certexpires","qry_personcert")),
IIf([txt_coursedate]<(DateAdd("m",-3,DMax("certexpires","qry_personcert"))),
DateAdd("m",39,[txt_coursedate]),[txt_coursedate])))

so for example, with certexpires : 21/07/2012 and txt_coursedate: 22/06/2012

txt_expdate should be 21/07/2015, not 22/06/2015 which it's giving me.

checking with txt_coursedate, 21/01/2012 gives 21/04/2015 as it should and with 22/07/2012 gives 22/07/2015 as it should, so the issue seems to be with the Between..And.. statement?

Can anyone point me in the right direction?

Thanks

 

[JamesMcS]

Have you tried swapping them around?

[FONT=Arial]IIf(([txt_coursedate] Between DateAdd("m",-3,DMax("certexpires","qry_personcert")) And DMax("certexpires","qry_personcert") ),

Not sure if Between requires lower then upper values, rather than upper then lower...[/FONT]

 

[nightflight]

No change :-/

 

[JamesMcS]

Weird, I've just tried copying and pasting your code into a textbox, putting actual dates in, and it comes up with a date in 1902.... I'll have another look at this in a bit

 

[nightflight]

Thanks,
I'm no expert in access, but i just can't see why it's taken the expression and reduced it to
if coursedate>certexpires then coursedate+36months
if coursedate<certexpires then coursedate+39months

 

[JamesMcS]

Is DMax looking up the right date? I assume that this is on a form which is displaying a single person's data - but you haven't got any filter criteria in your DMax statement, as in

[FONT=Arial]DMax("certexpires","qry_personcert","User=" & me.userid)

[/FONT]

 

[nightflight]

I think so?
it's looking up certexpires from qry_personcert which returns
certname, certtype, certnum and certexpires
with

[Forms]![frm_Person]![cbo_Name]

as the criteria for certname.

I figured that by using the query first it'd simplify the code for the DMax rather than adding criteria?

 

[JamesMcS]

If qry_personcert brings back more than one person, the dmax statement will just pull back the greatest date from the whole dataset, not the person whose record is being shown on the form.

Easy way to test is to have a text box whose control source is the dmax statement by itself.

 

[nightflight]

Just popped two text boxes onto the form,
with control sources as

=DMax("certexpires","qry_personcert")

and

=DateAdd("m",-3,DMax("certexpires","qry_personcert"))

and both of those are bringing up the correct date, ie. 21/07/2012 and 21/04/2012 respectively.


Extra weirdly, I've tried randomly using a few other names, and occasionally the txt_expdate will produce the correct date when txt_coursedate is less than 3 months before [certexpires]
eg.
[certexpires] = 18/09/2007
txt_coursedate = 18/08/2007
txt_expdate = 18/09/2010

But mostly it won't.

 

[JamesMcS]

Bonkers...! Any chance of posting a stripped out DB? Just change names to "person 1" and whatnot and I'll have a lookski.

 

[vbaInet]

Wrong bracketting or date format incorrect.

Try this first:

=IIf([txt_coursedate] > DMax("certexpires","qry_personcert"), DateAdd("m",36,[txt_coursedate]),
     IIf([txt_coursedate] Between DMax("certexpires","qry_personcert") And DateAdd("m",-3,DMax("certexpires","qry_personcert")),
     DateAdd("m",36,DMax("certexpires","qry_personcert")),
          IIf([txt_coursedate] < DateAdd("m",-3,DMax("certexpires","qry_personcert")),
          DateAdd("m",39,[txt_coursedate]), [txt_coursedate])))

 

[nightflight]

No joy vbaInet.

Just did some more testing and it seems to be something to do with the day value in txt_coursedate having to match the day value in [certexpires] in order for txt_expdate to give the correct answer?
eg.
[certexpires] = 17/08/2013
txt_coursedate = 17/06/2013
txt_expdate = 17/08/2016

but with
[certexpires] = 17/08/2013
txt_coursedate = 18/06/2013
txt_expdate = 18/06/2016

and weirdly
[certexpires] = 17/08/2013
txt_coursedate = 16/06/2013
txt_expdate = 16/09/2016

for each of those

=DateAdd("m",-3,DMax("certexpires","qry_personcert"))

is 17/05/2013

*Just working on getting an anonymised version of the DB up, work network won't allow uploading so trying to go via my phone.

 

[JamesMcS]

Can you attach the result of qry_personcert for us as an Excel spreadsheet?

 

[vbaInet]

If your date fields are true Date/Time fields then:

=IIf(CLng([txt_coursedate]) > CLng(DMax("certexpires","qry_personcert")), DateAdd("m",36,[txt_coursedate]),
     IIf(CLng([txt_coursedate]) Between CLng(DMax("certexpires","qry_personcert")) And CLng(DateAdd("m",-3,DMax("certexpires","qry_personcert"))),
     DateAdd("m",36,DMax("certexpires","qry_personcert")),
          IIf(CLng([txt_coursedate]) < CLng(DateAdd("m",-3,DMax("certexpires","qry_personcert"))),
          DateAdd("m",39,[txt_coursedate]), [txt_coursedate])))

 

[nightflight]

sendspace dot com /file/ww1qrp
Afraid I can't find any other way to upload the anonymised DB other than sendspace atm.
Apologies, I know it's counter etiquette.

 

[nightflight]

If your date fields are true Date/Time fields then:

=IIf(CLng([txt_coursedate]) > CLng(DMax("certexpires","qry_personcert")), DateAdd("m",36,[txt_coursedate]),
     IIf(CLng([txt_coursedate]) Between CLng(DMax("certexpires","qry_personcert")) And CLng(DateAdd("m",-3,DMax("certexpires","qry_personcert"))),
     DateAdd("m",36,DMax("certexpires","qry_personcert")),
          IIf(CLng([txt_coursedate]) < CLng(DateAdd("m",-3,DMax("certexpires","qry_personcert"))),
          DateAdd("m",39,[txt_coursedate]), [txt_coursedate])))

Double checked tbl_cert2 all dates are date/time (short date) format, and specified short date format for certexpire in qry_personcert.
using the above gives #error

 

[vbaInet]

You may have deleted a parens by mistake. I have removed the spaces, carefully copy and paste this whole line in.

=IIf(CLng([txt_coursedate]) > CLng(DMax("certexpires","qry_personcert")), DateAdd("m",36,[txt_coursedate]), IIf(CLng([txt_coursedate]) Between CLng(DMax("certexpires","qry_personcert")) And CLng(DateAdd("m",-3,DMax("certexpires","qry_personcert"))), DateAdd("m",36,DMax("certexpires","qry_personcert")), IIf(CLng([txt_coursedate]) < CLng(DateAdd("m",-3,DMax("certexpires","qry_personcert"))), DateAdd("m",39,[txt_coursedate]), [txt_coursedate])))

 

[nightflight]

copied and pasted as below but still #error.
=IIf(CLng([txt_coursedate]) > CLng(DMax("certexpires","qry_personcert")), DateAdd("m",36,[txt_coursedate]), IIf(CLng([txt_coursedate]) Between CLng(DMax("certexpires","qry_personcert")) And CLng(DateAdd("m",-3,DMax("certexpires","qry_personcert"))), DateAdd("m",36,DMax("certexpires","qry_personcert")), IIf(CLng([txt_coursedate]) < CLng(DateAdd("m",-3,DMax("certexpires","qry_personcert"))), DateAdd("m",39,[txt_coursedate]), [txt_coursedate])))

 

[vbaInet]

I suppose the only thing to do will be to see a stripped down version of your db with the query/tables in concern.

 

[nightflight]

unfortunately i can't upload via my phone or direct from work PC so have had to use sendspace to upload a stripped version.
As this is post ten, i'll put the link in the next post.