DLookup vs db.OpenRecordset(strSQL, dbOpenSnapshot)

[True.Kilted.Scot]

I have a database which records dates & times worked. With the date
worked field, I want to validate it against previous entries, with the
idea being to prevent users from entering a date that has previously
been "Submitted", via a submit button--a boolean field in each record
for the week, is set to TRUE when this is done.

At the moment, I have the following code:

strSQL = "SELECT * from tblTimeSheet " & _
"WHERE (fldDateWorked >= #" & Format(datWkStart,
"mm/dd/yyyy") & _
"# AND fldDateWorked <= #" & Format(datWkEnd,
"mm/dd/yyyy") & _
"#) AND (fldEmployeeID = " & Me!txtEmployeeID.Value &
");"

Set db = CurrentDb
Set rstPrevious = db.OpenRecordset(strSQL, dbOpenSnapshot)
rstPrevious.MoveFirst
If rstPrevious!fldSubmitted.Value = True Then
{ Display a message box saying invalid date }

However, I was wondering if a DLookup would be better in this case.

I have tried the following:

strSQL = "(fldDateWorked >= #" & Format(datWkStart, "dd/mm/yyyy") &
"# AND " & _
"fldDateWorked <= #" & Format(datWkEnd, "dd/mm/yyyy") &
"#) AND " & _
"(fldSubmitted = TRUE) AND " & _
"(fldEmployeeID = " & Me!txtEmployeeID.Value & ")"
varID = DLookup("[fldWorkID]", "tblTimeSheet", strSQL) <> Null
If Not IsNull(varID) Then
MsgBox "invalid entry"
End If

However, when I execute and trace the code, varID is equal to null,
even althogh I use a date that has previously been submitted.

So, is DLookup better to use than what I am using currently and if so,
what is wrong with my DLookup statement?

Many thanks

Duncs

 

[Douglas J Steele]

What's wrong with your DLookup is that you used dd/mm/yyyy as the date
format. Regardless of what your Short Date format has been set to in
Regional Settings, Access will not use dd/mm/yyyy format correct if the date
is in the first 12 days of the month: it will ALWAYS assume that it's in
mm/dd/yyyy format. After that, since there is no 13th month, Access will
interpret it correctly.