Hello All...

I am running queres that are linked to tables in FOXPRO, running on
SCO-UNIX.
I have a date stamp field that is encrypted by FOXPRO. After talking
to the owners for the program creating the tables, they gave me this
formula to decrypt the date stamp field.

cYear = str(asc(subs(cDTStmp,1,1)) + 1900,4)
cMonth = str(asc(subs(cDTStmp,2,1)),2)
cDay = str(asc(subs(cDTStmp,3,1)),2)
dResult = ctod(cMonth + "/" + cDay + "/" + cYear)

How can I apply this to a Between [Starting Date] And [Ending Date]
function in SQL, or within the GUI query builder.

Thanks to all ahead of time.

Jody

Jody,
I assume that the query is running from an access database with a linked
table to the data field cDTStmp.
Create a function such as the one below in an MSAccess module:

Public Function decryptFoxproDate(cDTStmp As String) As Date
Dim cYear As String
Dim cMonth As String
Dim cDay As String

cYear = CStr(Asc(Mid(cDTStmp, 1, 1)) + 1900)
cMonth = CStr(Asc(Mid(cDTStmp, 2, 1)))
cDay = CStr(Asc(Mid(cDTStmp, 3, 1)))
decryptFoxproDate = CDate(cMonth + "/" + cDay + "/" + cYear)

End Function

You can test the function in the immediate window by using for example the
following:
?decryptfoxprodate(Chr(66) & Chr(7) & chr(24))

Then in a query, use the function as you would any other function, so you
can then use in your query as an expression field:
Expr1: decryptFoxproDate([cDTStmp]), with the criteria you mentioned below.

HTH,
Ian.



"jcampbell" <jcampbell (AT) consolidatedtires (DOT) com> wrote

Mr. Russell,

Where/how do I reference the module in the query, once it is renamed to
"Module 1"?
Sorry if i am being a pain, but I have never been taught that before.

Thanks,

Jody

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

There is no need to refer to the module at all.

You use the procedure (function) name only.

And in your query, instead of :

Expr1: decryptFoxproDate([cDTStmp])

you need to put:
Expr1: decryptFoxproDate([name of the field you want to be decrypted])

And for readability, use something like FullDate: instead of Expr1:

So:
FullDate: decryptFoxproDate([name of the field you want to be decrypted])
(ignore any word wrap)

You can then use FullDate as if it were any other field on your table, in
forms or reports except that it's not a real field so doesn't store its
contents in the table.

cDTStmp is only used internally by the function. You don't use it outside
the function.

You can read about procedures in Visual Basic Help if you want.


Post back here if you still have concerns.

Regards

Peter Russell



jody Campbell previously wrote: