UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Display Error For Expiry Date, Access 2013    
 
   
vanzie
post Jun 16 2019, 11:16 AM
Post#1



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
me.lblAlert.visible=true
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
 
cheekybuddha
post Jun 16 2019, 11:54 AM
Post#2


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


welcome2UA.gif

It's OK.

In the VBA editor, if you reference a function that doesn't have any arguments it will remove the trailing parentheses.

You can use:
CODE
Me.lblAlert.Visible = Me.txtExpDate < Date


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
theDBguy
post Jun 16 2019, 11:56 AM
Post#3


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


Hi,

Welcome to UtterAccess!
welcome2UA.gif

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

I was expecting to see something more like this instead.
CODE
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
 
cheekybuddha
post Jun 17 2019, 03:48 AM
Post#4


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


Good spot DBG! thumbup.gif

Edit:
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!!
CODE
?DateADd("d", 1, Date)
18/06/2019
?DateAdd("d", Date, 1)
18/06/2019

--------------------


Regards,

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



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
 


Custom Search


RSSSearch   Top   Lo-Fi    19th August 2019 - 12:48 AM