Cstr/CDate Query

[vbtoad]

I am working with a database that stores dates in a number format.
This, of course, presents problems when attempting to query the data
on this field. For example, numbers like 110103 are considered
greater than 070104, when in fact it is an earlier date.

So...., I created a long formula that will convert the number to a
string (Cstr), extract the mm dd yy values from the string (Mid),
format the string with /'s (Format), convert the concatenated results
back to a date (CDate), and finally, format the date to a mm/dd/yyyy
value (Format).

Here is what the monster looks like -

EffDate: Format(CDate(Format(Mid(CStr(Format([caeffd],"000000")),1,2),"00\/")
& Format(Mid(CStr(Format([caeffd],"000000")),3,2),"00\/") &
Format(Mid(CStr(Format([caeffd],"000000")),5,2),"00")),"mm/dd/yyyy")

Now, my problem is this, when I attempt to query the results based on
this new 'date' field, I still am not picking up dates that are truly
earlier than the date entered, i.e. 11/01/2003 does not get returned
when asking for dates <= 07/26/2004.

The only thing I can think of is that I should break the formula out,
so that it is not converting a number to a string and then to a date
all at one time.

Is there any one out there who may have a solution to my dilemma?

TIA


David Tripp

 

[Rick Brandt]

I am working with a database that stores dates in a number format.
This, of course, presents problems when attempting to query the data
on this field. For example, numbers like 110103 are considered
greater than 070104, when in fact it is an earlier date.

So...., I created a long formula that will convert the number to a
string (Cstr), extract the mm dd yy values from the string (Mid),
format the string with /'s (Format), convert the concatenated results
back to a date (CDate), and finally, format the date to a mm/dd/yyyy
value (Format).

Here is what the monster looks like -

EffDate: Format(CDate(Format(Mid(CStr(Format([caeffd],"000000")),1,2),"00\/")
& Format(Mid(CStr(Format([caeffd],"000000")),3,2),"00\/") &
Format(Mid(CStr(Format([caeffd],"000000")),5,2),"00")),"mm/dd/yyyy")

Get rid of the final outside Format(). That is changing your date back into a
string.

 

[John Spencer (MVP)]

Try converting with the Dateserial function to generate a real Date instead of a datestring.

EffDate: DateSerial(2000 + Right(caeffd,2),Mid(caeffd,3,2), Left(Caeffd,2))

That will blow up if caeffd is ever null, however it will only give you
erroneous data if caeffd contains bad number data such as 77777

Also, this untested function may work (again, unless you have nulls or bad dates
in caeffd)

CDate(Format(caeffd,"@@\/@@\/@@"))

 

[Van T. Dinh]

I assume that you have the "number" in "mmddyy" format.
In this case, try:

EffDate: DateSerial([caeffd] MOD 100, [caeffd]\10000,
([caeffd]\100) MOD 100)

For "valid" date, the above expression works fine but you
need to watch out for Null and "invalid" date since as
your "number" makes assumption about the century and so
deos the DateSerial() function.

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
I am working with a database that stores dates in a number format.
This, of course, presents problems when attempting to query the data
on this field. For example, numbers like 110103 are considered
greater than 070104, when in fact it is an earlier date.

So...., I created a long formula that will convert the number to a
string (Cstr), extract the mm dd yy values from the string (Mid),
format the string with /'s (Format), convert the concatenated results
back to a date (CDate), and finally, format the date to a mm/dd/yyyy
value (Format).

Here is what the monster looks like -

EffDate: Format(CDate(Format(Mid(CStr(Format ([caeffd],"000000")),1,2),"00\/")
& Format(Mid(CStr(Format([caeffd],"000000")),3,2),"00\/") &

Format(Mid(CStr(Format

([caeffd],"000000")),5,2),"00")),"mm/dd/yyyy")

Now, my problem is this, when I attempt to query the results based on
this new 'date' field, I still am not picking up dates that are truly
earlier than the date entered, i.e. 11/01/2003 does not get returned
when asking for dates <= 07/26/2004.

The only thing I can think of is that I should break the formula out,
so that it is not converting a number to a string and then to a date
all at one time.

Is there any one out there who may have a solution to my dilemma?

TIA


David Tripp
.