DateSerial and Criteria Limits

[Guest]

I am building a query using the dateserial function to identify store open
dates that are currently stored in an integer YYYYMMDD format. I can convert
that to a date using DateSerial(left(4),Mid(5,2),Right(2)). Next I add
criteria to the query of >=date()-365 to get those stores opened within the
last year. Adding this last step causes a data type mismatch error. Any
ideas what I can do to solve the problem?

With gratitude,
zt

 

[Guest]

It never occurred to me that you could use Left, Mid, and Right with a long
integer. These functions obviously are not limited to the text datatypes
that the function description describes.

I tried the following in the immediate window, and it worked OK (printed
True). Can you post your entire SQL string?

?(Dateserial(left(20070806,4), mid(20070806,5,2), right(20070806,2)) >=
Date() - 365)


Dale

 

[Guest]

Hi,
Couple of points
1. You need to include the field name in the DateSerial
DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2))

2. If the field doesn't have 8 digits or it null it will create an error and
then you'll get a type mismatch. Try

NewDate: IIf(Len([FieldName] &
"")=8,DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2)),Null)

 

[Guest]

Ofer and Dale -

Thanks for the help. To your points, I did include the field name, just
omitted it from rewriting the formula in my earlier post. Also, all entries
are 8 digits long. Per Dale's request, here is the sequel string for an
abbreviated version of the query... (STSTOR is Store number field, STOPEN is
date open field in 8 digit integer format)

SELECT POSDBF_SLSTRPF.STSTOR, POSDBF_SLSTRPF.STOPEN,
DateSerial(Left([stopen],4),Mid([stopen],5,2),Right([stopen],2)) AS [Date
Open]
FROM POSDBF_SLSTRPF
GROUP BY POSDBF_SLSTRPF.STSTOR, POSDBF_SLSTRPF.STOPEN,
DateSerial(Left([stopen],4),Mid([stopen],5,2),Right([stopen],2))
HAVING
(((DateSerial(Left([stopen],4),Mid([stopen],5,2),Right([stopen],2)))>=Date()-365));

Kol tuv,
zt

Hi,
Couple of points
1. You need to include the field name in the DateSerial
DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2))

2. If the field doesn't have 8 digits or it null it will create an error and
then you'll get a type mismatch. Try

NewDate: IIf(Len([FieldName] &
"")=8,DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2)),Null)


--
Good Luck
BS"D

I am building a query using the dateserial function to identify store open
dates that are currently stored in an integer YYYYMMDD format. I can convert
that to a date using DateSerial(left(4),Mid(5,2),Right(2)). Next I add
criteria to the query of >=date()-365 to get those stores opened within the
last year. Adding this last step causes a data type mismatch error. Any
ideas what I can do to solve the problem?

With gratitude,
zt

 

[Dale Fye]

Try:


HAVING DateSerial(Left([stopen],4),Mid([stopen],5,2),Right([stopen],2)) >=
dateadd("yyyy", -1, date())

Personally, I think I would make that a WHERE clause (prior to the Group By)
to exclude the opening dates that are outside that range before Access
processes the GroupBy and Aggregation.

Another way to do it would be:

WHERE [stopen] >= clng(format(dateadd("yyyy", -1, date()), "yyyymmdd"))

Dale

Ofer and Dale -

Thanks for the help. To your points, I did include the field name, just
omitted it from rewriting the formula in my earlier post. Also, all
entries
are 8 digits long. Per Dale's request, here is the sequel string for an
abbreviated version of the query... (STSTOR is Store number field, STOPEN
is
date open field in 8 digit integer format)

SELECT POSDBF_SLSTRPF.STSTOR, POSDBF_SLSTRPF.STOPEN,
DateSerial(Left([stopen],4),Mid([stopen],5,2),Right([stopen],2)) AS [Date
Open]
FROM POSDBF_SLSTRPF
GROUP BY POSDBF_SLSTRPF.STSTOR, POSDBF_SLSTRPF.STOPEN,
DateSerial(Left([stopen],4),Mid([stopen],5,2),Right([stopen],2))
HAVING
(((DateSerial(Left([stopen],4),Mid([stopen],5,2),Right([stopen],2)))>=Date()-365));

Kol tuv,
zt

Hi,
Couple of points
1. You need to include the field name in the DateSerial
DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2))

2. If the field doesn't have 8 digits or it null it will create an error
and
then you'll get a type mismatch. Try

NewDate: IIf(Len([FieldName] &
"")=8,DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2)),Null)


