• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

Number subtracting using D-Max


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
mrwebmaster
Asked:
mrwebmaster
  • 7
  • 5
1 Solution
 
nico5038Commented:
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
 
mrwebmasterAuthor Commented:
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
 
nico5038Commented:
Try:

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

Nic;o)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mrwebmasterAuthor Commented:
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
 
mrwebmasterAuthor Commented:

The number below the top one will allways be a larger number than the one below. Doess this matter ?
0
 
nico5038Commented:
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
 
nico5038Commented:
BTW if it's a counter, please keep in mind that they can "overflow" and start from 1 again....

Nic;o)
0
 
mrwebmasterAuthor Commented:
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
 
nico5038Commented:
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
 
mrwebmasterAuthor Commented:

Yes,
But I Still get #ERROR in the field

0
 
nico5038Commented:
Can you drop the compacted and zipped .mdb ?
(See my profile for the address)

Nic;o)
0
 
nico5038Commented:
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now