Dlookup syntax and datatype

  • Thread starter Thread starter Bonnie
  • Start date Start date
B

Bonnie

I am getting a datatype mismatch error on the statement
below. The datatypes are text for both [StudentNo] and
[YearEnd] but the [SuspensionDate] is a date field. I
have tried to change the quotes and variable types but end
up with syntax errors. Can datatypes be mixed in dlookups

varSuspensionDate = DLookup
("[SuspensionDate]", "tblSuspension", "[StudentNo] = " &
strStudentNo & _
" And " & "[YearEnd] = " & strYear & " And "
& "[SuspensionDate] = " & strTxtSuspensionDate)
 
I am getting a datatype mismatch error on the statement
below. The datatypes are text for both [StudentNo] and
[YearEnd] but the [SuspensionDate] is a date field. I
have tried to change the quotes and variable types but end
up with syntax errors. Can datatypes be mixed in dlookups

varSuspensionDate = DLookup
("[SuspensionDate]", "tblSuspension", "[StudentNo] = " &
strStudentNo & _
" And " & "[YearEnd] = " & strYear & " And "
& "[SuspensionDate] = " & strTxtSuspensionDate)
Click to expand...

You do need either ' or " delimiters for the Text fields, and # as a
delimiter for the Date/Time field. Try:

varSuspensionDate = DLookup
("[SuspensionDate]", "tblSuspension",
"[StudentNo] = '" & strStudentNo & _
"' And [YearEnd] = '" & strYear & _
"' And [SuspensionDate] = #" & strTxtSuspensionDate & "#")

Note that also don't need to pull each little word out as a separate
string constant; you want the final string to contain (say)

And [SuspensionDate] = #3/18/2003#

but the " And [SuspensionDate] = #" portion doesn't change so it can
all be one string constant.
 
Dear Bonnie,

You must have # around the date (if you store the date as
text must you format the string as valid date within the
##):
varSuspensionDate = DLookup
("[SuspensionDate]", "tblSuspension", "[StudentNo] = " &
strStudentNo & _
" And [YearEnd] = " & strYear & " And [SuspensionDate] =
#" & strTxtSuspensionDate & "#")

If you store the StudentNo as string then must you set ''
around it. Same if YearEnd is stored as string as I have
done in following example:
varSuspensionDate = DLookup
("[SuspensionDate]", "tblSuspension", "[StudentNo] = '" &
strStudentNo & _
"' And [YearEnd] = '" & strYear & "' And [SuspensionDate]
= #" & strTxtSuspensionDate & "#")

Enjoy!
Arni Laugdal, MMI
 
Back
Top