SQL table join

[Doctor Kronenbo]

Hi,
I'm trying to join together two tables "tblApplications and tblApplications1" in an SQL statement to combine results from the both of them. The present SQL statement used on just the one table is as follows:

varSQL = ""
varSQL = "SELECT tblApplications.* FROM tblApplications WHERE ( [dteIDOC] > #03/31/2005# and ([txtAction] = 'A' or [txtAction] = 'R' or [txtAction] = 'T' ) "
varSQL = varSQL & varNationality & " and "

varSQL = varSQL & " ( DateValue([dteActioned]) > #" & Format(DateValue(dteS - 1), "mm/dd/yyyy") & "# and DateValue([dteActioned]) < #" & Format(DateValue(dteF + 1), "mm/dd/yyyy") & "# )"
varSQL = varSQL & " and AgeInYears([dteDOB],[dteActioned]) > 17 "
varSQL = varSQL & varOffices
varSQL = varSQL & ") ORDER BY tblApplications.intStatus, tblApplications.dteApplicationRecd;"

I've tried everything I can think of but keep getting parameter or syntax errors...

 

[SQL_Hell]

Hi,

only a brief check but I noticed a missing AND

varSQL = ""
varSQL = "SELECT tblApplications.* FROM tblApplications WHERE ( [dteIDOC] > #03/31/2005# and ([txtAction] = 'A' or [txtAction] = 'R' or [txtAction] = 'T' ) "
varSQL = varSQL & varNationality & " and "

how about this

varSQL = ""
varSQL = "SELECT tblApplications.* FROM tblApplications WHERE ( [dteIDOC] > #03/31/2005# and ([txtAction] = 'A' or [txtAction] = 'R' or [txtAction] = 'T' ) and "
varSQL = varSQL & varNationality & " and "

 

[SQL_Hell]

I also advise you make this query simple to begin with, then add where clauses one by one. this should help you debug

 

[Doctor Kronenbo]

Thanks for your reply! I tried the extra 'and' and got a syntax error as coding later in the form's code affected it. It's hard to explain without posting the whole code...

 

[SQL_Hell]

Hi there

any luck with stripping the query down and adding where clauses one by one?

I also noticed this

#" & Format(DateValue(dteS - 1),

SQL server will not reconise that function, load that into a a variable first