Textbox Control Source IIF statement

[eacollie]

I'm trying to set the control source of a textbox on a form with the following:

=IIf(DLookUp("[Room]","tblTEMPEventRooms","WHERE #" & Me.DateOccupied & "# BETWEEN [StartDate] and [EndDate]) AND [StartDateTime] =8")=122,1,0)

But it gives #Error. Is it the quotation marks?
[DateOccupied} is a textbox (format short date)
StartDate and EndDate are fields in my table tblTEMPEventRooms (short date)
StartDateTime is a field in the same table (medium time)

 

[pbaldy]

For starters, the criteria should be a valid SQL WHERE clause without the word WHERE. You also have an extra parentheses in there. Try

DLookUp("[Room]","tblTEMPEventRooms","#" & Me.DateOccupied & "# BETWEEN [StartDate] and [EndDate] AND [StartDateTime] =8")

8 seems like an odd value for a field titled "StartDateTime".

 

[eacollie]

Thank you pbaldy. It now shows#Name?. StartDateTime is Date/Time and I'm trying to select 8:00 AM.

 

[pbaldy]

Start with just the DLookup until you have that working. Since it's a date/time value:

DLookUp("[Room]","tblTEMPEventRooms","#" & Me.DateOccupied & "# BETWEEN [StartDate] and [EndDate] AND [StartDateTime] = #8:00#")

I'm trying to remember if Between is valid in a domain function.

 

[eacollie]

Thank you. it must have something to do with that because when I use just the time criteria (as you just gave to me) it works.

 

[pbaldy]

It doesn't work with Between? Try

DLookUp("[Room]","tblTEMPEventRooms","[StartDate] <= #" & Me.DateOccupied & "# and [EndDate] >= #" & Me.DateOccupied & "# AND [StartDateTime] = #8:00#")

 

[eacollie]

Comes back #Name?
Maybe I have to format the dates?

 

[pbaldy]

Can you attach the db here? You put an = before the formula, right?

 

[Mark_]

As you are looking for a "Room", please remember that DLookup will return a single value from a single record that matches, even if you have multiple matching records. It will also return NULL if you have NO matching value.

You may want to make a query that limits to "Room = 122", has your StartDate and EndDate, then use DCount to tell how many matches you have.

I am guessing this is very specifically checking if room 122 is occupied/free for a given date range. Since you'll probably want the same logic for other rooms I'd have the room number come from the same source as your StartDate/EndDate.

 

[eacollie]

Hope I did this correctly and that all the info you need is there.

 

[Mark_]

Can you please explain what you are trying to do, in simple business terms? Don't worry about using database terms, just simple English.

At a guess, you are attempting to show when a room is in use, in some type of calendar, correct?

 

[eacollie]

Thank you Mark. Yes, the room number is coming from the same table as the StartDate and EndDate and StartDateTime.

 

[eacollie]

Sorry Mark. I was typing my reply when you posted.
This is part of a form in which I'm trying to display which rooms are occupied/day and time period. Each textbox represents an hour's time period and will turn "red" when the room n occupied (with conditional formatting).

 

[pbaldy]

I'm an idiot. You can't use Me outside VBA:

=DLookUp("[Room]","tblTEMPEventRooms","[StartDate] <= #" & [Forms]![frmRoomPlanner2].[DateOccupied] & "# and [EndDate] >= #" & [Forms]![frmRoomPlanner2].[DateOccupied] & "# AND [StartDateTime] = #8:00#")

 

[eacollie]

It still comes up #Name? Did it work on the sample database I uploaded? I'm probably doing something very basically wrong!

 

[pbaldy]

This returns a blank with test date of 1/6/18, returns 141 with a test date of 1/25/18:

=DLookUp("[Room]","tblTEMPEventRooms","[StartDate] <= #" & [Forms]![frmRoomPlanner2].[DateOccupied] & "# and [EndDate] >= #" & [Forms]![frmRoomPlanner2].[DateOccupied] & "# AND [StartDateTime] = #8:00#")

So the forumla works, but I agree with Mark that there may be better ways of doing what you want. I'll let him follow up since it was his idea.

 

[June7]

BETWEEN AND definitely works in domain aggregate.

Suggest you set DefaultValue property of DateOccupied to Date().

Remove Me. from the expression in second textbox.

Use correct DLookup expression in the first textbox.

 

[Mark_]

I think you will need to try a different approach if EACH textbox represents one hour.

Can one room be booked more than once per day?

Will you be looking at it for more than one day at a time?

 

[Mark_]

OK, I see something that makes absolutely no sense to me in your table definition.

Why do you have both a "StartDate" and a "StardDateTime"? Same with "EndDate" and "EndDateTime"?

StartDate should contain both the date portion as well as the time portion. Same with EndDate.

 

[eacollie]

Thank you.
Yes, a room can be booked more than once/day.
The form is designed to look at one day at a time.
I've been struggling with this design for quite some time and can't seem to come up with a solution. Any suggestions would be greatly appreciated!