simple syntax problemo

[LuigiX]

Hi

I'm trying to send a string from a form to an SQL in a report.

This one works:

If cmbLocation.Enabled = False Then

Asset_Loc = "Like '*'"
Else
Asset_Loc = "= '" & cmbLocation & "'"
End If

but not this one:

If cmbLocation.Enabled = False Then

Asset_Loc = "Paris" Or "Rome"
Else
Asset_Loc = "= '" & cmbLocation & "'"
End If

Any help appreciated

Cheers

Luigi

 

[PaddyIrishMan]

The OR in "Paris" Or "Rome" is being picked up as a VB Keyword, you need to put the Or inside the quotes i.e.
"Paris Or Rome"

HTH,
Patrick

 

[LuigiX]

Hi Paddy

No go!

Getting a syntax error:

"Syntax error (missing operator) in query expression ........................... AND asset.Asset_Loc Paris Or RomeAND as....................."

???

Cheers

Luigi

 

[PaddyIrishMan]

Hi LuigiX,
by the looks of your string, you are missing an "=" and a space. You have:

"AND asset.Asset_Loc Paris Or RomeAND as....................."

Should this not be:

"AND asset.Asset_Loc = Paris Or Rome AND as....................."

Regards,
Patrick

 

[LuigiX]

Hi Patrick

No the pesky = is there thus:

Asset_Loc = "Paris Or Rome"

If you look carefully at the error msg you'll see Rome is concatted with the "AND" keyword

???

Cheers

Luigi

 

[PaddyIrishMan]

Hi LuigiX,
I'd say this is something really simple, but due to the nature of the beast we could be posting for hours trying to narrow it down. If you could post a zipped copy of your db or mail it to me I could fix it for you in a couple of minutes.

Regards,
Patrick.

 

[LuigiX]

Hi Patrick

I've attached a test report and form I've been using.

The report is called AM_Asset_Maintenance_List_EMS and it opens the form.

I'm basically just trying to get the Ok_Click event to send the two specified locations to the report. It works fine if I use a "Like" operator but this takes everything.

Thanks for your help

Cheers

Luigi

 

[PaddyIrishMan]

You forgot the attachment...

 

[LuigiX]

Hi Paddy

I did try

Have you got an email address I could send it to. Its a little bigger than the 102.4kB limit

Cheers

Luigi

 

[PaddyIrishMan]

Here you go: two_poc@hotmail.com

 

[PaddyIrishMan]

Hi Luigi,
The problem was nicely buried in there alright!!

Firstly: You need to change the code behind your Ok button - it needs quotes around the Paramaters which
you are passing in:

    If cmbLocation.Enabled = False Then
    	Asset_Loc = " 'Browns Bay' Or 'Poroti' "    '& Or & "Like  'Browns Bay' "
    Else
    	 Asset_Loc = "= '" & cmbLocation & "'"
    End If

Secondly: You were missing an "=" sign in your SQL string in the report code. (Third line from the bottom)

        SQL = "SELECT asset.Local_ID, asset.Category, asset.Asset_Label, asset.Asset_Loc, " _
            & "asset.Map_Ref, asset.Service_Status, asset.Condition_Status, " _
            & "asset_defects.Created_Date, defect_code.Descript " _
            & "FROM defect_code INNER JOIN (asset INNER JOIN asset_defects ON asset.Asset_ID = asset_defects.Asset_ID) " _
            & "ON defect_code.Mtce_Code = asset_defects.Mtce_Code " _
            & "WHERE asset_defects.Mtce_Code " & str3 & " " _
            & "AND asset.Asset_Loc =" & str4 _
            & "AND asset_defects.Created_Date " & strDate _
            & " ORDER BY asset.Local_ID"

(Both code examples are the fixed versions)

Hope this helps,
Patrick.

 

[LuigiX]

Hi Patrick

Many thanks for your help but there still remains one problem.

The report is displaying all the records when the OK btn is hit, not just the Browns Bay and Poroti ones. If the cmbLocation is not enabled it should just pass Browns Bay and Poroti.

????

Cheers

Luigi

 

[PaddyIrishMan]

We'll get there yet Luigi!! You need to use this code:

Code behind Ok Button:

    If cmbLocation.Enabled = False Then
      Asset_Loc = " asset.Asset_Loc = 'Browns Bay' OR asset.Asset_Loc= 'Poroti' "    '& Or & "Like  'Browns Bay' "
    Else
      Asset_Loc = "= '" & cmbLocation & "'"
    End If
    
    If cmbDefect.Enabled = False Then
       Defect_Code = "Like '*'"
    Else
      Defect_Code = "= '" & cmbDefect & "'"
    End If

Code for SQL string to use as Report source:

 SQL = "SELECT asset.Local_ID, asset.Category, asset.Asset_Label, asset.Asset_Loc, " _
   & "asset.Map_Ref, asset.Service_Status, asset.Condition_Status, " _
   & "asset_defects.Created_Date, defect_code.Descript " _
   & "FROM defect_code INNER JOIN (asset INNER JOIN asset_defects ON asset.Asset_ID = asset_defects.Asset_ID) " _
   & "ON defect_code.Mtce_Code = asset_defects.Mtce_Code " _
   & "WHERE " & str4 & " AND asset_defects.Created_Date " _
   & strDate & " AND asset_defects.Mtce_Code " & str3 & " " & "ORDER BY asset.Local_ID;"

This should do it
regards,
Patrick.

 

[LuigiX]

Paddy

You are an absolute legend!

I'm gonna open a celebratory can o liffy water in your honour!

Better still if you're ever down this way let me show you some legendary kiwi hospitality

Cheers

Luigi