Solved

Number subtracting using D-Max

Posted on 2002-07-18
12
255 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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now