X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Display Error For Expiry Date, Access 2013    
post Jun 16 2019, 11:16 AM

Posts: 2
Joined: 29-January 19

Hi everyone

Please excuse if this is a double post.

I need help with a simple VBA i cannot fix. In my table there is a field named IssuedDate

I then have a query that calculates the expiration date (ExpirationDate: DateAdd("d",[IssuedDate],1827) and preExpiryWarning date (PreWarningExp: DateAdd("d",[ExpirationDate],-30) from IssuedDate. The expiry date is 5 years from Issued Date.

I have a form where the DB Admin can select the name of the person from a combo box and the combo updates unbound textboxes with the details of that selected person. All i need is code that displays an error message (which will be a label) underneath the combobox to show that the certificate has expired and they need to renew the certificate. Also if the expiry date has not been reached, i want another error message to show up which displays a label telling the user the PreWarning expiry date.

I tried:
if me.txtExpDate.value < Now() then
end if

and i tried if me.txtExpDate.value<Date() then but as soon as i hit enter for the following line it removes the () and only show Date.

Any help will be appreciated. Thank you
Go to the top of the page
Start new topic
post Jun 16 2019, 11:56 AM

Access Wiki and Forums Moderator
Posts: 76,000
Joined: 19-June 07
From: SunnySandyEggo


Welcome to UtterAccess!

What is your Regional Settings? Does this really work?
ExpirationDate: DateAdd("d",[IssuedDate],1827)

I was expecting to see something more like this instead.
ExpirationDate: DateAdd("d",1827,[IssuedDate])

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
post Jun 17 2019, 03:48 AM

UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill

Good spot DBG! thumbup.gif

Actually, when using "d" as the interval I think it makes no difference if you get the Number and Date arguments the wrong way round, since they are effectively the same units!!
?DateADd("d", 1, Date)
?DateAdd("d", Date, 1)



David Marten
Go to the top of the page
post Jun 28 2019, 02:05 PM

Posts: 2
Joined: 29-January 19

Thank you for getting back to me. My regional settings is set on South Africa. I've removed the PreWarningExp and in my form i use unbounded text boxes to display the info from the selected name in the combobox but I did however read a topic that in the event of a date field (cannot remember exactly) I must first convert the value for the code to work so I used:

If CDate(txtExpDate.Value) < Date Then
Me.lblAlert.Visible = True

which works out great though. It displays an error label when the expiry date has been reached and refuses any further action on that form if the user with expired certificate clicks the proceed button.

I would however still like the form/database to send an email notification to the admin and user when a pre-expiry date has been triggered (30 days before exp date). This is where I need help. I already have the pre-exp set in a query based from issued date. All i need is either code or something that will send a email notification as well as when the user selects his name from the combo that the pre-exp also triggers a notification on the form.
Go to the top of the page

Posts in this topic

Custom Search

RSSSearch   Top   Lo-Fi    19th August 2019 - 09:02 AM