D Lookup with Multiple Criteria

[Paul.]

Hi Everyone,

Here is my problem:

I am receiving every month some Hotel Guest Surveys consolidated in a Table
called GSTS_MTH_Reports with the following fields:

*GSTSQuestID (number)

*MHRReportMonth (number, 1 to 12)

*HotelID (a 4 letters ID)

*3MRAGSS (the score for the question)

to explain, each record gives a score obtained for an hotel for a month and
for a given question.

I would like to have an additional column/field giving me for each record
the score obtained for the previous month.

I am trying to do this by a Select query which contains all the fields from
this table
and an additional field with a DLookup expression as follow input with the
expression builder as follow:

Expr1:
DLookUp("[GSTS_MTH_Reports]![3MRAGSS]","[GSTS_MTH_Reports]","[GSTS_MTH_Reports]![GSTSQuestID]="
& [GSTSQuestID] & "And [GSTS_MTH_Reports]![HotelID]=" & [HotelID] & "And
[GSTS_MTH_Reports]![3MRAGSS]+1=" & [3MRAGSS])

When I run the query, in my new column I get an #Error in the whole column

When I click a record also get an error message box telling me:

"The Object does not contain the Object 'SINHB."
(SINHB being the Hotel ID for this record)

I really don't know what's wrong, and since I am quite new to Access I might
be also having a wrong approach

Please help me ! I am desperate !

Paul

 

[George Nicholson]

1) Careful about spaces following variable concatenation. Things like
- ([HotelID] & "And") will translate as 'IDofHotelAnd' (what you have
now)
- ([HotelID] & " And") will translate as 'IDofHotel And' (what you want)

2) numeric and text values are handled differently in concatenation.
" And [HotelID]=" & [HotelID] & " And ....
is correct *if* [HotelID] is a numeric field. If it is a text field, you
need to add single quote delimiters. It would be:
" And [HotelID]='" & [HotelID] & "' And ...
Note: singlequote, doublequote, ampersand [HotelID] ampersand,
doublequote, singlequote, space....

3) Since [GSTS_MTH_Reports] is specified as the dlookup domain, you don't
need to preface each field name with that info, although it probably isn't
hurting anything.

HTH,