The expression you entered as a query parameter produced this error in query. (1 Viewer)

[trieudat191]

Hello everyone ,

I am a newbie to Access. I have used this forum for 2 months and find it very useful. Most of the time, I do just follow the instruction from the posts and it works.

However, today, I am not that lucky. When I use the following code:

=DLookUp("[Price of Gasolio at Sedes]![EURO/LITRI]";"[Price of Gasolio at Sedes]";"[Codice_installazione] = [Price of Gasolio at Sedes]![SEDE] and [UniqueDate]>= [Price of Gasolio at Sedes]![Unique_StartDate] and [UniqueDate]< [Price of Gasolio at Sedes]![Unique_EndDate]")

It pops up the following error:"The expression you entered as a query parameter produced this error: Microsoft Access cannot find the name 'Codice_installazione' you entered in the expression".

It is interesting that although the Error MessageBox pops up in both cases (Form and Query), the results are returned in the Form, while the Query show blanks. :banghead: I need the results shown in the Query for further steps.

All the multiple criteria are in number format and I used copy and paste the fields to avoid misspellings.

Any suggestion is highly appreciated. Thanks in advance.

 

[trieudat191]

I have just recognized that the error pops up in Form because of the error in the Query (I have the Query as the Record Source of the Form). So there is no error at all if I run the same code in the Form, but got an error in Query. It's strange!

 

[plog]

Do you have a DLookup in your query? If so, that's not right. There's no need for that, you simply link the table you are using in the Dlookup in the query and retrieve the value that way.

 

[trieudat191]

Hi plog,

Thank you very much for your prompt reply. Yes, I do have a DLookup in my query. I still do not understand what you mean. But my "domain" in the DLookUp is another query: [Price of Gasolio at Sedes], I need to retrieve the value of [EURO/LITRI] in that query with the criteria that if the [UniqueDate] in the current query (named Erogazioni) falls between the [Unique_StartDate] and [Unique_EndDate].

Could you please clarify what you mean?

 

[plog]

First, you really need to name your objects (field/names/queries) better. You shouldn't have spaces in names.

Now for this issue, in design view of your query, bring in [Price of Gasolio at Sedes]. Then link it to the existing table in your query by the two fields.

There's going to be another step, but do that and let me know you have it done.

 

[trieudat191]

I will rename them better afterwards. Thanks for your advice.

I see what you mean now. But the 2 tables ([Price of Gasolio at Sedes] and Erogazioni) do not have any field in common so that I cannot join them. What I have in the Erogazioni table is the Date and in [Price of Gasolio at Sedes] is the Interval of date (with two fields: [Unique_StartDate] and [Unique_EndDate]). What I need to use the DLookUp is that, if the Date falls in the Interval 1, then there will be Price 1, if the Date falls in Interval 2, then Price 2.

I look forward to your further explanation.

 

[plog]

Link that one field to both dates of the interval.

Then you will go into SQL view of the query--this will be the text the designer generated. In it there will be an INNER JOIN section. In that section you will see your date fields set equal to the other date. You will change the = sign to >= and <= as appropriate. Save it and then that's your query.

 

[trieudat191]

It works just great! You are amazing!!!! Thank you very much!