sql string refering to form through vba

[davikokar]

Hallo,
I have a query tha refers to some combos in a form. It is designed in order to give the user the possibility to choose some parameters and filter the data. When the user click a the runquery button a subform will display the resul of the query (stored). Everything works perfectly. Now what I would like to do is to give the possibility to the user to work with the records selected by the query.
The idea was to populate a recordset with the result of the query but I really cannot make the stored query working in a VBA module. I think I tried all the possible combinations of double and single quotes, but I came to a dead point: the message I get is this a type mismatch error(13):

here is the query:

strSql = "SELECT tbl_Person.Per_Name, tbl_Person.Per_LastName, tbl_Type.Typ_ID, tbl_Country.Cou_ID, tbl_Person.Per_ID FROM tbl_Country RIGHT JOIN (tbl_Type RIGHT JOIN tbl_Person ON tbl_Type.Typ_ID=tbl_Person.Per_Type) ON tbl_Country.Cou_ID=tbl_Person.Per_Country WHERE ((ISNULL(tbl_Type.[typ_ID]) AND '" & Forms!Form1!Cbo_Type.Value & "' = '" * "') OR tbl_Type.[typ_ID] Like '" & Forms!Form1!Cbo_Type.Value & "') AND ((ISNULL(tbl_Country.[Cou_ID]) AND '" & Forms!Form1!cbo_country.Value & "' = '" * "') OR tbl_Country.[cou_ID] Like '" & Forms!Form1!cbo_country.Value & "') AND ((ISNULL(tbl_Person.[per_name]) AND '" & Forms!Form1!cbo_Person.Value & "' = '" * "') OR tbl_Person.[per_name] Like '" & Forms!Form1!cbo_Person.Value & "');"

thanks for helping

 

[cmmrfrds]

I am not sure about the newest versions of Access, but a string is 255 bytes. So, it may be necessary to concatenate multiple strings.
Dim str1 as string, str2 as string, str3 as string

str1 = "SELECT tbl_Person.Per_Name, "
str1 = str1 + "tbl_Person.Per_LastName, tbl_Type.Typ_ID, "
str1 = str1 + " tbl_Country.Cou_ID, tbl_Person.Per_ID "
etc.. 'no more than 255 bytes.
str2 = "FROM tbl_Country RIGHT JOIN "
etc...

Me.RecordSource = str1 & str2
Debug.Print Me.RecordSource

'Then open the immediate window with Control G and look at what your string looks like. This will make it easier to figure out what is wrong with the syntax. Also, sometimes it is clearer to use chr(34) or chr(39) which are double and single quotes. example.
value = chr(34) & formfield & chr(34)
this will put double quotes around the formfield.

 

[Zion7]

For starters, "Isnull" in SQL is syntaxed not as...

