Ignore a Dynamic Field using a Switch function? (1 Viewer)

[LHolden]

Hi All-

I'm currently working on putting together an invoice in the form of a report and I'm having some trouble figuring something out.

One of the fees that our clients get charged is dynamic, but it also doesn't always exist. I'm having trouble setting up a dynamic field that Access will automatically ignore if the client does not have this fee.

The source table has a field [DynamicFee] which is a text field whose only entries are "Yes" or "No", and the report is run through a query, and pulled up using a button on a form.

I've tried everything from a switch function on the report (Switch([DynamicFee]="Yes", [FeeHere], [DynamicFee]="No",0), referencing a control on the report. I've tried the same thing referencing a control on the form, I've tried putting the switch function directly into the query, but no matter what I do the report always queries users [FeeHere].

If anyone could help me it would be greatly appreciated!

 

[CJ_London]

normally yes/no fields are yes/no, not text, so the first question has to be - is it definitely text? - they can be set up to display yes or no, but that does not make it text

 

[LHolden]

I know there is a boolean Yes/No option for fields, but that displays check boxes rather than strict "Yes" and "No" strings, which the userbase for the database did not want, so yes, it is a text field, but all of the entries are either "Yes" or "No". The Default Value for the table is "No", and any control that would affect the field in the table is a combo box which only has "Yes" and "No" in its source.

 

[vbaInet]

I'm having trouble setting up a dynamic field that Access will automatically ignore if the client does not have this fee.

Can you explain what you mean by "ignore"?

And when you say it doesn't work, what do you actually mean? It shows an error message perhaps?

 

[CJ_London]

OK - obvious thing to check first since it is a mistake many make.

In that case need to know more about your report and how you are trying to use the switch function

Can you post the SQL to the recordsource of your report plus explain where you have the switch function (in a vba event or controlsource to a control on the report etc)

 

[CJ_London]

but that displays check boxes rather than strict "Yes" and "No" strings

That is the default position but in case you didn't know, you can change the format from true/false to yes/no and the lookup from checkbox to textbox to show yes/no tho' the value stored is still -1 or 0

 

[LHolden]

That is the default position but in case you didn't know, you can change the format from true/false to yes/no and the lookup from checkbox to textbox to show yes/no tho' the value stored is still -1 or 0

Thanks for this information!

Can you post the SQL to the recordsource of your report plus explain where you have the switch function (in a vba event or controlsource to a control on the report etc)

The SQL for the record source just brings together clients and fees, so something like

SELECT tblClients.Client, tblClients.Fee1, tblClients.Fee2... tblClient.DynamicFeeField
FROM tblClients

So the invoicing shows the client, all of their fees, and then has some math controls to get to the Total amount the client owes. Most of the fees are static fees, if they want a particular service, they pay a set amount. This field is dynamic however, different clients pay different amounts.

I've tried putting the switch function in the query, in a control on the form, and also in a control on the report but not VBA:

 Switch([DynamicFeeField]="Yes", [DynamicFee], [DynamicFeeField]="No", 0)

or referencing the form from the report:

Switch([forms]![frmname]![DynamicFeeControl]="Yes", [DynamicFee], [forms]![frmname]![DynamicFeeControl]="No", 0)

Technically, the function does work, the problem is that it asks the user to define [DynamicFee] even when [DynamicFeeField] is "No". If the field is "No", then it will force it to 0, no matter what the user inputs, which is fine, but I would like the report to not even query for a value if the field is "No". Is that possible at all?

Thanks for all of your help by the way.

Can you explain what you mean by "ignore"?

See the above paragraph

 

[CJ_London]

hmm, not really providing enough information - it is generally better simply to copy and paste your code rather than retype it because typo's raise unneccesary questions

tblClients.Client, tblClients.Fee1, tblClients.Fee2... tblClient.DynamicFeeField

Is this a typo or two separate tables?

Switch([DynamicFeeField]="Yes", [DynamicFee], [DynamicFeeField]="No", 0)

What is dynamicFee? a field in a table? an unbound control?

the problem is that it asks the user to define [DynamicFee] even when [DynamicFeeField] is "No".

it will do because the whole statement is processed and it can't find dynamicFee which needs to be a field in a table in your query (assuming that is where the statement is)

tblClients.Fee1, tblClients.Fee2

Just a comment but this looks like your tables are not normalised