help on sql access language & a little question

  • Thread starter Thread starter Mr. x
  • Start date Start date
M

Mr. x

Hello,

I need one-line help on access sql (2002 for xp), please.
I have also a little question :
In sql server there are sql functions :
isnull, and case ...
How can I write those in access sql ?

Thanks :)
 
Sorry: I just remembered that IsNull is different in SQL than in Access. The
equivalent to SQL Server's IsNull function would be Nz:

Nz(variant[, valueifnull])

The Nz function has the following arguments.

Argument Description
variant A variable of data type Variant.
valueifnull Optional (unless used in a query). A Variant that
supplies a value to be returned if the variant
argument is Null.



--
Doug Steele, Microsoft Access MVP



Douglas J. Steele said:
IsNull should work in Access as well.

For Case, depending on what you need to do, you can either use Choose, IIf,
or Switch.

Choose:
Choose(index, choice-1[, choice-2, ... [, choice-n]])

The Choose function syntax has these parts:

Part Description
index Required. Numeric expression or field that results
in a value between 1 and the number of available choices.
choice Required. Variant expression containing one of the possible
choices.

Choose returns a value from the list of choices based on the value of index.
If index is 1, Choose returns the first choice in the list; if index is 2,
it returns the second choice, and so on.

Example:
=Choose([ShipperID], "Speedy", "United", "Federal")

You can use the Choose function to create a calculated control whose value
is determined by the value of a field in a table in your database. For
example, suppose you have a Shippers table that contains a field called
ShipperID. You could create a calculated control on a form to display a text
name for the shipper based on the value of the ShipperID field.

IIf:
IIf(expr, truepart, falsepart)

The IIf function syntax has these named arguments:

Part Description
expr Required. Expression you want to evaluate.
truepart Required. Value or expression returned if expr is True.
falsepart Required. Value or expression returned if expr is False.

Example:

= IIf([OrderAmount] > 1000, "Large", "Small")

Returns the word "Large" if the amount is greater than 1000; otherwise, it
returns the word "Small".


Switch:
Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])

The Switch function syntax has these parts:

Part Description
expr Required. Variant expression you want to evaluate.
value Required. Value or expression to be returned if the
corresponding expression is True.

Example:

= Switch([ShipCity] = "Madrid", "Spanish", _
[ShipCity] = "Berlin", "German", _
[ShipCity] = "Torino", "Italian", _
[ShipCountry] = "France", "French", _
True, "English")

If the city is Madrid, the Switch function returns "Spanish"; if it is
Berlin, it returns "German"; and so on. If the city is not one of those
listed, but the country is France, it returns "French". If the city in
question is not in the list, the Switch function returns "English".

--
Doug Steele, Microsoft Access MVP



Mr. x said:
Hello,

I need one-line help on access sql (2002 for xp), please.
I have also a little question :
In sql server there are sql functions :
isnull, and case ...
How can I write those in access sql ?

Thanks :)
Click to expand...
Click to expand...
 
Hmm. I posted a followup to this a few hours ago that doesn't seem to have
made the server.

The equivalent of SQL Server's IsNull is Access's Nz.

Nz(variant[, valueifnull])

The Nz function has the following arguments.

Argument Description
variant A variable of data type Variant.
valueifnull Optional (unless used in a query). A Variant that
supplies a value to be returned if the variant
argument is Null.


--
Doug Steele, Microsoft Access MVP



Douglas J. Steele said:
IsNull should work in Access as well.

For Case, depending on what you need to do, you can either use Choose, IIf,
or Switch.

Choose:
Choose(index, choice-1[, choice-2, ... [, choice-n]])

The Choose function syntax has these parts:

Part Description
index Required. Numeric expression or field that results
in a value between 1 and the number of available choices.
choice Required. Variant expression containing one of the possible
choices.

Choose returns a value from the list of choices based on the value of index.
If index is 1, Choose returns the first choice in the list; if index is 2,
it returns the second choice, and so on.

Example:
=Choose([ShipperID], "Speedy", "United", "Federal")

You can use the Choose function to create a calculated control whose value
is determined by the value of a field in a table in your database. For
example, suppose you have a Shippers table that contains a field called
ShipperID. You could create a calculated control on a form to display a text
name for the shipper based on the value of the ShipperID field.

IIf:
IIf(expr, truepart, falsepart)

The IIf function syntax has these named arguments:

Part Description
expr Required. Expression you want to evaluate.
truepart Required. Value or expression returned if expr is True.
falsepart Required. Value or expression returned if expr is False.

Example:

= IIf([OrderAmount] > 1000, "Large", "Small")

Returns the word "Large" if the amount is greater than 1000; otherwise, it
returns the word "Small".


Switch:
Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])

The Switch function syntax has these parts:

Part Description
expr Required. Variant expression you want to evaluate.
value Required. Value or expression to be returned if the
corresponding expression is True.

Example:

= Switch([ShipCity] = "Madrid", "Spanish", _
[ShipCity] = "Berlin", "German", _
[ShipCity] = "Torino", "Italian", _
[ShipCountry] = "France", "French", _
True, "English")

If the city is Madrid, the Switch function returns "Spanish"; if it is
Berlin, it returns "German"; and so on. If the city is not one of those
listed, but the country is France, it returns "French". If the city in
question is not in the list, the Switch function returns "English".

--
Doug Steele, Microsoft Access MVP



Mr. x said:
Hello,

I need one-line help on access sql (2002 for xp), please.
I have also a little question :
In sql server there are sql functions :
isnull, and case ...
How can I write those in access sql ?

Thanks :)
Click to expand...
Click to expand...
 
Back
Top