running code/module/function from Crystal Reports in Access

[gbfell]

Is there a way for Crystal to call a function or run some VBA code in Access?

I have a dynamic query that i need to make each and every time a report is ran, i could probably make a function to make the query in Access, but how to call it?

 

[JoeCrystal]

Can you explain what your dynamic query is doing. Are you dynamically building fields in your select statement, changing you Where clause, etc.

 

[gbfell]

building fields in the select statement. it needs to be able to select from column a and b one time, and another time select from column c or d.

 

[JoeCrystal]

In access you could built a query with parameter and build your select column so they are something like:

Field1: IIf([Parameter]=1,Table!FieldA,Table!FieldA)

Where [Parameter] is the query parameter.

You will have to connect via OBDC to see the parameter queries. Using this sample if you set Parameter=1 Field1 will be equal to FieldA; if other than 1 than Field1 will be equal FieldB.

The fields that you are selecting for a column should be the same size and type. You can also use the Access built in function Choose to allow to select more than two fields. I have try in the past to use VBA function I built to do a more complex selection but Crystal had problems. The query worked okay in Access but Crystal get complaining about the query not having any fields.