Does a form always have to be open to reference a value from it in another query?

typo

are there any reasons why you want a value accessed if the form is closed?
you might want to consider a table lookup or a global variables.

Hey, thanks for getting back to me.

I didn't want the form open because I'm really just using the field as a place holder for a date.  I have a numerous amount of reports that I need to run on a specific date.  When I was using a Parameter Value for specify the date, every time I would run the report I was required to input the parameter date like 6 times (quite annoying).

Can you suggest a possible better solution?

-Anthony

<I have a numerous amount of reports that I need to run on a specific date.>
what are these specific dates? how do you determine these dates?

Post the Sql view of the reports record source.

The dates change.  It depends on when it needs to be run.  Normally it's at month end, but that can vary.

Here is the attached code:

SELECT BILMAC_MBRMSTR.[MBSS#], BILMAC_MBRMSTR.MBSTUS, BILMAC_MBRMSTR.MBSEX, BILMAC_MBRMSTR.MBLANA, BILMAC_MBRMSTR.MBFINA, BILMAC_MBRMSTR.MBBIDA, CVDate(Format("19" & [MBBIDA],"0000\/00\/00")) AS BDAY, Abs(DateDiff("yyyy",[BDAY],[RUNDTE]![SPEC])-IIf(Format([BDAY],"mmdd")<=Format([RUNDTE]![SPEC],"mmdd"),0,1)) AS Age, BILMAC_MBRMSTR.MBSTOR, BILMAC_MBRMSTR.MBDEPT, BILMAC_MBRMSTR.MBFTPT, BILMAC_MBRMSTR.MBHIRE, CDate(IIf(Len([MBHIRE])=7,Format(Mid([MBHIRE],4,2) & "-" & Mid([MBHIRE],6,2) & "-" & Mid([MBHIRE],2,2),"mm\/dd\/yyyy"),Format(Mid([MBHIRE],3,2) & "-" & Mid([MBHIRE],5,2) & "-" & Left([MBHIRE],2),"mm\/dd\/yyyy"))) AS HIRE, (IIf([MBFTPT]="P",DateAdd("m",12,CDate(IIf(Len([MBHIRE])=7,Format(Mid([MBHIRE],4,2) & "-" & Mid([MBHIRE],6,2) & "-" & Mid([MBHIRE],2,2),"mm\/dd\/yyyy"),Format(Mid([MBHIRE],3,2) & "-" & Mid([MBHIRE],5,2) & "-" & Left([MBHIRE],2),"mm\/dd\/yyyy")))),DateAdd("m",3,CDate(IIf(Len([MBHIRE])=7,Format(Mid([MBHIRE],4,2) & "-" & Mid([MBHIRE],6,2) & "-" & Mid([MBHIRE],2,2),"mm\/dd\/yyyy"),Format(Mid([MBHIRE],3,2) & "-" & Mid([MBHIRE],5,2) & "-" & Left([MBHIRE],2),"mm\/dd\/yyyy")))))) AS ELIG, BILMAC_MBRMSTR.MBTRDT, BILMAC_MBRMSTR.MBSGNH, BILMAC_MBRMSTR.MBELDT, BILMAC_MBRMSTR.MBHTDT, STORE_BY_FUND.FUND
FROM BILMAC_MBRMSTR INNER JOIN STORE_BY_FUND ON BILMAC_MBRMSTR.MBSTOR = STORE_BY_FUND.STORE
WHERE (((BILMAC_MBRMSTR.MBSTUS)="A") AND ((BILMAC_MBRMSTR.MBSTOR)<>20 And (BILMAC_MBRMSTR.MBSTOR)<>30 And (BILMAC_MBRMSTR.MBSTOR)<>40 And (BILMAC_MBRMSTR.MBSTOR)<>45 And (BILMAC_MBRMSTR.MBSTOR)<>50 And (BILMAC_MBRMSTR.MBSTOR)<>60 And (BILMAC_MBRMSTR.MBSTOR)<>65 And (BILMAC_MBRMSTR.MBSTOR)<>70 And (BILMAC_MBRMSTR.MBSTOR)<>75 And (BILMAC_MBRMSTR.MBSTOR)<>77 And (BILMAC_MBRMSTR.MBSTOR)<>80 And (BILMAC_MBRMSTR.MBSTOR)<>90 And (BILMAC_MBRMSTR.MBSTOR)<>95 And (BILMAC_MBRMSTR.MBSTOR)<>96 And (BILMAC_MBRMSTR.MBSTOR)<>97) AND ((BILMAC_MBRMSTR.MBDEPT)<>25) AND ((BILMAC_MBRMSTR.MBHIRE)<>0) AND (((IIf([MBFTPT]="P",DateAdd("m",12,CDate(IIf(Len([MBHIRE])=7,Format(Mid([MBHIRE],4,2) & "-" & Mid([MBHIRE],6,2) & "-" & Mid([MBHIRE],2,2),"mm\/dd\/yyyy"),Format(Mid([MBHIRE],3,2) & "-" & Mid([MBHIRE],5,2) & "-" & Left([MBHIRE],2),"mm\/dd\/yyyy")))),DateAdd("m",3,CDate(IIf(Len([MBHIRE])=7,Format(Mid([MBHIRE],4,2) & "-" & Mid([MBHIRE],6,2) & "-" & Mid([MBHIRE],2,2),"mm\/dd\/yyyy"),Format(Mid([MBHIRE],3,2) & "-" & Mid([MBHIRE],5,2) & "-" & Left([MBHIRE],2),"mm\/dd\/yyyy")))))))<[RUNDTE]![SPEC]) AND ((BILMAC_MBRMSTR.MBSGNH)="E") AND ((BILMAC_MBRMSTR.MBELDT)<>0) AND ((BILMAC_MBRMSTR.MBHTDT)=0));

Select allOpen in new window

i don't see where you need the parameter date in the query.
what prompt do you get when you run the report?

The prompt is a parameter value that I created.  The code provided to you is referencing the form now.  Anywhere there is a "  [RUNDTE]![SPEC] there was a [RUN_DATE].  That was a parameter I set up in the query.  

better upload a copy of the db.

upload a  .mdb version

I can't upload the db b/c it contains sensitive data...

I had to do more research as to what he meant by a lookup. He pointed me in the right direction, but his answer was not totally thorough.