Solved

Number subtracting using D-Max

Posted on 2002-07-18
12
284 Views
Last Modified: 2006-11-17

I am using the code in my query below to subtract dates in the same column ( hope formating conmes out OK )

Date          MTBF
4/26/00          
5/4/00          8
5/5/00          1
etc.
below is the code:

MTBF: [ShQry_Hot CallsDateDiff].[DATE]-DMax("Date","[ShQry_Hot CallsDateDiff]","Date < #" & Format([ShQry_Hot CallsDateDiff].[Date],"mm\/dd\/yyyy") & "#")

How can I format this same thing to subtract numbers

0007          
0006          1
0002          4
etc.

0
Comment
Question by:mrwebmaster
  • 7
  • 5
12 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 7162866
The date is working as there is a date in all rows.
For the numbers you'll need e.g. an autonumber that's uniquely keeping them in the correct order.
Assume you have an autoID field then use:

NumDiff: [ShQry_Hot NumDiff].[NumberVal]-DLOOKUP("[NumberVal]","ShQry_Hot NumDiff","[autoID]="&DMax("[autoID]","[ShQry_Hot NumDiff]","[autoID] < " & [autoID])

But normally I would use a function to do this.

Nic;o)
0
 

Author Comment

by:mrwebmaster
ID: 7163286
Sorry I forgot to tell you the name of the foeld I am attempting to get the number from is called EQNUM.
Also when I tried to run the code you posted I got an err that said " The expression you entered is missing a closing parenthesis, bracket, (]) or a vertical bar.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7163371
Try:

NumDiff: [ShQry_Hot NumDiff].[EQNUM]-DLOOKUP("[EQNUM]","ShQry_Hot NumDiff","[autoID]="&DMax("[autoID]","[ShQry_Hot NumDiff]","[autoID] < " & [autoID]))

Nic;o)
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:mrwebmaster
ID: 7163462
nico5038

I did change the [autoID] field to the actual name, this is what I am using after modifying your code. I get a
#error in the NumDiff field. What Now...

NumDiff: [ShQry_PM_NumDiff].[WONUM]-DLookUp("[WONUM]","ShQry_PM_NumDiff","[WONUM]=" & DMax("[WONUM]","[ShQry_PM_NumDiff]","[WONUM] < " & [WONUM]))

I have also added more points for your hard work.
0
 

Author Comment

by:mrwebmaster
ID: 7163472

The number below the top one will allways be a larger number than the one below. Doess this matter ?
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 300 total points
ID: 7163542
Yes, then the same method as the date can be used:

NumDiff: [ShQry_Hot NumDiff].[EQNUM]-DMax("[EQNUM]","[ShQry_Hot NumDiff]","[EQNUM] < " & [EQNUM])

Nic;o)


0
 
LVL 54

Expert Comment

by:nico5038
ID: 7163545
BTW if it's a counter, please keep in mind that they can "overflow" and start from 1 again....

Nic;o)
0
 

Author Comment

by:mrwebmaster
ID: 7163793
I still get #Error, Here is the SQL, I did make all new and smaller query just to get it to work.
one table is called z-test1, this sets up criteria, below is the SQL from the other query called z-test2.
I have also tried only using 1 query directly from the table but no luck. I still get #Error where the numbers should be.

SELECT [z-test1].WONUM, [z-test1].[WONUM]-DMax("[WONUM]","[z-test1]","[WONUM] < " & [WONUM]) AS NumDiff
FROM [z-test1]
GROUP BY [z-test1].WONUM;

Thanks for your help
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7164247
I created a z-test1 with:
WONUM    
1    
4
7
9
and the copy/pasted SQL gave:
WONUM     NumDiff
1    
4     3
7     3
9     2

??
Are you sure wonum is a number field?

Nic;o)
0
 

Author Comment

by:mrwebmaster
ID: 7170318

Yes,
But I Still get #ERROR in the field

0
 
LVL 54

Expert Comment

by:nico5038
ID: 7170333
Can you drop the compacted and zipped .mdb ?
(See my profile for the address)

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7281480
Lost track of this one, but it's time to clean up this TA and I do recall something about solving this. If not, just add a comment.
I will leave a recommendation in the Cleanup topic area that this question is:
 - Answered by: nico5038
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question