Problem matching any number in query

[TD]

I have a query with this expression as one of the fields:

Expr1:
IIf(Len([tblSSPbatch]![Batch_Number])=4,Left([tblSSPbatch.Batch_Number],1),IIf(Len([tblSSPbatch]![Batch_Number])=5,Left([tblSSPbatch.Batch_Number],1),IIf(Len([tblSSPbatch]![Batch_Number])=6,Left([tblSSPbatch.Batch_Number],2))))

This expression extracts the vessel number from the batch number.

In the criteria section under the field above I have this:

IIf([Forms]![frmReports]![cboVesselNumber]="*",Like "*" &
[Forms]![frmReports]![cboVesselNumber] &
"*",[Forms]![frmReports]![cboVesselNumber])

This works if cboVesselNumber returns the numbers 1 thru 12 but if
cboVesselNumber returns "*" ,which represents all vessel numbers, then
the query returns zero records.

I can't figure this one out, help!

Thanks,
TD

[TD]

Anyone?

[CDMAPoster]

TD wrote:

Anyone?

Try:

SELECT
IIf(Len([tblSSPbatch]![Batch_Number])=4,Left([tblSSPbatch].[Batch_Number],1),IIf(Len([tblSSPbatch]![Batch_Number])=5,
Left([tblSSPbatch].[Batch_Number],1),
IIf(Len([tblSSPbatch]![Batch_Number])=6,
Left([tblSSPbatch].[Batch_Number],2)))) AS Expr1
FROM tblSSPBatch
WHERE IIf([Forms]![frmReports]![cboVesselNumber]="*",
IIf(Len([tblSSPbatch]![Batch_Number])=4,
Left([tblSSPbatch].[Batch_Number],1),
IIf(Len([tblSSPbatch]![Batch_Number])=5,
Left([tblSSPbatch].[Batch_Number],1),
IIf(Len([tblSSPbatch]![Batch_Number])=6,
Left([tblSSPbatch].[Batch_Number],2)))) Like "*" &
[Forms]![frmReports]![cboVesselNumber] & "*",
IIf(Len([tblSSPbatch]![Batch_Number])=4,
Left([tblSSPbatch].[Batch_Number],1),IIf(Len([tblSSPbatch]![Batch_Number])=5,
Left([tblSSPbatch].[Batch_Number],1),
IIf(Len([tblSSPbatch]![Batch_Number])=6,
Left([tblSSPbatch].[Batch_Number],2)))) =
[Forms]![frmReports]![cboVesselNumber]);

You had something like:
SELECT
X AS Expr1
FROM tblSSPBatch
WHERE X
=IIf([Forms]![frmReports]![cboVesselNumber]="*",
X Like "*" & [Forms]![frmReports]![cboVesselNumber] & "*",
[Forms]![frmReports]![cboVesselNumber]);

I changed it to:
SELECT X AS Expr1
FROM tblSSPBatch
WHERE IIf([Forms]![frmReports]![cboVesselNumber]="*", X Like "*" &
[Forms]![frmReports]![cboVesselNumber] & "*", X =
[Forms]![frmReports]![cboVesselNumber])

I just put the criteria inside the IIf. I didn't make any attempt to
organize this mess.

It seemed to do what you asked for with a few sample records. If that
doesn't work, call the Computer Whisperer.

James A. Fortune
CD********@FortuneJames.com

[CDMAPoster]

CD********@FortuneJames.com wrote:

I just put the criteria inside the IIf. I didn't make any attempt to
organize this mess.

Something like Left(Batch_Number, Len(Batch_Number) \ 3) should help
simplify things since 4 \ 3 = 1, 5 \ 3 = 1 and 6 \ 3 = 2. You'll still
need an IIf to catch any other lengths.

James A. Fortune
CD********@FortuneJames.com