Where's the error in the code?

Posted on 2003-03-20
Medium Priority
Last Modified: 2008-03-06
I use a form to select multiple addresses for the print of envelopes. For the indication of a customers address, I use KIDNR, which contains numbers and letters. I use now

Private Sub Umschaltfläche6_Click()
Dim Element As Variant
Dim Bedingung As String
Dim KIDNR As Variant

If Liste.ItemsSelected.Count = 0 Then Exit Sub

For Each Element In Liste.ItemsSelected

KIDNR = Liste.ItemData(Element)

Bedingung = Bedingung & _
"KIDNR= " & KIDNR & " OR "

Next Element

Bedingung = Left(Bedingung, Len(Bedingung) - 4)


DoCmd.OpenReport "Couvert spezVersand", acPreview, , Bedingung
DoCmd.Close acForm, "Couvert spezial Mehrfachauswahl", acSaveNo
End Sub

But this does not properly work. After choosing the addresses, I get a small pop-up where I have to put in KIDNR manually, after have this done, the report opens and all the chosen addresses appearing.  

Thank you

Question by:Kongta
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8173256
Are you sure that KIDNR is a part of the reports datasource.

Author Comment

ID: 8173299
I guess. I use partly the same query in the form & report. If I put KIDNR in the field, that appears on the wrong code, it after works and the report opens the right addresses. I guess the error has something to do with numbers and letters. I copied the code from a query where only numbers are used. As I know, it needs little changes when using letters. I've made the changes I thought have to be done, but does not work.
LVL 44

Accepted Solution

Arthur_Wood earned 1000 total points
ID: 8173301
if KIDNR is s TEXT value, then chnage your code like this:

Bedingung = Bedingung & _
"KIDNR= '" & KIDNR & "' OR "

and also:

Bedingung = Left(Bedingung, Len(Bedingung) - 5)

A Text field MUST be enclosed in quote marks ('....') otherwise Access will treat the value like a Number, and you will get a TYPE MISMATCH error.

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 18

Expert Comment

ID: 8173325
Does the query that feeds the report have a parameter in it?  Step through the data generation and you'll see it.

Author Comment

ID: 8173562
Thanks to all

The answer from Arthur works. But only with the first code. When I used Bedingung=Left(Bedingung, Len(Bedingung) -5), it didn't react at all. Only for my better understanding; why you changed this code?

LVL 44

Expert Comment

ID: 8175544
because I added an additional character:

"' OR "

which by my count is 5.

set a breakpoint on this line, and check what the real string is:

Bedingung=Left(Bedingung, Len(Bedingung) -5)

and adjust the (5) accordinly, to truncated the end of the string.


Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question