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 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
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
"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.
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.
"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.
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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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. ...
|
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...
|
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...
|
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...
|
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...
|
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;
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |