desperately seeking syntax

LuigiX · Mar 31, 2003

Gotta field in a form called txtWorkPack with a default value of ="*"

gotta report that takes a big sql string that includes txtWorkPack thus

str3 = Forms![NP_Work_Pack_Filter].[txtWorkPack ]

then:

SQL = "SELECT work_tasks.Work_Task_No,

"note from luigi "heavily edited"

& "WHERE asset_category.Master_Cat " & str12 & "" _
& "AND work_tasks.Work_Pack_No = " & str3 & "" _
& "AND work_tasks.Reported_Date " & strDate2 & "" _
& "AND locality.Descript " & str4 & ""

Me.RecordSource = SQL

Everything works fine and dandy when I overwrite the default value of ="*" with a work pack number but I want the default wild card to pass every work pack. But keep getting pesky syntax error.

Using Access 2000

Can post a file if ya want!

Any suggestions??

Ta Luigi

FoFa · Mar 31, 2003

Try a value of ='*' instead or force single quotes around it in your assign.

WayneRyan · Mar 31, 2003

Ta Luigi,

Code:

If str3 = "*" Then
   SQL = "SELECT work_tasks.Work_Task_No " & _
         "From YourTable " & _ 
         "WHERE asset_category.Master_Cat = '" & str12 & "' And " & _
         "      work_tasks.Reported_Date = #" & strDate2 & "# And " & _ 
         "      locality.Descript '" & str4 & "';"
Else
   SQL = "SELECT work_tasks.Work_Task_No " & _
         "From YourTable " & _ 
         "WHERE asset_category.Master_Cat = '" & str12 & "' And " & _
         "      work_tasks.Work_Pack_No = '" & str3 & "' And " & _
         "      work_tasks.Reported_Date = #" & strDate2 & "# And " & _ 
         "      locality.Descript '" & str4 & "';"
End If

Without the proper delimiters and the = signs, I don't see
how it could be working.

The If statement is because depending on str3 you will either
need Like or =.

btw, it will save you a lot of confusion later if you name
your fields to convey their meaning.

Wayne

LuigiX · Mar 31, 2003

Thanks guys but still no go

I've stripped the code down a bit to make it clearer.

Have tried a default value of defect_Ref in the form of both ="*" and ='*' in conjunction with the following code in the report:

If Cancel = True Then
GoTo StandardExit
Else
defectNo = Forms![TestForm].[txtDefectNo]

SQL = "SELECT defect_Ref, Asset_ID, Mtce_code " _
& "FROM asset_defects " _
& "WHERE asset_defects.defect_Ref =" & defectNo & ""

Me.RecordSource = SQL
End If

have also tried:
& "WHERE asset_defects.defect_Ref =' " & defectNo & " ' "

but getting syntax errors both ways.

Note: If I specify a legit defect no in the field "txtDefectNo" on the form the report returns the correct record okay. It just doesnt like the wildcard which I need to return all records.

Thanks for your help

Luigi

WayneRyan · Mar 31, 2003

Luigi,

Try:

Code:

SQL = "SELECT defect_Ref, Asset_ID, Mtce_code " & _
          "FROM asset_defects " & _ 
          "WHERE asset_defects.defect_Ref = " & defectNo & "' Or " & _
          "'" & defectNo & "' = '*'

If they supply a value:
SELECT defect_Ref, Asset_ID, Mtce_code
FROM    asset_defects 
WHERE asset_defects.defect_Ref = '1234' Or
            '1234' = '*'

If they leave the default of *:
SELECT defect_Ref, Asset_ID, Mtce_code
FROM    asset_defects 
WHERE asset_defects.defect_Ref = '*' Or
            '*' = '*'

To use the "*", you must use the LIKE operator. This method
will circumvent that need with the last clause on the Where.

Wayne

LuigiX · Mar 31, 2003

Hi

I tried the code in the report exactly as provided thus:

SQL = "SELECT defect_Ref, Asset_ID, Mtce_code " & _
"FROM asset_defects " & _
"WHERE asset_defects.defect_Ref = " & defectNo & "' Or " & _
"'" & defectNo & "' = '*'"

in conjunction with a default value of ="*" in the field on the form.

With nothing entered into the field I get the following error:

"Syntax error (missing operator) in query expression 'asset_defects.defect_Ref = *' Or '*' = '*"

???

Cheers

Luigi

WayneRyan · Apr 1, 2003

Luigi,

With nothing entered into the field I get the following error:

"Syntax error (missing operator) in query expression
'asset_defects.defect_Ref = *' Or '*' =

That's a definite syntax error.

= *'

No parser will like that.

Why don't you post a "cut and paste" example of your code or
post a sample db.

Wayne

LuigiX · Apr 1, 2003

Hi

Here's the code exactly as used. It works for a specific work pack no but just doesnt like the wild card in the form ="*"

If Cancel = True Then
GoTo StandardExit

Else
defectNo = Forms![AM_asset_maint_form_EMS].[txtDefectNo]

SQL = "SELECT defect_Ref, Asset_ID, Mtce_code " & _
"FROM asset_defects " & _
"WHERE asset_defects.defect_Ref = " & defectNo & "' Or " & "'" & defectNo & "' = '*'"

