Many strSQL

H

Hiro

Hello Forum Members:

I have many strSQl lines as follows:
~ ~ ~ ~ ~ ~ ~ ~
strSQL = "SELECT * FROM Declension where Preposition_I like " & strCriteria
& " or Instrumental_I like " & strCriteria & " or Possessive_I like " &
strCriteria & " or Preposition_You like " & strCriteria & " or
Instrumental_You like " & strCriteria & " or Possessive_You like " &
strCriteria & " or Preposition_You_F like " & strCriteria & " or
Instrumental_You_F like " & strCriteria & " or Possessive_You_F like " &
strCriteria & " or Preposition_He like " & strCriteria & " or Instrumental_He
like " & strCriteria & " or Possessive_He like " & strCriteria & " or
Preposition_She like " & strCriteria & " or Instrumental_She like " &
strCriteria & " or Possessive_She like " & strCriteria & " or Preposition_We
like " & strCriteria & " or Instrumental_We like " & strCriteria & " or
Possessive_We like " & strCriteria & " or Preposition_They like " &
strCriteria & " or Instrumental_They like " & strCriteria & " or
Possessive_They like " & strCriteria " & _

strSQL = strSQL & "where Preposition_Russian_I like " & strCriteria & " or
Instrumental_Russian_I like " & strCriteria & " or Possessive_Russian_I like
" & strCriteria & " or Preposition_Russian_You like " & strCriteria & " or
Instrumental_Russian_You like " & strCriteria & " or Possessive_Russian_You
like " & strCriteria & " or Preposition_Russian_You_F like " & strCriteria &
" or Instrumental_Russian_You_F like " & strCriteria & " or
Possessive_Russian_You_F like " & strCriteria & "; "

