Opening Forms Multiple Arguements

[Lupson2011]

Hi



I have created a command button using the wizard to open to another form where it currently has one condition which is to show specific information for the organisation. The argument is as follows (TRUST1 is the org name)


INcomeMainForm, Form, , ="[TRUST1]=" & "'" & [TRUST1] & "'", , Normal

However, I want to put in another argument as not only do I want to make sure I only show specific data on the IncomeMainForm linked to the TRUST1 field, but I also want to make sure its for a specific year.

So its working on first TRUST1 field (org name), then by Year_End field (the fiscal year). I cant seem to get the right code. Is it more complicated than just fitting in another condition to the above arguement?

If anyone could help that would be great?

thanks

marc

[rpeare]

to extract the year from a date you would use

datepart("yyyy", [DateFieldName])

Then you can apply a criteria to that calculation

[Lupson2011]

Thanks for the reply, alas I dont think I explained clearly enough.

The field name YEAR_END is already existing and the data is already there in the table, as you choose the YEAR_END from a combo box in the previous form.

Basically, I have a form which you choose the organisation (TRUST1) and the financial year (YEAR_END) from combo boxes, then input the text information for that year.

What I have is a command button which , when pressed, takes you through to an income analysis form, which I want to display data not only specific to that organisation (TRUST 1), but also the correct financial year (YEAR_END)

I can get it to linked back to the TRUST1 field (first condition), but I want it to also get it conditioned tot he YEAR_END field as well

So, for example, an organisation entitled AVON, could have financial figures inputed for 2008/09, 2009/2010 on the first form. I have the first form set to 2009/2010. This means when I activiate the Income Analysis button to take me to the Income Analysis form, I want the form to open for AVON on 2009/2010, not another year.

At the moment I can get it to open to AVON, but the year is never pulled through with it. Current code below

INcomeMainForm, Form, , ="[TRUST1]=" & "'" & [TRUST1] & "'", , Normal

I need to get the field YEAR_END into the above argument somehow to make sure it opens the form set on both conditions.

Hope that made a bit more sense?

Lupson2011

[rpeare]

INcomeMainForm, Form, , ="[TRUST1]=" & "'" & [TRUST1] & "'", , Normal

if this statement works for you try:

INcomeMainForm, Form, , ="[TRUST1]=" & "'" & [TRUST1] & "' AND [FIELDNAME2] = '" & [CRITERIAFIELD] & "'", , Normal

Normally I set my forms up to be based on queries and have a similar setup to you where I use the input as criteria for a query rather than running filters (which I think is what you're doing?) but all you really need is an AND or an OR statement.

[Lupson2011]

I think thats almost there, thanks, but I am having syntax problems. Trying to work out how to get the "", & in the right order

[rpeare]

you'll have to remove the ' marks from your statement if you're not using a text field, if the field is numeric they've got to be gone or if it's a date field they have to be surrounded by # marks.

[Lupson2011]

i tried this, which essentially was the first code (which worked) dupplicated..access not happy with it!

="[TRUST1]=" & "'" & [TRUST1] & "' AND "[YEAR_END]=" & "" & [YEAR_END] & "'", , Normal

[Lupson2011]

Well I got this argument to be accepted

INcomeMainForm, Form, , ="[TRUST1]=" & "'" & [TRUST1] & "' AND "" & [YEAR_END]=" & "" & [YEAR_END] & "", , Normal

Thought had cracked it, but when clicked on the command buttong got the 'the object doesnt contain the automation object 'YEAR_END'

damn so close!

[Lupson2011]

ok got over that problem, now using the following code I get the error below

INcomeMainForm, Form, , ="[TRUST1]=" & "'" & [TRUST1] & " AND "" & [YR_END]=" & "" & [YR_END] & "", , Normal

the error is syntax error in string in query expression.

i cant see which bit I have got wrong, anyone help?

Lupson2011

[rpeare]

INcomeMainForm, Form, , ="[TRUST1]=" & "'" & [TRUST1] & " AND "" & [YR_END]=" & "" & [YR_END] & "", , Normal

Is your YR_END field a text value or a numeric value? and you have removed the trailing ' mark from your TRUST 1 being identified as a text field

if TRUST1 is a text field and YR_END is a text value

INcomeMainForm, Form, , ="[TRUST1]= '" & [TRUST1] & "' AND [YR_END]= '" & [YR_END] & "'", , Normal

if TRUST1 is a text field and YR_END is a numeric value

INcomeMainForm, Form, , ="[TRUST1]= '" & [TRUST1] & "' AND [YR_END]= " & [YR_END], , Normal

[Lupson2011]

brilliant, problem solved.

thanks rpeare. Really appreciated your help

Lupson2011