Dlookup returns blank :(

[ahmed_optom]

I am making a very simple diary.

I have made a table, it has ID, Patient ID, AppointmentDate and SlotNumber.

all im trying to do is have a text box that will fill with patient ID if the slotnumber and date is checked.

This is what im using now :

=DLookUp("[Patient ID]","[Diary]","[AppointmentDate]=#" & [Text13] & "# and [SlotNumber]=1")

For some reason it works only about 10% of the time. the rest of the time its just returns a blank, no error.

What I have noticed it works for the first use of the form, but if add another entry to the table for a different date, then it doesnt return anything. It only seems to work with one entry into the table. Obviously not useful

The way the form is organised, means there will only ever be one appointment for each day in each slot.

Any ideas?

 

[arnelgp]

=DLookUp("[Patient ID]","[Diary]","[AppointmentDate]=" & Format([Text13], "\#mm\/dd\/yyyy\#") & " and [SlotNumber]=1")

 

[Minty]

Are your dates UK style by any chance?

Try
=DLookUp("[Patient ID]","[Diary]","[AppointmentDate]=#" & Format(Me.[Text13],"yyyy/mm/dd") & "# and [SlotNumber]=1")

Worth a read here http://allenbrowne.com/ser-36.html for the how's and why's !

 

[ahmed_optom]

thanks guys.

I wish I could get back the 3 hours of headaches...

 

[arnelgp]

I have Panadol and Tugmuloron, which one you prefer?