Expression to give total number of days between two dates

C

Catlady

I am trying to write an expression that would give the total number of days
between an "OfferSentDate" and "DateAccepted" for a metrics query. I simply
don't remember how (if I ever did) to do this and help hasn't been much help!
Suggestions would be very welcome.
 
C

Catlady

Now I feel like a total idiot, where does it go? I created a column for
ReportDate, do I put it there? I have between dates on both columns, is that
a problem? When I did it the way you showed it came back and said it had
data type syntax mismatch.
 
M

MacRae

How are you displaying this data? Are the two dates on a form in text
boxes? are they stored as variables in vba?

More information is essential
 
D

Douglas J. Steele

Not quite sure I understand what you're describing.

You created a column for ReportDate where? What do you mean by "I have
between dates on both columns"?

As MacRae suggests, more information is required!
 
C

Catlady

I have set up a query that has a column for OfferSentDate, DateAcceptSent.
I need an expression that will calculate the number of days between the two
dates for a specific period of time. So, under OfferSentDate we have a
range of dates: Between 01/01/2007 and 01/31/2007. Likewise under
DateAcceptSent because this is going to be a "Metrics" Query that is run at
the end of the month. When I put in the Expression: DateDiff("d",
"OfferSentDate","DateAcceptSent") it comes up with an error of "Data Type
Mismatch". I have tried several others as well and they either say I'm
missing a bracket or a parantheses and I just can't get it to calculate. My
hunch is maybe it is the range of dates that is messing it up.
 
C

Catlady

I actually got it to work now, but it is not filtering out the other dates
even though I put in the between dates paramaters. Any suggestions how I can
narrow it down to a specific date range?
 
D

Douglas J. Steele

The expression shouldn't have quotes around the field names: with quotes,
Access is going to try using the strings as dates, hence your error message:

DateDiff("d", OfferSentDate, DateAcceptSent)

or

DateDiff("d", [OfferSentDate], [DateAcceptSent])

(the square brackets make it clear you're referring to field names)

As to your other filtering question, are all your filters on the same line
in the query grid, or are they on multiple lines?

Easiest for me to deal with would be the actual SQL of your query. If you're
not familiar with SQL, while your query's open in Design view, select View |
SQL View from the menu. Copy what's in the window that appears, and paste it
into your reply.
 
C

Catlady

I finally got it to work by just creating a new query and putting in the same
exact info. Not sure why it wouldn't work before. Thanks for your help
 
Top