--
Good Luck
BS"D

I am building a query using the dateserial function to identify store
open
dates that are currently stored in an integer YYYYMMDD format. I can
convert
that to a date using DateSerial(left(4),Mid(5,2),Right(2)). Next I add
criteria to the query of >=date()-365 to get those stores opened within
the
last year. Adding this last step causes a data type mismatch error.
Any
ideas what I can do to solve the problem?

With gratitude,
zt

 

[raskew via AccessMonster.com]

Access stores dates as a double precision, floating type number which
represents the number of days since 31 Dec 1899. Example: today is 6 Aug 07.
It will be stored as 39300. Storing a date in a text field is going to
deprive you of many of Access' date related function.

Take a look at this: http://support.microsoft.com/kb/q130514/ for a more
extensive discussion

HTH - Bob

Try:

HAVING DateSerial(Left([stopen],4),Mid([stopen],5,2),Right([stopen],2)) >=
dateadd("yyyy", -1, date())

Personally, I think I would make that a WHERE clause (prior to the Group By)
to exclude the opening dates that are outside that range before Access
processes the GroupBy and Aggregation.

Another way to do it would be:

WHERE [stopen] >= clng(format(dateadd("yyyy", -1, date()), "yyyymmdd"))

Dale

Ofer and Dale -

[quoted text clipped - 43 lines]

 

[Rick Brandt]

Access stores dates as a double precision, floating type number which
represents the number of days since 31 Dec 1899. Example: today is 6
Aug 07. It will be stored as 39300. Storing a date in a text field
is going to deprive you of many of Access' date related function.

Take a look at this: http://support.microsoft.com/kb/q130514/ for a
more extensive discussion

<quibble>
30 Dec 1899
</quibble>

 

[raskew via AccessMonster.com]

Good catch!

Thanks - Bob

Access stores dates as a double precision, floating type number which
represents the number of days since 31 Dec 1899. Example: today is 6

[quoted text clipped - 3 lines]

Take a look at this: http://support.microsoft.com/kb/q130514/ for a
more extensive discussion

<quibble>
30 Dec 1899
</quibble>

 

[John W. Vinson]

<quibble>
30 Dec 1899
</quibble>

Geek bonus points for all who can explain *why*!

John W. Vinson [MVP]

 

[Rob Parker]

I'll have a stab at this:

The date system in Excel used/uses a system based on 31-Dec-1899 as 0. Day
1 was 1 Jan 1900. Unfortunately, Microsoft failed to realize that 1900 was
not a leap year, so did not contain a 29 Feb 1900 day. I guess that, when
they applied the same system in Access, they decided to adjust the baseline
so that Excel and Access dates after 1 Mar 1900 would be the same - image
the chaos if they were different!

Rob

 

[Rick Brandt]

I'll have a stab at this:

The date system in Excel used/uses a system based on 31-Dec-1899 as
0. Day 1 was 1 Jan 1900. Unfortunately, Microsoft failed to realize
that 1900 was not a leap year, so did not contain a 29 Feb 1900 day. I guess
that, when they applied the same system in Access, they
decided to adjust the baseline so that Excel and Access dates after 1
Mar 1900 would be the same - image the chaos if they were different!

And further...it was actually Lotus 123 that made this error. That program was
so entrenched in corporate use when MS came out with their products that it was
deemed better to duplicate the same error for the sake of
consistency/compatibility.

(at least that is what I have heard)

 

[Rob Parker]

Wow!!!

Once-upon-a-time, I actually used Lotus 123 - for evaluation, never really
in anger.

At that time of my life I was a rocket scientist rather than a computer geek
;-)

<set dinosaur.status = true>

Never noticed that - but I suspect that's not very surprising!!!

Rob

 

[John W. Vinson]

Once-upon-a-time, I actually used Lotus 123 - for evaluation, never really
in anger.

At that time of my life I was a rocket scientist rather than a computer geek
;-)

<set dinosaur.status = true>

Never noticed that - but I suspect that's not very surprising!!!

Yep. It's as simple as 1-2-3!

John W. Vinson [MVP]

 

[Jamie Collins]

And further...it was actually Lotus 123 that made this error. That program was
so entrenched in corporate use when MS came out with their products that it was
deemed better to duplicate the same error for the sake of
consistency/compatibility.

(at least that is what I have heard)

See:

My First BillG Review
by Joel Spolsky
http://www.joelonsoftware.com/items/2006/06/16.html

Jamie.

--