strSQL = strSQL & "where Preposition_Russian_He like " & strCriteria & " or
Instrumental_Russian_He like " & strCriteria & " or Possessive_Russian_He
like " & strCriteria & " or Preposition_Russian_She like " & strCriteria & "
or Instrumental_Russian_She like " & strCriteria & " or
Possessive_Russian_She like " & strCriteria & " or Preposition_Russian_We
like " & strCriteria & " or Instrumental_Russian_We like " & strCriteria & "
or Possessive_Russian_We like " & strCriteria & " or Preposition_Russian_They
like " & strCriteria & " or Instrumental_Russian_They like " & strCriteria &
" or Possessive_Russian_They like " & strCriteria
~ ~ ~ ~ ~ ~ ~ ~
It seems that the {" & _} on the first strSQL is not working. Does anyone
here know how to connect many strSQL lines?

Any help will be appreciated.
 
M

mray29

At the end of the first strsql grouping, you have a continuation character,
which you don't need, since you are using "strsql = strsql &" on the next
line. These are separate lines and should not be continued.
 
H

Hiro

Hello mray29:

Thanks for your very quick responce.
I removed the {" & _} and I get the Run-Time error 3075 on the following:

DoCmd.OpenForm "Declension_results", acFormFS, , , , acHidden
Forms!Declension_results.RecordSource = strSQL (This is where the error is.)
DoCmd.OpenForm "Declension_results", acFormFS

What did I do wrong?
Do you recommend that I use the {strSQL = "SELECT * FROM} and the {strSQL =
strSQL & "} sequentially? Such as"
{strSQL = "SELECT * FROM}
{strSQL = strSQL & "}
{strSQL = "SELECT * FROM}
{strSQL = strSQL & "}
in order to avoid the {" & _}? Is this a good practice?

Once again, any suggestions are greatly appreciated.
 
J

John W. Vinson

Hello Forum Members:

I have many strSQl lines as follows:
~ ~ ~ ~ ~ ~ ~ ~
strSQL = "SELECT * FROM Declension where Preposition_I like " & strCriteria
& " or Instrumental_I like " & strCriteria & " or Possessive_I like " &
strCriteria & " or Preposition_You like " & strCriteria & " or
Instrumental_You like " & strCriteria & " or Possessive_You like " &
strCriteria & " or Preposition_You_F like " & strCriteria & " or
Instrumental_You_F like " & strCriteria & " or Possessive_You_F like " &
strCriteria & " or Preposition_He like " & strCriteria & " or Instrumental_He
like " & strCriteria & " or Possessive_He like " & strCriteria & " or
Preposition_She like " & strCriteria & " or Instrumental_She like " &
strCriteria & " or Possessive_She like " & strCriteria & " or Preposition_We
like " & strCriteria & " or Instrumental_We like " & strCriteria & " or
Possessive_We like " & strCriteria & " or Preposition_They like " &
strCriteria & " or Instrumental_They like " & strCriteria & " or
Possessive_They like " & strCriteria " & _
Click to expand...

Well...

One reason you're having trouble is that your tables do not appear to be
correctly normalized. You're storing data - grammatical cases - in fieldnames.
I'd say that the Declension table should be tall and thin, rather than wide
and flat, with "Preposition_I", "Instrumental_I", and so on as *data values*
in a field, and as many rows as there are such cases. That would give you ONE
field to search instead of dozens!

John W. Vinson [MVP]
 
A

Albert D. Kallal

Hiro said:
Hello Forum Members:

I have many strSQl lines as follows:
~ ~ ~ ~ ~ ~ ~ ~
Click to expand...

That sql looks VERY VERY un-normailed. You should deisg this such that you
have 3 collums:


Catagory WordType WordValue
Preposition I Like text value
Possessive I like test value


With the above desing, you could add as many new "types" to seach for with
ease..and your final seach would be ONLY one query like:

select * from tblData where WordValue like "your criteria"

The above is SOOO easy to maintin.....

With what you have, you have a real difficult assigned to work with.

I would suggest that you can sit are some type of coding approach in which
you can feed a routine that list to the fields you want to search

eg, something like:

Dim strSql As String
Dim strFields As String
Dim fList() As String
Dim strCrit As String
Dim i As Integer

strCrit = " like 'abc*'"

strFields = "Possessive_I,Preposition_You,Instrumental_You," & _
"Possessive_You,Preposition_You_F,Instrumental_You_F,Possessive_You_F," &
_
"Preposition_He,Instrumental_He,Possessive_He,Preposition_She,Instrumental_She,"
& _
"Possessive_She,Preposition_We,Instrumental_We,Possessive_We,Preposition_They,"
& _
"Instrumental_They,Possessive_They"

fList = Split(strFields, ",")
For i = 0 To UBound(fList)
If strSql <> "" Then strSql = strSql & " or "
strSql = strSql & fList(i) & strCrit
Next i

strSql = "SELECT * FROM Declension where " & strSql

Debug.Print strSql

At least the above allows you to have a "list" of fields. furthermore I
would not even place that list of fields in the code as that's way too hard
coded, you might make the field list and actual mammal on reform and you
just type in the fields and then run the above code (that since the typing
the list into star fields, you use a text box on the form called stir
fields).

When you see such a "large" amount of difficult sql and code as you posted
you just *know* that the design you have is becoming too hard to work
with....

If the code is becoming hard and difficult...the solution is not to code
with more effort and complexity, but to find a design that makes the whole
thing very easy in the first place.
 
H

Hiro

Thank you for all your replies and suggestions.

Now, I've learned a design approach and I should do this for the new table.
But the current table that I am working on already has many records, so I
need to keep this table.

If I have only one strSQL, I have no problem with the folloing output.

DoCmd.OpenForm "Declension_results", acFormFS, , , , acHidden
Forms!Declension_results.RecordSource = strSQL
DoCmd.OpenForm "Declension_results", acFormFS

But when I use the combination of {strSQL = "SELECT * FROM} and the {strSQL
= strSQL & "}, I need to modify this {Forms!Declension_results.RecordSource =
strSQL}, which I don't know how.
Do you know how or any place where I can look at? Once again, thank you for
looking into this question.

Hiro
 
J

John W. Vinson

Thank you for all your replies and suggestions.

Now, I've learned a design approach and I should do this for the new table.
But the current table that I am working on already has many records, so I
need to keep this table.
Click to expand...

Well... actually you don't. It's not too hard to move data from a wide-flat
table into a tall-thin one. YOu can use a "Normalizing Union Query" such as

SELECT <primary key field> "Preposition_I" AS WordType, Preposition_I AS
WordValue
FROM Declension
WHERE Preposition_I IS NOT NULL
UNION ALL
SELECT <primary key field> "Instrumental_I" AS WordType, Instrumental_I AS
WordValue
FROM Declension
WHERE Instrumental_I IS NOT NULL
UNION ALL
If I have only one strSQL, I have no problem with the folloing output.

DoCmd.OpenForm "Declension_results", acFormFS, , , , acHidden
Forms!Declension_results.RecordSource = strSQL
DoCmd.OpenForm "Declension_results", acFormFS

But when I use the combination of {strSQL = "SELECT * FROM} and the {strSQL
= strSQL & "}, I need to modify this {Forms!Declension_results.RecordSource =
strSQL}, which I don't know how.
Do you know how or any place where I can look at? Once again, thank you for
looking into this question.
Click to expand...

You don't need multiple strSQLs.

Even with your current structure, you need *one* strSQL with many OR clauses.


John W. Vinson [MVP]
 
H

Hiro

Hello John:

Thanks for your advice, but I am still struggling on this. This table is
easier for me to place new records, so I am still using the same table. But I
changed the code from strSQL to stLinkCriteria = stLinkCriteria.

It is still not working. I am totally purplexed. Can you help me on this?

Here is the code:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Declension_results"

If Not IsNull(Me![Preposition_I]) Then
stLinkCriteria = stLinkCriteria & "[Preposition_I]=" & "'" &
Me![Preposition_I] & "'"
End If

' The similar values are continuing here. '

If Not IsNull(Me![Possessive_Russian_They]) Then
stLinkCriteria = stLinkCriteria & "[Possessive_Russian_They]=" & "'" &
Me![Possessive_Russian_They] & "'"
End If

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command108_Click:
Exit Sub

Err_Command108_Click:
MsgBox Err.Description
Resume Exit_Command108_Click

End Sub
 
J

John W. Vinson

Thanks for your advice, but I am still struggling on this. This table is
easier for me to place new records, so I am still using the same table. But I
changed the code from strSQL to stLinkCriteria = stLinkCriteria.
Click to expand...

I'm sorry, that makes no sense to me at all.

If you step through the code, what is the actual value of stLinkCriteria when
you've built it?

What are you DOING with stLinkCriteria when it';s built? stLinkCriteria =
stLinkCriteria is meaningless (setting a string to itself???)

John W. Vinson [MVP]
 
H

Hiro

cHello John:

Once again, thank you for your reply and I have to apologize for my poor
understanding on Acess.

Now, I have gone back to the original strSQL with some changes as follows.
I think I am getting a little closer because I am getting a result on the
2nd strSQL. However, the 1st strSQL is not working correctly.

Once again, it will be greatly appreciated if you could look into this and
provide a solution at your earliest convenience.


strSQL = "SELECT * FROM Declension where Prep_I like " & strCriteria & " or
Inst_I like " & strCriteria & " or Posse_I like " & strCriteria & " or
Prep_You like " & strCriteria & " or Inst_You like " & strCriteria & " or
Posse_You like " & strCriteria & " or Prep_You_F like " & strCriteria & " or
Inst_You_F like " & strCriteria & " or Posse_You_F like " & strCriteria & "
or Prep_He like " & strCriteria & " or Inst_He like " & strCriteria & " or
Posse_He like " & strCriteria & " or Prep_She like " & strCriteria & " or
Inst_She like " & strCriteria & " or Posse_She like " & strCriteria & " or
Prep_We like " & strCriteria & " or Inst_We like " & strCriteria & " or
Posse_We like " & strCriteria & " or Prep_They like " & strCriteria & " or
Inst_They like " & strCriteria & " or Posse_They like " & strCriteria

strSQL = "SELECT * FROM Declension where Prep_Russian_I like " &
strCriteria & " or Inst_Russian_I like " & strCriteria & " or Posse_Russian_I
like " & strCriteria & " or Prep_Russian_You like " & strCriteria & " or
Inst_Russian_You like " & strCriteria & " or Posse_Russian_You like " &
strCriteria & " or Prep_Russian_You_F like " & strCriteria & " or
Inst_Russian_You_F like " & strCriteria & " or Posse_Russian_You_F like " &
strCriteria & " or Prep_Russian_He like " & strCriteria & " or
Inst_Russian_He like " & strCriteria & " or Posse_Russian_He like " &
strCriteria & " or Prep_Russian_She like " & strCriteria & " or
Inst_Russian_She like " & strCriteria & " or Posse_Russian_She like " &
strCriteria & " or Prep_Russian_We like " & strCriteria & " or
Inst_Russian_We like " & strCriteria & " or Posse_Russian_We like " &
strCriteria & " or Prep_Russian_They like " & strCriteria & " or
Inst_Russian_They like " & strCriteria & " or Posse_Russian_They like " &
strCriteria

DoCmd.OpenForm "Declension_results", acFormFS, , , , acHidden
Forms!Declension_results.Form.RecordSource = strSQL
DoCmd.OpenForm "Declension_results", acFormFS
 
J

John W. Vinson

cHello John:

Once again, thank you for your reply and I have to apologize for my poor
understanding on Acess.

Now, I have gone back to the original strSQL with some changes as follows.
I think I am getting a little closer because I am getting a result on the
2nd strSQL. However, the 1st strSQL is not working correctly.
Click to expand...

I cannot debug this.

I cannot see what is actually being put into strSQL.

You can, however! Put a breakpoint in the code, and step through it until
strSQL has been defined, right after this statement.

In the Immediate Window type

?strSQL

It will display what has actually been put into this variable.

You can copy and paste this query into the SQL window of a new Query, and see
what errors occur; or you can post it to a message here, where I or another
volunteer might be able to diagnose the problem.

John W. Vinson [MVP]
 
H

Hiro

Hello John:

Thanks again for looking into this.

Under View menu and Immediate Windows, I typed ?strSQL, but nothing happens.

This is the entire code that I have on this click control. And only the 2nd
{strSQL = "SELECT *} is working. Do you see anything wrong?

Private Sub cmdFind_Click()

Dim strCriteria As String
Dim strSQL As String

If IsNull(Me.txtFindWhat) Then
MsgBox "Please enter a text in the Find What text box first!"
Exit Sub
Else
'Chr(34) means the quotation mark
strCriteria = Chr(34) & "*" & Me.txtFindWhat & "*" & Chr(34)
End If

'strSQL = " SELECT * FROM Declension where Preposition I like " & strCriteria

strSQL = "SELECT * FROM Declension where Prep_I like " & strCriteria & " or
Inst_I like " & strCriteria & " or Posse_I like " & strCriteria & " or
Prep_You like " & strCriteria & " or Inst_You like " & strCriteria & " or
Posse_You like " & strCriteria & " or Prep_You_F like " & strCriteria & " or
Inst_You_F like " & strCriteria & " or Posse_You_F like " & strCriteria & "
or Prep_He like " & strCriteria & " or Inst_He like " & strCriteria & " or
Posse_He like " & strCriteria & " or Prep_She like " & strCriteria & " or
Inst_She like " & strCriteria & " or Posse_She like " & strCriteria & " or
Prep_We like " & strCriteria & " or Inst_We like " & strCriteria & " or
Posse_We like " & strCriteria & " or Prep_They like " & strCriteria & " or
Inst_They like " & strCriteria & " or Posse_They like " & strCriteria

strSQL = "SELECT * FROM Declension where Prep_Russian_I like " &
strCriteria & " or Inst_Russian_I like " & strCriteria & " or Posse_Russian_I
like " & strCriteria & " or Prep_Russian_You like " & strCriteria & " or
Inst_Russian_You like " & strCriteria & " or Posse_Russian_You like " &
strCriteria & " or Prep_Russian_You_F like " & strCriteria & " or
Inst_Russian_You_F like " & strCriteria & " or Posse_Russian_You_F like " &
strCriteria & " or Prep_Russian_He like " & strCriteria & " or
Inst_Russian_He like " & strCriteria & " or Posse_Russian_He like " &
strCriteria & " or Prep_Russian_She like " & strCriteria & " or
Inst_Russian_She like " & strCriteria & " or Posse_Russian_She like " &
strCriteria & " or Prep_Russian_We like " & strCriteria & " or
Inst_Russian_We like " & strCriteria & " or Posse_Russian_We like " &
strCriteria & " or Prep_Russian_They like " & strCriteria & " or
Inst_Russian_They like " & strCriteria & " or Posse_Russian_They like " &
strCriteria

DoCmd.OpenForm "Declension_results", acFormFS, , , , acHidden
Forms!Declension_results.Form.RecordSource = strSQL
DoCmd.OpenForm "Declension_results", acFormFS

End Sub
 
J

John W. Vinson

Hello John:

Thanks again for looking into this.

Under View menu and Immediate Windows, I typed ?strSQL, but nothing happens.
Click to expand...

Because you did not follow my instructions. You need to type ?StrSQL *AFTER*
the value of strSQL has been set.
This is the entire code that I have on this click control. And only the 2nd
{strSQL = "SELECT *} is working. Do you see anything wrong?
Click to expand...

No, I cannot, because I do not know what's in Me.txtFindWhat, because I do not
know the fieldnames in your table, becase *I cannot see your computer*. I'm
not a magician, Hiro.

Private Sub cmdFind_Click()

Dim strCriteria As String
Dim strSQL As String

If IsNull(Me.txtFindWhat) Then
MsgBox "Please enter a text in the Find What text box first!"
Exit Sub
Else
'Chr(34) means the quotation mark
strCriteria = Chr(34) & "*" & Me.txtFindWhat & "*" & Chr(34)
End If

'strSQL = " SELECT * FROM Declension where Preposition I like " & strCriteria
Click to expand...

Open the VBA code in the module design window. Use the mouse and click in the
vertical grey bar to the left of the code window next to this next line. Run
the code; it will stop executing at this line (a "breakpoint" is what it's
called).
strSQL = "SELECT * FROM Declension where Prep_I like " & strCriteria & " or
Inst_I like " & strCriteria & " or Posse_I like " & strCriteria & " or
Prep_You like " & strCriteria & " or Inst_You like " & strCriteria & " or
Posse_You like " & strCriteria & " or Prep_You_F like " & strCriteria & " or
Inst_You_F like " & strCriteria & " or Posse_You_F like " & strCriteria & "
or Prep_He like " & strCriteria & " or Inst_He like " & strCriteria & " or
Posse_He like " & strCriteria & " or Prep_She like " & strCriteria & " or
Inst_She like " & strCriteria & " or Posse_She like " & strCriteria & " or
Prep_We like " & strCriteria & " or Inst_We like " & strCriteria & " or
Posse_We like " & strCriteria & " or Prep_They like " & strCriteria & " or
Inst_They like " & strCriteria & " or Posse_They like " & strCriteria
Click to expand...

Press the F8 key to step through the code. The code shortcuts can be seen by
selecting the Debug menu option.
strSQL = "SELECT * FROM Declension where Prep_Russian_I like " &
strCriteria & " or Inst_Russian_I like " & strCriteria & " or Posse_Russian_I
like " & strCriteria & " or Prep_Russian_You like " & strCriteria & " or
Inst_Russian_You like " & strCriteria & " or Posse_Russian_You like " &
strCriteria & " or Prep_Russian_You_F like " & strCriteria & " or
Inst_Russian_You_F like " & strCriteria & " or Posse_Russian_You_F like " &
strCriteria & " or Prep_Russian_He like " & strCriteria & " or
Inst_Russian_He like " & strCriteria & " or Posse_Russian_He like " &
strCriteria & " or Prep_Russian_She like " & strCriteria & " or
Inst_Russian_She like " & strCriteria & " or Posse_Russian_She like " &
strCriteria & " or Prep_Russian_We like " & strCriteria & " or
Inst_Russian_We like " & strCriteria & " or Posse_Russian_We like " &
strCriteria & " or Prep_Russian_They like " & strCriteria & " or
Inst_Russian_They like " & strCriteria & " or Posse_Russian_They like " &
strCriteria
Click to expand...

What you're doing here is setting a value of strSQL in the line two above -
the one with Prep_I and Inst_I and so on; then you are OVERWRITING and
destroying all of that SQL with this new SQL.

What are you intending the first SELECT clause to do? As it stands, it's doing
nothing at all.
DoCmd.OpenForm "Declension_results", acFormFS, , , , acHidden
Forms!Declension_results.Form.RecordSource = strSQL
DoCmd.OpenForm "Declension_results", acFormFS
Click to expand...

You will certainly need to change this last line (which will erase all the
work you did to set its recordsource) to

Forms!Declension_results.Visible = True

The steps would be to open the form, hidden; set its Recordsource; and then
make the form visible.


John W. Vinson [MVP]
 
H

Hiro

Hello John:

Thank you for your continued efforts to look into this problem. No matter
how I do it {?strSQL}, it does not produce anything.

The 2nd strSQL = "SELECT * FROM always returns the correct results.
The 1st strSQL = "SELECT * FROM does not return any results and no error
message. The form "Declension_results" comes back with a blank page. Is this
why I don't see anything after typing {?strSQL}?

I am wondering if there is any way to send this small file via e-mail or
send it to ftp site where you may be able to look at? I know I am taking so
much of your time on this and apologize.
 
J

John W. Vinson

I am wondering if there is any way to send this small file via e-mail or
send it to ftp site where you may be able to look at? I know I am taking so
much of your time on this and apologize.
Click to expand...

Well, ordinarily I leave such services for paying clients but I'm sufficiently
perplexed that I would like to take a look. Please Zip the file (in Windows
Explorer, rightclick and select Send To... Compressed (Zipped) Folder). Email
the folder to me at jvinson <at> wysard of info <dot> com - edit out the
blanks and replace the punctuation.

Let me know in the email the name of the form and the relevant modules.


John W. Vinson [MVP]
 
Top