CALCULATING EXPIRY DATE

9 replies [Last post]
Sat, 13 Jun 2015 - 3:58 AM
Nifty
Offline
Last seen: 23 weeks 2 days ago
Title: ★★
Joined: 21 May 2013
Posts: 20
I have been having problems trying to calculate and expiry date.

I have a badge that expires three years after issue. The date of issue is always the first day of the month following the shoot. For example on a shoot on 12/5/15 the date of issue is 01/06/15. I have a query which contains all my other information but can’t work this one out.

The date is calculated from a field called “SHOOTDATE”.

In my readings my date format could be the issue. I have all my dates as DD/MM/YY.

Can somebody assist please.

Top
Sun, 14 Jun 2015 - 10:52 AM
#1
charlie
charlie's picture
Online
Last seen: 4 min 47 sec ago
Title: ==Moderator==
Joined: 13 Jan 2013
Posts: 330
Hi Nifty,the following code
Hi Nifty, the following code adds three years as of the date field “data”. You can fit you? 

SUBSTR( "data", 9, 2 ) || '/' || SUBSTR( "data", 6, 2 ) || '/' || EXTRACT( YEAR FROM "data" ) + 3

So, 14/06/15 becomes 14/06/18.

Mac OSX 10.7.5 / OSX 10.11.4 (dual boot)- LibreOffice 4.3.7.2 www.charlieopenoffice.altervista.org
Top
Sun, 14 Jun 2015 - 8:07 PM
#2
Nifty
Offline
Last seen: 23 weeks 2 days ago
Title: ★★
Joined: 21 May 2013
Posts: 20
CALCULATING EXPIRY DATE
Thank you for taking the time to look at my problem.

I copied into my query but now get the error message

The value entered is not a valid date. Please enter a date in a valid format, for example, MM/DD/YY.

regards
Nifty

Top
Mon, 15 Jun 2015 - 12:14 AM
#3
charlie
charlie's picture
Online
Last seen: 4 min 48 sec ago
Title: ==Moderator==
Joined: 13 Jan 2013
Posts: 330
Can you attach a sample of
Can you attach a sample of your file?
Mac OSX 10.7.5 / OSX 10.11.4 (dual boot)- LibreOffice 4.3.7.2 www.charlieopenoffice.altervista.org
Top
Mon, 15 Jun 2015 - 12:25 AM
#4
Nifty
Offline
Last seen: 23 weeks 2 days ago
Title: ★★
Joined: 21 May 2013
Posts: 20
CALCULATING EXPIRY DATE
Please don’t laugh I am learning but here it is.

regards
Nifty

AttachmentSize
SHOOTINGNEV3.odb 107.63 KB
Top
Mon, 15 Jun 2015 - 1:03 AM
#5
charlie
charlie's picture
Online
Last seen: 4 min 48 sec ago
Title: ==Moderator==
Joined: 13 Jan 2013
Posts: 330
In my Query1 is my example.
In my Query1 is my example. In EXPITYDATE you can see SHOOTDATE + 3 years. How will you use then EXPIRYDATE?
AttachmentSize
SHOOTINGNEV3_0.odb 107.73 KB
Mac OSX 10.7.5 / OSX 10.11.4 (dual boot)- LibreOffice 4.3.7.2 www.charlieopenoffice.altervista.org
Top
Mon, 15 Jun 2015 - 1:13 AM
#6
Nifty
Offline
Last seen: 23 weeks 2 days ago
Title: ★★
Joined: 21 May 2013
Posts: 20
CALCULATING EXPIRY DATE
Thank you so much greatly appreciated – I had spent hours going around circles.
Top
Mon, 15 Jun 2015 - 1:58 AM
#7
Nifty
Offline
Last seen: 23 weeks 2 days ago
Title: ★★
Joined: 21 May 2013
Posts: 20
CALCULATING EXPIRY DATE
Again thank you.

I will be using the Expiry Date to produce a report on who has been issued the badge and the date the badge expires.

I also will use the query to check on who and who is not current.

regards
Nifty

Top
Mon, 15 Jun 2015 - 2:01 AM
#8
Nifty
Offline
Last seen: 23 weeks 2 days ago
Title: ★★
Joined: 21 May 2013
Posts: 20
CALCULATING EXPIRY DATE
Sorry I didn’t explain myself very well, if you check my Crossed Rifle Query you will see how I am using your work.
AttachmentSize
SHOOTINGNEV3_1.odb 107.75 KB
Top
Mon, 15 Jun 2015 - 2:10 AM
#9
charlie
charlie's picture
Online
Last seen: 4 min 48 sec ago
Title: ==Moderator==
Joined: 13 Jan 2013
Posts: 330
EXPIRY not a date type, but a
EXPIRY is not a date type, but a string. I think the same is fine if you are using to display it.
Mac OSX 10.7.5 / OSX 10.11.4 (dual boot)- LibreOffice 4.3.7.2 www.charlieopenoffice.altervista.org
Top

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.