vb and sql

juliya_sh · July 9th, 2003, 09:33 AM

hello,
i need get the field-date in specific format by sql in the vb
i know it something as dateformat but i didn't know
how i do it.
can somebody help ?
thanks

SerranoG · July 9th, 2003, 09:53 AM

Quote:

quote:Originally posted by juliya_sh
"SELECT Count(id) AS SumID " & _
     "from [Table] " & _
     "WHERE ( ((" & _
     "[table].[date1]) " & _
     "Between #" + Format(i, "dd/mm/yyyy") + "# And #" + Format (j, "dd/mm/yyyy") + "#) ) "

What exactly are i and j? Also, change the plus signs (+) to ampersands (&). You're concatenating strings, not adding numbers.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Steven · July 9th, 2003, 06:53 PM

I don't think i and j really have to be formatted for this, but, if you insist:

DatePart('d',i) & "/" & DatePart('m',i) & "/" & DatePart('yyyy',i)

likewise with j

And as a note, + can be used to concatenate strings, it's just by convention that most peole use &, which makes it less ambiguous.

+ will still work, and I know some people (well - only one to be honest) who always use + instead of &.

Oh yeah - and I'm assuming that i and j are already dates, not strings. which is why formatting wouldn't be necessary - unless of course i and j were dates with times, and you wanted to roll them both back to midnight.
If i and j are strings, with a format like "mm/dd/yyyy", then you'd have to use left, mid, right and len functions, like:

Mid(Field,4,2)& "/" & Left(Field,2) & "/" & Right(Field,4)

You'd run into a problem if the string didn't necessarily have all the digits filled, for example, 1/1/2001
in which case you'd have to use the InStr function to find the "/" and select the part of the string based on its location

I'm assuming this reply answers most scenarios.

Steven

There are 10 kinds of people in the world - those who understand binary - and those with friends