Switch values based on another field

[Greg]

I am trying to change the value of one field in my query based on the value
in another. If Field 1 had the text "ONACCNT" then in Feild 2 I want the
date to change to today's date. Any help would be great!
Thanks,

Greg

[MacDermott]

It's not completely clear what you're trying to do here.
Is Feild 2 a field in an underlying table, which you want to update with an
action query?
Or is Feild 2 a field whose value is calculated in the query itself?
In this case, what do you want the field to display if Field 1 does not
contain ONACCNT?

HTH
- Turtle
"Greg" <gk******@itlan .net> wrote in message
news:vp******** ****@corp.super news.com...

I am trying to change the value of one field in my query based on the value in another. If Field 1 had the text "ONACCNT" then in Feild 2 I want the
date to change to today's date. Any help would be great!
Thanks,

Greg

[Greg]

In the table I have a field that holds the customer Invoice # and another
field with the Invoice date. I am trying to get the total due for the
invoices due current, over 30, over 60 , over 90, over 120. I ran into on
little snag thought since the customer can be pulling from an ON Account
balance that they have already paid. The date that the On Account was
created falls under the Invoice date and throws my numbers off. What I am
trying to do is get the ON Accounts invoice dates to change to Today's date
just for the query so I can print out a report NOT a permanent change since
this is pulling from an ODBC Database connection. For all other invoices I
want the dates to stay as they are. I hope this makes more sense......

Thanks,

Greg

"Greg" <gk******@itlan .net> wrote in message
news:vp******** ****@corp.super news.com...

I am trying to change the value of one field in my query based on the value in another. If Field 1 had the text "ONACCNT" then in Feild 2 I want the
date to change to today's date. Any help would be great!
Thanks,

Greg

[Bob Quintal]

"Greg" <gk******@itlan .net> wrote in
news:l7******** *****@nwrdny03. gnilink.net:

In the table I have a field that holds the customer Invoice #
and another field with the Invoice date. I am trying to get
the total due for the invoices due current, over 30, over 60
, over 90, over 120. I ran into on little snag thought since
the customer can be pulling from an ON Account balance that
they have already paid. The date that the On Account was
created falls under the Invoice date and throws my numbers
off. What I am trying to do is get the ON Accounts invoice
dates to change to Today's date just for the query so I can
print out a report NOT a permanent change since this is
pulling from an ODBC Database connection. For all other
invoices I want the dates to stay as they are. I hope this
makes more sense......

Thanks,

Greg

I posted some crosstab query code to you tuesday night. you might
modify that as follows to handle your exception for ONACCT

Select Cust_Code,
30 * iif((now()-iif(ON,now(),In v_due_Date))\30 >4, 4,
(now()-iif(ON,now(),In v_due_Date)\30) ) as Aging,
Sum(INV_Bal)
From Invoices
Group by cust_code, iif((now()-iif(ON,now(),In v_due_Date))\30 >4, 4,
(now()-iif(ON,now(),In v_due_Date))\30 );

Bob Q.

[Greg]

Sorry Bob........... I did not make it clear that the ONACCNT is in the
Invoice Field. I need anything that has ONACCNT to place the current date
in the Invoice date field. Thanks again for your help.
Greg

"Bob Quintal" <bq******@gener ation.net> wrote in message
news:9a******** *************** *******@news.te ranews.com...

"Greg" <gk******@itlan .net> wrote in
news:l7******** *****@nwrdny03. gnilink.net:

In the table I have a field that holds the customer Invoice #
and another field with the Invoice date. I am trying to get
the total due for the invoices due current, over 30, over 60
, over 90, over 120. I ran into on little snag thought since
the customer can be pulling from an ON Account balance that
they have already paid. The date that the On Account was
created falls under the Invoice date and throws my numbers
off. What I am trying to do is get the ON Accounts invoice
dates to change to Today's date just for the query so I can
print out a report NOT a permanent change since this is
pulling from an ODBC Database connection. For all other
invoices I want the dates to stay as they are. I hope this
makes more sense......

Thanks,

Greg

I posted some crosstab query code to you tuesday night. you might
modify that as follows to handle your exception for ONACCT

Select Cust_Code,
30 * iif((now()-iif(ON,now(),In v_due_Date))\30 >4, 4,
(now()-iif(ON,now(),In v_due_Date)\30) ) as Aging,
Sum(INV_Bal)
From Invoices
Group by cust_code, iif((now()-iif(ON,now(),In v_due_Date))\30 >4, 4,
(now()-iif(ON,now(),In v_due_Date))\30 );

