Solved: SQL Problem

[gibbo1715]

I have a userform in excel which links to an access database and runs an sql query giving the results in a userform

I use the code below for the query

strSql = "SELECT * From Applications WHERE Applicant= '" & txtSearch & "';"

What I need to be able to do is run an SQL query that checks if column due_Date is before today and Column Submitted_Date is not a null value (i.e. blank)

I know this is not strictly an excel question but my interface is excel based and you ve helped me with similar in the past

cheers

Gibbo

[Bob Phillips]

Can't terst, but I think this will work

[VBA]
strSql = "SELECT * From Applications " & _
"WHERE Applicant= '" & txtSearch & "' AND " & _
" due_Date < #" & Format(Date, "dd/mm/yyyy") & "# AND " & _
" submitted_Date <> ''"
[/VBA]

[Norie]

Gibbo

The best way to get this SQL would be to query in Access.

Then copy it Excel VBA, altering it as needed.

[XL-Dennis]

Gibbo,

As Bob shows the date expressions must be placed between # and where we use the logical expressions (>,<,<>,= etc). Pay also attention to the apostrophe in use.

Given that the approach is adapted Norie points out a good way to play around with SQL-expressions.

Kind regards,
Dennis

[gibbo1715]

Thanks all,

Tried Norie's approach and edited it as per my needs

Gibbo