• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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