SQL Statement Problem

[SonicBoomAu]

Hi All.

 

I am having a problem with a SQL Statement.

 

Here's the code:

 

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

                       Call LoadDBIntoDG(strNewSQLStatement, "Permanent")

 

Here's 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

[kejpa]

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 you're trying to get multiple records by using *, you'll have to use LIKE and not =

 

WHERE SwitchLoc = '" & strSwitchLocValue & "'" & _

" AND BladeNo = '" & strBladeLocValue & "'" & _

" AND PortNo LIKE '" & strPortNoValue & "' "

 

HTH

/Kejpa

[SonicBoomAu]

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.

[kejpa]

My next question is are you able to have wildcard searches?

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*"

 

HTH

/Kejpa

[penfold69]

Careful - the wildcard charater is not always '*'

 

For example, on MySQL, the wildcard character is '%'

 

C.

[bri189a]

If you use a parameterized in-line SQL statement you'll 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.

[iebidan]

the wild card in SQL Server (Microsoft) is %

 

SELECT * FROM TABLE001 WHERE COLUMN001 LIKE 'ABC%'

[jmcilhinney]

The SQL standard (which should mean any SQL compliant database) uses "%" and "_" wildcards as equivalent to the Windows standard of "*" and "?".

[SonicBoomAu]

thank you everyone for your help.

Using the LIKE command and the % as the wildcard value works a treat.

 

Once again thank you.