Retrieve all records if criteria not entered?

  • Thread starter Thread starter jag
  • Start date Start date
J

jag

Hi

I have a query that gets it criteria from a form and this is no problem.
I am wondering how to get the query to retrieve all records if no criteria
is entered.

thanks in advance
 
Use the LIKE and * operators in the criteria of the query.

i.e. LIKE "*" & forms!frmName!controlname

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
This works fantastically most of the time, but not when criteria is entered
and there is similar data to be retrieved. I have tried something like

IIf(IsNull([forms]![frmSTR_ReportsSwitch].[Company]),Like
"*",[forms]![frmSTR_ReportsSwitch].[Company])

but this doesn't work.

any ideas.

thanks again

dee

--


-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com


[MVP] S. Clark said:
Use the LIKE and * operators in the criteria of the query.

i.e. LIKE "*" & forms!frmName!controlname

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Click to expand...
 
Like "*" & [forms]![frmSTR_ReportsSwitch].[Company]

jag said:
This works fantastically most of the time, but not when criteria is entered
and there is similar data to be retrieved. I have tried something like

IIf(IsNull([forms]![frmSTR_ReportsSwitch].[Company]),Like
"*",[forms]![frmSTR_ReportsSwitch].[Company])

but this doesn't work.

any ideas.

thanks again

dee

--


-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com


[MVP] S. Clark said:
Use the LIKE and * operators in the criteria of the query.

i.e. LIKE "*" & forms!frmName!controlname

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Click to expand...
Click to expand...
 
I did use

Like "*" & [forms]![frmSTR_ReportsSwitch].[Company]

and this works most of the time.

if i enter a company id of 2 for example in my form criteria,
this retrieves 2 records not one.

It retrieves company id 2 and company id 12 because of the like "*"

Do you have any ideas on getting around this

I want to retrieve all records if no criteria is entered and only the ones
specified if criteria is entered.

Thanks again

dee


--


-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com


[MVP] S. Clark said:
Like "*" & [forms]![frmSTR_ReportsSwitch].[Company]

This works fantastically most of the time, but not when criteria is entered
and there is similar data to be retrieved. I have tried something like

IIf(IsNull([forms]![frmSTR_ReportsSwitch].[Company]),Like
"*",[forms]![frmSTR_ReportsSwitch].[Company])

but this doesn't work.

any ideas.

thanks again

dee

--


-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com


[MVP] S. Clark said:
Use the LIKE and * operators in the criteria of the query.

i.e. LIKE "*" & forms!frmName!controlname

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hi

I have a query that gets it criteria from a form and this is no problem.
I am wondering how to get the query to retrieve all records if no
criteria
is entered.

thanks in advance
Click to expand...

-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com
Click to expand...
Click to expand...
Click to expand...
 
IIF(len([forms]![frmSTR_ReportsSwitch].[Company])=0, "*",
[forms]![frmSTR_ReportsSwitch].[Company])

jag said:
I did use

Like "*" & [forms]![frmSTR_ReportsSwitch].[Company]

and this works most of the time.

if i enter a company id of 2 for example in my form criteria,
this retrieves 2 records not one.

It retrieves company id 2 and company id 12 because of the like "*"

Do you have any ideas on getting around this

I want to retrieve all records if no criteria is entered and only the ones
specified if criteria is entered.

Thanks again

dee


--


-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com


[MVP] S. Clark said:
Like "*" & [forms]![frmSTR_ReportsSwitch].[Company]

This works fantastically most of the time, but not when criteria is entered
and there is similar data to be retrieved. I have tried something like

IIf(IsNull([forms]![frmSTR_ReportsSwitch].[Company]),Like
"*",[forms]![frmSTR_ReportsSwitch].[Company])

but this doesn't work.

any ideas.

thanks again

dee
Click to expand...

-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com


Use the LIKE and * operators in the criteria of the query.

i.e. LIKE "*" & forms!frmName!controlname

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hi

I have a query that gets it criteria from a form and this is no problem.
I am wondering how to get the query to retrieve all records if no
criteria
is entered.

thanks in advance

--
Click to expand...

-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com
Click to expand...
Click to expand...
Click to expand...
 
whoops sorry about the direct email.


I tried that last statement and it works if criteria is entered but not if
its not.
It doesn't seem to recognise the len as 0.

When i enter a company of 2 thru the form the query retrieves the correct
record,
but when i do the following it doesn't

