Fix SQL Statement in VBA Access

[boldcode]

Hi,

I am not sure how to fix the following SQL statement written in VBA in order for it to work properly:

 strSQL ="SELECT FiscalYear, WorkingDays from tblFiscalYear " &_
                        "where FiscalYear = " & arrYears(currentFYToken)

I keep getting a syntax error when I click on the command button and VBA editor points to this piece of code.

- thanks in advance

 

[mole999]

maybe

varname1 = ""
varname1 = varname1 & "SELECT FiscalYear " & vbCrLf
varname1 = varname1 & ", WorkingDays " & vbCrLf
varname1 = varname1 & " from tblFiscalYear " & vbCrLf
varname1 = varname1 & "where " & vbCrLf
varname1 = varname1 & "FiscalYear = arrYears(currentFYToken)"
strSQL = varname1

 

[Joe4]

arrYears(currentFYToken)

Is this a variable (currentFYToken) being passed into a function (arrYears)?
If so, where and how is this variable and function defined?

 

[Micron]

If you're going to dispose of the line continuation character approach (&_) then I'd write

strSQL ="SELECT FiscalYear, WorkingDays FROM tblFiscalYear "
strSQL = strSQL & "WHERE  FiscalYear = " & arrYears(currentFYToken) & ";"

The character return/line feeds are not required (the ; may not be required either). Before doing anything with this sql statement, insert this line: Debug.Print strSQL in your code (if this is a code constructed sql statement)

and look at the immediate window. If you cannot see the problem, copy the printed statement, open a new query, dispense with the 'add table' dialog, go to SQL view, paste the copied text and go to datasheet view. If it balks, Access should highlight the offending portion of your sql statement. This is a useful approach for trouble shooting.

I suspect the issue is that arrYears(currentFYToken) is returning a Null or your date delimiters are missing (#) around your date(s).

 

[xenou]

If its not clear, the syntax error is that there must be a space before the line continuation character:

strSQL ="SELECT FiscalYear, WorkingDays from tblFiscalYear " [B][COLOR="#FF0000"]&_[/COLOR][/B]
                        "where FiscalYear = " & arrYears(currentFYToken)

Change to:

 strSQL ="SELECT FiscalYear, WorkingDays from tblFiscalYear " [COLOR="#FF0000"][B]& _[/B][/COLOR]
                        "where FiscalYear = " & arrYears(currentFYToken)

 

[Micron]

Good catch. I 'hate' 'em, never use 'em. Even stuff I've grabbed from MS web pages and dumped into my code failed until I re-wrote it my way. Can't be all that great if they can't always get it right either!