Difference of 2 columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to take the difference of two columns. For example, I have panel
number (text) and a date (text). I want to do a count function for each of
the columns (panel number and date) and then subract them from each other. I
tried doing it and it said I have a data type mismatch. What am I doing
wrong? How would I figure out this problem? Thanks.
 
You cannot subtract two strings.


So, try:

CDate(yourTextFieldForDate) - Int(YouNumberAsText)




You can check in the Debug Immediate Window:


? CDate("1 Jan 2007") - Int( "31" )
2006.12.01



Hoping it may help,
Vanderghast, Access MVP
 
I am still lost. Here is what I'm trying to do in greater detail. I want to
find the difference of 2 columns (panel number and date) by using the count
function. For example,

Panel Number Date
1111 05/09/07
2222 05/10/07
3333 05/11/07
4444
5555

As you can see if you do a count function your result would be 5 for panel
number and 3 for date. I want to subrtract those two numbers. That number
would be my result. So the result of this example would be 2. Could I do
this in a query? My goal is for the user to click the button and this result
would show up. Hope this kind of explains things more. Thanks.
 
Chinny03 said:
I want to take the difference of two columns. For example, I have panel
number (text) and a date (text). I want to do a count function for each of
the columns (panel number and date) and then subract them from each other. I
tried doing it and it said I have a data type mismatch. What am I doing
wrong? How would I figure out this problem? Thanks.
Click to expand...
maybe this, but assumes Panel Number is always there.
SELECT Count(*) - Count(Table1.some_date) AS [Panel Date Diff]
FROM Table1;
 
SELECT Count([Panel number]) - Count([Date]) as DiffCount
FROM YourTable


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top