Two multi select box query

[gambit32]

I created two multi select boxes that allow a user to make multiple
selections in each. The result is a query based on what is selected in
each box. When I try to open my query after making my selections I get
an error.

The error reads: This expression is typed incorrectly, or it is too
complex to be evaluated. For example, a numeric expression may contain
too many complicated elements. Try simplifying the expression by
assigning parts of the expression to variables.

Here's the code:

strSQL = "SELECT
datetest1_tbl.fld_year,datetest1_tbl.fld_day,datetest1_tbl.fld_month,datetest1_tbl.fld_break_mins,datetest1_tbl.fld_break_hrs,datetest1_tbl.fld_date,datetest1_tbl.fld_client,datetest1_tbl.fld_project,datetest1_tbl.fld_subproject,datetest1_tbl.fld_currency,
datetest1_tbl.fld_duration_hrs,datetest1_tbl.fld_duration_mins,
datetest1_tbl.fld_note, datetest1_tbl.fld_rate,
datetest1_tbl.fld_amount FROM datetest1_tbl "
strWhere = "Where ((datetest1_tbl.fld_date) Between
Forms!aspdash_form!date1 And Forms!aspdash_form!date2) and
datetest1_tbl.fld_client IN ("
For i = 0 To listclient.ListCount - 1
If listclient.Selected(i) Then
strWhere = strWhere & "'" & listclient.Column(0, i) & "'"
End If
Next i
strWhere = strWhere & " AND datetest1_tbl.fld_currency IN ("
For i = 0 To listemployee.ListCount - 1
If listemployee.Selected(i) Then
strWhere = strWhere & "'" & listemployee.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & "));"
strSQL = strSQL & strWhere
MsgBox strSQL

....anyone have any ideas? Thanks in advance.

 

[Michel Walsh]

Hi,


It seems you are missing the , delimiter in your list (may be something else
too).


Dim flag As Boolean
For i = 0 To listclient.ListCount - 1
If listclient.Selected(i) Then
strWhere = strWhere & iif(flag, ", ", "") & "'" &
listclient.Column(0, i) & "'"
flag=true
End If
Next i


Hoping it may help,
Vanderghast, Access MVP

 

[gambit32]

Didn't work. Anymore ideas? Thanks for your help thus far.

 

[Guest]

What are the field types of these two fields?

fld_client
fld_currency


Also, add the line below, then open the immediate window and post the results.

strWhere = Left(strWhere, Len(strWhere) - 2) & "));"

Debug.Print strWhere '<<===== add this line =======

strSQL = strSQL & strWhere
MsgBox strSQL

 

[gambit32]

Hi Steve...

I did what you asked and I ran my query. Same result. I still get the
error message. I pasted the piece of code like you said and I ran my
query. The two fields are text fields. If you need more info just let
me know.

 

[Guest]

Did you open the immediate window? Copy the text and paste it in a reply.

The strSQL looks correct. Now we need to see what the strWhere looks like
after you run the code.

 

[gambit32]

Perhaps I did it wrong. When you say open the immediate window what do
you mean? I'm and Access newb.

 

[Michel Walsh]

Hi,


Instead of the MsgBox strSQL statement, try:

Debug.Print strSQL


that will cause the statement to be printed in the debug immediate window
(Ctrl_G).

Note that the first IN( miss its closing parenthesis, in addition to the
comas already mentioned. Anyhow, with the string of the SQL statement, that
should be easier for you to see the error(s). If not, cut and paste it into
the SQL view of a new query, and see it the query designer get some other
error, or paste it here, so we can see if there is (are) other error.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

From the main database window, press control-G. Or from the visual basic
editor, in the menu, select VIEW/IMMEDIATE WINDOW.

A pane will open below the code; there should be some text that looks like
what was diaplayed in the message box. Copy that and paste it in a post.

Since we can't see your mdb, it will allow us to see what the strWhere
clause looks like.

 

[gambit32]

Now I get you...here's the code:

Where ((datetest1_tbl.fld_date) Between Forms!aspdash_form!date1 And
Forms!aspdash_form!date2) and datetest1_tbl.fld_client IN ('Cascade
Vascular Associates', AND datetest1_tbl.fld_currency IN ('Mark
Babauta'));

Hope this helps. You guys are awesome.

 

[Guest]

OK Mark,

Do you see where the problem is? Like Michel said, you are missing a comma
and the closing parenthesis, so you need to modify your code. Can you do it
or do you need help?

Also, is the following correct?

datetest1_tbl.fld_currency IN ('Mark Babauta'));


You are looking at a currency field and searching for a name????


Now you have learned another debugging tool... "Debug.Print" along with
MsgBox and stepping thru the code.

If you use Debug.Print strSQL after (or the line before) Msgbox strSQL, you
will be able to see the whole SQL string just before it is executed to ensure
that it is constructed correctly.

 

[gambit32]

That was really helpful, seeing where the error is occuring. I took a
shot at it this afternoon and I think I'm going to need a little more
help with how to insert the comma and the closing parentheses.

As for the 'currency' field, someone else built this and decided to use
this field as an employee field but never changed the name to
'employee,' they just left it as currency.

Thanks so much!
Mark

 

[Guest]

Mark,

After looking at the results of the Debug.Print, I noticed there is a
problem with the dates also. If you look at the Debug.Print, you will see
that instead of dates after "Between", there is the name of the form and
control. The references to date1 and date2 need to be outside of the seting
to have the dates concantated to the string.

Here is the modified strWhere part of the SQL:

'this should be one line
strWhere = "Where (datetest1_tbl.fld_date Between #" &
Forms!aspdash_form!date1 & "# And #" & Forms!aspdash_form!date2 & "#)"

strWhere = strWhere & " AND datetest1_tbl.fld_client IN ("
For i = 0 To listclient.ListCount - 1
If listclient.Selected(i) Then
strWhere = strWhere & "'" & listclient.Column(0, i) & "', "
End If
Next i

'remove the last single quote and comma
' and add a closing parenthesis
strWhere = Left(strWhere, Len(strWhere) - 2) & ") "

strWhere = strWhere & " AND datetest1_tbl.fld_currency IN ("
For i = 0 To listemployee.ListCount - 1
If listemployee.Selected(i) Then
strWhere = strWhere & "'" & listemployee.Column(0, i) & "', "
End If
Next i

'remove the last single quote and comma
' and add a closing parenthesis
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

'Debug.Print strWhere

strSQL = strSQL & strWhere
MsgBox strSQL




HTH

 

[gambit32]

Hi Steve,
I copied and pasted the modified code and it ran but no query resulted.
Nothing showed up in the immediate window either. There seems to be a
problem with a particular line as it's in red. I noted the line below.

strSQL = "SELECT
datetest1_tbl.fld_year,datetest1_tbl.fld_day,datetest1_tbl.fld_month,datetest1_tbl.fld_break_mins,datetest1_tbl.fld_break_hrs,datetest1_tbl.fld_date,datetest1_tbl.fld_client,datetest1_tbl.fld_project,datetest1_tbl.fld_subproject,datetest1_tbl.fld_currency,
datetest1_tbl.fld_duration_hrs,datetest1_tbl.fld_duration_mins,
datetest1_tbl.fld_note, datetest1_tbl.fld_rate,
datetest1_tbl.fld_amount FROM datetest1_tbl "
strWhere = "Where ((datetest1_tbl.fld_date) Between
Forms!aspdash_form!date1 And Forms!aspdash_form!date2) and
datetest1_tbl.fld_client IN ("
For i = 0 To listclient.ListCount - 1
If listclient.Selected(i) Then
strWhere = strWhere & "'" & listclient.Column(0, i) & "',"
End If
Next i
THIS LINE IS IN RED --> strWhere = strWhere & "'" &
datetest1_tbl.fld_currency IN ("
For i = 0 To listemployee.ListCount - 1
If listemployee.Selected(i) Then
strWhere = strWhere & "'" & listemployee.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & "));"
Debug.Print strWhere '
strSQL = strSQL & strWhere
MsgBox strSQL

Mark

 

[Guest]

In the code window, any time a line is in red, it means there is a problem.
When examples of code are posted, there is usually a problem with line wrap.
When I copied the code from your post, most of it was red. I had to fix the
lines until it was all black.


'THIS SHOULD BE ONE LINE
strWhere = strWhere & " AND datetest1_tbl.fld_client IN ("


It looks like you deleted the comments I added. You should get into the
habit of commenting your code. It will help you when you look at it months or
years down the road. You'll slap your forehead and say "What was I thinking?
What is this code for?" (been there - done that!)

 

[gambit32]

Actually the line wrap is my fault. When I went to show what line was
in red it wrapped it automatically. I'm still getting that line in red
and nothing shows up in the immediate window. I'm stumped. As for the
comments, I didn't remove them. When I copied and pasted into the VB
editor they disappeared for some reason.

Thanks for sticking with me on this so far!

 

[Guest]

Try repasting the code. Make sure that there are no red lines before running
the code.
If you want the line to print in the immediate window, make sure that there
is not an apostrophe in front of "Debug.Print"

If you still can't get the code to work, I can email the code - it seems to
run in my test mdb.

Hang in there...

 

[gambit32]

Still no luck. I still get the line of code in red. Do you want to
try emailing me the code? Here's my email addy:

(e-mail address removed)

-Mark

Try repasting the code. Make sure that there are no red lines before running
the code.
If you want the line to print in the immediate window, make sure that there
is not an apostrophe in front of "Debug.Print"

If you still can't get the code to work, I can email the code - it seems to
run in my test mdb.

Hang in there...