sql syntax

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a record in my table table that is of datatype string. i'm storing a
date in that field. what i'd like to write a query to convert the field and
add 4 to it so i can compare it to variable current date. How would i be able
to do this. thanks in advance.
 
You could use the CDate() function to convert your string to a date, or
alternatively (and possibly more efficiently if the field is indexed) you
could use the Format() function to convert the current date to a string. I'm
not sure I follow the bit about adding four, though? Why do you need to add
four of anything (years? months? days?) in order to compare to the current
date? Do you perhaps mean that you want to find records where the date is
within four days of the current date, something like that?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
yes that's it. the field of type string stores a value like this 04-NO-04 and
i'd like to check if the record is 4 days older than the current date. what
do you suggest? also can i simply add a numeric(i.e. 4 in this case) to a
field of type date? thanks.
 
yes that's it. the field of type string stores a value like this 04-NO-04 and
i'd like to check if the record is 4 days older than the current date. what
do you suggest? also can i simply add a numeric(i.e. 4 in this case) to a
field of type date? thanks.
Click to expand...

That's an odd date format! Is 04-JU-O4 in June or July? Any chance you
could convert this value to a true Date/Time?

No, you cannot add 4 to a string; but there is a function named
DateAdd which can add four days to a date:

DateAdd("d", 4, [datefield])

I'm not certain how you would be able to translate your nonstandard
date code to a true Date though, I've never seen a two-letter month
code in a date. If I knew the code-to-month translation I could write
you a small VBA function to translate, though.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
See John Vinson's reply elsewhere in this thread. As John says, you'll have
to explain the code-to-month translation before we can help. Also, while it
seems likely that your format is day-month abbreviation-year, based on the
information available we could not entirely rule out the possibility that it
might be year-month abbreviation-day.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Back
Top