Me.RecordSource = SQL
End If

Cheers

Luigi

PaddyIrishMan · Apr 1, 2003

Hi Luigi,
I find the easiest way to solve problems like this is to debug.print the SQL query to the immediate window then copy/paste into the SQL view of a query. When you run the query it will either show an error or fix itself. You can then view the fixed SQL code & replicate that it your VBA.

If that's no help to you, if you post your DB I'll fix it for you.

Regards,
Patrick

neileg · Apr 1, 2003

I'm not good with raw SQL, but I can't make sense of yours. I always like to 'translate' the SQL

SQL = "SELECT defect_Ref, Asset_ID, Mtce_code " & _
"FROM asset_defects " & _
"WHERE asset_defects.defect_Ref = " & defectNo & "' Or " & "'" & defectNo & "' = '*'"

So this means
Get a few fields from the table where the defect_ref is the same as the number I gave you or the number I gave you is anything

It's the last bit that doesn't make sense. Don't you mean
Get a few fields from the table where the defect_ref is the same as the number I gave you or the defect_ref is anything

Excuse my grasp of SQL sytax but should the statement look like
SQL = "SELECT defect_Ref, Asset_ID, Mtce_code " & _ "FROM asset_defects " & _
"WHERE asset_defects.defect_Ref = Like *" & defectNo & "*"

Sorry if I'm way off. I never claimed to be a programmer.

harra · Apr 1, 2003

If my memory is correct this should work:

Code:

If Cancel = True Then 
    GoTo StandardExit 
Else 
    defectNo = Forms![AM_asset_maint_form_EMS].[txtDefectNo] 
    SQL = "SELECT defect_Ref, Asset_ID, Mtce_code " & _ 
          "FROM asset_defects " & _ 
          "WHERE asset_defects.defect_Ref Like '" & nz(defectNo,"*") & "'" 
    Me.RecordSource = SQL 
End If

To have this work, then the textbox (txtDefectNo) must be allowed to remain empty, if the user wants to retreive all DefectNumbers. The NZ() function will look at the value in txtDefectNo and if it is Null it will plug in the wildcard character, otherwise it will insert the value of txtDefectNo.

This is also assuming that DefectNo is a string value. If it is not a string, then remove the single quotes (') from the SQL string

EDIT:

I edited the above text becuase I re-read it and it had a few typos that screwed up the message I was trying to convey

WayneRyan · Apr 1, 2003

Luigi,

harra's got my vote. That's the way to go.

Wayne

LuigiX · Apr 1, 2003

Hi Ray

Thanks for this. It works fine except for one thing.

If I specify a defect no that record is returned. However if I leave the default value in the field on the form as ="*", it only returns the first defect from the table. However I want all defects to be returned.

If I leave the field in the form blank, of course I get an invalid null value error.

Any ideas?

Cheers

Luigi

WayneRyan · Apr 1, 2003

Luigi,

See harra's prior post. I like his solution. The textbox should
not have a default value.

In our first looks into this we were centering on a default
value of '*'. harra's way is better.

Wayne

LuigiX · Apr 1, 2003

Hi Harrah

Oops sorrry about that. I got your name wrong. Its Harrah not Ray.

Thanks again

Cheers

Luigi

LuigiX · Apr 1, 2003

Hi Wayne

Without a default value in the textfield of ="*" I get an "invalid use of null" error.

Entering the wild card * into the textfield I get the first record only returned.

Entering a valid defect no into the text feild I get the correct record returned.

The problem is I want all the records returned if the textfield is blank or *

Cheers

Luigi

harra · Apr 1, 2003

Luigi,

You got my name right the first time. I am Ray. Harra is my screenname

Did you get it working or are you still having the same problem. You need to make sure that there is no default value for your textbox. If the user keys something in, that will be used for the "search", otherwise they should leave it blank

harra · Apr 1, 2003

Luigi,

Could you post the code you are using?

Also, is txtDefectNo storing a numeric or alphanumeric value?

LuigiX · Apr 1, 2003

Hi Ray (how did I guess this the first time?)

txtDefectNo in the form does not have a specific format

the table stores asset_defects.defect_Ref as a number

the report code declares defectNo as a string

Here's the report code I'm using:

Dim defectNo As String

defectNo = Forms![AM_asset_maint_form_EMS].[txtDefectNo]

If Cancel = True Then
GoTo StandardExit

Else

SQL = "SELECT defect_Ref, Asset_ID, Mtce_code " & _
"FROM asset_defects " & _
"WHERE asset_defects.defect_Ref Like '" & Nz(defectNo, "*") & "'"

Me.RecordSource = SQL

End If

Cheers

Luigi

harra · Apr 1, 2003

Luigi,

I decided to try and set up a similar situation in one of my databases and discovered that the code doesn't like trying to load a NULL value into the string variable. So the defectNo=Forms![AM_asset_maint_form_EMS].[txtDefectNo] needs to go. Try this:

Please See following post for what I believe is a more accurate code

It should at least get rid of that stupid Illegal Use of Null error and it should give you the results you want.

EDIT:

I edited this post since it had incorrect code and I didn't want to confuse future visitors who don't take the time to read the entire thread

desperately seeking syntax

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Users who are viewing this thread