Test current date inbetween dates in table (1 Viewer)

monkeytrumpet

New member
Local time
Today, 13:13
Joined
May 16, 2018
Messages
1
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?

:banghead::banghead::banghead:
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:13
Joined
Aug 30, 2003
Messages
36,118
You can't get the values from the table like that. Try DLookup(). To get today's date use the Date() function.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:13
Joined
May 7, 2009
Messages
19,169
What does the combobox hold? Shoe some data.
 

Users who are viewing this thread

Top Bottom