By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,779 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Problem matching any number in query

P: n/a
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

Jun 8 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
TD

Anyone?

Jun 8 '06 #2

P: n/a
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

Jun 9 '06 #3

P: n/a
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

Jun 9 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.