Problem setting criteria

[Jak82]

Hi,

I am trying to set the criteria of a date field in a query, the field is called "flowdate" and an example data excerpt is "2011-02-23" when I use the <Date()-365 it does not return anything, even when I use <Date() it does not return anything.

If I remove the criteria all works can someone help as to why the criteria does not seem to be working,

Thanks

 

[pr2-eugin]

Is there any formatting on the field "flowdate"? I think it does.. because the default format would be dd/mm/yyyy (or mm/dd/yyyy if you are in US) the format you have is SQL Server format... So make sure that the type is date/time..

Also to take away or add days to dates, use the DateDiff/DateAdd function..

 

[Jak82]

Hi Eugin,

Thats right Ive setup the tables via a link to our SQLServer, thanks will have a look at the formatting.

 

[Jak82]

I am having problems setting the field format because it is a linked table to SQL server, I cannot change the date format on the server because another program uses the database.. any idea?

Thanks

 

[pr2-eugin]

No you do not have to change the format of the server, just in the Query use a Format.. since I cannot see your query, i believe it should be something like..

WHERE (Format([DateFieldName],"dd/mm/yyyy")<Date())

 

[Jak82]

Thanks again Eugen,

The code I have tried see below does not seem to work as I still receive the results for this year.

SELECT dbo_flowitems.name, dbo_orgs.orgtype, dbo_orgs.orgname, dbo_people.firstname, dbo_people.lastname, dbo_people.role, dbo_people.email, [Year Reminder].[Emailed?], dbo_flows.flowdate, dbo_flows.flowtype
FROM ((((dbo_orgs INNER JOIN dbo_calibrations ON dbo_orgs.id = dbo_calibrations.id) INNER JOIN dbo_people ON dbo_calibrations.id = dbo_people.id) LEFT JOIN [Year Reminder] ON dbo_people.id = [Year Reminder].ID) INNER JOIN dbo_flows ON dbo_orgs.id = dbo_flows.orgid) INNER JOIN dbo_flowitems ON dbo_flows.id = dbo_flowitems.flowid
WHERE (((dbo_flows.flowtype)="Accounts Invoice")) AND (Format([flowdate],"dd/mm/yyyy")<Date()-365);

 

[pr2-eugin]

Hmmm.. strange.. might use

CDate(Format([flowdate],"dd/mm/yyyy"))<Date()-365

 

[Jak82]

Thanks Eugen that seems to have resolved it, will have to look into the differences.

 

[pr2-eugin]

You are welcome Jak82, The difference is simple, the Date from the Server was considered a String, so we needed to cast to the correct type.. which is CDate.. Good luck..