PDA

View Full Version : Solved: SQL Problem



gibbo1715
11-05-2005, 03:34 AM
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
11-05-2005, 05:42 AM
Can't terst, but I think this will work


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

Norie
11-05-2005, 07:59 AM
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
11-05-2005, 08:11 AM
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
11-05-2005, 08:57 AM
Thanks all,

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

Gibbo