Dlookup with multiple criteria

[jeffwest2]

I have this

If Not IsNull(strCount = DLookup("[Number_Of_Records]", "All_Booked_Callbacks ", "[CallBack_Date] =#" & Me.CB_DAte.Value & "#" _
        & " And [CallBack_Time] = #" & Me.CB_Time.Value & "#")) Then strCount = DLookup("[Number_Of_Records]", "All_Booked_Callbacks ", "[CallBack_Date] =#" & Me.CB_DAte.Value & "#" _
        & " And [CallBack_Time] = #" & Me.CB_Time.Value & "#") Else strCount = "0"

All_Booked_Callbacks is a query which has a date and time columns and count of callbacks for that day and time, this always returns 0

 

[namliam]

Not too surpriced your inline if doesnt work as intended....
For starters, limit the use of DLookups and DCounts and all that stuff
Second limit the use of inline If statements.

try using something like:

dim MyVar as variant

myvar = DLookup("[Number_Of_Records]", "All_Booked_Callbacks ", "[CallBack_Date] =#" & Me.CB_DAte.Value & "#" _
                                                         & " And [CallBack_Time] =#" & Me.CB_Time.Value & "#")

If Not IsNull(myvar) Then 
    strCount = myvar 
Else 
    strCount = "0"
endif

Which one might find a lot more readable as well.

 

[jeffwest2]

Thanks for this, only thing it still is returning 0 when I know that the date and time I am adding in are in the results of the query :-(

 

[namliam]

are you entering your date in a text box by chance and in another format than MM/DD/YYYY???

Also time in reality is always a date/time are you sure it is only the time you are looking at? and not a date/time formatted to show only time

Also time is a rounding to seconds it can in actual fact contain miliseconds as well.

 

[George21]

[FONT=&quot]Try this:

[/FONT]If Not IsNull(strCount = DLookup("[Number_Of_Records]", "All_Booked_Callbacks", "[CallBack_Date] = #" & _
Format(Me.CB_DAte, "mm/dd/yyyy") & "#" & " And [CallBack_Time] = #" & Format(Me.CB_Time, "HH:MM:ss") _
& "#")) Then strCount = DLookup("[Number_Of_Records]", "All_Booked_Callbacks ", "[CallBack_Date] = #" & _
Format(Me.CB_DAte, "mm/dd/yyyy") & "#" & " And [CallBack_Time] = #" & Format(Me.CB_Time, "HH:MM:ss" & _
"#") Else strCount = "0"

 

[jeffwest2]

George thanks that worked a treat.

 

[George21]

George thanks that worked a treat.

You are welcome! Happy I could help.

 

[namliam]

exactly the problem of abusing Dlookup.
You are using 2 times the same DLookup which performance wize is a nightmare!