Query Paramater Connection

[magicmike92]

Hi There,



I have two queries. One has a date criteria. I want the other one to recognize the date in the first query, and subtract 12 months from it. So essentially one query will have todays date this year and the second query will have todays date last year but I only have to input the date into the first query.

Thanks!

MagicMike92

[pbaldy]

Personally I'd use a form for the user to enter the date. Then both queries can use it, the second using DateAdd() to subtract.

[magicmike92]

Great! Thanks!

Also, do you know how to subtract an alphanumeric value? For example, it is currently F13, and when I plug that into a Form, I want another drop down box to automatically change to F12 to represent last fiscal year. Does that make sense?

Thanks!

[pbaldy]

You'd probably need to peel out the numeric part, subtract, and add the letter back, like:

?"F" & mid("F12",2) - 1
F11

[magicmike92]

I don't quite understand. Would I just plug that into the control source? How do I add the reference to the F13 combo box?

Thanks!

[pbaldy]

As a control source, something like:

="F" & Mid(FieldName, 2) - 1

[magicmike92]

Thank you!!

[pbaldy]

Happy to help, and welcome to the site by the way!

[magicmike92]

Thanks!

Ok also, I am trying to make a combo box dependent on another so that the choices of the second one are limited depending on what is chosen for the first combo box. Do you have any suggestions on how to go about doing this?

Thanks!

[pbaldy]

Like this?

Baldy-Cascading Combos

[magicmike92]

yeah kind of but if I put the lists in as Values, where do I write them out?

[pbaldy]

I don't know. I rarely use value lists as having the data in a table is more dynamic and maintainable. Other than hard-coding values into code, I can't think of a way to do it with value lists.

[magicmike92]

Can I just make another table with the 2 lists and reference that?

[pbaldy]

I don't know the nature of the data, but probably.

[magicmike92]

It's dates corresponding to a year