Bob Q.

[Bob Quintal]

"Greg" <gk******@itlan .net> wrote in
news:Zd******** ******@nwrdny03 .gnilink.net:

Sorry Bob........... I did not make it clear that the ONACCNT
is in the Invoice Field. I need anything that has ONACCNT to
place the current date in the Invoice date field. Thanks
again for your help.
Greg
replace the word ON with invoice="ONACCT " and it should work.

Alternatively, in the query that underlies your report, you could
just filter out your ONACCT values, because they should not be
listed with the invoices due, they are prepaid.

Bob

"Bob Quintal" <bq******@gener ation.net> wrote in message
news:9a******** *************** *******@news.te ranews.com...

"Greg" <gk******@itlan .net> wrote in
news:l7******** *****@nwrdny03. gnilink.net:

> In the table I have a field that holds the customer Invoice
> # and another field with the Invoice date. I am trying to
> get the total due for the invoices due current, over 30,
> over 60 , over 90, over 120. I ran into on little snag
> thought since the customer can be pulling from an ON
> Account balance that they have already paid. The date that
> the On Account was created falls under the Invoice date and
> throws my numbers off. What I am trying to do is get the
> ON Accounts invoice dates to change to Today's date just
> for the query so I can print out a report NOT a permanent
> change since this is pulling from an ODBC Database
> connection. For all other invoices I want the dates to
> stay as they are. I hope this makes more sense......
>
> Thanks,
>
> Greg

I posted some crosstab query code to you tuesday night. you
might modify that as follows to handle your exception for
ONACCT

Select Cust_Code,
30 * iif((now()-iif(ON,now(),In v_due_Date))\30 >4, 4,
(now()-iif(ON,now(),In v_due_Date)\30) ) as Aging,
Sum(INV_Bal)
From Invoices
Group by cust_code,
iif((now()-iif(ON,now(),In v_due_Date))\30 >4, 4,
(now()-iif(ON,now(),In v_due_Date))\30 );

Bob Q.

[Greg]

Thanks Bob I made the change last night and the totals are matching up for
me now. The problem with the ONACCNT is that the program we are using shows
it as a invoice with a negative number so before I found this little tid bit
out I was getting totals that would not match a report they have in the
program. I also had to make a change to the code you sent me because I
needed it to show everything 30 days and under as current and everything
31-60 days as over 30, 61-90 as over 90, etc.....

Thanks again for all of your help getting this to work.
Greg

"Bob Quintal" <bq******@gener ation.net> wrote in message
news:62******** *************** *******@news.te ranews.com...

"Greg" <gk******@itlan .net> wrote in
news:Zd******** ******@nwrdny03 .gnilink.net:

Sorry Bob........... I did not make it clear that the ONACCNT
is in the Invoice Field. I need anything that has ONACCNT to
place the current date in the Invoice date field. Thanks
again for your help.
Greg

replace the word ON with invoice="ONACCT " and it should work.

Alternatively, in the query that underlies your report, you could
just filter out your ONACCT values, because they should not be
listed with the invoices due, they are prepaid.

Bob

"Bob Quintal" <bq******@gener ation.net> wrote in message
news:9a******** *************** *******@news.te ranews.com...

"Greg" <gk******@itlan .net> wrote in
news:l7******** *****@nwrdny03. gnilink.net:

> In the table I have a field that holds the customer Invoice
> # and another field with the Invoice date. I am trying to
> get the total due for the invoices due current, over 30,
> over 60 , over 90, over 120. I ran into on little snag
> thought since the customer can be pulling from an ON
> Account balance that they have already paid. The date that
> the On Account was created falls under the Invoice date and
> throws my numbers off. What I am trying to do is get the
> ON Accounts invoice dates to change to Today's date just
> for the query so I can print out a report NOT a permanent
> change since this is pulling from an ODBC Database
> connection. For all other invoices I want the dates to
> stay as they are. I hope this makes more sense......
>
> Thanks,
>
> Greg

I posted some crosstab query code to you tuesday night. you
might modify that as follows to handle your exception for
ONACCT

Select Cust_Code,
30 * iif((now()-iif(ON,now(),In v_due_Date))\30 >4, 4,
(now()-iif(ON,now(),In v_due_Date)\30) ) as Aging,
Sum(INV_Bal)
From Invoices
Group by cust_code,
iif((now()-iif(ON,now(),In v_due_Date))\30 >4, 4,
(now()-iif(ON,now(),In v_due_Date))\30 );

Bob Q.