Can you set an input mask within a parameter?

[MariaL]

Hello everyone. I have the parameter on a date field:
Between [TYPE IN BEGINNING INS ELIG DATE] And [TYPE IN ENDING ELIG DATE]
When the prompt appears, they are forced to use 00/00/00 or it will not let them continue. Is there a way to set an input mask so they will know how to type in the date?
I appreciate your time and help.

 

[jdegeorge]

Yes, type your parameter as:

Between [TYPE IN BEGINNING INS ELIG DATE as 00/00/00] And [TYPE IN ENDING ELIG DATE as 00/00/00]


Jim DeGeorge

 

[MariaL]

Thank you for your response. This will communicate to them that they need to add the "/" when entering date. But, they want to be able to enter a date just as you do when you enter a date with an input mask on a table. you do not have to put 00/00/00, you just type 000000 and the"/" is built into the input mask. Can you create an input mask within a parameter? Thank you!

 

[jdegeorge]

Where are you using this paramter? Can you paste the SQL here for the whole query?

Jim DeGeorge

 

[MariaL]

Here is the whole SQL for the query. The prompt is on the "InsEligibilityDate" field. Thank you!

SELECT OfficePersonnel.EmployeeID, OfficePersonnel.LastName, OfficePersonnel.FirstName, OfficePersonnel.MiddleInitial, OfficepersonnelInsurance.MedComp, OfficepersonnelInsurance.DentalComp, OfficepersonnelInsurance.InsEligibilityDate, OfficePersonnel.EmploymentStatus
FROM OfficePersonnel INNER JOIN OfficepersonnelInsurance ON OfficePersonnel.EmployeeID = OfficepersonnelInsurance.EmployeeID
WHERE (((OfficepersonnelInsurance.MedComp) Like [TYPE IN MED COMP] & "*&quot AND ((OfficepersonnelInsurance.DentalComp) Like [TYPE IN DENTAL COMP] & "*&quot AND ((OfficepersonnelInsurance.InsEligibilityDate) Between [TYPE IN BEGINNING INS ELIG DATE] And [TYPE IN ENDING INS ELIG DATE]) AND ((OfficePersonnel.EmploymentStatus) Like [TYPE IN EMPLOYMENT STATUS] & "*&quot);

 

[jdegeorge]

MariaL

I didn't get a chance to try this because I don't have the time to set up tables to match yours, but I added the FORMAT function to your SQL:

((OfficepersonnelInsurance.InsEligibilityDate) Between Format([TYPE IN BEGINNING INS ELIG DATE],"mm/dd/yy&quot And Format([TYPE IN ENDING INS ELIG DATE],"mm/dd/yy&quot)

Hope this works!

Jim DeGeorge

 

[Clandon]

Maria:

I just tried using a form using the query with the parameter prompt. In the properties of the field, you can set the input mask to 00/00/00. Not sure if you are running off a form or not. Even with the input mask above, the date still displays as 00/00/0000, but the user would only has to type the last two digits of the year.

Just a thought. Interesting question.