Query Parameters

[HOJU15]

My Query looks like

PARAMETERS LSD Value, [SECTION] Value, TOWNSHIP Value, RANGE Value, MERIDIAN Value;
SELECT [located in].Lsd, [located in].Section, [located in].Township, [located in].Range, [located in].Meridian, [located in].Job
FROM [located in]
WHERE ((([located in].Lsd)=[LSD]) AND (([located in].Section)=[SECTION]) AND (([located in].Township)=[TOWNSHIP]) AND (([located in].Range)=[RANGE]) AND (([located in].Meridian)=[MERIDIAN])) OR ((([located in].Lsd) Is Null) AND (([located in].Section) Is Null) AND (([located in].Township) Is Null) AND (([located in].Range) Is Null) AND (([located in].Meridian) Is Null));


But if I do not type anyting in the parameters field I get no records. I think its because

"If the type of join you're using retrieves only matching records from the two joined tables, any records in which the joined field contains a Null value won't be included in the query's results. You can use the Nz function to convert Null values to a zero, zero-length string, or other specified value so that those records are included in the query's results. "

How do I use this function

 

[Nouba]

I think you have to adjust the where clause for getting all the values of the specified fields in case you just hit the enter key instead of typing in a parameter value. If you're not using the query for a cross tab, you can remove the PARAMETERS part of the query.

The Nz function will give you a default or self defined value in case the field content is empty (Null). For a numerical field you will get 0 as the default value, for a text field it is "". That may be not what you're looking for. The Nz function is also well described in you help file.

PARAMETERS
  LSD Value
, [SECTION] Value
, TOWNSHIP Value
, RANGE Value
, MERIDIAN Value;
SELECT 
  Lsd
, Section
, Township
, Range
, Meridian
, Job
FROM [located in]
WHERE
  (Lsd=[LSD] Or [LSD] Is Null) AND
  (Section=[SECTION] Or [SECTION] Is Null) AND
  (Township=[TOWNSHIP] Or [TOWNSHIP] Is Null) AND
  (L.Range=[RANGE] Or [RANGE] Is Null) AND
  (Meridian=[MERIDIAN] Or [MERIDIAN] Is Null) AND
  (Job=[JOB] Or [JOB] Is Null)