Dlookup problem

[JeroenMioch]

I am trying to Dlookup two fields with dates in them. If the Dlookup returns that today is in the range (e.g. 06-03-2015 in the first field and 10-03-2015 in the second) then i want the system to display a messagebox when im opening up a form.
If today is not in the range then the messagebox should not pop up.

If DLookup("HollidayFrom", "tblBuildingManager", "[HollidayFrom] >= Date()") And DLookup("HollidayTill", "tblBuildingManager", "[HollidayTill] <= Date()") Then
msgBox "A building manager is on holliday"
end if

The table looks like this :

BuildingManager	ManagerID	Phone1	Phone2	Phone3	HollidayFrom	HollidayTill
George Bush	1	06-11111111	06-11111112	06-11111113	1-4-2015	9-4-2015
Bill Clinton	2	06-22222222	06-11111114	06-11111115	30-8-2015	19-9-2015

[ranman256]

You have DATE() inside the dbl quote. It must be outside it to resolve into a date.

[pbaldy]

I think your <> are mixed up. You may have an international dates issue, but perhaps not with Date() inside the quotes. If so:

http://allenbrowne.com/ser-36.html#Format

[CJ_London]

You need to compare your dlookup with something otherwise you will get

if 02/04/2015 then
...

dlookup returns null if no record is found so use

if not isnull(dlookup(....)) then
....

Alternatively you could use dcount and compare it with a value - also you can use between to simplify the code a bit . Having Date() within the quotation marks shouldn't matter since it is a function

If DCount("*", "tblBuildingManager", "Date() between [HollidayFrom]  and [HollidayTill]") >0 Then
    msgBox "A building manager is on holliday"
end if

[JeroenMioch]

Thanks Ajax, your Dcount works flawlessly !

Now im trying to expand this by informing the user wich building manager is on holliday.
Could you point me in the right direction for that one as well ?

Thanks for your time mates !

[CJ_London]

Now im trying to expand this by informing the user wich building manager is on holliday

You would be better to state your final objective rather than moving step by step

Dim Person as string
person=nz(DLookup("[BuildingManager]", "tblBuildingManager", "Date() between [HollidayFrom]  and [HollidayTill]"),"")
 if person<>"" Then
    msgBox person & " is on holliday"
end if

Note you will need a completely different solution if more than one manager can be away at the same time and you need to list them

[JeroenMioch]

You are completely right, my appologies mate.
Your solution works like a charm and i thank you !