We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Anthony6890
Anthony6890 asked
on
Medium Priority
167 Views
Last Modified: 2012-05-11
I have a query that is referencing a value in a form.  The query will only work once the form is open.  This might be a dumb question, but does the form always have to be open to use that value?  

-Anthony
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Top Expert 2016

Commented:
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.

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2016

Commented:
<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.

Author

Commented:
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));

Open in new window

CERTIFIED EXPERT
Top Expert 2016

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

Author

Commented:
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.  
CERTIFIED EXPERT
Top Expert 2016

Commented:
better upload a copy of the db.

upload a  .mdb version

Author

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

Author

Commented:
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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.