Compound in a DLookUp function

[Lou_Reed]

In these three formulas for the Ceil tutorial found here:



http://www.functionx.com/access/appl...ns/ceilinn.htm

one of the formulas is:

=IIf(IsNull([RoomNumber]),"",DLookUp("RoomType","Rooms","RoomNumber= '" & [RoomNumber] & "'"))
If one were interested in BedType or Rate then simply substitute that term for RoomType.



Now I am somewhat familiar with how DLookUp works. In other words get RoomType from a table Rooms where there is a given RoomNumber.

Where I get confused is the last clause:

"RoomNumber = '" & [RoomNumber] & ""

I realize it is a compound statement hence the &, but it uses two &s and more. Please explain what they are.

Any help appreciated. Thanks in advance.

Respectfully,


Lou_Reed

[June7]

Parameters for a text field require apostrophe delimiters. The second & is to concatenate the closing apostrophe, which is seen in the IIf() expression but not in your extract.

[CJ_London]

for this dlookup to work, the roomnumber datatype must be text - which needs to be surrounded with single quote marks (highlighted red)

"RoomNumber= '" & [RoomNumber] & "'"

if roomnumber datatype is actually number then the code would be

"RoomNumber= " & [RoomNumber]

you can check your datatype in the Rooms table.

[Lou_Reed]

I have copied from the Ceil Inn tutorial shown here

http://www.functionx.com/access/appl...ns/ceilinn.htm

and I have copied these formulas from the tutorial exactly. It is still not working.

I do not there is any variable undefined here.


Maybe if I try and make the RoomNumber datatype a number instead of text and use
the second formula.

I see what happens.


Respectfully,


Lou_Reed

[CJ_London]

It is still not working.

not helpful, your question was about the '&'s - perhaps you are trying to find a room that does not exist or the roomtype is blank

[Lou_Reed]

It does work! I spoke too soon. It just works in a way that I did not expect.

Let me explain:

Working on the NwOccupancy Form:

I type in employee number and the employee name pops up in the assigned space next to it, instantly.

I type in customer number and the customer name pops up in the assigned space next to it, again instantly.

I type DateOccupied and nothing happens - I did not expect it would.

I type in Room Number on the NewOccupancy form and again nothing happens. I expected it would
show all information about the room. It does not.

However, if I then open the Occupancy form all of the info is there - all of it. Even the room info is there.
it is just not in the NewOccupancy form.

The room rate, room type, and bed type are all there - on the Occupancy form. As I said they are missing on the
NewOccupancy form. This outcome makes me question what is supposed to pop after an identifying number (Employee #, Customer Accnt. #
and Room Number is typed.

As I said on the NewOccupancy form the first two inputs work and the third one does not - go figure.

Also, the Occupancy Table is missing any and all info that should be there once the room number is known.

What should pop and why (also how do you make it popup it currently does not) when you type in a some
identifying number?

Any help appreciated. Thanks in advance.

Respectfully,

Lou_Reed

[CJ_London]

sorry, I am not familiar with the Ceil tutorial and do not have time to investigate it. So no idea what should pop up and why - I would have thought that is something the tutorial would take you through (perhaps later) or it is for you to decide what you want to pop up and write the code accordingly

[Lou_Reed]

I got it to work, but by the most indirect route.

It is true that typing in the room number would not allow an immediate popup on the NewOccupancy Form.
There is nothing in the tutorial that says it should, it is just that it happens on the previous two inputs when relevant info
pops up after an Employee ID number or a Customer Accnt. number is input.

Inputting the room number next at first evinces no pop up output. It is all on the Occupancy Form, however, and if one continues to the
next data input on the NewOccupancy form and input data, and then steps back one data point, the output is now there.

I do not know why it works; in the Ceil Inn tutorial its is clear when these popups occur because they are correct
and relevant.

Why in the world they do not all work instantaneously is a mystery to me. I try to find out why.


Respectfully,


Lou_Reed