Date Calculation

[jiajian]

I am retrieving data from my company ERP system with Access .One of the field which mean for date [SDADDJ]was appear as 6 figure.
E.g 102365 mean 31th Dec 2002.

I am able to convert it with expression in the fields :
DelDate: Format(((DateValue("01/01/"+LTrim(Str(1900+Int([SDADDJ]/1000))))+[SDADDJ]-Int([SDADDJ]/1000)*1000))-1,"mm-dd-yy")

where [SDADDJ] is the date field & Deldate is the coverted field for the date.

The foramt come up is perfect when I am try to ask with (in query):
Between [Date Start] and [Date End].
The report is beutiful & correct & I ma happy it. However, later on I met several problem in calculating this date format.

Qusetion:

(1) When I want to check for today date using =Date(), it don't appear. How should I do?

(2) When I want to check the last 7 days using =< Date()-7, it also do not appear too. Where I am wrong?

(3) I would like to check from 1st day of the month till today e.g 23th with :
Between DateSerial(1,Year(Date()),Month(Date())) And Date(), it also not appear any result (zero output)


I have a doubt on the format of the date convertion, please commend what should I do for my above 3 question.

Thanks

jiajian

 

[Jon K]

Try converting the expression to date/time format with CDate():-

DelDate: CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int([SDADDJ]/1000))))+[SDADDJ]-Int([SDADDJ]/1000)*1000))-1,"mm-dd-yy"))

 

[raskew]

Think the trick here is to keep the formatting separate from the date calculation.
Without the formatting, the calculation returns a true date, e.g. from the debug
window (I simplified your formula somewhat)

Jul = 102365
x = dateserial(1900 + jul \ 1000, 1,1) + jul mod 1000 -1
? x
12/31/02

To show that it's returning a date as stored in Access' form
? cdbl(x)
37621

However, when you wrap formatting with the same equation it returns a formatted string
x =format(dateserial(1900 + jul \ 1000, 1,1) + jul mod 1000 -1, "mm-dd-yyyy")
? x
12-31-2002

When you attempt to see the stored value
? cdbl(x)
It returns a Type Mismatch error

To test this, created a small tblJul, with just one field [Jul]
jul
102365
103121
103221

Then created this query

SELECT tblJul.jul, DateSerial(1900+[jul]\1000,1,1)+[jul] Mod 1000-1 AS x
FROM tblJul;

Initially, this returns x in "Short Date" format

jul x
102365 12/31/02
103121 5/1/03
103221 8/9/03

In query design mode, highlighted the x calculated field, right-clicked to bring up
the menu, selected Properties | Format and inserted mm-dd-yyyy.

This resulted in:
jul X
102365 12-31-2002
103121 05-01-2003
103221 08-09-2003

To test, inserted the following as criteria for x (when attempting your format with
hyphens instead of slashes, Access automatically converted the hypens to slashes)

Between #1/1/03# And #8/1/03#
Resulting in
jul x
103121 05-01-2003

The entire SQL looks like:

SELECT tblJul.jul, DateSerial(1900+[jul]\1000,1,1)+[jul] Mod 1000-1 AS x
FROM tblJul
WHERE (((DateSerial(1900+[jul]\1000,1,1)+[jul] Mod 1000-1) Between #1/1/2003# And #8/1/2003#));