storing date parameters as a variable

[mongous]

I have a report, that pulls from a query, some stats for employees. What I want to do is somehow use the user-entered 'date range' as a parameter for a textbox on the report that will display the month reported.

For example: if user enters dates 7/1/03 & 7/31/03, I want the text box on the report to show: 'July'


But, the date range criteria is not shown in the query so, I don't know how to reference it. Does that make sense?

 

[hwkranger]

format(yourvalue, "mmmm&quot


Randall Vollen
National City Bank Corp.

 

[hwkranger]

fyi, if you are doing just 1 month, why do a range?

you can use the dateadd() function to report 1 month on just the original date entered.

eg.

DateAdd("m", 1, myMonth)

then have your query have >= mymonth, <dateadd(&quot;m&quot;, 1, mymonth)

or however... only reason to do a range would be if you wanted unique ranges always. if this is the case then you'll need to report that on your report - and not just a single month named.


Randall Vollen
National City Bank Corp.

 

[mongous]

Ok, two questions: On this [red]format(yourvalue, &quot;mmmm&quot[/red] - how do I store [red]'yourvalue'[/red] from the query? Especially as it's a 'BETWEEN' two dates. What i mean is, how do i store & convert 7/1/03-7/31/03 into [red]'yourvalue'[/red]?

On your second comment, I like that concept, but am still confused. Could you elaborate? Thanks.

 

[hwkranger]

If you're using a query, in design view you can use the format function in the query.

The only part i'm lost on, is how you can be querying via date range, but not have it included in the query.

otherwise,

for the &quot;box&quot; on your report you can use a &quot;dlookup&quot; to get your date, i'm sure you have a date somewhere. just use the

format(dlookup(&quot;[field]&quot;, &quot;

&quot;, &quot;criteria&quot, &quot;mmmm&quot



Randall Vollen
National City Bank Corp.

 

[mongous]

Oh, the date range is included in the query, it's entered via prompts given to the user. In the [red][date][/red] field criteria row I entered: [blue]Between[Start Date]And[End Date][/blue]

Does that make sense? So my dilemma is how to assign a variable to just one of those dates so I could pull the month out with DatePart or something like that.

Am I missing something obvious? It wouldn't surprise me, I am still an amateur...

 

[evilla]

text1.text = Format(startDate, &quot;mmmm&quot
' this should do it

 

[hwkranger]

for whatever box you have on your report put for the data contorl source:


format(dlookup(&quot;[date]&quot;, &quot;[yourqueryname]&quot, &quot;mmmm&quot


(data control source can be found by right clicking on your text box in design view, clicking on the data tab, the control source is right there. you can click on the ... to build or you can type this straight into the box provided)

also remember to replace [yourqueryname] with the name of hte query that your report is based upon.

Randall Vollen
National City Bank Corp.

 

[mongous]

Great! Got it to work with the [red]= Format(startDate, &quot;mmmm&quot[/red]


Thank you both for your help! And thanks to hwkranger for turning me on to the 'DatePart' and 'DateAdd' functions!

Thanks again! This site rules!