code to open a report 1

[dday01]

Hi All,

I am working on a form with a couple of list boxes on it. I want the values chosen to drive the criteria of a report that comes up. The Do command to open the report opens fine with the one criteria in the where clause, however when I add the "and" part I get a type mismatch when bringing the report up. Here is my code:

If Me.List7 <> &quot;ALL&quot; Then
DoCmd.OpenReport stDocName, acPreview, , &quot;arepsec = '&quot; & Me.List7 & &quot;'&quot; And &quot;submit1 = X &quot;
Else: DoCmd.OpenReport stDocName, acPreview

Can someone tell me what is wrong with the syntax after the &quot;And&quot; ? all I want to do is get records with avalue of &quot;X&quot; for that field.

Any help is greatly appreciated.

Thanks,

D

 

[mpastore]

Try

DoCmd.OpenReport stDocName, acPreview, , &quot;arepsec = '&quot; & Me.List7 & &quot;' And submit1 = 'X'&quot;

Mike Pastore

Hats off to (Roy) Harper

 

[cP6uH]

Try this

DoCmd.OpenReport stDocName, acPreview, , &quot;(arepsec = '&quot; & Me.List7 & &quot;') And (submit1 = X )&quot;

 

[dday01]

Thanks for the responses,

Mike, I am still getting a type mismatch when I use that code.

cP6uH, When I use that code i get a criteria box that comes up and has an X and is requesting a value, when I then put an X in there I get the results I want.

Thanks for the responses, can you think of any other way to do this?

Thanks,

D

 

[mpastore]

What field is the list box bound to? What data type is the bound field?

Mike Pastore

Hats off to (Roy) Harper

 

[dday01]

Hi Mike,

The field submit1 is a a simple text field from a table.

Thanks,

D

 

[mpastore]

Ok, you are going to need to debug this. Put a STOP in the code before the IF statement.

In your code window type

?me.list7

Make sure you get a text value back.

If so, try this shortened code:

DoCmd.OpenReport stDocName, acPreview, , &quot;arepsec = '&quot; & Me.List7 & &quot;'&quot;

does it work?



Mike Pastore

Hats off to (Roy) Harper

 

[dday01]

Hi Mike this works, the staement functions fine with the first criteria, (report opens withe the correct records. It is after I add the: And &quot;submit1 = X &quot; that I get the type mismatch message coming up.

Your help is appreciated.

D

 

[mpastore]

What data type is submit1? Check the table in design mode to make sure.

Mike Pastore

Hats off to (Roy) Harper

 

[cP6uH]

Sorry for not reponding yesterday, but here I am today, so try this:

DoCmd.OpenReport stDocName, acPreview, , &quot;(arepsec = '&quot; & Me.List7 & &quot;') And (submit1 = 'X' )&quot;

Hope it helps.

 

[dday01]

cP6uH,

Holy no &quot;Type mismatch error&quot; Batman, it works! Thanks alot, Thanks for your help also Mike

D