Syntax Error (1 Viewer)

access7

Registered User.
Local time
Today, 07:23
Joined
Mar 15, 2011
Messages
172
Hello

I am hoping someone can help identify where I am going wrong ... I am having some trouble with the where clause - i think it is the ContractRenewal that is wrong - I need to show all companies whose contract is up for renewal in 3 months time...

lsSQL = ""
lsSQL = lsSQL & " SELECT [Company],[ContractRenewal] FROM tbl_Company "
lsSQL = lsSQL & " Where ContractRenewal = <Now()+90 "
lsSQL = lsSQL & " & CompanyType = 1"
lsSQL = lsSQL & " & Archived = False"

Thanks in anticipation....
 

pr2-eugin

Super Moderator
Local time
Today, 07:23
Joined
Nov 30, 2011
Messages
8,494
Hi Access7, I find one or more syntax error here.. also one small suggestion for using Date()..
Code:
lsSQL = ""
lsSQL =[COLOR=Red] "[/COLOR]SELECT [Company],[ContractRenewal] FROM tbl_Company "
lsSQL = lsSQL & " Where ContractRenewal [B][COLOR=Red]<=[/COLOR][/B] [COLOR=Blue]DateAdd("d",90,Date())[/COLOR] "
lsSQL = lsSQL & "[COLOR=Red][B] AND[/B][/COLOR] CompanyType = 1"
lsSQL = lsSQL & " [COLOR=Red][B]AND[/B][/COLOR] Archived = False[COLOR=Red][B];[/B][/COLOR]"
The syntax changes are highlighted in Red and Suggestion in Blue..
* The logical operator you should use is AND not '&'.
* Since SELECT is the start of the string you do not need to precede it with the empty string, though it is not a syntax error, might be best.
* If your ContractRenewal field is set as Date format then the format you have used will never be true.. as Date is never equal to Now.
Date() = 31/08/2012
Now() = 31/08/2012 12:35:56
Unless you want to seperate the DatePart of Now and then add 90 days to it..
Hope this makes sense..
 

access7

Registered User.
Local time
Today, 07:23
Joined
Mar 15, 2011
Messages
172
Thank you for your advice, I definately should be using Date() rather than now()... I have made the amendments suggested but it is giving me an error on the line

lsSQL = lsSQL & " Where ContractRenewal <= DateAdd("d",90,Date()) "

I will try and work out why that may be but if you have suggestions that would be much appreicated.

Thanks so far :)
 

access7

Registered User.
Local time
Today, 07:23
Joined
Mar 15, 2011
Messages
172
lsSQL = ""
lsSQL = " SELECT [Company],[ContractRenewal] FROM tbl_Company "
lsSQL = lsSQL & " Where ContractRenewal <= (Date()+90) "
lsSQL = lsSQL & " AND CompanyType = 1"
lsSQL = lsSQL & " AND Archived = False"

This is working :)
 

Users who are viewing this thread

Top Bottom