Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Number subtracting using D-Max

Posted on 2002-07-18
12
Medium Priority
?
316 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 1200 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

636 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