AND ((ISNULL(tbl_Person.[per_name])

but as...

AND (((tbl_Person.[per_name] Is Null)


this...
AND '" & Forms!Form1!cbo_Person.Value & "' = '" * "')OR...
should be...
AND '" & Forms!Form1!cbo_Person.Value & "Like '*') OR...

...but I'm having a hard time following the SQL.

Your Where criteria, aren't always being compared with a field in the SQL.

If i follow correctly, This..

WHERE ((ISNULL(tbl_Type.[typ_ID]) AND '" & Forms!Form1!Cbo_Type.Value & "' = '" * "')

should be written as...
WHERE (((tbl_Type.[typ_ID] Is Null) AND tbl_Type.[typ_ID] Like'" & Forms!Form1!Cbo_Type.Value & "')

As said earlier, this part is strange...
AND '" & Forms!Form1!cbo_Person.Value & "' = '" * "')

finally, no quotes for numbers, single quotes are for strings, double quotes for unpredictable strings, (strings with apostrophes, quotes, commas etc...), so simply replace every instance of a single quote, with a 2 doubles. eg..

tbl_Person.[per_name]='" & cboValue... & "' OR...

becomes...
tbl_Person.[per_name]=""" & cboValue... & """ OR...

Hope this helps, good luck!

 

[willir]

cmmrfrdsYou are bang on about the 255 characters.

karerda - Your query is complex. I deal with this 255 character limit issue several ways...
- Use aliases for your tables.
- Use variables where appropriate
- You can use strSELECT, strFROM, strWHERE to break up you select statment into three parts. The use the three strings together such as...

DoCmd.RunSQL strSELECT & strFROM & strWHERE

More Examples

'Alias:  Table / Field AS AliasName
Dim strSQL as String, strQ as String, strWhere as String
Dim strPerson as String, strCountry as String, strType as String

strQ = Chr$(34)

'Assign values from the form to a variable
strType = Nz(Forms!Form1!Cbo_Type.Value, "")
strCountry = Nz(Forms!Form1!cbo_country.Value, "")
strPerson = Nz(Forms!Form1!cbo_Person.Value, ")

strSql = "SELECT P.Per_Name, P.Per_LastName, T.Typ_ID, C.Cou_ID, P.Per_ID FROM tbl_Country as C RIGHT JOIN (tbl_Type as T RIGHT JOIN tbl_Person as P ON T.Typ_ID=P.Per_Type) ON C.Cou_ID=P.Per_Country "

strWhere = "WHERE ((ISNULL(T.[typ_ID]) AND " & LEN(strType) & "= 0 OR T.[typ_ID] Like " _
& strQ & strType & strQ & ") " AND ((ISNULL(C.[Cou_ID]) AND " _
& LEN(strCountry) & "= 0) OR C.[cou_ID] Like " & strQ & strCountry & strQ _
& ") AND ((ISNULL(P.[per_name]) AND " & strQ & strPerson & strQ & "= " _
& strQ & "*) OR P.[per_name] Like " & strQ & strPerson & strQ  & ");"

You might have to break up the WHERE clause into two to avoid the 255 character limit.

Hint:
Make sure the SQL statment works - use the query builder. (And yes, it will accept the aliases)

Then when you get the error, bring up the debugger. Enter
? strSQL
... to see your SQL statment ... and
? LEN(strSQL)
... to see the length.

Richard

 

[davikokar]

thanks to all for the input. i'll try your suggetstions. For the SQL mistakes (isnull) the strange thing is that in a stored query it works properly. And yes, dboulos this part is ppart:
AND '" & Forms!Form1!cbo_Person.Value & "' = '" * "')
is strange. I also think it might be the one that causes the problem. The aim of it is that: I have a combo which rowsource is a field in a table. The field can be null (not known). If I choose * I wold like the wuery to return all fields, including the null ones. If I avoid this part:
((ISNULL(tbl_Type.[typ_ID]) AND '" & Forms!Form1!Cbo_Type.Value & "' = '" * "')
and I write * in the combo, the query will show all the fields but the empty ones...

 

[Zion7]

Interesting.

again then, using a wildcard in a SQL statement, implies a value.

...AND txtCountry Like'*'.... Or
...AND txtCountry Not Is Null OR txtCountry<>""
means that NO empty or null strings will be gathered.

to allow empty & null...
...AND txtCountry Is Null OR txtCountry = ""....

but, again, unless I'm missing something here...
((ISNULL(tbl_Type.[typ_ID]) AND '" & Forms!Form1!Cbo_Type.Value & "' = '" * "')

outside of the syntax issue, you are allowing nulls to be returned from the [typ_ID] field, fine as far as that goes, but then referencing a combobox without comparing a field to it? The combo box should have a criterion to compare within a field from your SQL statement.
In other words, you're not really saying anything with that statement?...
...AND '" & Forms!Form1!Cbo_Type.Value & "' = '" * "')

If you want all records to show for [typ_ID], you can either remove it altogether from the SQL, or put..
...[typ_ID] Is Null OR [typ_ID]Like'*'....

Actually, I think I'm understanding your objective.
From the cbobox, you want the SQL to either return ALL records for [typ_ID] or just non null values.

It sounds like your cbobox contains an asterix. For only non null values, you would write...
(tbl_Type.[typ_ID]Like'" & Forms!Form1!Cbo_Type.Value & "' AND...

but now to retrieve all records, the only way I can see, is to append another line to the SQL, or remove the one I just wrote completely. In other words, I don't know how, toggling between only one criterion, to restrict the records
and then show all. Off hand, I would consider...

strSql = "SELECT tbl_Person.Per_Name ...." & strType

on the afterupdate event of the cbobox have ...

If Cbo_Type = "*" Then
strType = "AND (tbl_Type.[typ_ID]Like'" & Forms!Form1!Cbo_Type.Value & "')"
Else
strType = ""

something to that extent.

I hope I haven't missed your point, or wasn't clear on certain issues.

Good Luck!

 

[davikokar]

hallo,
is someone is still following this issue. I finally found a working way:

StrSELECT = "SELECT tp.Per_Name, tp.Per_LastName, tt.Typ_ID, tc.Cou_ID, tp.Per_ID "

StrFROM = "FROM tbl_Country AS tc RIGHT JOIN (tbl_Type AS tt RIGHT JOIN tbl_Person AS tp ON tt.[Typ_ID]=tp.[Per_Type]) ON tc.[Cou_ID]=tp.[Per_Country] "

StrWHERE1 = "WHERE ((ISNULL(tt.[typ_ID]) AND '" & Me.Cbo_Type & "' = '*') OR tt.[typ_ID] LIKE '" & Me.Cbo_Type & "') "

StrWHERE2 = "AND ((ISNULL(tc.[Cou_ID]) AND '" & Me.cbo_country & "' = '*') OR tc.[cou_ID] LIKE '" & Me.cbo_country & "') "

StrWHERE3 = "AND ((ISNULL(tp.[per_name]) AND '" & cboPerson & "' = '*') OR tp.[per_name] LIKE '" & cboPerson & "');"

thanks again for your inputs

 

[willir]

Good job on using aliases.