Date fields

[sdc1234]

Greetings, I am trying to run a query that will give me <=Now()-7 on my end dates column, Is there a way to get this done without converting back to Julien Dates, any help would be appreciated



v/r
Novice

[June7]

Convert from what? Why would anything need to be converted?

[sdc1234]

I don't know but I am trying to separate courses taken by less than today's date -7 days on my start date column and then extract the rest of the courses to a different table < today's date which I have already extracted

[sdc1234]

I'm trying to use regular date fields ex.(11/01/2014)

[June7]

The criteria you posted should work on a date/time field. What happens - error message, wrong results, nothing? Post the query SQL statement.

[sdc1234]

Give me a few hrs I have to go to some meetings when I get back I will post the 2 queries 1 extracts the future classes and the other extracts the previous (it doesn't) it no error msg or anything

[sdc1234]

SELECT [03b-Initialize NETC Updates].SID, [03b-Initialize NETC Updates].PID, [03b-Initialize NETC Updates].Cadre, [03b-Initialize NETC Updates].[Position Code], [03b-Initialize NETC Updates].LName, [03b-Initialize NETC Updates].FName, [03b-Initialize NETC Updates].MInital, [03b-Initialize NETC Updates].Code1, [03b-Initialize NETC Updates].Code2, [03b-Initialize NETC Updates].[Course Code], [03b-Initialize NETC Updates].[Course Title], [03b-Initialize NETC Updates].[Start Date], [03b-Initialize NETC Updates].[End Date], [03b-Initialize NETC Updates].[Organization Name], [03b-Initialize NETC Updates].[Organization City], [03b-Initialize NETC Updates].State, [03b-Initialize NETC Updates].Zip, [03b-Initialize NETC Updates].Pass, [03b-Initialize NETC Updates].Jurisdiction, [03b-Initialize NETC Updates].[Business Type], [03b-Initialize NETC Updates].STATUS, [03b-Initialize NETC Updates].TYPE, [03b-Initialize NETC Updates].SALARY, [03b-Initialize NETC Updates]![SID]+[03b-Initialize NETC Updates]![PID]+[03b-Initialize NETC Updates]![LName]+[03b-Initialize NETC Updates]![Course Code]+Format([03b-Initialize NETC Updates]![Start Date],"mm/dd/yyyy")+Format([03b-Initialize NETC Updates]![End Date],"mm/dd/yyyy") AS [select]
FROM [03b-Initialize NETC Updates]
WHERE (((IIf([03b-Initialize NETC Updates]![End Date]<=Now()-7,1,0))=1))
GROUP BY [03b-Initialize NETC Updates].SID, [03b-Initialize NETC Updates].PID, [03b-Initialize NETC Updates].Cadre, [03b-Initialize NETC Updates].[Position Code], [03b-Initialize NETC Updates].LName, [03b-Initialize NETC Updates].FName, [03b-Initialize NETC Updates].MInital, [03b-Initialize NETC Updates].Code1, [03b-Initialize NETC Updates].Code2, [03b-Initialize NETC Updates].[Course Code], [03b-Initialize NETC Updates].[Course Title], [03b-Initialize NETC Updates].[Start Date], [03b-Initialize NETC Updates].[End Date], [03b-Initialize NETC Updates].[Organization Name], [03b-Initialize NETC Updates].[Organization City], [03b-Initialize NETC Updates].State, [03b-Initialize NETC Updates].Zip, [03b-Initialize NETC Updates].Pass, [03b-Initialize NETC Updates].Jurisdiction, [03b-Initialize NETC Updates].[Business Type], [03b-Initialize NETC Updates].STATUS, [03b-Initialize NETC Updates].TYPE, [03b-Initialize NETC Updates].SALARY, [03b-Initialize NETC Updates]![SID]+[03b-Initialize NETC Updates]![PID]+[03b-Initialize NETC Updates]![LName]+[03b-Initialize NETC Updates]![Course Code]+Format([03b-Initialize NETC Updates]![Start Date],"mm/dd/yyyy")+Format([03b-Initialize NETC Updates]![End Date],"mm/dd/yyyy");

[sdc1234]

thats the one that will not work

[June7]

Format function results in a string value so yes, think you need to convert back to date/time type. Can wrap the expression into CDate().

[sdc1234]

can you give me an example of how I would do that?

[June7]

Never mind, I misread the SQL. The date is not formatted for the WHERE condition.

WHERE (((IIf([03b-Initialize NETC Updates]![End Date]<=Now()-7,1,0))=1))

That should work but why bother with the IIf()?

WHERE [03b-Initialize NETC Updates]![End Date]<=Now()-7

Don't see need for the GROUP BY - there are no aggregate calcs.

[sdc1234]

Never mind, I misread the SQL. The date is not formatted for the WHERE condition.

WHERE (((IIf([03b-Initialize NETC Updates]![End Date]<=Now()-7,1,0))=1))

That should work but why bother with the IIf()?

WHERE [03b-Initialize NETC Updates]![End Date]<=Now()-7

Don't see need for the GROUP BY - there are no aggregate calcs.

Thanks, That worked...
where can I post an excel VBA ? that I have....

v/r
novice

[June7]

You want to post what?

You can start a thread and post whatever you want. No guarantee you will get an answer.