I am trying to get results based on a bid date that is two weeks previous and all future dates.  I created a view in MSSQL but it doesn't work in
DBISAM.  Can anymore help me rework this where clause to pull the correct info.

SELECT reportdata.BidDate, reportdata.Estimator,
      reportdata.GC, reportdata.PrjNm,
      reportdata.ProjNo, reportdata.Div,
      reportdata.BidStat,
      reportdata."BidList BidAmount" BidList_BidAmount
FROM reportdata reportdata
WHERE (reportdata.BidDate between (dateadd(day,-14,getdate())) and dateadd(year,20,getdate())))

Kenneth


> I am trying to get results based on a bid date that is two weeks previous
and all future dates.  I created a view in MSSQL but it doesn't work in
> DBISAM.  Can anymore help me rework this where clause to pull the correct
info.

How about


SELECT reportdata.BidDate, reportdata.Estimator,
      reportdata.GC, reportdata.PrjNm,
      reportdata.ProjNo, reportdata.Div,
      reportdata.BidStat,
      reportdata."BidList BidAmount" BidList_BidAmount
FROM reportdata reportdata
WHERE (reportdata.BidDate >= current_date-14)


John

When I try that, I get Expected Nul, fixed char, GUID, string, memo or Blob in
select sql statment at line 7

Kenneth,

<< When I try that, I get Expected Nul, fixed char, GUID, string, memo or
Blob in select sql statment at line 7 >>

What is the type of the BidDate column ?

--
Tim Young
Elevate Software
www.elevatesoft.com

The biddate a regular date field but it is converted to a substring to get the info in the right format

-- convert to yyyy/mm/dd format
substring(convert(char,biddate,112),1,4)+'/'+
substring(convert(char,biddate,112),5,2)+'/'+
substring(convert(char,biddate,112),7,2) as biddate2,

The biddate in the sql is calling the biddate2 above not the original biddate.  Maybe that is my problem. It can't read the string.  I will check that
and if you get any other ideas, please let me know

"Kenneth Sewell" <kennethsewell@gmail.com> wrote in message
news:1012A4A4-ACDE-42C1-8167-9CCC2B68905A@news.elevatesoft.com...
>
> The biddate in the sql is calling the biddate2 above not the original
> biddate.  Maybe that is my problem.

It is.

Robert

I changed the biddate field to the same as the sql date field with no converting and I still get the same error.  Just to troubleshoot  I removed the -
14 days and still get the error

SELECT reportdata.BidDate, reportdata.Estimator,
     reportdata.GC, reportdata.PrjNm,
     reportdata.ProjNo, reportdata.Div,
     reportdata.BidStat,
     reportdata."BidList BidAmount" BidList_BidAmount
FROM reportdata reportdata
WHERE (reportdata.BidDate >= current_date)


I am getting expected NULL, Date, Time or Timestamp expression but instead found reportdate.BidDate

Kenneth

> SELECT reportdata.BidDate, reportdata.Estimator,
>       reportdata.GC, reportdata.PrjNm,
>       reportdata.ProjNo, reportdata.Div,
>       reportdata.BidStat,
>       reportdata."BidList BidAmount" BidList_BidAmount
> FROM reportdata reportdata
> WHERE (reportdata.BidDate >= current_date)
>
>
> I am getting expected NULL, Date, Time or Timestamp expression but instead
found reportdate.BidDate


I know you said previously that reportdata.BidDate is a date type but this
error suggests that it is not.  I would check the table and ensure that the
query is being run against the database/table in which BidDate is a date
type.

John

I know that is is a date field in sql, but I am using a 3 party reporting application called GPS or EBA from Vanguard and it must be converting it
somehow??

"Kenneth Sewell" <kennethsewell@gmail.com> wrote in message
news:16ED1497-AB96-409C-9A05-F3928C1F9CE7@news.elevatesoft.com...
>I know that is is a date field in sql, but I am using a 3 party reporting
>application called GPS or EBA from Vanguard and it must be converting it
> somehow??
>

Look, it is nothing "in SQL". SQL is just a data manipulation tool. What is
the field in reality? Why not just open the table in DBSYS and look at the
data and the structure?

The error you are getting is without a doubt caused by the field in question
being NOT a date but some sort of character or string field.

Robert