how to display nearest date to expiry on form in text box

[sspreyer]

hi all
right.. I have main form call frmemployee and subform call frmlcourse subform

the main form hold's employee information e.g. first name addresss

the sub form hold's all the training courses that the employee has been on

right..... now my problem

I have a text box called txtnextcourse due to display when the next course due date is

using

=DMin("Expirydate","tblcourses","[ID]=" & [empID])

this works perfect but.....

I have check box on my subform called ex course if this is true
I don't want the dmin to count that record I have tried

 =DMin("Expirydate","tblcourses","[ID]=" & [empID]) [forms]![frmlcourse subform]![ex course]=false

but it does not work and I'm very lost :banghead::banghead:

thank in advance for any help

shane

 

[vbaInet]

Use an IIF() function.

 

[sspreyer]

hi vbainet

right I have tried

=IIf([Forms]![frmlcourses subform]![ExCourse]=False,DMin("Expirydate","tblcourses","[ID]=" & [ID]))

but with no success

thanks for the help

shane

 

[vbaInet]

Both fields are on the subform right? And ID is a Number data type right?

=IIf([ExCourse]= False, DMin("Expirydate","tblcourses","[ID]=" & [ID]), Null)

 

[sspreyer]

sorry typo! error:banghead:

=IIf([ExCourse]= False, DMin("Expirydate","tblcourses","[ID]=" & [empID]), Null)

right that's not working as my text box that display's next due course date is on the main form frmemployee so there no reference I 'm guessing for excourse

thanks

shane

 

[vbaInet]

I asked you two questions.

 

[sspreyer]

Both fields are on the subform right? And ID is a Number data type right?

=IIf([ExCourse]= False, DMin("Expirydate","tblcourses","[ID]=" & [ID]), Null)

yes your right both fields are on my subform yes id is the data type the "link" between the forms
thanks

shane

 

[vbaInet]

The code I wrote were based on those assumptions, so I wasn't really expecting you to just copy and paste.

If both fields are on the subform, why not put the textbox on the subform?

 

[sspreyer]

The code I wrote were based on those assumptions, so I wasn't really expecting you to just copy and paste.

If both fields are on the subform, why not put the textbox on the subform?

right I have tried putting the text on the subform still code doesn't work

if I have expiry date 01/04/14 and then make this true the textbox goes blank and doesn't so the next due I even try removing the null no different

cheers

shane

 

[vbaInet]

What code did you put on the subform?

Did you move to another control after checking the checkbox?

 

[sspreyer]

right.
I put this in my text box control source on sub form

 =IIf([ExCourse]= False, DMin("Expirydate","tblcourses","[ID]=" & [empID]), Null)

it does show the next due date but if I make the next course a excourse and go to another control it still say's the same date doesn't find the next due date or is sometimes blank

cheers
shane

 

[vbaInet]

Let me a screenshot of the layout of your form.

 

[sspreyer]

hi,
vbainet

as requested I have made screen shots before ex course is true and when ex course is true and I have made a temp base to show you the code in the text box

I have added speech bubble to my screen shot to explain more

thanks again

shane

screen shots:
View attachment before ex course is true.zip
View attachment when excourse made true.zip

temp database
View attachment cousre test for shane.zip

 

[vbaInet]

Shane you could have put everything in one zip file or at least uploaded the screenshots without zipping it. The process of downloading a zip file just to open an image is a bit much.

By the way was your form created for the Orange network?

I'll take a look in a min.

 

[sspreyer]

I do like a bit of orange lol

sorry some reason I was having trouble uploading the screen shots as a file so I had to zip them. but on the over hand I could of made one zip file :banghead:

I'll take a look in a min.

no worries

thanks again

shane

 

[vbaInet]

Shane it's a very simple matter of changing the False to True. You can already see that it's doing the opposite.

 

[sspreyer]

Shane it's a very simple matter of changing the False to True. You can already see that it's doing the opposite.

I tried that but it still shows 10/04/14 is next due date even if this record is ex course record and another issue is if there are no ex course records the next course due date text box goes blank

View attachment changed false to true.zip
thanks

shane

 

[vbaInet]

... and another issue is if there are no ex course records the next course due date text box goes blank

If there are no records there's nothing to filter on right?
And you're filtering by the empid which is also the Link Master Field of the subform (i.e. it exists in the parent form) so the records in the subform should have no bearing on the result. So perhaps you want to re-think what you're doing.

In the meantime see attached.

 

[sspreyer]

hi,

VbaInet

just update I have solve this issue by a slight redesign by making query to show course due in 30days and courses over due and making ex course =false as criteria then using this to power a new form

I don't no why I didn't think of this before if I did have textbox showing the next course due date the user would still have to find the record within the form to what course this apply's too.

so by creating new form power by my new query and opening it by linked id's of the main frmemployee to my new form

it will now show course over due and 30 days till due for that employee saving the time of the user scrolling to find the course next due

I have now also added a text box frmemployee to display a count of my query that's over due and due in 30days



thanks again VbaInet!!! for your help

shane

 

[vbaInet]

No attachment?