IIF(len([forms]![frmSTR_ReportsSwitch].[Company])=0, 2,
[forms]![frmSTR_ReportsSwitch].[Company])

Any ideas?

dee



--


-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com


[MVP] S. Clark said:
IIF(len([forms]![frmSTR_ReportsSwitch].[Company])=0, "*",
[forms]![frmSTR_ReportsSwitch].[Company])

I did use

Like "*" & [forms]![frmSTR_ReportsSwitch].[Company]

and this works most of the time.

if i enter a company id of 2 for example in my form criteria,
this retrieves 2 records not one.

It retrieves company id 2 and company id 12 because of the like "*"

Do you have any ideas on getting around this

I want to retrieve all records if no criteria is entered and only the ones
specified if criteria is entered.

Thanks again

dee


--


-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com


[MVP] S. Clark said:
Like "*" & [forms]![frmSTR_ReportsSwitch].[Company]

This works fantastically most of the time, but not when criteria is
entered
and there is similar data to be retrieved. I have tried something like

IIf(IsNull([forms]![frmSTR_ReportsSwitch].[Company]),Like
"*",[forms]![frmSTR_ReportsSwitch].[Company])

but this doesn't work.

any ideas.

thanks again

dee
Click to expand...

-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com


Use the LIKE and * operators in the criteria of the query.

i.e. LIKE "*" & forms!frmName!controlname

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hi

I have a query that gets it criteria from a form and this is no
problem.
I am wondering how to get the query to retrieve all records if no
criteria
is entered.

thanks in advance

--
Click to expand...

-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com
Click to expand...
Click to expand...
Click to expand...
 
Pardon me for jumping in. But is the company field TEXT or Numeric?

You can use criteria similar to the following if Company is a text Field

Like IIF(IsNull([forms]![frmSTR_ReportsSwitch]![Company]), "*",
[forms]![frmSTR_ReportsSwitch]![Company])

If you want to Test the length of [forms]![frmSTR_ReportsSwitch]![Company] you
should add an empty string to the end to ensure you are testing a string. If
the control shows as blank, then it will be null. SO,

IIF(Len([forms]![frmSTR_ReportsSwitch]![Company] & "") = 0, ....

IF Company field is numeric then you might try criteria of:
Between NZ([forms]![frmSTR_ReportsSwitch]![Company]),-1000000)
And NZ([forms]![frmSTR_ReportsSwitch]![Company]),1000000)

All the above solutions RELY on the company field not being ever being null. If
the field could ever be null and you still want to return those records then you
need to post back for an alternative solution. If you do postback for an
alternative solution, please tell us the TYPE of the company field.

whoops sorry about the direct email.

I tried that last statement and it works if criteria is entered but not if
its not.
It doesn't seem to recognise the len as 0.

When i enter a company of 2 thru the form the query retrieves the correct
record,
but when i do the following it doesn't

IIF(len([forms]![frmSTR_ReportsSwitch].[Company])=0, 2,
[forms]![frmSTR_ReportsSwitch].[Company])

Any ideas?

dee

--

-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com

[MVP] S. Clark said:
IIF(len([forms]![frmSTR_ReportsSwitch].[Company])=0, "*",
[forms]![frmSTR_ReportsSwitch].[Company])

I did use

Like "*" & [forms]![frmSTR_ReportsSwitch].[Company]

and this works most of the time.

if i enter a company id of 2 for example in my form criteria,
this retrieves 2 records not one.

It retrieves company id 2 and company id 12 because of the like "*"

Do you have any ideas on getting around this

I want to retrieve all records if no criteria is entered and only the ones
specified if criteria is entered.

Thanks again

dee


--


-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com


Like "*" & [forms]![frmSTR_ReportsSwitch].[Company]

This works fantastically most of the time, but not when criteria is
entered
and there is similar data to be retrieved. I have tried something like

IIf(IsNull([forms]![frmSTR_ReportsSwitch].[Company]),Like
"*",[forms]![frmSTR_ReportsSwitch].[Company])

but this doesn't work.

any ideas.

thanks again

dee

--




-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com


Use the LIKE and * operators in the criteria of the query.

i.e. LIKE "*" & forms!frmName!controlname

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hi

I have a query that gets it criteria from a form and this is no
problem.
I am wondering how to get the query to retrieve all records if no
criteria
is entered.

thanks in advance

--
Click to expand...
Click to expand...
Click to expand...
 
Back
Top