473,320 Members | 2,111 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Switch values based on another field

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
Nov 12 '05 #1
6 4661
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.supernews.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

Nov 12 '05 #2
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.supernews.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

Nov 12 '05 #3
"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(),Inv_due_Date))\30>4, 4,
(now()-iif(ON,now(),Inv_due_Date)\30)) as Aging,
Sum(INV_Bal)
From Invoices
Group by cust_code, iif((now()-iif(ON,now(),Inv_due_Date))\30>4, 4,
(now()-iif(ON,now(),Inv_due_Date))\30);

Bob Q.

Nov 12 '05 #4
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******@generation.net> wrote in message
news:9a******************************@news.teranew s.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(),Inv_due_Date))\30>4, 4,
(now()-iif(ON,now(),Inv_due_Date)\30)) as Aging,
Sum(INV_Bal)
From Invoices
Group by cust_code, iif((now()-iif(ON,now(),Inv_due_Date))\30>4, 4,
(now()-iif(ON,now(),Inv_due_Date))\30);

Bob Q.

Nov 12 '05 #5
"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******@generation.net> wrote in message
news:9a******************************@news.teranew s.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(),Inv_due_Date))\30>4, 4,
(now()-iif(ON,now(),Inv_due_Date)\30)) as Aging,
Sum(INV_Bal)
From Invoices
Group by cust_code,
iif((now()-iif(ON,now(),Inv_due_Date))\30>4, 4,
(now()-iif(ON,now(),Inv_due_Date))\30);

Bob Q.



Nov 12 '05 #6
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******@generation.net> wrote in message
news:62******************************@news.teranew s.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******@generation.net> wrote in message
news:9a******************************@news.teranew s.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(),Inv_due_Date))\30>4, 4,
(now()-iif(ON,now(),Inv_due_Date)\30)) as Aging,
Sum(INV_Bal)
From Invoices
Group by cust_code,
iif((now()-iif(ON,now(),Inv_due_Date))\30>4, 4,
(now()-iif(ON,now(),Inv_due_Date))\30);

Bob Q.


Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Myster Ious | last post by:
Polymorphism replaces switch statements, making the code more compact/readable/maintainable/OO whatever, fine! What I understand, that needs to be done at the programming level, is this: a...
2
by: AA Arens | last post by:
Somebody willing to assist me in the following? I want to have values from one field copied to another field when I choose choose a item from a drop down menu. I have 2 forms: 1 Company...
3
by: pgraeve | last post by:
I am a convert from VB to C# so bear with me on this "conversion" question C# switch statement seems to be the closest relative to VB's Select Case. I used VB's Select Case statement liberally. ...
13
by: Fei Liu | last post by:
Hi Group, I've got a problem I couldn't find a good solution. I am working with scientific data files in netCDF format. One of the properties of netCDF data is that the actual type of data is only...
6
by: Sanjay | last post by:
Hi, Is it possible to save all the names/values of a form to another memo textfield before a Insert to the database then when required on another page split that memo textfield into the name and...
7
by: cytec123187 | last post by:
Hello, I am working on an Adobe Acrobat file that uses javascript for calculations. I am trying to create a field that uses two other fields to determine a number value. I think this requires...
1
by: Rajasuds | last post by:
I have a question in MS Access I have two tables MASTER with Fields 1, Field 2, Field 3 TRANS with Field1, Field 2, Field,10 I need to set up a look up condition where, I need !Field 2 to...
12
by: | last post by:
Is it fine to call another method from Switch? Eg. Switch (stringVar) { case ("a"): somVar = "whatever"; Another_Method(); //call another method return;
6
by: asadikhan | last post by:
Hello, I have a bit of a design issue around this application I am developing, and I just want to run it through some of the brains out here. So I have a table called ErrorCheck which...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.