Date search

[BelleS]

Hi,
Im trying to have a search page in my form where users can select one field or both for a search criteria which will open another page to display results. My first field"Payment subject" works fine on its own, but my date field is not working, is anyone able to help me with this small problem??

If IsDate(txtDueDate) Then
strSql = "SELECT Payment.* FROM Payment" _
& " WHERE Format([Due Date],'dd-mmm-yyyy')= #" _
& Format(txtDueDate, "dd-mmm-yyyy") & "# "
Else
'''''dateSearch = False ' if this is a boolean function it will be false by default
txtDuetDate.SetFocus
MsgBox "You must enter a valid date first"
Exit Sub
End If

The error i get it run time error saying sql expression is incorrect and it goes to debug this error:
If dateSearch = True And subjectSearch = False Then
=> DoCmd.OpenForm "Select Payment", acNormal, , strSql
End If

 

[cmmrfrds]

If you are sending the SQL Statement to SQL Server then it is necessary to use ANSI SQL syntax not Access syntax. SQL Server uses Transact SQL which was originally developed by Sybase when they where supporting the SQL Server product for Microsoft.

Look up the Convert function is the SQL Server documentation. Convert(data type,thefield,the style)

strSql = "SELECT Payment.* FROM Payment" _
& " WHERE Format([Due Date],'dd-mmm-yyyy')= #" _
& Format(txtDueDate, "dd-mmm-yyyy") & "# "

strSql = "SELECT Payment.* FROM Payment" _
& " WHERE Convert(varchar(10)[Due Date],101)= '12/31/2004'