invalid date syntax when using findfirst

[aft3rgl0w]

hey all, here's my problem: i have:
table: for distribution of disks
form: has a listbox that contains Number, Company, Name and Date from Distr.
table, and functions to open the selected record
problem: i have no key column in my table, for various reasons, so i've
modified the default code Access creates for the listbox to include matching
all 4 fields in the listbox, not just the first. works great except when i
try clicking on an entry that has no date entered. i get a "syntax error in
date" when i click on one of these. how can i modify my code so i include
the date field but not get this error when no date is present/ thanks in
advance!!

Private Sub List10_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[D4_Serial] = " & Str(Me![List10]) & " AND [orgn] = '" &
Me!List10.Column(1) & "'" & _
" AND [name] = '" & Me!List10.Column(2) & "' AND [date]
= #" & Me!List10.Column(3) & "#"
Me.Bookmark = rs.Bookmark
End Sub

 

[George Nicholson]

One approach (aircode):

strSQL = "[D4_Serial] = " & Str(Me![List10])
strSQL = strSQL & " AND [orgn] = '" & Me!List10.Column(1) & "'"
strSQL = strSQL & " AND [name] = '" & Me!List10.Column(2) & "'"

If IsNull(Me!List10.Column(3)) then
'Do nothing, OR maybe:
strSQL = strSQL & " AND [date] Is Null"
Else
strSQL = strSQL & " AND [date] > = #" & Me!List10.Column(3) & "#"
End If

rs.FindFirst strSQL

HTH,

 

[Steve Schapel]

Aft3rgl0w,

I think this will do it:

.... " AND [name] = '" & Me!List10.Column(2) & "' AND ([date] Is Null Or
[date] = #" & Me!List10.Column(3) & "#)"

By the way, as an aside, 'name' and 'date' are Reserved Words (i.e. have
a special meaning) in Access, and as such should not be used as the
names of fields or controls or database objects. Not critical, but it
is recommended to change these if possible.