Change date and formula trouble in query - please help!!

[Tina C.]

Hello,

I need help with two things in Access 2000:
1. I would like the date in the query (now shown as
mm/dd/yyyy)to appear as month or to run the query based on
the month only.

2. In the same query - In column "X", the options are
Over, Under, or Diff. I need a If/then function to say if
the item is over in X then mutiply quantity by .3,if it is
under then multiply by .12, or if diff. then nothing.
(eg - item1 (Product-a) 12 (quantity-b) over (x-c) =12*.3
(tax-d)

If anyone could help me, I would greatly appreciate it.
I'm a basic user and I'm completely stuck.

Thanks,

T

 

[John Vinson]

Hello,

I need help with two things in Access 2000:
1. I would like the date in the query (now shown as
mm/dd/yyyy)to appear as month or to run the query based on
the month only.

Regardless of how it's displayed, a Date/Time field is stored
internally as a number ( a count of days since an arbitrary start
point, December 30, 1899). If you want to see that date as a month
only simply use a Format property of "mmmm" (for December, "mmm" for
Dec) on the Textbox in which you display the data. (Do use a Form for
onscreen display, or a Report for printing; query datasheets are VERY
limited in their capability).

2. In the same query - In column "X", the options are
Over, Under, or Diff. I need a If/then function to say if
the item is over in X then mutiply quantity by .3,if it is
under then multiply by .12, or if diff. then nothing.
(eg - item1 (Product-a) 12 (quantity-b) over (x-c) =12*.3
(tax-d)

The Choose() function is helpful here: it takes any number of pairs of
arguments and loops through them. If the first argument of a pair is
True it returns the other member of that pair. An expression like

Tax-D: [Quantity] * Choose([X] = "Over", .3, [X] = "Under", .12, [X] =
"Diff", NULL)

depending on what you mean by "nothing" - using NULL will display a
blank no matter what is in Quantity.

 

[Eddy]

John,

Isn't that the same as a switch statement? hmm?? confused.

-----Original Message-----

Hello,

I need help with two things in Access 2000:
1. I would like the date in the query (now shown as
mm/dd/yyyy)to appear as month or to run the query based on
the month only.

Regardless of how it's displayed, a Date/Time field is stored
internally as a number ( a count of days since an arbitrary start
point, December 30, 1899). If you want to see that date as a month
only simply use a Format property of "mmmm" (for December, "mmm" for
Dec) on the Textbox in which you display the data. (Do use a Form for
onscreen display, or a Report for printing; query datasheets are VERY
limited in their capability).

2. In the same query - In column "X", the options are
Over, Under, or Diff. I need a If/then function to say if
the item is over in X then mutiply quantity by .3,if it is
under then multiply by .12, or if diff. then nothing.
(eg - item1 (Product-a) 12 (quantity-b) over (x-c) =12*.3
(tax-d)

The Choose() function is helpful here: it takes any number of pairs of
arguments and loops through them. If the first argument of a pair is
True it returns the other member of that pair. An expression like

Tax-D: [Quantity] * Choose([X] = "Over", .3, [X] = "Under", .12, [X] =
"Diff", NULL)

depending on what you mean by "nothing" - using NULL will display a
blank no matter what is in Quantity.


.