Question on doing a date calculation in MS Access Query

[Daniel Hidalgo]

Hello,

I have an issue that is probably really simple.

I am trying to do a date calculation between two date fields but I keep
getting an error in Access. Here is the info that I am working with.
I have added notes next to the field names stating what the fields are.

Table Name: CPE_Order_Log
Field Names:

MONTH (Month Order Received)
CUSTOMER (Customer Name)
ORDER_ID (System Record Number)
PURCHASE_ORDER (Our Order Number We Assigned)
SYS_DO_ID (Sister System Order Number)
FC_EOL_ID (Internal Requestition Number to Vendor)
DATE_RCVD (Date We Received The Order)
DATE_CLEAN (Date Order Was Ready To Place)
DATE_LOGGED (Date Order Was Updated In System)
DO_DATE (Date Order Placed With Vendor)
DATE_LOADED (Date Order Reflected In System)
EOL_DATE_ENTERED (Date Vendor Confirmed Order)
EOL_COMPLETE_DATE (Date Vendor Shipped Order)
INVOICE_DATE (Date Customer Was Sent Bill)
ITEMS_PER_EOL (Number of Items On Order)

I am trying to write a couple of queries that I can run to show the
following:

Interval between DATE_RCVD and DATE_CLEAN
Interval between DATE_RCVD and EOL_DATE_ENTERED
Interval between DATE_RCVD and EOL_COMPLETE_DATE
Interval between DATE_RCVD and INVOICE_DATE

I am assuming that the calculation would be the same just plugging in
different fields.

I tried entering a DateDiff calculation in MS Access Query but when I
ran it the result was blank. The end result is that I want to be able
to generate a report showing what the results are per line for the
intervals.

Any help that you can provide would be most appreciated.

Thanks,

Daniel Hidalgo

 

[Yellowstone Valley Tree Surgeons]

I have done this using a new field in the query like this: Date difference:
[Date_Rcvd]-[Date_Clean]

You can name "date difference" anything you want in a new field in the
query.

 

[John Spencer]

You didn't post what you tried, so it is hard to correct that.

Field: RcvdToClean: DateDiff("d",[Date_Rcvd],[Date_Clean])

Is that what you used? If it failed, how did it fail? An error message (what
was the message), wrong data, no data?

Are your fields date time fields or text fields? Are they Null (Blank)? If
they are null, what result do you want? Zero, blank, something else?

Field: RcvdToClean: IIF(IsNull(Date_rcvd) or IsNull(Date_clean), Null, DateDiff("d",[Date_Rcvd],[Date_Clean]))

 

[Daniel Hidalgo]

John,

I tried using a DateDiff expression such as Interval:
DateDiff("d",[Date_Rcvd],[Date_Clean])
I received an error stating "The expression that you entered has an
invalid .(dot) or ! Operator or invalid parentheses and the cursor is
on the parentheses before the "d". I was able to build it via the
build expression tool and not get an error but then did not get any
results.

The fields are date time fields. There are some blanks but 90% have
data. If they are blank then I would prefer that the field be left
blank.

Thanks again,

Daniel Hidalgo

 

[John Spencer]

Well, at this point I can only suggest that you post the SQL of the query
that is NOT working, The only time I've seen something like this is when I
have an error in my SQL statement.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

That message usually means that you have a missing parentheses.

If you are building the code in VBA, post the code that builds the SQL
statement. If you are building the statement in the SQL text window, then
your code should read more like

DateDiff("d",[Date_Rcvd],[Date_Clean]) as Interval

The only other possibility, I can see right now is that Interval is a
reserved word in Access and is causing the problem.