Query Criteria

[jcpender]

I have a what I think should be a simple enough query, but I'm pulling my hair out trying to get this to work. I have a report based off of a query which prompts the user to either enter a record number manually or simply use the record number for the current record being displayed as the default. My criteria is setup as [Enter Record Number] Or [Forms]![Formname]![RecordNumber]. This works fine if I let it default to the current record being displayed. The problem occurs when I manually enter a different record number than the record currently being displayed, it reports on both record numbers. Any suggestions

 

[CraigDolphin]

Maybe try something like...

IIF([Forms]![Formname]![RecordNumber] & "" = "",[Enter Record Number],[Forms]![Formname]![RecordNumber])

... as your criterion

 

[jcpender]

Thanks for the quick reply Craig, no luck, comes back with the same results.

 

[CraigDolphin]

Really? I have a query and form that uses the same criteria expression...It pops open the parameter request each time and if no value is in the form's textbox it uses the manually entered number. If there is a number in the form it ignores the manually entered number and uses the form number.

Maybe if you post the sql of your query we can see what's going on.

An alternative approach would be a criteria like
Nz([Forms]![Formname]![RecordNumber],[Enter Record Number])

Or, if you only want the dialog box to show up when the form is empty:
create a public function in a separate code module (not the code module for the form).

Public Function GetInfo() As String
If [Forms]![fmLookupSample]![SampleNo] & "" = "" Then
GetInfo = InputBox("Input Record Number:")
Else
GetInfo = [Forms]![fmLookupSample]![SampleNo]
End If
End Function

(this is for a text string, if you want to work with integers then change the data type of the GetInfo funtion )

And in the criteria row of the query grid use
Eval("GetInfo()")

 

[jcpender]

Hey Craig, this is the sql for the query.

SELECT [MRB TABLE II].[MRB STATUS], [MRB TABLE II].SHEET_NO, [MRB TABLE II].PART_NUMBE
FROM [MRB TABLE II]
WHERE ((([MRB TABLE II].SHEET_NO)=[Enter Record Number] Or ([MRB TABLE II].SHEET_NO)=[forms]![Inspection]![Sheet_No]));

 

[CraigDolphin]

Your sql isn't using the criteria I gave you. It should look like:

SELECT [MRB TABLE II].[MRB STATUS], [MRB TABLE II].SHEET_NO, [MRB TABLE II].PART_NUMBE
FROM [MRB TABLE II]
WHERE ((([MRB TABLE II].[SHEET_NO]=IIf([Enter Record Number] & ""<>"",[Enter Record Number],[Forms]![Inspection]![Sheet_No])));

This will prompt you for a manual code, if you leave it blank it will use the value in the form, if you enter a number it will use that number instead.

 

[jcpender]

Thanks a million Craig, that did it. I've been picking at this one for quite a while now!

 

[CraigDolphin]

Glad we got it sorted. I'm just not sure why you weren't able to implement my original answer sucessfully since the sql I posted back just used that same solution.