You are viewing limited content. For full access, please sign in.

Question

Question

lookup dropdown field to only give certain values

asked on August 31, 2016

I have a lookup query in a form for registering for exams by exam number and title, but the same exam can be given on different dates, which are in the database..  For example Exam 1 can be given on many dates, some of those dates are in the past, but are still in the database.  I would like the lookup to only give future dates.  Any ideas on how I could do that?  

See attached.

LFLookup.png
LFLookup.png (43.64 KB)
0 0

Answer

SELECTED ANSWER
replied on September 1, 2016

You (or your SQL admin) would write the procedure against the database you are currently looking to in SQL Management Studio.  Once the procedure is written use the stored procedure instead of the lookup in the Lookup Rules area of the form you are on.

1 0

Replies

replied on August 31, 2016 Show version history

Instead of using a lookup I would use a Stored Procedure.  Write a stored procedure that has an input of the exam number and returns only the dates that a greater than or equal to the current date.

Something like:

USE [databasename]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[procname]
    @examnum int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT [examdate]
    FROM tablename
    WHERE examnumber=@examnum AND examdate <= GETDATE()
END
 

1 0
replied on September 1, 2016

And how do I use that in forms? 

 

Sorry, I'm just learning all of this.

0 0
SELECTED ANSWER
replied on September 1, 2016

You (or your SQL admin) would write the procedure against the database you are currently looking to in SQL Management Studio.  Once the procedure is written use the stored procedure instead of the lookup in the Lookup Rules area of the form you are on.

1 0
replied on September 1, 2016

THANK YOU!!!

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.