Go Back   Access World Forums > Apps and Windows > Visual Basic

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-16-2018, 05:12 AM   #1
monkeytrumpet
Newly Registered User
 
Join Date: May 2018
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
monkeytrumpet is on a distinguished road
Test current date inbetween dates in table

Hi, I'm new here, and definitely new to access! I have a db designed to track safety documents, with 3 tables, some queries and a number of forms. One table for document acceptors, with date of authorisation and expiry, one with document details, and one for safety controllers, similar to acceptors.

I have a query based on the permits table to bring up a lift of documents which are prepared and ready to issue, which populates a split form in which the document can be issued to the acceptor, by means of a combobox. My problem is that I want to check that the acceptors authorisation is in date, but I'm struggling to work out how. I was thinking of using the 'on change' property of the combobox to trigger VBA code along the lines of

Code:
dim t1 as date
dim t2 as date
 t1 = [Acceptors]![Authorisation Date]!value
 t2 = [Acceptors]![Expiry Date]!value
if date(today)>t1 AND <t2,
msgbox("authorised",vbOKonly)
DoCmd.Close acForm, "Issue Document", acSaveYes
DoCmd.OpenForm ("Front Page")
else
msgbox("Acceptor's authorisation has expired, cannot issue document")
but I have no idea how to get this working, can anyone help please? I think the problem may be that the form I'm working from is looking at the permits table, where the field 'acceptor' is a lookup from the 'acceptors' table, if this makes sense?


monkeytrumpet is offline   Reply With Quote
Old 05-16-2018, 05:48 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,674
Thanks: 9
Thanked 3,795 Times in 3,738 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Test current date inbetween dates in table

You can't get the values from the table like that. Try DLookup(). To get today's date use the Date() function.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 05-16-2018, 06:16 AM   #3
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,322
Thanks: 54
Thanked 2,026 Times in 1,939 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Test current date inbetween dates in table

What does the combobox hold? Shoe some data.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Rule for inbetween two dates? dominoes Tables 2 02-22-2013 03:50 PM
How to check if current date is between two dates Adzi Queries 2 05-21-2007 12:48 PM
It is possible to list all dates inbetween a date range? Please help joe789 Queries 3 08-20-2004 11:45 AM
QueryByForm, search for records with date field inbetween a range... code not working Sharky II Modules & VBA 1 07-14-2004 04:01 PM
Finding records inbetween dates Elle Forms 10 09-19-2002 11:57 PM




All times are GMT -8. The time now is 08:10 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World