SQL Statement Problem

S

SonicBoomAu

Well-known member
Joined
Oct 30, 2003
Messages
179
Location
Australia
Hi All.

I am having a problem with a SQL Statement.

Heres the code:

Code: 
Dim strNewSQLStatement As String
                        strNewSQLStatement = "SELECT " & strP2Custom & _
                            " From (PermanentAssets) " & _
                            "WHERE SwitchLoc = " & strSwitchLocValue & _
                            " AND BladeNo = " & strBladeLocValue & _
                            " AND PortNo = " & strPortNoValue & " "

                        Call LoadDBIntoDG(strNewSQLStatement, "Permanent")

Heres the error I get:

"Syntax error (missing operator) in query expression SwitchLoc = - AND BladeNo = - AND PortNo = *

SwitchLoc, BladeNo and PortNo are all columns in an Access DB. Does anyone know where I am going wrong with this????? :confused:

I have tried the SQL statement on a single line and received the same error. I.E. strNewSQLStatement = "SELECT " & strP2Custom & " From PermanentAssets WHERE SwitchLoc = " & strSwitchLocValue & " AND BladeNo = " & strBladeLocValue & " AND PortNo = " & strPortNoValue & ""


Please Help
 
Hi!
Seems to me like SwitchLoc , BladeNo and PortNo are strings.
SQL handles strings better if enclosed with quotes ( or " depending on local settings)

Another thing, if youre trying to get multiple records by using *, youll have to use LIKE and not =

WHERE SwitchLoc = " & strSwitchLocValue & "" & _
" AND BladeNo = " & strBladeLocValue & "" & _
" AND PortNo LIKE " & strPortNoValue & " "
Click to expand...

HTH
/Kejpa
 
I have had some success with this. Thanks for your help. My next question is are you able to have wildcard searches?

If I set all 3 values to something I can get a result but if I leave a value blank I want it to allow everything.

Hope this makes sense.
 
SonicBoomAu said:
My next question is are you able to have wildcard searches?
Click to expand...
Of course you can have wildcards searches, you have to have some logic to take care of the different cases though.
The idea is however to use the LIKE operator
Select name from Person where lastname like "a*" and firstname like "a*"
Click to expand...

HTH
/Kejpa
 
Careful - the wildcard charater is not always *

For example, on MySQL, the wildcard character is %

C.
 
If you use a parameterized in-line SQL statement youll do several things...one not have to have a concatenated SQL statement that two, opens you up to SQL injection attacks and a host of other problems. Better yet, increase your performance by moving into into a stored procedure.
 
the wild card in SQL Server (Microsoft) is %

Code: 
SELECT * FROM TABLE001 WHERE COLUMN001 LIKE ABC%
 
The SQL standard (which should mean any SQL compliant database) uses "%" and "_" wildcards as equivalent to the Windows standard of "*" and "?".
 
Back
Top