Runtime error 13 W/Report

[Briandr]

DoCmd.OpenReport "JSA", , , "[JSANo] Like 'B*'", and "[Area] Like 'Knife'"

I get a Runtime error 13, data type mistmatch. I have played around with this for a while and here is what drives me crazy. It will test clean in the compiler and then throw the error at you when its run. Is Access really supposed to behave this way? Is it a behavior by design, test clean and then throw a error?

If someone can help me clean up the syntax I would appreciate it. Thanks.

 

[boblarson]

Is JSANo a number or text?

 

[Briandr]

JSANo is a number. The command was working fine until I added on the last part:

and "[Area] Like 'Knife'"

Thanks!

 

[boblarson]

If it's a number, then it doesn't make sense to have a number be like "B*" as B is not a number.

 

[Briandr]

I stand corrected. The field type is text and not number.

 

[boblarson]

Try:
DoCmd.OpenReport "JSA", , , "[JSANo] Like 'B*' AND [Area] Like '*Knife*'"

 

[Briandr]

Thanks! That worked great. Only one thing I'd like to tweak.

Can I get them to print in order. In order would be:

B1
B50
B60

etc etc.

Right now its printing them randomly and not in order. Again. Thank you!

 

[boblarson]

You will have to set your report's recordsource to be a query instead of a table and you can create a column that will sort them in the order you want.

 

[Briandr]

Ok. I already had a query that sorted them out for the form that lists them. How do I set this up so we have them print in order:

B1
B2
B3

as oppsed to:

B1
B11
B12
B2

This is what I did in the query:

SELECT JSA.ID, JSA.JSANo, JSA.JSATitle, JSA.OriginalDate, JSA.RevisionDate, JSA.Department, JSA.Area, *
FROM JSA
WHERE (((JSA.Department) Like "Board") AND ((JSA.Area) Like "Knife"))
ORDER BY Left([JSA].JSANo,1), Val(Mid([JSA].JSANo,2));

I take it I need to do something similiar with the report?