Changing Criteria Parameters

[tuktuk]

I have a report that is build on monday where a module runs qrys and exports to an excel file.

my issue is that every Monday i need to chagne the date criteria on all of the 9 queries.

for example: qry_PostPriceBanding has a criteria ">=#5/16/2011#" set on a field called "SendDate". this data was correct for the run yesterday but when Monday 7/25/11 comes around i'll need to add 7 days to the criteria ">=#5/16/2011#" to get the desired output.

is there an easy way to do this? can it be done using vba?

thanks
tuk

 

[xenou]

I like to use a form - the date goes in a form field and then your queries all refer to the form:

OLD QUERY (hard coded date):

SELECT Table1.Field1 
FROM Table1 
WHERE Table1.SomeDateField = #1/1/2011#

NEW QUERY (date comes from form):

SELECT Table1.Field1 
FROM Table1 
WHERE Table1.SomeDateField = [Forms]![Form1]![TextBox1]

 

[tuktuk]

hmmmm makes sense.....but how to you get the date to change in the form. i guess i could manually change it.

T

 

[Joe4]

Is there some "hard-and-fast" rule for determining what the date should be, maybe based on the current date?

If so, we could probably determine the data dynamically. But from your original question, I can't tell if that is the case or what the rule is.

 

[tuktuk]

after further consideration i'd be better form me to type in the date on the form.

I do have a question tho.....i've never built used the criteria to link to a text box.

here is what i have.....on a form "Criteria_Dates" i have an Unbound text box within the properties of the text box i have the name set to "txt_PY_Criteria_Dates".

in the criteria line of the qry i have "<=[Forms]![Criteria_Dates]![txt_PY_Criteria_Dates]"

but i an getting an error doesnt recognize '[Forms]![Criteria_Dates]![txt_PY_Criteria_Dates]'

am i missing something?

 

[xenou]

Should work, I think.
Make sure the form is open and you've got a value in the textbox ...
I'm always leaving forms like this in design view as I test ...

 

[tuktuk]

hm,mm still same error.....i have the format set to ShortDate to match how i would have type it in on the criteria line.

i'm okay with having it bound to a qry results to. in efforts to solve this i created a qry, "qry_Criteria_PY_Dates" pulling in the MaxOfPY_Date.

in the text box i bound the control source to = "=[qry_Criteria_PY_Dates]![MaxOfPY_Date]".

if i run the qry i get the desired date.....if i open the form i see "#NAME?" in the text box.

hmmmmmmm

 

[xenou]

I'm possibly as confused as you are now.

Try it first with the text box unbound ...
In it you will type a date ...
the query will have a where clause referencing the form:
= ........ WHERE .... = [Forms]![Form1]![Textbox1]

Where Form1 is the name of your form and Textbox1 is the name of your textbox control.

Format doesn't matter - as long as you type the date in a way that Access recognizes it (in the database itself, dates are numbers, and Access will match this numeric value, not the format string).

Once this works, we'll have to figure out what went wrong with your MaxOfDate thing.

 

[tuktuk]

alright i'm getting there...

i created a qry wiht the MaxOf_Date...used the form wizard to get a text box to show the txt_MaxOF_Date.

then tried using the [Forms]![FormName]![TextBox1] as my criteria.

it game the same error when i tried to use on my QRY (a CrosstabQry that is)

so i create a simple Select to see if the date was passing thru. AND IT DID!

does this mean i cannot use this concept on a Crosstab criteria?

seems strange as in not sure why it'd work on one but not the other

 

[tuktuk]

well after further investigation, using parameters within crosstab require and extra step:

here is the article that helped out: http://allenbrowne.com/ser-67.html

Handle parameters
A query can ask you to supply a value at runtime. It pops up a parameter dialog if you enter something like this:
[What order date]
Or, it can read a value from a control on a form:
[Forms].[Form1].[StartDate]

But, parameters do not work with crosstab queries, unless you:
a) Declare the parameter, or
b) Specify the column headings.

i think this will get me there. i'm testing right now.

Thanks
Tuk