Calculated field (1 Viewer)

[artefact]

Hello,
I have an access query in which I'm using a calculated field based on a cuom function as shows:

Statut: statutelmtmrp([ZPRPP01].[Elément MRP];[ZPRPP01].[Date besoin])

The query runs well, but when I enter a something in the criterion field, access pops me a message saying that I've got data type issue. It turns out that data tye of the function is string even if I forced the converstion (using cstr function), it still raises me the error message.

Is it possible to use the criterion field for a caculated field?

Thanks for your help.

Arte

 

[plog]

Is it possible to use the criterion field for a caculated field?

Yes, but you have to use the right datatype.

Can you show the SQL of your query and the declaration line of your function?

 

[plog]

Is it possible your function is returning a NULL value?

 

[artefact]

Hello,

Thanks for your quick reply.
As requested, here is the function declaration:

Function statutelmtmrp(elmtmrp As String, datebesoin As Date) As String

SELECT zprpp01.Article, zprpp01.[Désignation Article], zpuddp05.Indice, zpuddp05.[Lead Time], zprpp01.[Document d'achat], zprpp01.[Numéro du poste], zprpp01.Quantité, zprpp01.[Date besoin], anneesemaine([ZPRPP01].[Date besoin]) AS [semaine de livraison], anneesemaine(Date()) AS [Semaine reporting], statutelmtmrp([Elément MRP],[Date besoin]) AS Statut INTO [Carnet de commande]
FROM (zprpp01 LEFT JOIN zpuddp05 ON zprpp01.Article = zpuddp05.Article) LEFT JOIN Gestionnaire ON zprpp01.Gestionnaire = Gestionnaire.[Numéro gestionnaire]
WHERE (((statutelmtmrp([Elément MRP],[Date besoin]))="Quantité") AND ((zprpp01.[Fournisseur designation])="EOLANE NEUILLY-EN-THELLEEMELEC TECH") AND ((zprpp01.[Date besoin])<Now()+1825 Or (zprpp01.[Date besoin]) Is Null) AND ((zprpp01.[Elément MRP])<>"D.A." And (zprpp01.[Elément MRP])<>"Lot QM"))
ORDER BY zprpp01.[Fournisseur designation], zprpp01.Article, zprpp01.[Document d'achat], zprpp01.[Numéro du poste], zprpp01.[Date besoin];

and in red the statement that raises the issue.

thank you very much for your precious help.
Arte

 

[plog]

My guess is this isn't the criteria being applied, but the arguments being sent. I bet [Element MRP] or [Date besoin] is null in one of the records in the underlying data sources. Or possibly they aren't the right datatypes that the function expects.

You can test this with a query. Create a query based on the tables/queries that [Element MRP] and [Date besoin] are in. Bring down each field, then add 2 calculated fields for each--one to test to see if the field is null (IsNull) and then one to test to make sure its the data type you expect (IsNumeric, IsDate, etc.).

 

[artefact]

Hello Plog,

Thanks a lot! You were right. Now, I test the parameters before passing value to the function!!

Have a nice day.

Arte