Date Format

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I am trying to build a query that shows all work scheduled to start prior to
today. The "Scheduled Start Date" field is a 9 character NUMBER field where
the date appears as 20090324. When I try to set the parameter <Date(), I get
an ODBC error. How can I re-format the date field or set the search parameter?
 
You need a function to use in your query.

Something like this aircode

Function NewDate(YearFirstDate as long)
Dim strYearFirst as String
strYearFirst = cstr(YearFirstDate)
NewDate = cdate(mid(strYearDate,5,2) & "/" & right(strYearDate,2) & "/"
& left(strYearDate,4))
End Function

Then in your query you use an expression on the field line like:

LessThanToday: NewDate([Scheduled Start Date])
and the criteria is
< today()

Regards

Kevin
 
Did That work for you?


KC-Mass said:
You need a function to use in your query.

Something like this aircode

Function NewDate(YearFirstDate as long)
Dim strYearFirst as String
strYearFirst = cstr(YearFirstDate)
NewDate = cdate(mid(strYearDate,5,2) & "/" & right(strYearDate,2) &
"/" & left(strYearDate,4))
End Function

Then in your query you use an expression on the field line like:

LessThanToday: NewDate([Scheduled Start Date])
and the criteria is
< today()

Regards

Kevin

Jim said:
I am trying to build a query that shows all work scheduled to start prior
to
today. The "Scheduled Start Date" field is a 9 character NUMBER field
where
the date appears as 20090324. When I try to set the parameter <Date(), I
get
an ODBC error. How can I re-format the date field or set the search
parameter?
Click to expand...
Click to expand...
 
one thing you can try if your field is a number field (and not a text
field that is composed of numbers) is:

< Val(Format(Date(),"yyyymmdd")

Your other choice is to convert the Scheduled Start Date field to an
actual date. That can get a bit tricky if you have null values or
values that can't be properly converted. The solution varies if your
field is a text string or an actual number field.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top