parameter query

  • Thread starter Thread starter Christina
  • Start date Start date
C

Christina

Is there a trick way like using the asterisk to allow a parameter query to
return all records if the parameter criteria is left blank. I think I saw it
before.

Thanks

Christina
 
Is there a trick way like using the asterisk to allow a parameter query to
return all records if the parameter criteria is left blank. I think I saw it
before.

Thanks

Christina
Click to expand...

Wish you would have posted your actual SQL so that my reply would be
relevant.
Something like:
Select .... etc...
Where YourTable.SomeField = [Enter the name] or [Enter the name] Is
Null
 
Christina

I suppose you could try (untested):

Like * & [Enter your parameter]

in the Selection Criterion.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
If the relevant TEXT field always has data (or you don't care if fields with
Null values are excluded)

Return exact match to parameter input or all non-null records if input is
left blank.
Field: SomeTextField
Criteria: Like Nz([What Value do you want?],"*")

Return all records starting with input value and all non-null records if
input is left blank
Field: SomeTextField
Criteria: Like [What Value do you want?] & "*"

Return exact matcht to parameter input or ALL records if input is left blank
Field: SomeTextField
Criteria: =[What Value do you want?] OR [What Value do you want?] Is Null

NOTE: The immediately above criteria will be rearranged by Access if you are
using the Design view (query grid).

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks. I tried it and it worked. I am using a dialog box to entry three
seperate parameters. There is also another criteria which in the criteria
says between [enter beg date] and [enter end date]. It does not seem to work
with that. Any suggestions.

KARL DEWEY said:
Like [Enter parameter] & "*"
--
KARL DEWEY
Build a little - Test a little


Christina said:
Is there a trick way like using the asterisk to allow a parameter query to
return all records if the parameter criteria is left blank. I think I saw it
before.

Thanks

Christina
Click to expand...
Click to expand...
 
You can not use the asterisk and 'Like' with your DateTime fields.
Use this ---
Between IIF([enter beg date] Is Null, #12/31/1899#, [enter beg date]) And
IIF([enter end date] Is Null, #12/31/9999#, [enter end date])


--
KARL DEWEY
Build a little - Test a little


Christina said:
Thanks. I tried it and it worked. I am using a dialog box to entry three
seperate parameters. There is also another criteria which in the criteria
says between [enter beg date] and [enter end date]. It does not seem to work
with that. Any suggestions.

KARL DEWEY said:
Like [Enter parameter] & "*"
--
KARL DEWEY
Build a little - Test a little


Christina said:
Is there a trick way like using the asterisk to allow a parameter query to
return all records if the parameter criteria is left blank. I think I saw it
before.

Thanks

Christina
Click to expand...
Click to expand...
Click to expand...
 
Absolutely grateful..It worked perfectly.

Thanks

KARL DEWEY said:
You can not use the asterisk and 'Like' with your DateTime fields.
Use this ---
Between IIF([enter beg date] Is Null, #12/31/1899#, [enter beg date]) And
IIF([enter end date] Is Null, #12/31/9999#, [enter end date])


--
KARL DEWEY
Build a little - Test a little


Christina said:
Thanks. I tried it and it worked. I am using a dialog box to entry three
seperate parameters. There is also another criteria which in the criteria
says between [enter beg date] and [enter end date]. It does not seem to work
with that. Any suggestions.

KARL DEWEY said:
Like [Enter parameter] & "*"
--
KARL DEWEY
Build a little - Test a little


:

Is there a trick way like using the asterisk to allow a parameter query to
return all records if the parameter criteria is left blank. I think I saw it
before.

Thanks

Christina
Click to expand...
Click to expand...
Click to expand...
 
Back
Top