Problem code

[mo]

The SQL string below gives me the error:

"Runtime Error '3601'
Too few Parameters. Expected 1"

Now I know that the problem is with the line:-

"strSQL = strSQL & " Int(Mid([SampBarCode],5,5)) AS SBC "

so I changed the code so that the FROM clause was based on a query instead
of table (i.e. 'SBC' is already present in the query). The question is why
should this be? Is there a way of re-writing the problematic line so that
the code would work regardless of whether it is based on a query or a table?

Thanks for any help.


------------------------------CODE-------------------------------

strSQL = "SELECT SampBarCode, HospNum, DoB, Forename, Surname, AddressLine1,
AddressLine2, "
strSQL = strSQL & " Int(Mid([SampBarCode],5,5)) AS SBC "
strSQL = strSQL & "FROM tbl_registration0003 "
strSQL = strSQL & "WHERE SBC >= '" & Me.txt_SBCStart & "' "
strSQL = strSQL & "AND SBC <= '" & Me.txt_SBCEnd & "'"

 

[Dan Artuso]

Hi,
I believe the problem is that you cannot use the alias name in the Where
clause. You have to repeat the expression.

 

[mo]

Thanks Dan and Allen for pointing this out. You were both right as usual.

Mo