[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

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

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
0
Anthony6890
Asked:
Anthony6890
  • 5
  • 5
1 Solution
 
Rey Obrero (Capricorn1)Commented:
<but does the form always have to be open to use that value?  >

Yes..

are there any reasons why you want a value accesses if the form is closed?
you might want to consider a table lookup or a global variables.
0
 
Rey Obrero (Capricorn1)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.
0
 
Anthony6890Author 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
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Rey Obrero (Capricorn1)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.
0
 
Anthony6890Author 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

0
 
Rey Obrero (Capricorn1)Commented:
i don't see where you need the parameter date in the query.
what prompt do you get when you run the report?
0
 
Anthony6890Author 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.  
0
 
Rey Obrero (Capricorn1)Commented:
better upload a copy of the db.

upload a  .mdb version
0
 
Anthony6890Author Commented:
I can't upload the db b/c it contains sensitive data...
0
 
Anthony6890Author 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.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now